PostgreSQL 10 Release Candidate 1 est disponible, à vos tests ! https://www.postgresql.org/about/news/1783/

[ndt: MeetUp à Lille le 17 octobre :https://www.meetup.com/fr-FR/Meetup-PostgreSQL-Lille/]

Les nouveautés des produits dérivés

Offres d'emplois autour de PostgreSQL en septembre

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/20170925030820.GA18035@fetter.org

Submit news and announcements by Sunday at 3:00pm EST5EDT. Please send English language ones to david@fetter.org, German language to pwn@pgug.de, Italian language to pwn@itpug.org.

Correctifs appliqués

Peter Eisentraut pushed:

Tom Lane pushed:

  • Fix, or at least ameliorate, bugs in logicalrep_worker_launch(). If we failed to get a background worker slot, the code just walked away from the logicalrep-worker slot it already had, leaving that looking like the worker is still starting up. This led to an indefinite hang in subscription startup, as reported by Thomas Munro. We must release the slot on failure. Also fix a thinko: we must capture the worker slot's generation before releasing LogicalRepWorkerLock the first time, else testing to see if it's changed is pretty meaningless. BTW, the CHECK_FOR_INTERRUPTS() in WaitForReplicationWorkerAttach is a ticking time bomb, even without considering the possibility of elog(ERROR) in one of the other functions it calls. Really, this entire business needs a redesign with some actual thought about error recovery. But for now I'm just band-aiding the case observed in testing. Back-patch to v10 where this code was added. Discussion: https://postgr.es/m/CAEepm=2bP3TBMFBArP6o20AZaRduWjMnjCjt22hSdnA-EvrtCw@mail.gmail.com https://git.postgresql.org/pg/commitdiff/3e1683d37e1d751eb2df9a5cb0507bebc6cf7d05
  • Make DatumGetFoo/PG_GETARG_FOO/PG_RETURN_FOO macro names more consistent. By project convention, these names should include "P" when dealing with a pointer type; that is, if the result of a GETARG macro is of type FOO *, it should be called PG_GETARG_FOO_P not just PG_GETARG_FOO. Some newer types such as JSONB and ranges had not followed the convention, and a number of contrib modules hadn't gotten that memo either. Rename the offending macros to improve consistency. In passing, fix a few places that thought PG_DETOAST_DATUM() returns a Datum; it does not, it returns "struct varlena *". Applying DatumGetPointer to that happens not to cause any bad effects today, but it's formally wrong. Also, adjust an ltree macro that was designed without any thought for what pgindent would do with it. This is all cosmetic and shouldn't have any impact on generated code. Mark Dilger, some further tweaks by me Discussion: https://postgr.es/m/EA5676F4-766F-4F38-8348-ECC7DB427C6A@gmail.com https://git.postgresql.org/pg/commitdiff/4bd1994650fddf49e717e35f1930d62208845974
  • Make ExplainOpenGroup and ExplainCloseGroup public. Extensions with custom plan nodes might like to use these in their EXPLAIN output. Hadi Moshayedi Discussion: https://postgr.es/m/CA+_kT_dU-rHCN0u6pjA6bN5CZniMfD=-wVqPY4QLrKUY_uJq5w@mail.gmail.com https://git.postgresql.org/pg/commitdiff/66917bfaa7bb0b6bae52a5fe631a8b6443203f55
  • Minor code-cleanliness improvements for btree. Make the btree page-flags test macros (P_ISLEAF and friends) return clean boolean values, rather than values that might not fit in a bool. Use them in a few places that were randomly referencing the flag bits directly. In passing, change access/nbtree/'s only direct use of BUFFER_LOCK_SHARE to BT_READ. (Some think we should go the other way, but as long as we have BT_READ/BT_WRITE, let's use them consistently.) Masahiko Sawada, reviewed by Doug Doole Discussion: https://postgr.es/m/CAD21AoBmWPeN=WBB5Jvyz_Nt3rmW1ebUyAnk3ZbJP3RMXALJog@mail.gmail.com https://git.postgresql.org/pg/commitdiff/eb5c404b17752ca566947f12cb702438dcccdcb1
  • Cache datatype-output-function lookup info across calls of concat(). Testing indicates this can save a third to a half of the runtime of the function. Pavel Stehule, reviewed by Alexander Kuzmenkov Discussion: https://postgr.es/m/CAFj8pRAT62pRgjoHbgTfJUc2uLmeQ4saUj+yVJAEZUiMwNCmdg@mail.gmail.com https://git.postgresql.org/pg/commitdiff/ed22fb8b0091deea23747310fa7609079a96cf82
  • Remove no-op GiST support functions in the core GiST opclasses. The preceding patch allowed us to remove useless GiST support functions. This patch actually does that for all the no-op cases in the core GiST code. This buys us whatever performance gain is to be had, and more importantly exercises the preceding patch. There remain no-op functions in the contrib GiST opclasses, but those will take more work to remove. Discussion: https://postgr.es/m/CAJEAwVELVx9gYscpE=Be6iJxvdW5unZ_LkcAaVNSeOwvdwtD=A@mail.gmail.com https://git.postgresql.org/pg/commitdiff/2d484f9b058035d41204f2eb8a0a8d2e8ee57b44
  • Allow no-op GiST support functions to be omitted. There are common use-cases in which the compress and/or decompress functions can be omitted, with the result being that we make no data transformation when storing or retrieving index values. Previously, you had to provide a no-op function anyway, but this patch allows such opclass support functions to be omitted. Furthermore, if the compress function is omitted, then the core code knows that the stored representation is the same as the original data. This means we can allow index-only scans without requiring a fetch function to be provided either. Previously you had to provide a no-op fetch function if you wanted IOS to work. This reportedly provides a small performance benefit in such cases, but IMO the real reason for doing it is just to reduce the amount of useless boilerplate code that has to be written for GiST opclasses. Andrey Borodin, reviewed by Dmitriy Sarafannikov Discussion: https://postgr.es/m/CAJEAwVELVx9gYscpE=Be6iJxvdW5unZ_LkcAaVNSeOwvdwtD=A@mail.gmail.com https://git.postgresql.org/pg/commitdiff/d3a4f89d8a3e500bd7c0b7a8a8a5ce1b47859128
  • Fix erroneous documentation about noise word GROUP. GRANT, REVOKE, and some allied commands allow the noise word GROUP before a role name (cf. grantee production in gram.y). This option does not exist elsewhere, but it had nonetheless snuck into the documentation for ALTER ROLE, ALTER USER, and CREATE SCHEMA. Seems to be a copy-and-pasteo in commit 31eae6028, which did expand the syntax choices here, but not in that way. Back-patch to 9.5 where that came in. Discussion: https://postgr.es/m/20170916123750.8885.66941@wrigleys.postgresql.org https://git.postgresql.org/pg/commitdiff/36b564c648a044e42ca461466ae14d8588e6c5e2
  • Fix instability in subscription regression test. 005_encoding.pl neglected to wait for the subscriber's initial synchronization to happen. While we have not seen this fail in the buildfarm, it's pretty easy to demonstrate there's an issue by hacking logicalrep_worker_launch() to fail most of the time. Michael Paquier Discussion: https://postgr.es/m/27032.1505749806@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/4939488af9b86edfff9b981773cd388d361c5830
  • Improve dubious memory management in pg_newlocale_from_collation(). pg_newlocale_from_collation() used malloc() and strdup() directly, which is generally not per backend coding style, and it didn't bother to check for failure results, but would just SIGSEGV instead. Also, if one of the numerous error checks in the middle of the function failed, the already-allocated memory would be leaked permanently. Admittedly, it's not a lot of memory, but it could build up if this function were called repeatedly for a bad collation. The first two problems are easily cured by palloc'ing in TopMemoryContext instead of calling libc directly. We can fairly easily dodge the leakage problem for the struct pg_locale_struct by filling in a temporary variable and allocating permanent storage only once we reach the bottom of the function. It's harder to get rid of the potential leakage for ICU's copy of the collcollate string, but at least that's only allocated after most of the error checks; so live with that aspect. Back-patch to v10 where this code came in, with one or another of the ICU patches. https://git.postgresql.org/pg/commitdiff/7b86c2ac9563ffd9b870cfd73a769431b7922e81
  • Give a better error for duplicate entries in VACUUM/ANALYZE column list. Previously, the code didn't think about this case and would just try to analyze such a column twice. That would fail at the point of inserting the second version of the pg_statistic row, with obscure error messsages like "duplicate key value violates unique constraint" or "tuple already updated by self", depending on context and PG version. We could allow the case by ignoring duplicate column specifications, but it seems better to reject it explicitly. The bogus error messages seem like arguably a bug, so back-patch to all supported versions. Nathan Bossart, per a report from Michael Paquier, and whacked around a bit by me. Discussion: https://postgr.es/m/E061A8E3-5E3D-494D-94F0-E8A9B312BBFC@amazon.com https://git.postgresql.org/pg/commitdiff/71480501057fee9fa3649b072173ff10e2b842d0
  • Revert "Fix bool/int type confusion". This reverts commit 0ec2e908babfbfde83a3925680f06b16408739ff. We'll use the upstream (IANA) fix instead. https://git.postgresql.org/pg/commitdiff/a890432a872afc9ca2327573f3313fd994d17384
  • Sync our copy of the timezone library with IANA tzcode master. This patch absorbs a few unreleased fixes in the IANA code. It corresponds to commit 2d8b944c1cec0808ac4f7a9ee1a463c28f9cd00a in https://github.com/eggert/tz. Non-cosmetic changes include: TZDEFRULESTRING is updated to match current US DST practice, rather than what it was over ten years ago. This only matters for interpretation of POSIX-style zone names (e.g., "EST5EDT"), and only if the timezone database doesn't include either an exact match for the zone name or a "posixrules" entry. The latter should not be true in any current Postgres installation, but this could possibly matter when using --with-system-tzdata. Get rid of a nonportable use of "++var" on a bool var. This is part of a larger fix that eliminates some vestigial support for consecutive leap seconds, and adds checks to the "zic" compiler that the data files do not specify that. Remove a couple of ancient compatibility hacks. The IANA crew think these are obsolete, and I tend to agree. But perhaps our buildfarm will think different. Back-patch to all supported branches, in line with our policy that all branches should be using current IANA code. Before v10, this includes application of current pgindent rules, to avoid whitespace problems in future back-patches. Discussion: https://postgr.es/m/E1dsWhf-0000pT-F9@gemulon.postgresql.org https://git.postgresql.org/pg/commitdiff/47f849a3c9005852926dca551d70ad8111f09f3a
  • Assume wcstombs(), towlower(), and sibling functions are always present. These functions are required by SUS v2, which is our minimum baseline for Unix platforms, and are present on all interesting Windows versions as well. Even our oldest buildfarm members have them. Thus, we were not testing the "!USE_WIDE_UPPER_LOWER" code paths, which explains why the bug fixed in commit e6023ee7f escaped detection. Per discussion, there seems to be no more real-world value in maintaining this option. Hence, remove the configure-time tests for wcstombs() and towlower(), remove the USE_WIDE_UPPER_LOWER symbol, and remove all the !USE_WIDE_UPPER_LOWER code. There's not actually all that much of the latter, but simplifying the #if nests is a win in itself. Discussion: https://postgr.es/m/20170921052928.GA188913@rfd.leadboat.com https://git.postgresql.org/pg/commitdiff/85feb77aa09cda9ff3e12cf95c757c499dc25343
  • Mop-up for commit 85feb77aa09cda9ff3e12cf95c757c499dc25343. Adjust commentary in regc_pg_locale.c to remove mention of the possibility of not having <wctype.h> functions, since we no longer consider that. Eliminate duplicate code in wparser_def.c by generalizing the p_iswhat macro to take a parameter saying what to return for non-ASCII chars in C locale. (That's not really a consequence of the USE_WIDE_UPPER_LOWER-ectomy, but I noticed it while doing that.) https://git.postgresql.org/pg/commitdiff/ed87e1980706975e7aa412bee200087774c5ff22
  • Allow up to 3 "-P 1" reports per thread in pgbench run of 2 seconds. There seems to be some considerable imprecision in the timing of -P progress reports. Nominally each thread ought to produce 2 reports in this test, but about 10% of the time we only get one, and 1% of the time we get three, as per buildfarm results so far. Pending further investigation, treat the last case as a "pass". (I, tgl, am suspicious that this still might not be lax enough, now that it's obvious that the behavior is load-dependent; but there's not yet buildfarm evidence to confirm that suspicion.) Fabien Coelho Discussion: https://postgr.es/m/26654.1505232433@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/0f574a7afb5c998d19dc3d981e45cb10267286ed
  • Ten-second timeout in 013_crash_restart.pl is not enough, let's try 60. Per buildfarm member topminnow. https://git.postgresql.org/pg/commitdiff/01c7d3ef85d4b0e1c52cc1a3542864f95f386f76
  • Improve memory management in autovacuum.c. Invoke vacuum(), as well as "work item" processing, in the PortalContext that do_autovacuum() has manufactured, which will be reset before each such invocation. This ensures cleanup of any memory leaked by these operations. It also avoids the rather dangerous practice of calling vacuum() in a context that vacuum() itself will destroy while it runs. There's no known live bug there, but it's not hard to imagine introducing one if we leave it like this. Tom Lane, reviewed by Michael Paquier and Alvaro Herrera Discussion: https://postgr.es/m/13849.1506114543@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/335f3d04e4c8dd495c4dd30ab1049b6fe8f25052
  • Fix bogus size calculation in strlist_to_textarray(). It's making an array of Datum, not an array of text *. The mistake is harmless since those are currently the same size, but it's still wrong. https://git.postgresql.org/pg/commitdiff/737639017c87d5a0a466e8676f1eadc61d775c78
  • ... and the very same bug in publicationListToArray(). Sigh. https://git.postgresql.org/pg/commitdiff/24541ffd788d56009126fff52b2341ada6c84245
  • Remove pgbench "progress" test pending solution of its timing issues. Buildfarm member skink shows that this is even more flaky than I thought. There are probably some actual pgbench bugs here as well as a timing dependency. But we can't have stuff this unstable in the buildfarm, it obscures other issues. https://git.postgresql.org/pg/commitdiff/ad51c6fb5708342e603d12a730bbc4e663bd637e
  • Fix assorted infelicities in new SetWALSegSize() function. * Failure to check for malloc failure (ok, pretty unlikely here, but that's not an excuse). * Leakage of open fd on read error, and of malloc'd buffer always. * Incorrect assumption that a short read would set errno to zero. * Failure to adhere to message style conventions (in particular, not reporting errno where relevant; using "couldn't open" rather than "could not open" is not really in line with project style either). * Missing newlines on some messages. Coverity spotted the leak problems; I noticed the rest while fixing the leaks. https://git.postgresql.org/pg/commitdiff/8485a25a8c9a419ff3e0d30e43e4abd5e680cc65

Andres Freund pushed:

Andrew Dunstan pushed:

Bruce Momjian pushed:

Magnus Hagander pushed:

  • Mention need for --no-inc-recursive in rsync command. Since rsync 3.0.0 (released in 2008), the default way to enumerate changes was changed in a way that makes it less likely that the hardlink sync mode works. Since the whole point of the documented procedure is for the hardlinks to work, change our docs to suggest using the backwards compatibility switch. https://git.postgresql.org/pg/commitdiff/2c74e6c1dcc5002fa8b822e5757f6c95d899fb7a

Robert Haas pushed:

�lvaro Herrera pushed:

Correctifs en attente

Rushabh Lathia sent in another revision of a patch to fix an error that manifested as GatherMerge failing to push a target list down.

Jesper Pedersen sent in another revision of a patch to implement hash partitioning.

Oliver Ford sent in another revision of a patch to add Roman numeral conversion to to_number.

Dilip Kumar sent in another revision of a patch to prune partitions more efficiently in queries.

Ashutosh Bapat sent in a patch to skip "dead" relations in create_lateral_join_info().

Jeevan Chalke sent in another revision of a patch to implement partition-wise aggregation/grouping for declaratively partitioned tables.

Kyotaro HORIGUCHI sent in another revision of a patch to fix a race condition between SELECT and ALTER TABLE NO INHERIT.

Noah Misch sent in a patch to change the release notes to do much less invasive surgery on the information_schema.table_privileges view, replacing it rather than re-creating the information schema whole cloth.

Micha�l Paquier sent in another revision of a patch to remove ALLOW_DANGEROUS_LO_FUNCTIONS for LO-related superuser checks, replace superuser checks of large object import/export by ACL checks, remove ACL checks for large objects when opening them.

Haribabu Kommi sent in another revision of a patch to add the support of storage handler as an access method, add storage AM API hooks and related functions, add storageam handler to the relation structure, add a tuple visibility function, slot hooks, a tuple insert API, and scan functions to the storage AM, and remove HeapScanDesc uses outside heap-specific code.

Rushabh Lathia sent in another revision of a patch to add parallel B-tree index build sorting.

Jeff Janes sent in another revision of a patch to ensure that parallel paths include target list cost.

Thomas Munro sent in another revision of a patch to make SERIALIZABLE isolation work with parallel operations.

Masahiko Sawada sent in three more revisions of a patch to enable custom initialization for pgbench.

Ashutosh Bapat sent in two more revisions of a patch to implement partition-wise join for joins between (declaratively) partitioned tables.

Micha�l Paquier sent in another revision of a patch to rewrite tests of pg_upgrade as TAP tests.

Craig Ringer sent in a patch to add a utility class for value and identifier quoting for use in TAP tests.

Vaishnavi Prabakaran sent in a patch to correct the debug log to reflect the change of function name from lo_open to be_lo_open.

Amit Khandekar sent in another revision of a patch to implement parallel append.

Andres Freund sent in a patch to add the ability to force libpq to negotiate a specific version of the protocol and add minimal v2 protocol regression tests based on same.

Alexander Korotkov sent in two more revisions of a patch to add SP-GiST compress methods, and GiST support for circles and polygons.

Masahiko Sawada sent in three revisions of a patch to fix an assertion failure when the non-exclusive pg_stop_backup aborted.

Amul Sul sent in a patch to only handle oid sysattr, to reduce overhead in a patch set intended to improve syscache/catcache performance.

Rajkumar Raghuwanshi sent in a patch to add some tests for partition-wise join between declaratively partitioned tables.

Thomas Munro sent in a shell program which shows which blocks of SQL hit which lines of C.

Peter Eisentraut sent in another revision of a patch to run only top-level recursive lcov, have lcov exclude external files, add lcov --initial, add the PostgreSQL version to coverage output, remove the coverage details view, run coverage commands quietly, improve vpath support in the plperl build, support coverage on vpath builds, and exclude flex-generated code from coverage testing.

Ashutosh Sharma sent in three more revisions of a patch to rewrite hash index scan to work page at a time, remove redundant hash function _hash_step and do some code cleanup, and improve the locking startegy during VACUUM in hash index for regular tables.

Lucas Fittl sent in a patch to only skip query cancel itself when query cancel holdoff count is positive.

Rafia Sabih sent in another revision of a patch to make gather faster.

Tom Lane sent in another revision of a patch to support arrays of domains.

Kyotaro HORIGUCHI sent in a patch to fix a misbehavior of hash indexes on unlogged tables.

Julien Rouhaud sent in two more revisions of a patch to implement sorted append.

Nathan Bossart sent in three more revisions of a patch to enable specifying multiple tables in VACUUM commands.

Nikita Glukhov sent in a patch to fix a circle bounding box inconsistency in the GiST compress method and fix GiST ordering by distance for NULLs and NaNs.

Kyotaro HORIGUCHI sent in a patch to fix an issue where analyzeCTE is too strict about typmods.

Amit Khandekar sent in another revision of a patch to enable UPDATEs of a partition key which move the row to another partition.

Micha�l Paquier sent in a patch to document new wait events.

Andres Freund sent in another revision of a patch to improve sys/catcache performance.

Yura Sokolov sent in a patch to skip scanning a btree index if no pending deleted pages exists.

Chen Huajun sent in another revision of a patch to make pg_rewind to not copy useless WAL files.

Robert Haas sent in a patch to skip LSN checks during hash index scans.

Peter Eisentraut sent in a patch to expand the collation documentation and add test cases for the new advanced ICU features.

Fabien COELHO sent in a patch to pgbench to use enum to store its meta commands.

Yura Sokolov sent in another revision of a patch to improve compactify_tuples.

Fabien COELHO sent in another revision of a patch to add server version information to psql.

Tom Lane sent in a patch to make construct_[md_]array() handle empty arrays correctly.

Andrew Dunstan sent in two more revisions of a patch to fix an infelicity in the enum type.

Amit Kapila sent in another revision of a patch to change metapage usage for B-tree and hash indexes.

Fabien COELHO sent in another revision of a patch to fix a pgbench regression test failure.

Thomas Munro sent in a patch to improve the LDAP cleanup code in error paths, log diagnostic messages if errors occur during LDAP auth, and add a regression test to log an LDAP diagnostic message.

Mark Rofail sent in another revision of a patch to add foreign key arrays.

Tomas Vondra sent in another revision of a patch to make VACUUM and ANALYZE agree on what reltuples means.

Erik Rijkers sent in a patch to improve some comments in prepunion.c.

Tomas Vondra sent in another revision of a patch to implement a generational memory allocator.

Thomas Munro sent in another revision of a patch to use fallocate() to preallocate "posix" DSM segments on Linux.