Communauté francophone de PostgreSQL

<p><strong>Nouvelles hebdomadaires de PostgreSQL - 20 d&eacute;cembre 2009</strong></p> <p>La <em>commitfest</em> de novembre est maintenant termin&eacute;e. F&eacute;licitations &agrave; Greg Smith pour la gestion, et &agrave; tous ceux qui ont relu, patch&eacute; et particip&eacute; d'une mani&egrave;re ou d'une autre.</p> <p>L'appel &agrave; conf&eacute;rencier pour "East" est lanc&eacute; avec, comme date limite, le 30 janvier 2010. D&eacute;tails sur&nbsp;: <a target="_blank" href="http://www.postgresqlconference.org/">http://www.postgresqlconference.org/</a></p> <p>Les &eacute;lections 2009 pour la direction du PgUS sont en cours&nbsp;: <a target="_blank" href="https://www.postgresql.us/elections/2009">https://www.postgresql.us/elections/2009</a></p> <p>&Agrave; la recherche de volontaires pour la rencontre de la "Open Day Linux Conf AU" &agrave; Wellington, Nouvelle Z&eacute;lande, le 23 janvier 2010 entre 11 et 14h. Contactez selena -at- postgresql.org.</p> <p><strong>Les nouveaut&eacute;s des produits d&eacute;riv&eacute;s</strong></p> <ul> <li>Alerce 1.0.0, un syst&egrave;me de r&eacute;plication synchrone pour Python&nbsp;: <a target="_blank" href="http://pgfoundry.org/projects/pyreplica/">http://pgfoundry.org/projects/pyreplica/</a></li> <li>Gluster Storage Platform 3.0&nbsp;: <a target="_blank" href="http://www.gluster.org/">http://www.gluster.org/</a></li> <li>pgpool-II 2.3.1, un gestionnaire de connexion et bien d'avantage&nbsp;: <a target="_blank" href="http://pgfoundry.org/projects/pgpool/">http://pgfoundry.org/projects/pgpool/</a></li> <li>pg_rman 1.1.1, un outil de sauvegarde/restauration pour PostgreSQL&nbsp;: <a target="_blank" href="http://code.google.com/p/pg-rman/">http://code.google.com/p/pg-rman/</a></li> <li>Devrim GUNDUZ a cr&eacute;&eacute; un nouveau LiveCD bas&eacute; sur Fedora 12 et PostgreSQL 8.4.2&nbsp;: <a target="_blank" href="http://pglivecd.org/">http://pglivecd.org/</a></li> </ul> <p><strong>La fonctionnalit&eacute; 8.5 de la semaine</strong></p> <p>Clauses "WHEN" sur les d&eacute;clencheurs (triggers)&nbsp;: dans la version 8.5 alpha 3, il sera possible de cr&eacute;er des d&eacute;clencheurs avec une clause WHEN pour conditionner leur ex&eacute;cution. Merci &agrave; Itagaki Takahiro et l'&eacute;quipe NTT.</p> <p><strong>L'astuce de la semaine</strong></p> <p>Astuce de la semaine&nbsp;: PostgreSQL offre trois moyens de g&eacute;rer les arbres&nbsp;: les requ&ecirc;tes WITH RECURSIVE, le module contrib "ltree" et la fonction connect_by() dans le module tablefunc.</p> <p><strong>Offres d'emplois autour de PostgreSQL en d&eacute;cembre</strong></p> <ul> <li><a target="_blank" href="http://archives.postgresql.org/pgsql-jobs/2009-12/threads.php">http://archives.postgresql.org/pgsql-jobs/2009-12/threads.php</a></li> </ul> <p><strong>PostgreSQL Local</strong></p> <ul> <li>La premi&egrave;re r&eacute;union du "New York Capital District PUG" aura lieu le 7 janvier 2010&nbsp;: <a target="_blank" href="http://nycdpug.x10hosting.com/">http://nycdpug.x10hosting.com/</a></li> <li>Stefan Keller, de l'universit&eacute; des sciences appliqu&eacute;es de Rapperswil (HSR), animera un s&eacute;minaire intitul&eacute; "Introduction &agrave; PostGIS/PostgreSQL" le 14 janvier 2010 &agrave; Zurich (Suisse)&nbsp;: <a target="_blank" href="http://www.gis.hsr.ch/wiki/Agenda">http://www.gis.hsr.ch/wiki/Agenda</a></li> <li>Le PGDay-Cuba 2010 sera tenu du 4 au 6 f&eacute;vrier 2010 &agrave; l'Universit&eacute; des Sciences Informatiques, &agrave; La Havane. Sont invit&eacute;s les participants de Cuba, de la R&eacute;publique Dominicaine et du Venezuela.</li> <li>Le FOSDEM 2010 aura lieu &agrave; Bruxelles les 6 &amp; 7 f&eacute;vrier 2010&nbsp;: <a target="_blank" href="http://www.fosdem.org/">http://www.fosdem.org/</a></li> <li>Le "Chemnitzer Linuxtage" aura lieu &agrave; Chemnitz (All.) les 13 &amp; 14 mars 2010&nbsp;: <a target="_blank" href="http://chemnitzer.linux-tage.de/">http://chemnitzer.linux-tage.de/</a></li> <li>La PgCon 2010 aura lieu les 20 &amp; 21 mai 2010 &agrave; Ottawa avec des tutoriels les 18 et 19. L'appel &agrave; conf&eacute;renciers est lanc&eacute;&nbsp;! <a target="_blank" href="http://www.pgcon.org/2010/papers.php">http://www.pgcon.org/2010/papers.php</a></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> <li>Planet PostgreSQLFr&nbsp;: <a target="_blank" href="http://planete.postgresql.fr/">http://planete.postgresql.fr/</a></li> </ul> <p><i>PostgreSQL Weekly News / les nouvelles hebdomadaires vous sont offertes cette semaine par David Fetter et Josh Berkus. Traduction par l'&eacute;quipe PostgreSQLFr sous licence CC BY-NC-SA.</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>(<a target="_blank" href="http://www.postgresql.org/community/weeklynews/pwn20091220">lien vers l'article original</a>)</p> <p><strong>Revues</strong></p> <p>Greg Smith reviewed ITAGAKI Takahiro's latest patch to enable pgbench to launch shell commands.</p> <p>Marko Kreen reviewed Kurt Harriman's patch to remove the gcc dependency in the definition of inline functions.</p> <p>Hiroyuki Yamada found a bug in hot standby where it can freeze when the startup process calls LockBufferForCleanup(), complete with repro.</p> <p><strong>Correctifs appliqu&eacute;s</strong></p> <p>ITAGAKI Takahiro a commit&eacute;&nbsp;:</p> <ul> <li>In pgsql/src/bin/pg_ctl/pg_ctl.c, fix pg_ctl initdb without options. Passing NULL string to snprintf is avoided.</li> <li>Add \shell and \setshell meta commands to pgbench. \shell command runs an external shell command. \setshell also does the same and sets the result to a variable. Original patch by Michael Paquier with some editorialization by ITAGAKI Takahiro, and reviewed by Greg Smith.</li> <li>In pgsql/doc/src/sgml/ref/alter_large_object.sgml, add missing Id keyword to alter_large_object.sgml.</li> <li>Add a whitespace to fix the query to dump large objects. PL/pgSQL-by-default patch broke the code for 8.3 &lt;= server_version &lt; 8.5.</li> </ul> <p>Robert Haas a commit&eacute;&nbsp;:</p> <ul> <li>Add an EXPLAIN (BUFFERS) option to show buffer-usage statistics. This patch also removes buffer-usage statistics from the track_counts output, since this (or the global server statistics) is deemed to be a better interface to this information. Itagaki Takahiro, reviewed by Euler Taveira de Oliveira.</li> <li>Several fixes for EXPLAIN (FORMAT YAML), plus one for EXPLAIN (FORMAT JSON). ExplainSeparatePlans() was busted for both JSON and YAML output - the present code is a holdover from the original version of my machine-readable explain patch, which didn't have the grouping_stack machinery. Also, fix an odd distribution of labor between ExplainBeginGroup() and ExplainYAMLLineStarting() when marking lists with "- ", with each providing one character. This broke the output format for multi-query statements. Also, fix ExplainDummyGroup() for the YAML output format. Along the way, make the YAML format use escape_yaml() in situations where the JSON format uses escape_json(). Right now, it doesn't matter because all the values are known not to need escaping, but it seems safer this way. Finally, I added some comments to better explain what the YAML output format is doing. Greg Sabino Mullane reported the issues with multi-query statements. Analysis and remaining cleanups by Robert Haas.</li> <li>Improve documentation for pg_largeobject changes. Rewrite the documentation in more idiomatic English, and in the process make it somewhat more succinct. Move the discussion of specific large object privileges out of the "server-side functions" section, where it certainly doesn't belong, and into "implementation features". That might not be exactly right either, but it doesn't seem worth creating a new section for this amount of information. Fix a few spelling and layout problems, too.</li> <li>Fix a few typos in the latest 8.5alpha3 release notes.</li> <li>In pgsql/doc/src/sgml/backup.sgml, fix two small typos in Hot Standby docs. John Naylor.</li> <li>More cleanups for the recent large object permissions patch. Rewrite or adjust various comments for clarity. Remove one bogus comment that doesn't reflect what the code actually does. Improve the description of the lo_compat_privileges option.</li> </ul> <p>Tom Lane a commit&eacute;&nbsp;:</p> <ul> <li>In pgsql/doc/src/sgml/pgbench.sgml, fix broken markup.</li> <li>Support ORDER BY within aggregate function calls, at long last providing a non-kluge method for controlling the order in which values are fed to an aggregate function. At the same time eliminate the old implementation restriction that DISTINCT was only supported for single-argument aggregates. Possibly release-notable behavioral change: formerly, agg(DISTINCT x) dropped null values of x unconditionally. Now, it does so only if the agg transition function is strict; otherwise nulls are treated as DISTINCT normally would, ie, you get one copy. Andrew Gierth, reviewed by Hitoshi Harada.</li> <li>Add a hook to let loadable modules get control at ProcessUtility execution, and use it to extend contrib/pg_stat_statements to track utility commands. Itagaki Takahiro, reviewed by Euler Taveira de Oliveira.</li> <li>In pgsql/src/backend/tsearch/wparser_def.c, avoid unnecessary copying of source string when generating a cloned TParser. For long source strings the copying results in O(N2) behavior, and the multiplier can be significant if wide-char conversion is involved. Andres Freund, reviewed by Kevin Grittner.</li> <li>Avoid a premature coercion failure in transformSetOperationTree() when presented with an UNKNOWN-type Var, which can happen in cases where an unknown literal appeared in a subquery. While many such cases will fail later on anyway in the planner, there are some cases where the planner is able to flatten the query and replace the Var by the constant before it has to coerce the union column to the final type. I had added this check in 8.4 to provide earlier/better error detection, but it causes a regression for some cases that worked OK before. Fix by not making the check if the input node is UNKNOWN type and not a Const or Param. If it isn't going to work, it will fail anyway at plan time, with the only real loss being inability to provide an error cursor. Per gripe from Britt Piehler. In passing, rename a couple of variables to remove confusion from an inner scope masking the same variable names in an outer scope.</li> <li>Force the TZ environment variable to be set during initdb. This is to short-circuit the rather expensive identify_system_timezone() procedure, which we have no real need for during initdb since nothing done here depends on the timezone setting. Since we launch quite a few standalone backends during the initdb sequence, this adds up to a significant savings, and seems worth doing to save developer time even though it will hardly matter to end users. Per my report today on pgsql-hackers.</li> <li>Fix link that doesn't work in standalone INSTALL document.</li> <li>Bump catversion to reflect the fact that Hiroshi Saito patch changed pg_proc contents, and PG_CONTROL_VERSION to reflect the fact that it changed pg_control contents. (I see we did at least remember to change XLOG_PAGE_MAGIC for the WAL contents changes.)</li> <li>Adjust some more places in the documentation to match the fact that plpgsql is now installed by default.</li> <li>Join in the fun of editorializing on the alpha release notes.</li> <li>plpgsql patch broke the MSVC build, too.</li> <li>Sigh, I managed to break the no-links-in-plain-text-docs rule too...</li> <li>There is no good reason for the CREATE TABLE LIKE INCLUDING COMMENTS code to have hard-wired knowledge of the rules for naming index columns. It can just look at the actual names in the source index, instead. Do some minor formatting cleanup too.</li> </ul> <p>Peter Eisentraut a commit&eacute;&nbsp;:</p> <ul> <li>Python 3 support in PL/Python. Behaves more or less unchanged compared to Python 2, but the new language variant is called plpython3u. Documentation describing the naming scheme is included.</li> <li>If there is no sigdelset(), define it as a macro. This removes some duplicate code that recreated the identical workaround when the newer signal API is missing.</li> <li>Don't unblock SIGQUIT in the SIGQUIT handler. This was possibly linked to a deadlock-like situation in glibc syslog code invoked by the ereport call in quickdie(). In any case, a signal handler should not unblock its own signal unless there is a specific reason to.</li> <li>8.5alpha3 release notes up to Fri Dec 18 21:37:38 2009 +0000</li> <li>Translation updates</li> <li>Add hot standby to release notes</li> <li>Add documentation why reassigning PL/Python function parameters in the function body can have undesirable outcomes. (bug #5232)</li> </ul> <p>Michael Meskes a commit&eacute;&nbsp;:</p> <ul> <li>Fixed auto-prepare to not try preparing statements that are not preparable. Bug found and solved by Zoltan Boszormenyi some small adjustments by Michael Meskes.</li> <li>Reverting accidently commited changes.</li> </ul> <p>Magnus Hagander a commit&eacute;&nbsp;:</p> <ul> <li>In pgsql/doc/src/sgml/hstore.sgml, remove spurious '22' that clearly shouldn't be there. David E. Wheeler.</li> </ul> <p>Bruce Momjian a commit&eacute;&nbsp;:</p> <ul> <li>Install server-side language PL/pgSQL by default.</li> <li>Add comments about places where system oids have to be preserved for binary migration.</li> <li>Convert tabs to spaces in SGML.</li> </ul> <p>Alvaro Herrera a commit&eacute;&nbsp;:</p> <ul> <li>In pgsql/doc/src/sgml/start.sgml, update tutorial on the fact that backslash commands are no longer listed in psql's startup banner.</li> <li>Update ecpg regression tests to plpgsql installed by default.</li> </ul> <p>Simon Riggs a commit&eacute;&nbsp;:</p> <ul> <li>Allow read only connections during recovery, known as Hot Standby. Enabled by recovery_connections = on (default) and forcing archive recovery using a recovery.conf. Recovery processing now emulates the original transactions as they are replayed, providing full locking and MVCC behaviour for read only queries. Recovery must enter consistent state before connections are allowed, so there is a delay, typically short, before connections succeed. Replay of recovering transactions can conflict and in some cases deadlock with queries during recovery; these result in query cancellation after max_standby_delay seconds have expired. Infrastructure changes have minor effects on normal running, though introduce four new types of WAL record. New test mode "make standbycheck" allows regression tests of static command behaviour on a standby server while in recovery. Typical and extreme dynamic behaviours have been checked via code inspection and manual testing. Few port specific behaviours have been utilised, though primary testing has been on Linux only so far. This commit is the basic patch. Additional changes will follow in this release to enhance some aspects of behaviour, notably improved handling of conflicts, deadlock detection and query cancellation. Changes to VACUUM FULL are also required. Simon Riggs, with significant and lengthy review by Heikki Linnakangas, including streamlined redesign of snapshot creation and two-phase commit. Important contributions from Florian Pflug, Mark Kirkwood, Merlin Moncure, Greg Stark, Gianni Ciolli, Gabriele Bartolini, Hannu Krosing, Robert Haas, Tatsuo Ishii, Hiroyuki Yamada plus support and feedback from many other community members.</li> </ul> <p><strong>Correctifs rejet&eacute;s (&agrave; ce jour)</strong></p> <ul> <li>Pas de d&eacute;ception cette semaine&nbsp;:-)</li> </ul> <p><strong>Correctifs en attente</strong></p> <ul> <li>Zoltan Boszormenyi sent in four more revisions of the ECPG patch for SQLDA, this time as an ECPG native feature rather than compat-mode-only.</li> <li>Michael Paquier sent in another revision of the patch to enable pgbench to launch shell commands.</li> <li>Robert Haas sent in a patch to fix explain output for multi-plan statements.</li> <li>ITAGAKI Takahiro sent in another revision of the patch which reworks VACUUM FULL.</li> <li>Kurt Harriman sent in another revision of the patch to remove the gcc dependency in definitions of inline functions.</li> <li>Zoltan Boszormenyi sent in another revision of the ECPG patch to support DESCRIBE [OUTPUT].</li> <li>Zoltan Boszormenyi sent in four more revisions of the ECPG patch to support out-of-scope cursor support in Informix mode.</li> <li>Peter Eisentraut sent in three patches to fix a bug where recovery gets stuck when children are not processing SIGQUIT from a previous crash. The first defines sigdelset as a macro if it's not already there. The second moves the operation of unblocking SIGQUIT out of the SIGQUIT handler. The third times out the ereport call in quickdie after 60 seconds.</li> <li>KaiGai Kohei sent in a patch to clean up existing privilege checks and consolidate same.</li> <li>KaiGai Kohei sent in a patch to make EnableDisableRule() check the ownership of the relation which owns the rule.</li> <li>KaiGai Kohei sent in a patch which removes obscure permission checks in FindConversion().</li> <li>David Wheeler sent in a patch to add hstore_to_json().</li> <li>KaiGai Kohei sent in a patch to fix an issue with ALTER TABLE...ALTER COLUMN...RENAME TO which happened in situations where the table in question is the child, via table inheritance, of multiple tables.</li> <li>KaiGai Kohei sent in a patch to remove redundant ownership checks.</li> <li>Robert Haas sent in a patch to clean up comments with non-standard version names.</li> <li>KaiGai Kohei sent in a patch to help pg_dump work with the new large object (lo) ACLs.</li> <li>James Pye sent in a patch to add some new SPI functions to PL/Python3U.</li> <li>Martin Pihlak sent in a patch to fix a bug in foreign data wrappers.</li> <li>Florian Pflug sent in a WIP patch that allows easier introspection of composite types from SQL and PL/pgsql.</li> <li>John Naylor sent in another patch to revamp the BKI infrastructure.</li> <li>Tom Lane sent in a WIP patch to choose better default names for indexes, per discussion.</li> </ul>