Nouvelles hebdomadaires de PostgreSQL - 17 mai 2009

Le PgDay de San Jose aura lieu le dimanche 19 juin, annonçant l'ouverture de l'OSCON. Soumettez vos propositions avant le 8 juin à conference-sf-speakers AT pgfoundry DOT org, les résultats seront donnés le 20 juin : http://www.postgresql.org/about/news.1082

"Yet Another Perl Conference 10" est programmée à Pittsburgh du 22 au 24 juin avec des conférences données par David Fetter et suivie de cours par le fameux professeur Joe Celko : http://yapc10.org

Les "PGCon 2009" ont lieu cette semaine à Ottawa. Attendez-vous après coup à un afflux de nouvelles.

La fonctionnalité 8.4 de la semaine

Les expressions de tables communes (CTE). Avec une syntaxe facile d'accès, vous pouvez configurer, à la volée, vos propres vues à usage unique. Et elles sont récursives, ce qui place les structures hiérarchiques à portée de main. [ndt: http://docs.postgresql.fr/8.4/queries-with.html]

L'astuce de la semaine

N'utilisez jamais fsync=off à moins que vous puissiez vous permettre de reconstruire tout votre cluster BDD à partir de rien. Si le log de transaction (WAL) fait trop baisser les performances, utilisez plutôt synchronous_commit=false.

(lien vers l'article original)

Offres d'emplois autour de PostgreSQL en May

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

PostgreSQL Local

• Le 20 mai à Pise, Gabriele Bartolini de 2ndQuadrant Italie participera au rendez-vous "Open-Source: companies meet the students" organisé par le "Tuscany Open Source Software Laboratory". Plus d'informations (en italien) : http://www.tosslab.it/open-source-aziende-e-studenti-si-incontrano
• 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/
• Le PgDay de Florianopolis aura lieu le 22 mai. Contactez Dickson S. Guedes (guedes AROBASE guedesoft DOT net) pour participer ou proposer une conférence : http://www.postgresql.org.br/eventos/pgday/sc
• Le PGDay de Junin / Buenos Aires aura lieu le 6 juin à l'Unnoba : http://www.arpug.com.ar/trac/wiki/PgDayUnnoba
• Notez la date : pgDay San Jose, dimanche 19 juillet juste avant l'OSCON. Appel à conférenciers, plus d'infos sous peu !
• La "PGCon Brazil" prendra place à l'Unicamp de Campinas (État de São Paulo) les 23 & 24 octobre 2009.
• Le PGDay.EU 2009 est programmé pour les 6 & 7 novembre 2009 à Telecom ParisTech (ndt: ENST ?) : http://www.pgday.eu/

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 et Josh Berkus. 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

Tom Lane a commité :

• Fix cost_nestloop and cost_hashjoin to model the behavior of semi and anti joins a bit better, ie, understand the differing cost functions for matched and unmatched outer tuples. There is more that could be done in cost_hashjoin but this already helps a great deal. Per discussions with Robert Haas.
• In pgsql/src/bin/pg_dump/pg_dumpall.c, adjust pg_dumpall so that it emits ENCODING, LC_COLLATE, and LC_CTYPE options in its CREATE DATABASE commands only for databases that have settings different from the installation defaults. This is a low-tech method of avoiding unnecessary platform dependencies in dump files. Eventually we ought to have a platform-independent way of specifying LC_COLLATE and LC_CTYPE, but that's not going to happen for 8.4, and this patch at least avoids the issue for people who aren't setting up per-database locales. ENCODING doesn't have the platform dependency problem, but it seems consistent to make it act the same as the locale settings.
• In pgsql/src/backend/optimizer/util/predtest.c, make a marginal performance improvement in predicate_implied_by and predicate_refuted_by: if either top-level input is a single-element list, reduce it to its lone member before proceeding. This avoids a useless level of AND-recursion within the recursive proof routines. It's worth doing because, for example, if the clause is a 100-element list and the predicate is a 1-element list then we'd otherwise strip the predicate's list structure 100 times as we iterate through the clause. It's only needed at top level because there won't be any trivial ANDs below that --- this situation is an artifact of the decision to represent even single-item conditions as Lists in the "implicit AND" format, and that format is only used at the top level of any predicate or restriction condition.
• In pgsql/src/backend/optimizer/util/predtest.c, partially revert my patch of 2008-11-12 that installed a limit on the number of AND/OR clause branches that predtest.c would attempt to deal with. As noted in bug #4721, that change disabled proof attempts for sizes of problems that people are actually expecting it to work for. The original complaint it was trying to solve was O(N2) behavior for long IN-lists, so let's try applying the limit to just ScalarArrayOpExprs rather than everything. Another case of "foolish consistency" I fear. Back-patch to 8.2, same as the previous patch was.
• Do some minor code refactoring in preparation for changing the APIs of find_inheritance_children() and find_all_inheritors(). I got annoyed that these are buried inside the planner but mostly used elsewhere. So, create a new file catalog/pg_inherits.c and put them there, along with a couple of other functions that search pg_inherits. The code that modifies pg_inherits is (still) in tablecmds.c --- it's kind of entangled with unrelated code that modifies pg_depend and other stuff, so pulling it out seemed like a bigger change than I wanted to make right now. But this file provides a natural home for it if anyone ever gets around to that. This commit just moves code around; it doesn't change anything, except I succumbed to the temptation to make a couple of trivial optimizations in typeInheritsFrom().
• Modify find_inheritance_children() and find_all_inheritors() to add the ability to lock relations as they scan pg_inherits, and to ignore any relations that have disappeared by the time we get lock on them. This makes uses of these functions safe against concurrent DROP operations on child tables: we will effectively ignore any just-dropped child, rather than possibly throwing an error as in recent bug report from Thomas Johansson (and similar past complaints). The behavior should not change otherwise, since the code was acquiring those same locks anyway, just a little bit later. An exception is LockTableCommand(), which is still behaving unsafely; but that seems to require some more discussion before we change it.
• Fix LOCK TABLE to eliminate the race condition that could make it give weird errors when tables are concurrently dropped. To do this we must take lock on each relation before we check its privileges. The old code was trying to do that the other way around, which is a bit pointless when there are lots of other commands that lock relations before checking privileges. I did keep it checking each relation's privilege before locking the next relation, which is a detail that ALTER TABLE isn't too picky about.
• In pgsql/src/backend/utils/adt/xml.c, fix intratransaction memory leaks in xml_recv, xmlconcat, xmlroot, and xml_parse, all arising from the same sloppy usage of parse_xml_decl. The original coding had that function returning its output string parameters in the libxml context, which is long-lived, and all but one of its callers neglected to free the strings afterwards. The easiest and most bulletproof fix is to return the strings in the local palloc context instead, since that's short-lived. This was only costing a dozen or two bytes per function call, but that adds up fast if the function is called repeatedly ... Noted while poking at the more general problem of what to do with our libxml memory allocation hooks. Back-patch to 8.3, which has the identical coding.
• In pgsql/doc/src/sgml/release-8.4.sgml, update release notes for changes through 2009-05-11. Also some minor copy-editing and reordering of items.
• Rewrite xml.c's memory management (yet again). Give up on the idea of redirecting libxml's allocations into a Postgres context. Instead, just let it use malloc directly, and add PG_TRY blocks as needed to be sure we release libxml data structures in error recovery code paths. This is ugly but seems much more likely to play nicely with third-party uses of libxml, as seen in recent trouble reports about using Perl XML facilities in pl/perl and bug #4774 about contrib/xml2. I left the code for allocation redirection in place, but it's only built/used if you #define USE_LIBXMLCONTEXT. This is because I found it useful to corral libxml's allocations in a palloc context when hunting for libxml memory leaks, and we're surely going to have more of those in the future with this type of approach. But we don't want it turned on in a normal build because it breaks exactly what we need to fix. I have not re-indented most of the code sections that are now wrapped by PG_TRY(); that's for ease of review. pg_indent will fix it. This is a pre-existing bug in 8.3, but I don't dare back-patch this change until it's gotten a reasonable amount of field testing.
• In pgsql/doc/src/sgml/func.sgml, remove a useless backslash from a pattern-match example. Michael Toews.
• In pgsql/src/backend/rewrite/rewriteDefine.c, add checks to DefineQueryRewrite() to prohibit attaching rules to relations that aren't RELKIND_RELATION or RELKIND_VIEW, and to disallow attaching rules to system relations unless allowSystemTableMods is on. This is to make the behavior of CREATE RULE more like CREATE TRIGGER, which disallows the comparable cases. Per discussion of bug #4808.
• In pgsql/src/backend/access/transam/xlog.c, improve a couple of comments.
• In pgsql/doc/src/sgml/pgstandby.sgml, clean up overly hasty docs patch for pg_standby.
• In pgsql/doc/src/sgml/release-8.4.sgml, update release notes to today.
• In pgsql/src/backend/access/transam/recovery.conf.sample, include recovery_end_command in recovery.conf.sample. Per suggestion of Jaime Casanova.
• Fix all the server-side SIGQUIT handlers (grumble ... why so many identical copies?) to ensure they really don't run proc_exit/shmem_exit callbacks, as was intended. I broke this behavior recently by installing atexit callbacks without thinking about the one case where we truly don't want to run those callback functions. Noted in an example from Dave Page.
• In pgsql/src/backend/libpq/pg_ident.conf.sample, improve comments in pg_ident.conf.sample.
• In pgsql/doc/src/sgml/client-auth.sgml, make an editorial pass over the Client Authentication material.
• In pgsql/doc/src/sgml/storage.sgml, minor editorialization on storage.sgml's documentation of free space maps.
• In pgsql/doc/src/sgml/maintenance.sgml, minor copy-editing for description of partial-table vacuuming.

Magnus Hagander a commité :

• In pgsql/src/backend/libpq/be-secure.c, support SSL certificate chains in the server certificate file. Andrew (RhodiumToad) Gierth.
• In pgsql/doc/src/sgml/release-8.4.sgml, edit the SSL and Kerberos parts of the release notes a bit, and add a note about the certificates chains patch just applied.
• In pgsql/doc/src/sgml/release-8.4.sgml, move crypt auth comment to proper section. Add some details about the name=value format of auth options.

Heikki Linnakangas a commité :

• Add recovery_end_command option to recovery.conf. recovery_end_command is run at the end of archive recovery, providing a chance to do external cleanup. Modify pg_standby so that it no longer removes the trigger file, that is to be done using the recovery_end_command now. Provide a "smart" failover mode in pg_standby, where we don't fail over immediately, but only after recovering all unapplied WAL from the archive. That gives you zero data loss assuming all WAL was archived before failover, which is what most users of pg_standby actually want. recovery_end_command by Simon Riggs, pg_standby changes by Fujii Masao and myself.
• In pgsql/doc/src/sgml/release-8.4.sgml, add a note to release notes about the smart failover mode in pg_standby.

Alvaro Herrera a commité :

• Translation updates.

Marc Fournier a commité :

• commit for BETA2.

Correctifs rejetés (à ce jour)

• Dickson S. Guedes's patch to change charset.sgml. CVS failure.

Correctifs en attente

• Damien Clochard sent in another revision of the patch to add inherited tables to \d+ output.
• Khee Chin sent in three more revision of his patch to add index type and index expression to \d output in psql.
• ITAGAKI Takahiro sent in a WIP patch to allow the FORCE QUOTE and FORCE NOT NULL options in COPY ... CSV not to take a column name as argument, and for absence of that argument to mean that the options would apply to all columns.
• Pavel Stehule sent in two revisions of the GROUPING SETS patch.
• KaiGai Kohei sent in another revision of his SE-PostgreSQL patches.
• Euler Taveira de Oliveira sent in a patch to use ngettext on some more plural forms.