<p><strong>Nouvelles hebdomadaires de PostgreSQL - 9 mai 2009</strong></p> <p>Nouveau sondage&nbsp;: quelle fonctionnalit&eacute; vous attire le plus dans la 8.4&nbsp;? <a href="http://www.postgresql.org/community" target="_blank">http://www.postgresql.org/community</a></p> <p>Le site web de PostgreSQL a besoin d'une refonte, et 2000&nbsp;$ ont &eacute;t&eacute; allou&eacute;s &agrave; cela. Usez de vos talents pour acqu&eacute;rir gloire et (petite) fortune&nbsp;: <a target="_blank" href="http://wiki.postgresql.org/wiki/Website_Overhaul_Requirements">http://wiki.postgresql.org/wiki/Website_Overhaul_Requirements</a></p> <p>Le PGDay de Junin / Buenos Aires aura lieu le 6 juin &agrave; l'Unnoba&nbsp;: <a target="_blank" href="http://www.arpug.com.ar/trac/wiki/PgDayUnnoba">http://www.arpug.com.ar/trac/wiki/PgDayUnnoba</a></p> <p>Le PGDay.EU 2009 est programm&eacute; pour les 6 &amp; 7 novembre 2009 &agrave; Telecom ParisTech (ndt: ENST ?)&nbsp;: <a target="_blank" href="http://www.pgday.eu/">http://www.pgday.eu/</a></p> <p><strong>Les nouveaut&eacute;s des produits d&eacute;riv&eacute;s</strong></p> <ul> <li>check_postgres 2.8.0, un module Nagios pour la surveillance de PostgreSQL&nbsp;: <a target="_blank" href="http://bucardo.org/check_postgres/">http://bucardo.org/check_postgres/</a></li> <li>Jopr 2.2, une application de gestion et de surveillance serveur&nbsp;: <a target="_blank" href="http://www.jboss.org/jopr/">http://www.jboss.org/jopr/</a></li> <li>Le "PostgreSQL RPM Building Project" a publi&eacute; une nouvelle version de "The PostgreSQL LiveCD"&nbsp;: <a target="_blank" href="http://www.pglivecd.org">http://www.pglivecd.org</a></li> <li>Npgsql2 2.0.5, un gestionnaire .NET de donn&eacute;es pour PostgreSQL&nbsp;: <a target="_blank" href="http://www.npgsql.org">http://www.npgsql.org</a></li> <li>pgAdmin 1.10 beta 3, une interface graphique de gestion pour PostgreSQL&nbsp;: <a target="_blank" href="http://www.postgresql.org/ftp/pgadmin3/release/v1.10.0-beta3/">http://www.postgresql.org/ftp/pgadmin3/release/v1.10.0-beta3/</a></li> <li>pgpool-II 2.2.2, un gestionnaire de connexions et bien d'avantage&nbsp;: <a target="_blank" href="http://pgfoundry.org/projects/pgpool/">http://pgfoundry.org/projects/pgpool/</a></li> <li>La "United States PostgreSQL Association" est &agrave; pr&eacute;sent qualifi&eacute;e 501(c)(3) non-lucrative. F&eacute;licitations&nbsp;! <a target="_blank" href="http://www.postgresql.us/">http://www.postgresql.us/</a></li> <li>PostGIS 1.3.6, un syst&egrave;me g&eacute;ospatial pour PostgreSQL&nbsp;: <a target="_blank" href="http://postgis.refractions.net/">http://postgis.refractions.net/</a></li> <li>Slony 1.2.16 et 2.0.2, un syst&egrave;me de r&eacute;plication ma&icirc;tre-esclave en cascade&nbsp;: <a target="_blank" href="http://slony.info/">http://slony.info/</a></li> </ul> <p><strong>La fonctionnalit&eacute; 8.4 de la semaine</strong></p> <p>Les fonctions de fen&ecirc;trage. Ces requ&ecirc;tes sont devenues nettement plus accessibles. 'PARTITION BY' y est particuli&egrave;rement utile.</p> <p><strong>L'astuce de la semaine</strong></p> <p>Le c&ocirc;t&eacute; r&eacute;f&eacute;ren&ccedil;ant ("fils") des champs de clef &eacute;trang&egrave;res devrait &ecirc;tre index&eacute;, &agrave; moins que les valeurs r&eacute;f&eacute;renc&eacute;es aient une tr&egrave;s faible cardinalit&eacute;. PostgreSQL ne met pas cela en place automatiquement.</p> <p>(<a target="_blank" href="http://www.postgresql.org/community/weeklynews/pwn20090509">lien vers l'article original</a>)</p> <p><strong>Offres d'emplois autour de PostgreSQL en mai</strong></p> <ul> <li><a target="_blank" href="http://archives.postgresql.org/pgsql-jobs/2009-05/threads.php">http://archives.postgresql.org/pgsql-jobs/2009-05/threads.php</a></li> </ul> <p><strong>PostgreSQL Local</strong></p> <ul> <li>Le 20 mai &agrave; Pise, Gabriele Bartolini de 2ndQuadrant Italie participera au rendez-vous "Open-Source: companies meet the students" organis&eacute; par le "Tuscany Open Source Software Laboratory". Plus d'informations (en italien)&nbsp;: <a target="_blank" href="http://www.tosslab.it/open-source-aziende-e-studenti-si-incontrano">http://www.tosslab.it/open-source-aziende-e-studenti-si-incontrano</a></li> <li>La PGCon 2009 se tiendra &agrave; l'Universit&eacute; d'Ottawa les 21 et 22 mai 2009. Elle sera pr&eacute;c&eacute;d&eacute;e de deux jours de tutoriels les 19 &amp; 20 mai&nbsp;: <a target="_blank" href="http://www.pgcon.org/2009/">http://www.pgcon.org/2009/</a></li> <li>Le PgDay de Florianopolis aura lieu le 22 mai. Contactez Dickson S. Guedes (guedes AROBASE guedesoft DOT net) pour participer ou proposer une conf&eacute;rence&nbsp;: <a target="_blank" href="http://www.postgresql.org.br/eventos/pgday/sc">http://www.postgresql.org.br/eventos/pgday/sc</a></li> <li>Notez la date&nbsp;: pgDay San Jose, dimanche 19 juillet juste avant l'OSCON. Appel &agrave; conf&eacute;renciers, plus d'infos sous peu&nbsp;!</li> <li>La "PGCon Brazil" prendra place &agrave; l'Unicamp de Campinas (&Eacute;tat de S&atilde;o Paulo) les 23 &amp; 24 octobre 2009.</li> </ul> <p><strong>PostgreSQL dans les m&eacute;dia</strong></p> <ul> <li>Planet PostgreSQL&nbsp;: <a target="_blank" href="http://planet.postgresql.org/">http://planet.postgresql.org/</a></li> </ul> <p><i>PostgreSQL Weekly News / les nouvelles hebdomadaires vous sont offertes cette semaine par David Fetter et Josh Berkus. La traduction en est assur&eacute;e par l'&eacute;quipe PostgreSQLFr.</i></p> <p><i>Proposez vos articles ou annonces avant dimanche 15:00 (heure du Pacifique). Merci de les envoyer en anglais &agrave; david (a) fetter.org, en allemand &agrave; pwn (a) pgug.de, en italien &agrave; pwn (a) itpug.org.</i></p> <p><strong>Correctifs appliqu&eacute;s</strong></p> <p>Peter Eisentraut a commit&eacute;&nbsp;:</p> <ul> <li>Replace a couple of references to files that no longer exist in the source tree with references to the appropriate URLs. Robert Haas</li> <li>Disable the use of Unicode escapes in string constants (U&amp;'') when standard_conforming_strings is not on, for security reasons.</li> </ul> <p>Magnus Hagander a commit&eacute;&nbsp;:</p> <ul> <li>In pgsql/src/backend/port/win32_shmem.c, call SetLastError(0) before calling the file mapping functions to make sure that the error code is reset, as a precaution in case the API doesn't properly reset it on success. This could be necessary, since we check the error value even if the function doesn't fail for specific success cases.</li> <li>In pgsql/src/port/dirmod.c, update no longer current comments in header.</li> <li>In pgsql/src/backend/port/win32_shmem.c, make the win32 shared memory code try 10 times instead of one if it fails because the shared memory segment already exists. This means it can take up to 10 seconds before it reports the error if it *does* exist, but hopefully it will make the system capable of restarting even when the server is under high load.</li> </ul> <p>Heikki Linnakangas a commit&eacute;&nbsp;:</p> <ul> <li>Fix the query used for \d against 8.2 and 8.3 servers.</li> <li>Request XLOG switch before writing checkpoint in pg_start_backup(). Otherwise you can end up with an unrecoverable backup if you start a new base backup right after finishing archive recovery. In that scenario, the redo pointer of the checkpoint that pg_start_backup() writes points to the XLOG segment where the timeline-changing end-of-archive-recovery checkpoint is. The beginning of that segment contains pages with the old timeline ID, and we don't accept that in recovery unless we find a history file covering the old timeline ID. If you omit pg_xlog from the base backup and clear the archive directory before starting the backup, there will be no such history file available. The bug is present in all versions since PITR was introduced in 8.0, but I'm back-patching only back to 8.2. Earlier versions didn't have XLOG switch records, making this fix unfeasible. Given the lack of reports until now, it doesn't seem worthwhile to spend more effort to fix 8.0 and 8.1. Per report and suggestion by Mikael Krantz</li> <li>Add alternative expected output files for cs_CZ locale for btree_gist and tsearch2 tests. This should make 'comet_moth' buildfarm member pass contrib check. Zdenek Kotala.</li> </ul> <p>Tom Lane a commit&eacute;&nbsp;:</p> <ul> <li>In pgsql/src/bin/psql/describe.c, code review for \df rewrite: fix assorted bugs, make type and volatility columns localizable.</li> <li>In pgsql/src/backend/commands/analyze.c, avoid integer overflow in the loop that extracts histogram entries from ANALYZE's total sample. The original coding is at risk of overflow for statistics targets exceeding about 2675; this was not a problem before 8.4 but it is now. Per bug #4793 from Dennis Noordsij.</li> <li>In pgsql/src/backend/access/nbtree/nbtpage.c, update comment for _bt_relandgetbuf.</li> <li>Insert CHECK_FOR_INTERRUPTS() calls into btree and hash index scans at the points where we step right or left to the next page. This should ensure reasonable response time to a query cancel request during an unsuccessful index scan, as seen in recent gripe from Marc Cousin. It's a bit trickier than it might seem at first glance, because CHECK_FOR_INTERRUPTS() is a no-op if executed while holding a buffer lock. So we have to do it just at the point where we've dropped one page lock and not yet acquired the next. Remove CHECK_FOR_INTERRUPTS calls at the top level of btgetbitmap and hashgetbitmap, since they're pointless given the added checks. I think that GIST is okay already --- at least, there's a CHECK_FOR_INTERRUPTS at a plausible-looking place in gistnext(). I don't claim to know GIN well enough to try to poke it for this, if indeed it has a problem at all. This is a pre-existing issue, but in view of the lack of prior complaints I'm not going to risk back-patching.</li> <li>Install a "dead man switch" to allow the postmaster to detect cases where a backend has done exit(0) or exit(1) without having disengaged itself from shared memory. We are at risk for this whenever third-party code is loaded into a backend, since such code might not know it's supposed to go through proc_exit() instead. Also, it is reported that under Windows there are ways to externally kill a process that cause the status code returned to the postmaster to be indistinguishable from a voluntary exit (thank you, Microsoft). If this does happen then the system is probably hosed --- for instance, the dead session might still be holding locks. So the best recovery method is to treat this like a backend crash. The dead man switch is armed for a particular child process when it acquires a regular PGPROC, and disarmed when the PGPROC is released; these should be the first and last touches of shared memory resources in a backend, or close enough anyway. This choice means there is no coverage for auxiliary processes, but I doubt we need that, since they shouldn't be executing any user-provided code anyway. This patch also improves the management of the EXEC_BACKEND ShmemBackendArray array a bit, by reducing search costs. Although this problem is of long standing, the lack of field complaints seems to mean it's not critical enough to risk back-patching; at least not till we get some more testing of this mechanism.</li> <li>In pgsql/src/backend/storage/ipc/ipc.c, install an atexit(2) callback that ensures that proc_exit's cleanup processing will still be performed if something in a backend process calls exit() directly, instead of going through proc_exit() as we prefer. This is a second response to the issue that we might load third-party code that doesn't know it should not call exit(). Such a call will now cause a reasonably graceful backend shutdown, if possible. (Of course, if the reason for the exit() call is out-of-memory or some such, we might not be able to recover, but at least we will try.)</li> <li>Make new complaint about unsafe Unicode literals include an error location. Every other ereport in scan.l has one, this should too.</li> <li>In pgsql/src/backend/port/win32_shmem.c, minor improvement: avoid assuming that GetLastError value cannot be affected by CloseHandle() or Sleep().</li> <li>In pgsql/src/backend/commands/dbcommands.c, tweak a comment to agree a bit better with the new dispensation that locales are database-wide, not server-wide.</li> <li>Fix ecpg tests for change that disallowed Unicode literals unless standard_conforming_strings is on.</li> <li>Modify CREATE DATABASE to enforce that the source database's encoding setting must be used for the new database, except when copying from template0. This is the same rule that we now enforce for locale settings, and it has the same motivation: databases other than template0 might contain data that would be invalid according to a different setting. This represents another step in a continuing process of locking down ways in which encoding violations could occur inside the backend. Per discussion of a few days ago. In passing, fix pre-existing breakage of mbregress.sh, and fix up a couple of ereport() calls in dbcommands.c that failed to specify sqlstate codes.</li> <li>In pgsql/src/backend/optimizer/plan/initsplan.c, tweak distribute_qual_to_rels so that when we decide a pseudoconstant qual can be pushed to the top of the join tree, we update both the relids and qualscope variables to keep them in sync. This prevents a possible later failure of an Assert clause, and affects nothing else since qualscope isn't used later except for that Assert. At the moment the Assert shouldn't be reachable when we've pushed the qual up; but this is cheap insurance, and it's more sensible anyway in terms of the overall logic of the routine. Per analysis of a bug report from Stefan Huehner. I'm not back-patching this since it's just future-proofing; but if anyone gets tempted to change check_outerjoin_delay again in the back branches, this might be needed.</li> <li>In pgsql/src/backend/optimizer/plan/initsplan.c, ooops ... make_outerjoininfo wasn't actually enforcing the join order restrictions specified for semijoins in optimizer/README, to wit that you can't reassociate outer joins into or out of the RHS of a semijoin. Per report from Heikki Linnakangas.</li> <li>Change pgbench to use the table names pgbench_accounts, pgbench_branches, pgbench_history, and pgbench_tellers, rather than just accounts, branches, history, and tellers. This is to prevent accidental conflicts with real application tables, as has been reported to happen at least once. Also remove the automatic "SET search_path = public" that it did at startup, as this seems to restrict testing flexibility without actually buying much. Per proposal by Joshua Drake and ensuing discussion. Joshua Drake and Tom Lane.</li> <li>Add an option to AlterTableCreateToastTable() to allow its caller to force a toast table to be built, even if the sum-of-column-widths calculation indicates one isn't needed. This is needed by pg_migrator because if the old table has a toast table, we have to migrate over the toast table since it might contain some live data, even though subsequent column drops could mean that no recently-added rows could require toasting.</li> </ul> <p>Bruce Momjian a commit&eacute;&nbsp;:</p> <ul> <li>In pgsql/src/include/access/transam.h, 'PGDLLIMPORT' ShmemVariableCache, needed for pg_migrator.so function linkage on Win32. Tested by Hiroshi Saito.</li> <li>In pgsql/src/tools/fsync/test_fsync.c, add missing third argument to open().</li> </ul> <p><strong>Correctifs rejet&eacute;s (&agrave; ce jour)</strong></p> <ul> <li>No one was disappointed this week :-)</li> </ul> <p><strong>Correctifs en attente</strong></p> <ul> <li>Robert Haas sent in another revision of the ALTER TABLE ... ALTER COLUMN ... SET DISTINCT patch.</li> <li>Andres Freund sent in two revisions of a patch which implements ALTER TABLE ... DROP [COLUMN | CONSTRAINT] IF EXISTS ...</li> <li>Dickson S. Guedes sent in a patch to add a way to display both client and server versions in the psql prompt.</li> <li>Seth Robertson sent in a patch to automate selection of client certificates in libpq.</li> <li>Andrew (RhodiumToad) Gierth sent in a patch to change src/backend/libpq/be-secure.c from calling SSL_CTX_use_certificate_file to SSL_CTX_use_certificate_chain_file.</li> <li>Joshua Drake sent in a patch to make version_stamp.pl strict-clean.</li> <li>Khee Chin sent in a patch to make psql's \di show the type of index used.</li> <li>David Fetter sent in a patch to make all the perl programs strict-clean.</li> </ul>