Nouvelles hebdomadaires de PostgreSQL - 11 janvier 2009

Les programmes d'installation pour les instantanés de développement de la 8.4 sont disponibles. N'hésitez pas et faites un tour à bord de la future 8.4 ! http://www.postgresql.org/download/snapshots

Plus que 2 semaines pour proposer des conférences pour les pgCon ! www.pgcon.org

L'astuce de la semaine

En ligne de commande dans psql, "\x" vous permet de basculer l'affichage des résultats en mode "étendu". C'est très utile pour les ensembles contenant de longues lignes.

(lien vers l'article original)

Les nouveautés des produits dérivés

• MyJSQLView 3.01 : http://myjsqlview.sourceforge.net/

Offres d'emplois autour de PostgreSQL en janvier

• http://archives.postgresql.org/pgsql-jobs/2009-01/threads.php

PostgreSQL Local

• Jeff Davis tiendra une conférence devant le PUG de San Francisco le mardi 13 janvier au Blurb. RSVP ci-dessous pour les invitations et la nourriture : http://postgresql.meetup.com/1/calendar/9351228/
• Richard Broersma fait le lien entre les volontaires pour la rencontre de SCALE (cf. plus bas). Contact à richard POINT broersma CHEZ gmail POINT com.
• Les groupes moscovites de PostgreSQL et MySQL tiendront une table ronde commune le 22 janvier. Détails et inscriptions ci-dessous : http://forum.postgresqlrussia.org/viewtopic.php?f=6&t=96
• Le SYDPUG se réunira le 3 février 2009 à 18h30. Suivez le lien pour les détails : http://archives.postgresql.org/sydpug/
• Beaucoup des suspects habituels se retrouveront au FOSDEM les 7 & 8 février à Bruxelles, Belgique : http://wiki.postgresql.eu/wiki/FOSDEM_2009
• PostgreSQL sera au "SCALE" du 20 au 22 février à Los Angeles : http://www.socallinuxexpo.org/
• Conférence d'Andreas 'ads' Scherbaum lors du "Perl Workshop 2009" le 25 février à Francfort-sur-le-Main, en Allemagne : http://www.perl-workshop.de/talks/151/view
• "PostgreSQL Conference, U.S." organise un PgDay lors de la "LinuxFest Northwest" (25 & 26 avril). L'appel aux conférences est visible à l'adresse : http://www.postgresqlconference.org/
• La PGCon 2009 se tiendra à l'Université d'Ottawa les 21 et 22 mai 2009. Elle sera précédée de deux jours de tutoriels les 19 & 20 mai : http://www.pgcon.org/2009/papers.php
• La "PGCon Brazil" prendra place à l'Unicamp de Campinas (État de São Paulo) les 23 & 24 octobre 2009.

PostgreSQL dans les média

• Planet PostgreSQL : http://planet.postgresql.org/

PostgreSQL Weekly News / les nouvelles hebdomadaires vous sont offertes cette semaine par David Fetter, Josh Berkus et Dave Page. La traduction en est assurée par l'équipe PostgreSQLFr.

Proposez vos articles ou annonces avant dimanche 15:00 (heure du Pacifique). Merci de les envoyer en anglais à david (a) fetter.org, en allemand à pwn (a) pgug.de, en italien à pwn (a) itpug.org.

Correctifs appliqués

Peter Eisentraut a commité :

• Allow out-of-tree builds on mingw and cygwin. Author: Richard Evans.
• Fix for cross-compilation between mingw32 and something else. The choice of pwd vs. pwd -W is correctly a function of the build system, not the host system.
• When cross-compiling, allow and require an external zic program to be used when --with-system-tzdata is not used. Initial patch by Richard Evans
• Produce a full version string for Sun Studio. From Zdenek Kotala.
• In pgsql/doc/src/sgml/ddl.sgml, add note that not all SQL commands support ONLY in the same way.
• Rewrite update-po target, so that it works less like a shell script and more like a makefile with real dependencies. Instead of overwriting the old po file, write the new one to .po.new. This is less annoying and integrates better with the NLS web site. Also, we can now merge languages that don't have a po file yet, by merging against all other po files of that language, to pick up recurring translations automatically. This previously only worked when a po file already existed.
• In pgsql/src/nls-global.mk, more portable use of "find".
• Change chapter titles thus: "III. Server Administration 15. Installation from Source Code 16. Installation from Source Code on Windows 17. Server Setup and Operation" to give users of binary installations a better idea where to start reading. Suggested by Nikolay Samokhvalov
• Remove fairly useless mixed-case identifier test that causes locale dependencies.
• Make tests pass with or without locale.

Tom Lane a commité :

• In pgsql/src/backend/utils/misc/guc.c, fix define_custom_variable to preserve any sourcefile/sourceline information present in the placeholder. Noted while testing pg_stat_statements.
• Add EmitWarningsOnPlaceholders calls to contrib modules that are likely to get listed in custom_variable_classes.
• Quiet repeated "loaded library" messages from child backends that are just re-loading a library already loaded into the postmaster. ITAGAKI Takahiro
• Add a --role option to pg_dump, pg_dumpall, and pg_restore. This allows performing dumps and restores in accordance with a security policy that forbids logging in directly as superuser, but instead specifies that you should log into an admin account and then SET ROLE to the superuser. In passing, clean up some ugly and mostly-broken code for quoting shell arguments in pg_dumpall. Benedek Laszlo, with some help from Tom Lane
• In pgsql/src/backend/optimizer/util/clauses.c, fix an oversight in the function-default-arguments patch: after adding some default expressions to a function call, eval_const_expressions must recurse on those expressions. Else they don't get simplified, and in particular we fail to insert additional default arguments if any functions needing defaults are in there. Per report from Rushabh Lathia.
• In pgsql/src/backend/commands/functioncmds.c, add some comments about why function parameter default expressions are restricted.
• Doesn't seem like a good idea to be doing AC_CHECK_SIZEOF(void *) so much earlier than all the other sizeof checks, and it certainly fails to follow the order suggested at the file head. Rearrange.
• In pgsql/src/backend/utils/adt/pg_lzcompress.c, revert the default toast compression strategy to the former behavior where there's no limit on the size of datum we'll try to compress. Other 8.4 tweaks to the behavior remain in place. Per discussion.
• Change a couple of ill-advised uses of INFO elog level to WARNINGs; in particular this allows EmitWarningsOnPlaceholders messages to show up in the postmaster log by default. Update elog.h comment to make it clearer what INFO is for, and fix one example in the SGML docs that was misusing it. Per my gripe of yesterday.
• Remove references to pgsql-ports and pgsql-patches mailing lists from various documentation, since those lists are now dead/deprecated. Point to pgsql-bugs and/or pgsql-hackers as appropriate.
• In pgsql/src/backend/commands/analyze.c, clarify a confusing comment about MCVs vs histogram entries. Per Nathan Boley.
• Fix executor/spi.h to follow our usual conventions for include files, ie, not include postgres.h nor anything else it doesn't directly need. Add #includes to calling files as needed to compensate. Per my proposal of yesterday. This should be noted as a source code change in the 8.4 release notes, since it's likely to require changes in add-on modules.
• Insert conditional SPI_push/SPI_pop calls into InputFunctionCall, OutputFunctionCall, and friends. This allows SPI-using functions to invoke datatype I/O without concern for the possibility that a SPI-using function will be called (which could be either the I/O function itself, or a function used in a domain check constraint). It's a tad ugly, but not nearly as ugly as what'd be needed to make this work via retail insertion of push/pop operations in all the PLs. This reverts my patch of 2007-01-30 that inserted some retail SPI_push/pop calls into plpgsql; that approach only fixed plpgsql, and not any other PLs. But the other PLs have the issue too, as illustrated by a recent gripe from Christian Schroeder. Back-patch to 8.2, which is as far back as this solution will work. It's also as far back as we need to worry about the domain-constraint case, since earlier versions did not attempt to check domain constraints within datatype input. I'm not aware of any old I/O functions that use SPI themselves, so this should be sufficient for a back-patch.
• Create a third option named "partition" for constraint_exclusion, and make it the default. This setting enables constraint exclusion checks only for appendrel members (ie, inheritance children and UNION ALL arms), which are the cases in which constraint exclusion is most likely to be useful. Avoiding the overhead for simple queries that are unlikely to benefit should bring the cost down to the point where this is a reasonable default setting. Per today's discussion.
• In pgsql/src/backend/parser/analyze.c, defend against null input in analyze_requires_snapshot(), per report from Rushabh Lathia.
• Arrange for function default arguments to be processed properly in expressions that are set up for execution with ExecPrepareExpr rather than going through the full planner process. By introducing an explicit notion of "expression planning", this patch also lays a bit of groundwork for maybe someday allowing sub-selects in standalone expressions.
• In pgsql/src/interfaces/libpq/fe-protocol3.c, fix libpq so that it reports PGRES_EMPTY_QUERY not PGRES_COMMAND_OK when an empty query string is passed to PQexecParams and related functions. Its handling of the NoData response to Describe messages was subtly incorrect. Per my report of yesterday. Although I consider this a bug, it's a behavioral change that might affect applications, so not back-patched. In passing fix a second issue in the same code: it didn't react well to an out-of-memory failure while trying to make the PGresult object.
• In pgsql/doc/src/sgml/ref/psql-ref.sgml, a further attempt at clarifying the \distv business.
• Revise the TIDBitmap API to support multiple concurrent iterations over a bitmap. This is extracted from Greg Stark's posix_fadvise patch; it seems worth committing separately, since it's potentially useful independently of posix_fadvise.
• Re-enable the old code in xlog.c that tried to use posix_fadvise(), so that we can get some buildfarm feedback about whether that function is still problematic. (Note that the planned async-preread patch will not really prove anything one way or the other in buildfarm testing, since it will be inactive with default GUC settings.)
• Implement prefetching via posix_fadvise() for bitmap index scans. A new GUC variable effective_io_concurrency controls how many concurrent block prefetch requests will be issued. (The best way to handle this for plain index scans is still under debate, so that part is not applied yet --- tgl)

Bruce Momjian a commité :

• Update Japanese FAQ. Jun Kuwamura
• In pgsql/src/timezone/Makefile, fix new timezone cross-compile rule to avoid a bug in gmake 3.78.1; document change.
• In pgsql/src/backend/access/common/reloptions.c, suppress compiler warning.
• Update SELECT version() to show whether it is a 32 or 64-bit backend binary.
• In pgsql/src/backend/access/common/reloptions.c, suppress compiler warning in a different way, per Alvaro Herrera.
• In pgsql/src/bin/pg_dump/pg_restore.c, update pg_restore --help text to match the SGML documentation.
• Make pg_dump and pg_dumpall --clean options match the SGML docs, for consistency.
• In pgsql/doc/src/sgml/installation.sgml, document that Cygwin does not support OpenSSL.
• This makes all the \dX commands (most importantly to most: \df) work like \dt does, in that it requires a \dXS to see system items. Greg Sabino Mullane
• Add documentation for new \d*S* patch, and clean up some of the docs. Fix \do and trigger display for the patch too.
• In pgsql/src/bin/psql/help.c, improve \z psql \? help display.
• In pgsql/src/bin/psql/help.c, add spaces around psql \d* columns, per idea from Joshua Drake.
• In pgsql/src/bin/psql/help.c, remove duplicate \dd psql help mention.
• Update MinGW so it handles fseeko() similar to Unix.
• In pgsql/src/backend/utils/error/elog.c, make the log output of 'vxid' between csvlog and stderr/syslog consistent. Currently, in csvlog, vxid of an auxiliary process isn't displayed. On the other hand, in stderr/syslog, invalid vxid (-1/0) of that is displayed. Fujii Masao
• In pgsql/doc/src/sgml/ref/psql-ref.sgml, break out \distv into four separate lines in the psql documentation, for clarity.
• In pgsql/src/backend/tcop/postgres.c, update comment associated with 'debug_query_string'.
• In pgsql/src/backend/utils/adt/misc.c, add comment that it is difficult to access the more accurate 'query_string' from current_query().
• Have current_query() use ActivePortal->sourceText rather than debug_query_string; this allows current_query() to be more accurate; docs updated; per idea from Tom Lane.
• In pgsql/src/backend/parser/gram.y, add comment about why BETWEEN uses operator strings and not opclasses, with URL pointing to email discussion.
• Revert current_query() change to use debug_query_string again; add comment.
• In pgsql/doc/src/sgml/func.sgml, document current_query() as being the _client_ query.
• In pgsql/doc/src/sgml/func.sgml, document that txid_visible_in_snapshot() cannot be used with subtransaction ids.
• In pgsql/src/test/regress/pg_regress.c, fix memory leak for file name if expect file name contains a dot, per report from dvice_null AT yahoo DOT com.
• Update release notes for 8.3.5, 8.2.11, and 8.1.15 to mention the need to reindex GiST indexes to read: "If you were running a previous 8.X.X release, REINDEX all GiST indexes after the upgrade."
• In pgsql/doc/src/sgml/catalogs.sgml, remove tabs from SGML docs.
• In pgsql/doc/src/sgml/README.links, add file to explain SGML linking options, for use by SGML document writers.
• In pgsql/doc/src/sgml/README.links, add guidelines section.
• In pgsql/doc/src/sgml/README.links, update link guidelines.
• In pgsql/doc/src/sgml/libpq.sgml, modify libpq environment variable documentation to point to the relevant connection options documentation section, per idea from Magnus.

Alvaro Herrera a commité :

• Change the reloptions machinery to use a table-based parser, and provide a more complete framework for writing custom option processing routines by user-defined access methods. Catalog version bumped due to the general API changes, which are going to affect user-defined "amoptions" routines.
• Fix string reloption handling, per KaiGai Kohei.
• A couple further reloptions improvements, per KaiGai Kohei: add a validation function to the string type and add a couple of macros for string handling. In passing, fix an off-by-one bug of mine.

Tatsuo Ishii a commité :

• Remove outdated Japanese README files per discussion.

Heikki Linnakangas a commité :

• In pgsql/src/backend/commands/vacuumlazy.c, fix logic in lazy vacuum to decide if it's worth trying to truncate the heap. If the table was smaller than REL_TRUNCATE_FRACTION (= 16) pages, we always tried to acquire AccessExclusiveLock on it even if there were no empty pages at the end. Report by Simon Riggs. Back-patch all the way to 7.4.

Magnus Hagander a commité :

• In pgsql/src/backend/utils/adt/pg_locale.c, code review of strftime patch, per comments from Tom Lane. Use correct buffer size MAX_L10N_DATA, and use strlcpy instead of StrNCpy.
• Modified Files: --------------
• In pgsql/src/tools/msvc/Solution.pm, make the MSVC build output "32-bit" at the end of the version string, the same way the unix build now does.
• In pgsql/src/interfaces/libpq/fe-secure.c, don't require pqGetHomeDirectory to succeed if the user has specified hardcoded paths for SSL rootcert/crl/clientcert/key. As noted by Andrew Chernow.
• In pgsql/doc/src/sgml/config.sgml, add appropriate text for SIGHUP parameters instead of just removing it like previous patch did. Per note from Tom Lane.
• Allow krb_realm (krb5, gssapi and sspi) and krb_server_hostname (krb5 only) authentication options to be set in pg_hba.conf on a per-line basis, to override the defaults set in postgresql.conf.
• Add hba parameter include_realm to krb5, gss and sspi authentication, used to pass the full username@realm string to the authentication instead of just the username. This makes it possible to use pg_ident.conf to authenticate users from multiple realms as different database users.
• Make krb_realm and krb_server_hostname be pg_hba options only, and remove their GUCs. In passing, noted that the pg_hba options for krb5 authentication weren't listed at all - so add this.
• In pgsql/src/backend/utils/adt/pg_locale.c, fix strftime usage on Win32 when trying to fetch the locale-aware parts of a time string so it properly handles different encodings. Original patch by Hiroshi Saito, heavily reworked by me and ITAGAKI Takahiro.

Andrew Dunstan a commité :

• In pgsql/src/include/pg_config.h.win32, define HAVE_FSEEKO for MSVC.
• In pgsql/doc/src/sgml/catalogs.sgml, document values for pg_constraint confupdtype, confdeltype and confmatchtype columns.

Correctifs rejetés (à ce jour)

• Pas de déception cette semaine :-)

Correctifs en attente

• KaiGai Kohei sent in two more revisions of his SE-PostgreSQL patches.
• Martin Pihlak sent in another revision of his SQL/MED compatible connection manager code including functions which list foreign connection options as a string and as a result set.
• Nathan Boley sent in a fix where in heavy-tailed distributions, it's possible for ANALYZE to duplicate histogram boundaries by limiting mcvs to be twice the statistics target and when it finds values frequently enough, reduces the number of histogram buckets.
• Bryce Cutt sent in another revision of the patch to improve performance of multi-batch hash join for skewed data sets.
• KaiGai Kohei sent in a fix atop Stephen Frost's column-level privileges patch which makes sure only available columns are used in JOINs, etc.
• Robert Haas sent in a cleaned-up version of the patch to improve performance of multi-batch hash join for skewed data sets.
• Heikki Linnakangas sent in a patch to unlock index blocks atop Simon Riggs's hot standby patch.
• Fujii Masao sent in another revision of the signal handling patch which is infrastructure for synchronous replication.
• ITAGAKI Takahiro sent in a patch to fix LC_TIME on Windows in Japanese.
• David Wheeler sent in a patch which adds a mention of CITEXT to the FAQ that discusses case-insensitive comparisons.
• Hitoshi Harada sent in an example of a user-defined windowing function.
• Stephen Frost sent in four more revisions of his column-level permissions patch.
• Heikki Linnakangas sent in a patch to help the hot standby patch by refactoring things so that redo-functions are responsible for calling RestoreBkpBlocks.
• Andrew Dunstan sent in another revision of his parallel restore patch.
• Magnus Hagander sent in two approaches to fixing a kerberos problem, the first, removing support for getting the username, and the second, to suppress the error message until later.
• Hiroshi Inoue sent in another revision of a patch to fix lc_messages on Windows for Japanese language.
• Peter Eisentraut sent in a patch which adds ONLY support to TRUNCATE and LOCK, making the default behavior recursive on inherited tables.
• KaiGai Kohei sent in another revision of his SE-PostgreSQL patches.
• Alvaro Herrera sent in a patch which fixes the reloptions code.
• Jeff Davis sent in an updated version of Kenneth Marshall's patch which updates the hash functions.
• Bernd Helmle sent in two more revisions of his updateable views patch.
• Heikki Linnakangas sent in a patch which removes the slotids from the hot standby patch.
• Fujii Masao sent in another revision of the synchronous replication patch.
• Robert Haas sent in another revision of the POSIX fadvise patch.
• Jeff Davis sent in an updated version of the B-Tree emulation for GIN patch.