PostgreSQL@SCaLE est un événement de 2 jours à double programmes qui aura lieu les 2 & 3 mars 2017 au centre de convention de Pasadena, intégré au SCaLE 15X. L'appel à conférenciers court jusqu'au 15 novembre 2016 : http://www.socallinuxexpo.org/scale/15x/cfp

Les nouveautés des produits dérivés

Offres d'emplois autour de PostgreSQL en octobre

PostgreSQL Local

PostgreSQL dans les média

PostgreSQL Weekly News / les nouvelles hebdomadaires vous sont offertes cette semaine par David Fetter. Traduction par l'équipe PostgreSQLFr sous licence CC BY-NC-SA. La version originale se trouve à l'adresse suivante : http://www.postgresql.org/message-id/20161023230118.GA30097@fetter.org

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 et en espagnol à pwn (a) arpug.com.ar.

Correctifs appliqués

  • Replace PostmasterRandom() with a stronger way of generating randomness. This adds a new routine, pg_strong_random() for generating random bytes, for use in both frontend and backend. At the moment, it's only used in the backend, but the upcoming SCRAM authentication patches need strong random numbers in libpq as well. pg_strong_random() is based on, and replaces, the existing implementation in pgcrypto. It can acquire strong random numbers from a number of sources, depending on what's available: - OpenSSL RAND_bytes(), if built with OpenSSL - On Windows, the native cryptographic functions are used - /dev/urandom - /dev/random Original patch by Magnus Hagander, with further work by Michael Paquier and me. Discussion: <CAB7nPqRy3krN8quR9XujMVVHYtXJ0_60nqgVc6oUk8ygyVkZsA@mail.gmail.com> http://git.postgresql.org/pg/commitdiff/9e083fd4683294f41544e6d0d72f6e258ff3a77c
  • Fix use-after-free around DISTINCT transition function calls. Have tuplesort_gettupleslot() copy the contents of its current table slot as needed. This is based on an approach taken by tuplestore_gettupleslot(). In the future, tuplesort_gettupleslot() may also be taught to avoid copying the tuple where caller can determine that that is safe (the tuplestore_gettupleslot() interface already offers this option to callers). Patch by Peter Geoghegan. Fixes bug #14344, reported by Regina Obe. Report: <20160929035538.20224.39628@wrigleys.postgresql.org> Backpatch-through: 9.6 http://git.postgresql.org/pg/commitdiff/d8589946ddd5c43e1ebd01c5e92d0e177cbfc198
  • Revert "Replace PostmasterRandom() with a stronger way of generating randomness." This reverts commit 9e083fd4683294f41544e6d0d72f6e258ff3a77c. That was a few bricks shy of a load: * Query cancel stopped working * Buildfarm member pademelon stopped working, because the box doesn't have /dev/urandom nor /dev/random. This clearly needs some more discussion, and a quite different patch, so revert for now. http://git.postgresql.org/pg/commitdiff/faae1c918e8aaae034eaf3ea103fcb6ba9adc5ab
  • Fix WAL-logging of FSM and VM truncation. When a relation is truncated, it is important that the FSM is truncated as well. Otherwise, after recovery, the FSM can return a page that has been truncated away, leading to errors like: ERROR: could not read block 28991 in file "base/16390/572026": read only 0 of 8192 bytes We were using MarkBufferDirtyHint() to dirty the buffer holding the last remaining page of the FSM, but during recovery, that might in fact not dirty the page, and the FSM update might be lost. To fix, use the stronger MarkBufferDirty() function. MarkBufferDirty() requires us to do WAL-logging ourselves, to protect from a torn page, if checksumming is enabled. Also fix an oversight in visibilitymap_truncate: it also needs to WAL-log when checksumming is enabled. Analysis by Pavan Deolasee. Discussion: <CABOikdNr5vKucqyZH9s1Mh0XebLs_jRhKv6eJfNnD2wxTn=_9A@mail.gmail.com> http://git.postgresql.org/pg/commitdiff/917dc7d2393ce680dea7a59418be9ff341df3c14
  • Use OpenSSL EVP API for symmetric encryption in pgcrypto. The old "low-level" API is deprecated, and doesn't support hardware acceleration. And this makes the code simpler, too. Discussion: <561274F1.1030000@iki.fi> http://git.postgresql.org/pg/commitdiff/5ff4a67f63fd6d3eb01ff9707d4674ed54a89f3b

Robert Haas pushed:

  • By default, set log_line_prefix = '%m [%p] '. This value might not be to everyone's taste; in particular, some people might prefer %t to %m, and others may want %u, %d, or other fields. However, it's a vast improvement on the old default of ''. Christoph Berg http://git.postgresql.org/pg/commitdiff/7d3235ba42f8d5fc70c58e242702cc5e2e3549a6
  • Fix typo in comment. Amit Langote http://git.postgresql.org/pg/commitdiff/fca41acb86902b90218dcc3bc0ffc462850a090f
  • Improve regression test coverage for hash indexes. On my system, this improves coverage for src/backend/access/hash from 61.3% of lines to 88.2% of lines, and from 83.5% of functions to 97.5% of functions, which is pretty good for 36 lines of tests. Mithun Cy, reviewing by Amit Kapila and Álvaro Herrera http://git.postgresql.org/pg/commitdiff/b801e120080de836b834c1b756c4c4d81ce841b5
  • Remove a comment which is now incorrect. Before 5d305d86bd917723f09ab4f15c075d90586a210a, this comment was correct, but now it says we do something which we don't actually do. Accordingly, remove the comment. http://git.postgresql.org/pg/commitdiff/ec7db2b483e0ff247ed41612cdb5716022401fe6
  • ename "pg_xlog" directory to "pg_wal". "xlog" is not a particularly clear abbreviation for "write-ahead log", and it sometimes confuses users into believe that the contents of the "pg_xlog" directory are not critical data, leading to unpleasant consequences. So, rename the directory to "pg_wal". This patch modifies pg_upgrade and pg_basebackup to understand both the old and new directory layouts; the former is necessary given the purpose of the tool, while the latter merely avoids an unnecessary backward-compatibility break. We may wish to consider renaming other programs, switches, and functions which still use the old "xlog" naming to also refer to "wal". However, that's still under discussion, so let's do just this much for now. Discussion: CAB7nPqTeC-8+zux8_-4ZD46V7YPwooeFxgndfsq5Rg8ibLVm1A@mail.gmail.com Michael Paquier http://git.postgresql.org/pg/commitdiff/f82ec32ac30ae7e3ec7c84067192535b2ff8ec0e
  • postgres_fdw: Push down aggregates to remote servers. Now that the upper planner uses paths, and now that we have proper hooks to inject paths into the upper planning process, it's possible for foreign data wrappers to arrange to push aggregates to the remote side instead of fetching all of the rows and aggregating them locally. This figures to be a massive win for performance, so teach postgres_fdw to do it. Jeevan Chalke and Ashutosh Bapat. Reviewed by Ashutosh Bapat with additional testing by Prabhat Sahu. Various mostly cosmetic changes by me. http://git.postgresql.org/pg/commitdiff/7012b132d07c2b4ea15b0b3cb1ea9f3278801d98
  • Fix comment formatting. http://git.postgresql.org/pg/commitdiff/919c811ca1e2a545cb1db243af93d55270d84469
  • postgres_fdw: Attempt to stabilize regression results. Set enable_hashagg to false for tests involving least_agg(), so that we get the same plan regardless of local costing variances. Also, remove a test involving sqrt(); it's there to test deparsing of HAVING clauses containing expressions, but that's tested elsewhere anyway, and sqrt(2) deparses with different amounts of precision on different machines. Per buildfarm. http://git.postgresql.org/pg/commitdiff/ad13a09d762f0c903a52ed0ec668a0ba51a61047

Tom Lane pushed:

  • Fix cidin() to handle values above 2^31 platform-independently. CommandId is declared as uint32, and values up to 4G are indeed legal. cidout() handles them properly by treating the value as unsigned int. But cidin() was just using atoi(), which has platform-dependent behavior for values outside the range of signed int, as reported by Bart Lengkeek in bug #14379. Use strtoul() instead, as xidin() does. In passing, make some purely cosmetic changes to make xidin/xidout look more like cidin/cidout; the former didn't have a monopoly on best practice IMO. Neither xidin nor cidin make any attempt to throw error for invalid input. I didn't change that here, and am not sure it's worth worrying about since neither is really a user-facing type. The point is just to ensure that indubitably-valid inputs work as expected. It's been like this for a long time, so back-patch to all supported branches. Report: <20161018152550.1413.6439@wrigleys.postgresql.org> http://git.postgresql.org/pg/commitdiff/6f13a682c86801cfb9ae4f3126888b42f3cb5c46
  • Update time zone data files to tzdata release 2016g. DST law changes in Turkey. Historical corrections for America/Los_Angeles, Europe/Kirov, Europe/Moscow, Europe/Samara, and Europe/Ulyanovsk. Rename Asia/Rangoon to Asia/Yangon, with a backward compatibility link. The IANA crew continue their campaign to replace invented time zone abbrevations with numeric GMT offsets. This update changes numerous zones in Antarctica and the former Soviet Union, for instance Antarctica/Casey now reports "+08" not "AWST" in the pg_timezone_names view. I kept these abbreviations in the tznames/ data files, however, so that we will still accept them for input. (We may want to start trimming those files someday, but today is not that day.) An exception is that since IANA no longer claims that "AMT" is in use in Armenia for GMT+4, I replaced it in the Default file with GMT-4, corresponding to Amazon Time which is in use in South America. It may be that that meaning is also invented and IANA will drop it in a future update; but for now, it seems silly to give pride of place to a meaning not traceable to IANA over one that is. http://git.postgresql.org/pg/commitdiff/ecbac3e6e038e990f24a2e0eacdcd6738292105f
  • Suppress "Factory" zone in pg_timezone_names view for tzdata >= 2016g. IANA got rid of the really silly "abbreviation" and replaced it with one that's only moderately silly. But it's still pointless, so keep on not showing it. http://git.postgresql.org/pg/commitdiff/a3215431ab7c667bf581728f10c80a36abbe1d5a
  • Windows portability fix. Per buildfarm. http://git.postgresql.org/pg/commitdiff/ad90ac4d671d320ade3c127f215e97cd49c307fb
  • Sync our copy of the timezone library with IANA release tzcode2016g. This is mostly to absorb some corner-case fixes in zic for year-2037 timestamps. The other changes that have been made are unlikely to affect our usage, but nonetheless we may as well take 'em. http://git.postgresql.org/pg/commitdiff/f3094920a567cde6c86adf36a1a033d7431b11ff
  • Another portability fix for tzcode2016g update. clang points out that SIZE_MAX wouldn't fit into an int, which means this comparison is pretty useless. Per report from Thomas Munro. http://git.postgresql.org/pg/commitdiff/23ed2ba8121178474f8c51774c6c258cb165a562
  • Update time zone data files to tzdata release 2016h. (Didn't I just do this? Oh well.) DST law changes in Palestine. Historical corrections for Turkey. Switch to numeric abbreviations for Asia/Colombo. http://git.postgresql.org/pg/commitdiff/d8fc45bd0f62fcebac80c63840b753f8e3b737ff
  • Sync our copy of the timezone library with IANA release tzcode2016h. This absorbs a fix for a symlink-manipulation bug in zic that was introduced in 2016g. It probably isn't interesting for our use-case, but I'm not quite sure, so let's update while we're at it. http://git.postgresql.org/pg/commitdiff/5e21b6811148fdc1fce9dcdcdc777418cc901fe4
  • Fix EXPLAIN so that it doesn't emit invalid XML in corner cases. With track_io_timing = on, EXPLAIN (ANALYZE, BUFFERS) will emit fields named like "I/O Read Time". The slash makes that invalid as an XML element name, so that adding FORMAT XML would produce invalid XML. We already have code in there to translate spaces to dashes, so let's generalize that to convert anything that isn't a valid XML name character, viz letters, digits, hyphens, underscores, and periods. We could just reject slashes, which would run a bit faster. But the fact that this went unnoticed for so long doesn't give me a warm feeling that we'd notice the next creative violation, so let's make it a permanent fix. Reported by Markus Winand, though this isn't his initial patch proposal. Back-patch to 9.2 where track_io_timing was added. The problem is only latent in 9.1, so I don't feel a need to fix it there. Discussion: <E0BF6A45-68E8-45E6-918F-741FB332C6BB@winand.at> http://git.postgresql.org/pg/commitdiff/709e461befa8a4999c4ccdbfc7260ef8092e805c
  • Doc: wording tweak for PERL, PYTHON, TCLSH configuration variables. Replace "Full path to ..." with "Full path name of ...". At least one user has misinterpreted the existing wording as meaning "Directory containing ...". http://git.postgresql.org/pg/commitdiff/7aa2c10ac6785a2de683609b98da607e588a6d02
  • First-draft release notes for 9.6.1. As usual, the release notes for other branches will be made by cutting these down, but put them up for community review first. http://git.postgresql.org/pg/commitdiff/eacaf6e29fd2a3047aff9738a35a8e9b05e55375
  • Improve documentation about use of Linux huge pages. Show how to get the system's huge page size, rather than misleadingly referring to PAGE_SIZE (which is usually understood to be the regular page size). Show how to confirm whether huge pages have been allocated. Minor wordsmithing. Back-patch to 9.4 where this section appeared. http://git.postgresql.org/pg/commitdiff/1885c88459698251eca64f095d9942c540ba0fa8
  • Avoid testing tuple visibility without buffer lock in RI_FKey_check(). Despite the argumentation I wrote in commit 7a2fe85b0, it's unsafe to do this, because in corner cases it's possible for HeapTupleSatisfiesSelf to try to set hint bits on the target tuple; and at least since 8.2 we have required the buffer content lock to be held while setting hint bits. The added regression test exercises one such corner case. Unpatched, it causes an assertion failure in assert-enabled builds, or otherwise would cause a hint bit change in a buffer we don't hold lock on, which given the right race condition could result in checksum failures or other data consistency problems. The odds of a problem in the field are probably pretty small, but nonetheless back-patch to all supported branches. Report: <19391.1477244876@sss.pgh.pa.us> http://git.postgresql.org/pg/commitdiff/6292c2339186bac215bab5a1f01370f9735582c1

Andres Freund pushed:

Peter Eisentraut pushed:

Magnus Hagander pushed:

Correctifs en attente

Haribabu Kommi and Vinayak Pokale traded patches to add a pg_stat_sql system view.

Dilip Kumar sent in two more revisions of a patch to add parallel bitmap heap scan.

Masahiko Sawada sent in another revision of a patch to add quorum commit for multiple synchronous replication.

Kyotaro HORIGUCHI sent in another revision of a patch to implement asynchronous execution.

Laurenz Albe sent in a patch to add PGDLLEXPORT to a sample C function.

Ashutosh Bapat and Etsuro Fujita traded patches to ensure that altering a foreign table invalidates plans involving same.

Aleksander Alekseev sent in two revisions of a patch to enable logging the contents of COPY statements.

Heikki Linnakangas and Michaël Paquier traded patches to fix an FSM corruption leading to errors.

Gilles Darold sent in another revision of a patch to implement pg_current_logfile().

Dmitry Dolgov sent in another revision of a patch to implement generic type subscripting.

Michaël Paquier sent in two more revisions of a patch to implement SCRAM auth.

Rushabh Lathia sent in another revision of a patch to add Gather Merge.

Thom Brown sent in another revision of a patch to implement failover on the libpq connect level.

Jeevan Chalke and Ashutosh Bapat traded patches to implement aggregate pushdown.

Peter Geoghegan sent in a patch to fix ON CONFLICT bugs at higher isolation levels.

Oleksandr Shulgin sent in a patch to prevent psql from sending commands after a connection reset.

Vinayak Pokale sent in a patch to fix a typo in pgstat.h.

Vinayak Pokale sent in a patch to fix a typo in pgstat.c.

Constantin S. Pan and Michaël Paquier traded patches to fix the fact that there can be a lot of orphan temp tables.

Tomas Vondra sent in two more revisions of a patch to add two slab-like memory allocators.

Kyotaro HORIGUCHI sent in another revision of a patch to implement a radix tree for character conversion.

Masahiko Sawada sent in a patch to allow specifying the log file name of pgbench -l option.

Julien Rouhaud sent in a patch to fix the fact that when track_commit_timestamp is enabled, the oldestCommitTsXid and newestCommitTsXid don't persist after a server restart.

Bruce Momjian sent in a patch to mention pg_reload_conf() as a way to reload configurations in the docs.

Ashutosh Bapat sent in another revision of a patch to allow pushing down more FULL JOINs to the the PostgreSQL FDW.

Michaël Paquier sent in another revision of a patch to rename pg_clog to pg_xact and pg_subtrans to pg_subxact.