Communauté francophone de PostgreSQL

<p><strong>Nouvelles hebdomadaires de PostgreSQL - 3 janvier 2010</strong></p> <p>Des paquets RPM pour la 8.5alpha3 sont disponibles, gr&acirc;ce au dur travail de Devrim. &Agrave; vos tests&nbsp;! <a target="_blank" href="http://yum.pgsqlrpms.org/news-8.5alpha-packages-ready-for-testing.php">http://yum.pgsqlrpms.org/news-8.5alpha-packages-ready-for-testing.php</a></p> <p><strong>Les nouveaut&eacute;s des produits d&eacute;riv&eacute;s</strong></p> <ul> <li>Benetl 3.3, un outil d'ETL pour fichiers utilisant PostgreSQL&nbsp;: <a target="_blank" href="http://www.benetl.net">http://www.benetl.net</a></li> <li>Dataware Quick Application Suite 1.1, un outil RAD qui supporte PostgreSQL&nbsp;: <a target="_blank" href="http://www.datawarestudio.com">http://www.datawarestudio.com</a></li> <li>Npgsql 2.0.8, 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>psqlODBC 08.04.0200, un pilote ODBC pour PostgreSQL&nbsp;: <a target="_blank" href="http://psqlodbc.projects.postgresql.org/release.html">http://psqlodbc.projects.postgresql.org/release.html</a></li> </ul> <p><strong>La fonctionnalit&eacute; 8.5 de la semaine</strong></p> <p>Dans psql, \d montre &agrave; pr&eacute;sent combien de tables filles une table a en h&eacute;ritage, et \d+ les liste.</p> <p><strong>Offres d'emplois autour de PostgreSQL en janvier</strong></p> <ul> <li><a target="_blank" href="http://archives.postgresql.org/pgsql-jobs/2010-01/threads.php">http://archives.postgresql.org/pgsql-jobs/2010-01/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>&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.</li> <li>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></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 PUG allemand aura un stand dans "l'Open Source Park" au Cebit 2010 du 2 au 6 mars 2010 &agrave; Hanovre&nbsp;: <a target="_blank" href="http://www.cebit.de/">http://www.cebit.de/</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> <li>L'appel aux propositions est lanc&eacute; pour l'OSCON, qui aura lieu &agrave; Portland (Oregon) du 19 au 23 juillet 2010&nbsp;: <a target="_blank" href="http://post.oreilly.com/f2f/9z1zqmm5lhkab0uogt3avlvc4u59bro6f917re423d8">http://post.oreilly.com/f2f/9z1zqmm5lhkab0uogt3avlvc4u59bro6f917re423d8</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. 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/pwn20100103">lien vers l'article original</a>)</p> <p><strong>Revues</strong></p> <ul> <li>Robert Haas reviewed Pavel Stehule's patch to enable single- and double-quoting behavior for psql variables.</li> <li>Robert Haas reviewed ITAGAKI Takahiro's patch to add buffer usage columns to contrib/pg_stat_statements.</li> <li>Robert Haas reviewed the patch implementing red-black tree for GIN by Teodor Sigaev.</li> <li>Jaime Casanova reviewed Robert Haas's patch to add per-tablespace random_page_cost and seq_page_cost settings.</li> </ul> <p><strong>Correctifs appliqu&eacute;s</strong></p> <p>Bruce Momjian a commit&eacute;&nbsp;:</p> <ul> <li>In pgsql/src/backend/port/win32/mingwcompat.c, remove non-ascii characters from source code.</li> <li>Add PGDLLIMPORT for binary_upgrade global variables so shared object libraries can access them.</li> <li>Remove PGDLLIMPORT used for binary upgrade; must be on the externs, per Tom.</li> </ul> <p>Peter Eisentraut a commit&eacute;&nbsp;:</p> <ul> <li>In pgsql/doc/src/sgml/plpgsql.sgml, small wording improvement and clarification in PL/pgSQL trigger documentation</li> <li>In pgsql/doc/src/sgml/Makefile, revert makefile refactoring (version 1.123) because it doesn't work when building several files at once (e.g., gmake postgres-A4.pdf postgres-US.pdf).</li> <li>In pgsql/src/backend/catalog/sql_features.txt, update SQL conformance: search conditions on triggers are supported</li> <li>Fill in information schema column for trigger WHEN condition.</li> <li>Add information_schema.triggered_update_columns. This reflects the recently added support for triggers on columns.</li> <li>In pgsql/src/backend/catalog/sql_features.txt, update SQL features list for aggregate ORDER BY support</li> <li>In pgsql/src/backend/catalog/sql_features.txt, update SQL features supported list.</li> </ul> <p>Heikki Linnakangas a commit&eacute;&nbsp;:</p> <ul> <li>Previous fix for temporary file management broke returning a set from PL/pgSQL function within an exception handler. Make sure we use the right resource owner when we create the tuplestore to hold returned tuples. Simplify tuplestore API so that the caller doesn't need to be in the right memory context when calling tuplestore_put* functions. tuplestore.c automatically switches to the memory context used when the tuplestore was created. Tuplesort was already modified like this earlier. This patch also removes the now useless MemoryContextSwitch calls from callers. Report by Aleksei on pgsql-bugs on December 22, 2009. Backpatch to 8.1, like the previous patch that broke this.</li> <li>In, pgsql/contrib/tablefunc/tablefunc.c, oops, previous backpatch applied incorrectly.</li> <li>In pgsql/contrib/tablefunc/tablefunc.c, remove a now unused local variable.</li> </ul> <p>Tom Lane a commit&eacute;&nbsp;:</p> <ul> <li>Add the ability to store inheritance-tree statistics in pg_statistic, and teach ANALYZE to compute such stats for tables that have subclasses. Per my proposal of yesterday. autovacuum still needs to be taught about running ANALYZE on parent tables when their subclasses change, but the feature is useful even without that.</li> <li>Add an index on pg_inherits.inhparent, and use it to avoid seqscans in find_inheritance_children(). This is a complete no-op in databases without any inheritance. In databases where there are just a few entries in pg_inherits, it could conceivably be a small loss. However, in databases with many inheritance parents, it can be a big win.</li> <li>Set errno to zero before invoking SSL_read or SSL_write. It appears that at least in some Windows versions, these functions are capable of returning a failure indication without setting errno. That puts us into an infinite loop if the previous value happened to be EINTR. Per report from Brendan Hill. Back-patch to 8.2. We could take it further back, but since this is only known to be an issue on Windows and we don't support Windows before 8.2, it does not seem worth the trouble.</li> <li>Revise pgstat's tracking of tuple changes to improve the reliability of decisions about when to auto-analyze. The previous code depended on n_live_tuples + n_dead_tuples - last_anl_tuples, where all three of these numbers could be bad estimates from ANALYZE itself. Even worse, in the presence of a steady flow of HOT updates and matching HOT-tuple reclamations, auto-analyze might never trigger at all, even if all three numbers are exactly right, because n_dead_tuples could hold steady. To fix, replace last_anl_tuples with an accurately tracked count of the total number of committed tuple inserts + updates + deletes since the last ANALYZE on the table. This can still be compared to the same threshold as before, but it's much more trustworthy than the old computation. Tracking this requires one more intra-transaction counter per modified table within backends, but no additional memory space in the stats collector. There probably isn't any measurable speed difference; if anything it might be a bit faster than before, since I was able to eliminate some per-tuple arithmetic operations in favor of adding sums once per (sub)transaction. Also, simplify the logic around pgstat vacuum and analyze reporting messages by not trying to fold VACUUM ANALYZE into a single pgstat message. The original thought behind this patch was to allow scheduling of analyzes on parent tables by artificially inflating their changes_since_analyze count. I've left that for a separate patch since this change seems to stand on its own merit.</li> <li>In pgsql/src/backend/commands/analyze.c, dept of second thoughts: recursive case in ANALYZE shouldn't emit a pgstats message. This might need to be done differently later, but with the current logic that's what should happen.</li> <li>Redefine Datum as uintptr_t, instead of unsigned long. This is more in keeping with modern practice, and is a first step towards porting to Win64 (which has sizeof(pointer) &gt; sizeof(long)). Tsutomu Yamada, Magnus Hagander, Tom Lane.</li> <li>In pgsql/src/backend/storage/ipc/procarray.c, add missing 'static' tag.</li> <li>In pgsql/src/backend/storage/ipc/standby.c, suppress compiler warning (pid_t isn't int everywhere).</li> <li>In pgsql/src/backend/libpq/auth.c, suppress compiler warning, per buildfarm member narwhal.</li> <li>Support "x IS NOT NULL" clauses as indexscan conditions. This turns out to be just a minor extension of the previous patch that made "x IS NULL" indexable, because we can treat the IS NOT NULL condition as if it were "x &lt; NULL" or "x &gt; NULL" (depending on the index's NULLS FIRST/LAST option), just like IS NULL is treated like "x = NULL". Aside from any possible usefulness in its own right, this is an important improvement for index-optimized MAX/MIN aggregates: it is now reliably possible to get a column's min or max value cheaply, even when there are a lot of nulls cluttering the interesting end of the index.</li> <li>Add an "argisrow" field to NullTest nodes, following a plan made way back in 8.2beta but never carried out. This avoids repetitive tests of whether the argument is of scalar or composite type. Also, be a bit more paranoid about composite arguments in some places where we previously weren't checking.</li> <li>check_exclusion_constraint didn't actually work correctly for index expressions: FormIndexDatum requires the estate's scantuple to already point at the tuple the values are supposedly being extracted from. Adjust test case so that this type of confusion will be exposed. Per report from hubert depesz lubaczewski.</li> <li>In pgsql/src/backend/utils/adt/regexp.c, fix similar_escape() to convert parentheses to non-capturing style. This is needed to avoid unwanted interference with SUBSTRING behavior, as per bug #5257 from Roman Kononov. Also, add some basic intelligence about character classes (bracket expressions) since we now have several behaviors that aren't appropriate inside a character class. As with the previous patch in this area, I'm reluctant to back-patch since it might affect applications that are relying on the prior behavior.</li> <li>In pgsql/src/bin/psql/tab-complete.c, add missing schema-qualification in tab completion query.</li> <li>In pgsql/src/backend/access/nbtree/nbtutils.c, dept of second thoughts: my first cut at supporting "x IS NOT NULL" btree indexscans would do the wrong thing if index_rescan() was called with a NULL instead of a new set of scankeys and the index was DESC order, because sk_strategy would not get flipped a second time. I think that those provisions for a NULL argument are dead code now as far as the core backend goes, but possibly somebody somewhere is still using it. In any case, this refactoring seems clearer, and it's definitely shorter.</li> </ul> <p>Robert Haas a commit&eacute;&nbsp;:</p> <ul> <li>Reject invalid input in int2vectorin. Since the int2vector type is intended only for internal use, this patch doesn't worry about prettifying the error messages, which has the fringe benefit of avoiding creating additional translatable strings. For a type intended to be used by end-users, we would want to do better, but the approach taken here seems like the correct trade-off for this case. Caleb Welton.</li> </ul> <p>Heikki Linnakangas a commit&eacute;&nbsp;:</p> <ul> <li>In pgsql/src/backend/access/transam/xlog.c, reset minRecoveryPoint at checkpoints, so that we don't uselessly update it in the control file at crash recovery following an archive recovery. Per Fujii Masao and subsequent discussion.</li> </ul> <p>Magnus Hagander a commit&eacute;&nbsp;:</p> <ul> <li>In pgsql/src/tools/msvc/Solution.pm, exclude part of the product name string that is localized in Japanese versions of MSVC when detecting MSVC version. Hiroshi Inoue.</li> <li>In pgsql/src/port/win32env.c, make the win32 putenv() override update *all* present versions of the MSVCRxx runtime, not just the current + Visual Studio 6 (MSVCRT). Clearly there can be an almost unlimited number of runtimes loaded at the same time. Per report from Hiroshi Inoue.</li> <li>In pgsql/src/tools/msvc/clean.bat, delete solution cache file on clean, if it exists.</li> <li>Detect a 64-bit build environment on Windows, and generate the appropriate project files. Based on the work of Tsutomu Yamada, but much refactored.</li> <li>In pgsql/src/backend/utils/mmgr/aset.c, silence compiler warning on 64-bit windows build</li> <li>In pgsql/src/port/open.c, fix cast for _open_osfhandle(). Tsutomu Yamada.</li> <li>Fix one more cast for _open_osfhandle(). Tsutomu Yamada.</li> <li>Support 64-bit shared memory when building on 64-bit Windows. Tsutomu Yamada</li> <li>In pgsql/src/include/pg_config.h.win32, set proper sizes for size_t and void* on 64-bit Windows builds. Tsutomu Yamada.</li> <li>In pgsql/src/tools/msvc/Project.pm, silence compiler warning about size of size_t being larger than the result variable it's stored in. We know this can never happen. Per discussion.</li> <li>In pgsql/src/include/port/win32.h, make ssize_t 64-bit on Win64, for compatibility with for example plpython.</li> </ul> <p>Andrew Dunstan a commit&eacute;&nbsp;:</p> <ul> <li>Enable examples to compile on Windows. Patch from Hiroshi Saito.</li> </ul> <p>ITAGAKI Takahiro a commit&eacute;&nbsp;:</p> <ul> <li>In pgsql/src/backend/port/win32_shmem.c, silence compiler warning about printf format for HANDLE.</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>ITAGAKI Takahiro sent in a patch to make pg_bench error out better on bad variable names.</li> <li>Robert Haas sent in another revision of the patch to enable setting random_page_cost and seq_page_cost at the tablespace level.</li> <li>Zoltan Boszormenyi sent in a patch to add row counts to SELECT INTO and CREATE TABLE AS [query].</li> <li>Andreas Freund sent in a patch to delay fsync in database creation, speeding up that operation.</li> <li>Mark Cave-Ayland sent in two revisions of a patch to add subdirectory support to DATA and DOCS in PGXS.</li> <li>Hitoshi Harada sent in a patch to move the declaration of IntArray from c.h to execQual.c, the only place it is currently used.</li> <li>Pavel Stehule sent in a patch to add quote_ident and quote_literal, using :"foo" and :'foo' respectively, to psql's variables.</li> <li>Tatsuo Ishii sent in two patches to correct an issue where pgpool was crashing PostgreSQL under certain conditions.</li> <li>Heikki Linnakangas sent in a patch in response to Fujii Masao's patch for streaming replication.</li> <li>Teodor Sigaev sent in another revision of the k-nearest-neighbor patch.</li> <li>Teodor Sigaev sent in an updated patch to add point operations to GiST. Robert Haas reviewed and revised this patch.</li> <li>Hitoshi Harada sent in a patch to add ROWS|RANGE n PRECEDING|FOLLOWING to windowing functions.</li> <li>Simon Riggs sent in a patch which expands pg_cancel_backend()'s functionality to allow it to cancel and idle transaction.</li> <li>Gurjeet Singh sent in a patch to re-allow SET ROLE in SECURITY DEFINER functions.</li> <li>Simon Riggs sent in a patch to implement recovery conflict signalling using SIGUSR1 multiplexing, then uses a SessionCancelPending mode similar to Joachim Wieland's TransactionCancelPending patch.</li> <li>John Naylor sent in another revision of the patch to refactor BKI.</li> <li>Magnus Hagander sent in a patch to fix an issue in win64 where socket definitions are not declared correctly.</li> <li>KaiGai Kohei sent in another revision of the patch to disallow ALTER COLUMN...RENAME TO... in the case where a column name is inherited from multiple relations.</li> <li>Magnus Hagander sent in a patch to change to config.pl processing in the msvc build environment.</li> <li>David Fetter sent in a patch to add tab completion for DO blocks in psql.</li> <li>ITAGAKI Takahiro sent in another revision of the patch to check correctness of variable names in pgbench.</li> </ul>