Versions correctives 9.6.1, 9.5.5, 9.4.10, 9.3.15, 9.2.19 et 9.1.24 disponibles. Mettez à jour dès que possible. La 9.1.24 est la dernière publication de la série 9.1 : https://www.postgresql.org/about/news/1712/

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/20161031023727.GE23627@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

Tom Lane pushed:

  • Don't throw serialization errors for self-conflicts in INSERT ON CONFLICT. A transaction that conflicts against itself, for example INSERT INTO t(pk) VALUES (1),(1) ON CONFLICT DO NOTHING; should behave the same regardless of isolation level. It certainly shouldn't throw a serialization error, as retrying will not help. We got this wrong due to the ON CONFLICT logic not considering the case, as reported by Jason Dusek. Core of this patch is by Peter Geoghegan (based on an earlier patch by Thomas Munro), though I didn't take his proposed code refactoring for fear that it might have unexpected side-effects. Test cases by Thomas Munro and myself. Report: <CAO3NbwOycQjt2Oqy2VW-eLTq2M5uGMyHnGm=RNga4mjqcYD7gQ@mail.gmail.com> Related-Discussion: <57EE93C8.8080504@postgrespro.ru> http://git.postgresql.org/pg/commitdiff/a6c0a5b6e8a9498540c6a7bb1b6d68958acc9bc6
  • Avoid testing tuple visibility without buffer lock. INSERT ... ON CONFLICT (specifically ExecCheckHeapTupleVisible) contains another example of this unsafe coding practice. It is much harder to get a failure out of it than the case fixed in commit 6292c2339, because in most scenarios any hint bits that could be set would have already been set earlier in the command. However, Konstantin Knizhnik reported a failure with a custom transaction manager, and it's clearly possible to get a failure via a race condition in async-commit mode. For lack of a reproducible example, no regression test case in this commit. I did some testing with Asserts added to tqual.c's functions, and can say that running "make check-world" exposed these two bugs and no others. The Asserts are messy enough that I've not added them to the code for now. Report: <57EE93C8.8080504@postgrespro.ru> Related-Discussion: <CAO3NbwOycQjt2Oqy2VW-eLTq2M5uGMyHnGm=RNga4mjqcYD7gQ@mail.gmail.com> http://git.postgresql.org/pg/commitdiff/8f1fb7d621b0e6bd2eb0ba2ac9634c5b5a03564b
  • Release notes for 9.6.1, 9.5.5, 9.4.10, 9.3.15, 9.2.19, 9.1.24. http://git.postgresql.org/pg/commitdiff/7d80417d3dfc88b0c03b5c08a18b29f9d430e217
  • Update release notes for last-minute commit timestamp fix. http://git.postgresql.org/pg/commitdiff/30a6f98ed8a2223ef876197b5dea0d94e5807b51
  • Suppress unused-variable warning in non-assert builds. Introduced in commit 7012b132d. Kyotaro Horiguchi http://git.postgresql.org/pg/commitdiff/8529686ccbb9f1c2fe350920ad324c223135a957
  • Doc: improve documentation about inheritance. Clarify documentation about inheritance of check constraints, in particular mentioning the NO INHERIT option, which didn't exist when this text was written. Document that in an inherited query, the applicable row security policies are those of the explicitly-named table, not its children. This is the intended behavior (per off-list discussion with Stephen Frost), and there are regression tests for it, but it wasn't documented anywhere user-facing as far as I could find. Do a bit of wordsmithing on the description of inherited access-privilege checks. Back-patch to 9.5 where RLS was added. http://git.postgresql.org/pg/commitdiff/162477a63d3c0fd1c31197717140a88077a8d0aa
  • Fix not-HAVE_SYMLINK code in zic.c. I broke this in commit f3094920a. Apparently it's dead code anyway, at least as far as our buildfarm is concerned (and the upstream IANA code doesn't worry at all about symlink() not being present). But as long as the rest of our code is willing to guard against not having symlink(), this should too. Noted while investigating a tangentially-related complaint from Sandeep Thakkar. Back-patch to keep branches in sync. http://git.postgresql.org/pg/commitdiff/19b2094d96807e43d29687b3860e8fffb9da61b4
  • Fix incorrect trigger-property updating in ALTER CONSTRAINT. The code to change the deferrability properties of a foreign-key constraint updated all the associated triggers to match; but a moment's examination of the code that creates those triggers in the first place shows that only some of them should track the constraint's deferrability properties. This leads to odd failures in subsequent exercise of the foreign key, as the triggers are fired at the wrong times. Fix that, and add a regression test comparing the trigger properties produced by ALTER CONSTRAINT with those you get by creating the constraint as-intended to begin with. Per report from James Parks. Back-patch to 9.4 where this ALTER functionality was introduced. Report: <CAJ3Xv+jzJ8iNNUcp4RKW8b6Qp1xVAxHwSXVpjBNygjKxcVuE9w@mail.gmail.com> http://git.postgresql.org/pg/commitdiff/a522fc3d806ca6ebe3f66e55b3f8cecb85116711
  • Improve speed of aggregates that use array_append as transition function. In the previous coding, if an aggregate's transition function returned an expanded array, nodeAgg.c and nodeWindowAgg.c would always copy it and thus force it into the flat representation. This led to ping-ponging between flat and expanded formats, which costs a lot. For an aggregate using array_append as transition function, I measured about a 15X slowdown compared to the pre-9.5 code, when working on simple int[] arrays. Of course, the old code was already O(N^2) in this usage due to copying flat arrays all the time, but it wasn't quite this inefficient. To fix, teach nodeAgg.c and nodeWindowAgg.c to allow expanded transition values without copying, so long as the transition function takes care to return the transition value already properly parented under the aggcontext. That puts a bit of extra responsibility on the transition function, but doing it this way allows us to not need any extra logic in the fast path of advance_transition_function (ie, with a pass-by-value transition value, or with a modified-in-place pass-by-reference value). We already know that that's a hot spot so I'm loath to add any cycles at all there. Also, while only array_append currently knows how to follow this convention, this solution allows other transition functions to opt-in without needing to have a whitelist in the core aggregation code. (The reason we would need a whitelist is that currently, if you pass a R/W expanded-object pointer to an arbitrary function, it's allowed to do anything with it including deleting it; that breaks the core agg code's assumption that it should free discarded values. Returning a value under aggcontext is the transition function's signal that it knows it is an aggregate transition function and will play nice. Possibly the API rules for expanded objects should be refined, but that would not be a back-patchable change.) With this fix, an aggregate using array_append is no longer O(N^2), so it's much faster than pre-9.5 code rather than much slower. It's still a bit slower than the bespoke infrastructure for array_agg, but the differential seems to be only about 10%-20% rather than orders of magnitude. Discussion: <6315.1477677885@sss.pgh.pa.us> http://git.postgresql.org/pg/commitdiff/9a00f03e479c2d4911eed5b4bd1994315d409938
  • Fix bogus tree-flattening logic in QTNTernary(). QTNTernary() contains logic to flatten, eg, '(a & b) & c' into 'a & b & c', which is all well and good, but it tries to do that to NOT nodes as well, so that '!!a' gets changed to '!a'. Explicitly restrict the conversion to be done only on AND and OR nodes, and add a test case illustrating the bug. In passing, provide some comments for the sadly naked functions in tsquery_util.c, and simplify some baroque logic in QTNFree(), which I think may have been leaking some items it intended to free. Noted while investigating a complaint from Andreas Seltenreich. Back-patch to all supported versions. http://git.postgresql.org/pg/commitdiff/24ebc444c61306f50777f674544e8559e765ad81
  • Fix nasty performance problem in tsquery_rewrite(). tsquery_rewrite() tries to find matches to subsets of AND/OR conditions; for example, in the query 'a | b | c' the substitution subquery 'a | c' should match and lead to replacement of the first and third items. That's fine, but the matching algorithm apparently takes about O(2^N) for an N-clause query (I say "apparently" because the code is also both unintelligible and uncommented). We could probably do better than that even without any extra assumptions --- but actually, we know that the subclauses are sorted, indeed are depending on that elsewhere in this very same function. So we can just scan the two lists a single time to detect matches, as though we were doing a merge join. Also do a re-flattening call (QTNTernary()) in tsquery_rewrite_query, just to make sure that the tree fits the expectations of the next search cycle. I didn't try to devise a test case for this, but I'm pretty sure that the oversight could have led to failure to match in some cases where a match would be expected. Improve comments, and also stick a CHECK_FOR_INTERRUPTS into dofindsubquery, just in case it's still too slow for somebody. Per report from Andreas Seltenreich. Back-patch to all supported branches. Discussion: <8760oasf2y.fsf@credativ.de> http://git.postgresql.org/pg/commitdiff/5ec81aceec8cc5a0edcc4609ee8edbc43a47e878

Álvaro Herrera pushed:

Magnus Hagander pushed:

Robert Haas pushed:

  • postgres_fdw: Try again to stabilize aggregate pushdown regression tests. A query that only aggregates one row isn't a great argument for pushdown, and buildfarm member brolga decides against it. Adjust the query a bit in the hopes of getting remote aggregation to win consistently. Jeevan Chalke, per suggestion from Tom Lane http://git.postgresql.org/pg/commitdiff/f5d6bce63ceb3c59a964814bb0df5a0648e750e5
  • Fix possible pg_basebackup failure on standby with "include WAL". If a restartpoint flushed no dirty buffers, it could fail to update the minimum recovery point, leading to a minimum recovery point prior to the starting REDO location. perform_base_backup() would interpret that as meaning that no WAL files at all needed to be included in the backup, failing an internal sanity check. To fix, have restartpoints always update the minimum recovery point to just after the checkpoint record itself, so that the file (or files) containing the checkpoint record will always be included in the backup. Code by Amit Kapila, per a design suggestion by me, with some additional work on the code comment by me. Test case by Michael Paquier. Report by Kyotaro Horiguchi. http://git.postgresql.org/pg/commitdiff/f267c1c2447bb8da6e4a6b2fcbb612762c3579a8
  • If the stats collector dies during Hot Standby, restart it. This bug exists as far back as 9.0, when Hot Standby was introduced, so back-patch to all supported branches. Report and patch by Takayuki Tsunakawa, reviewed by Michael Paquier and Kuntal Ghosh. http://git.postgresql.org/pg/commitdiff/4f714b2fd2a580d909607de889ac822956eb8299
  • Fix leftover reference to background writer performing checkpoints. This was changed in PostgreSQL 9.2, but somehow this comment never got updated. http://git.postgresql.org/pg/commitdiff/33839b5ffbd7be56681f31d107ec8238c4a0494a
  • pgstattuple: Don't take heavyweight locks when examining a hash index. It's currently necessary to take a heavyweight lock when scanning a hash bucket, but pgstattuple only examines individual pages, so it doesn't need to do this. If, for some hypothetical reason, it did need to do any heavyweight locking here, this logic would probably still be incorrect, because most of the locks that it is taking are meaningless. Only a heavyweight lock on a primary bucket page has any meaning, but this takes heavyweight locks on all pages regardless of function - and in particular overflow pages, where you might imagine that we'd want to lock the primary bucket page if we needed to lock anything at all. This is arguably a bug that has existed since this code was added in commit dab42382f483c3070bdce14a4d93c5d0cf61e82b, but I'm not going to bother back-patching it because in most cases the only consequence is that running pgstattuple() on a hash index is a little slower than it otherwise might be, which is no big deal. Extracted from a vastly larger patch by Amit Kapila which heavyweight locking for hash indexes entirely; analysis of why this can be done independently of the rest by me. http://git.postgresql.org/pg/commitdiff/d4b5d4caddb73f954d9ef86641201dc99677719d

Bruce Momjian pushed:

Peter Eisentraut pushed:

Heikki Linnakangas pushed:

  • Support multi-dimensional arrays in PL/python. Multi-dimensional arrays can now be used as arguments to a PL/python function (used to throw an error), and they can be returned as nested Python lists. This makes a backwards-incompatible change to the handling of composite types in arrays. Previously, you could return an array of composite types as "[[col1, col2], [col1, col2]]", but now that is interpreted as a two- dimensional array. Composite types in arrays must now be returned as Python tuples, not lists, to resolve the ambiguity. I.e. "[(col1, col2), (col1, col2)]". To avoid breaking backwards-compatibility, when not necessary, () is still accepted for arrays at the top-level, but it is always treated as a single-dimensional array. Likewise, [] is still accepted for composite types, when they are not in an array. Update the documentation to recommend using [] for arrays, and () for composite types, with a mention that those other things are also accepted in some contexts. This needs to be mentioned in the release notes. Alexey Grishchenko, Dave Cramer and me. Reviewed by Pavel Stehule. Discussion: <CAH38_tmbqwaUyKs9yagyRra=SMaT45FPBxk1pmTYcM0TyXGG7Q@mail.gmail.com> http://git.postgresql.org/pg/commitdiff/94aceed317730953476bec490ce0148b2af3c383
  • Give a hint, when [] is incorrectly used for a composite type in array. That used to be accepted, so let's try to give a hint to users on why their PL/python functions no longer work. Reviewed by Pavel Stehule. Discussion: <CAH38_tmbqwaUyKs9yagyRra=SMaT45FPBxk1pmTYcM0TyXGG7Q@mail.gmail.com> http://git.postgresql.org/pg/commitdiff/510e1b8ecf2a6f0d91d50f41f6b7fd75242273a0
  • Fix typos in comments. Vinayak Pokale http://git.postgresql.org/pg/commitdiff/56f39009c53e752493ca4478449a1311865dd51a
  • Fix typo in comment. Daniel Gustafsson http://git.postgresql.org/pg/commitdiff/8a2f08fbeaf75f67da122b49f05f96257df3faed
  • Fix regression test to also work with Python 2. Per buildfarm. http://git.postgresql.org/pg/commitdiff/e131ba4fe58123ce5726c1405486913b429c068c
  • Only treat Python Lists as array dimensions. Instead of treating all python sequence types as array dimensions, except for tuples and various kinds of strings, only treat Python lists as dimensions. The PyBytes_Check() function used previously is only available on Python 2.6 and newer, and it was a bit fiddly anyway. The list of exceptions would require adjustment if Python got a new kind of a sequence similar to bytes/unicodes/strings, so only checking for Lists seems more future-proof. The documentation only mentioned using Lists, so this is closer to what was documented, anyway. This should fix the buildfarm failures on systems building with Python 2.5, although I don't have Python 2.5 installed myself to test with. http://git.postgresql.org/pg/commitdiff/cfd9c87a54b0d414a59a912710c6633313174e51
  • Avoid using platform-dependent floats in test case. The number of decimals printed for floats varied in this test case, as noted by several buildfarm members. There's nothing special about floats and arrays in the code being tested, so replace the floats with numerics to make the output platform-independent. http://git.postgresql.org/pg/commitdiff/73c8e8506cd1933ccf5c5d61088ca171a5f414c0
  • Remove platform-dependent PL/python test case. Turns out that the output format of Python Decimal isn't totally platform- independent either. There are other tests for multi-dimensional arrays, so rather than try to fix this test case, just remove it. Per buildfarm member prairiedog. http://git.postgresql.org/pg/commitdiff/8eb6337f9f53c85e707f60157e42fcacfe927668

Tatsuo Ishii pushed:

Correctifs en attente

Samuel D. Leslie sent in a patch to add a radiustimeout parameter for RADIUS HBA.

Tsutomu Yamada and Ashutosh Bapat traded patches to change a returned NULL to NIL in create_foreignscan_path(), and a similar issue in a create_foreignscan_path() call in add_foreign_grouping_paths().

Petr Jelinek sent in another revision of a patch to implement logical replication.

Petr Jelinek sent in another revision of a patch to follow timeline switches in logical decoding and add a test harness for same.

Laurenz Albe sent in two more revisions of a patch to add PGDLLEXPORT to PG_FUNCTION_INFO_V1 function declarations.

Haribabu Kommi sent in another revision of a patch to add macaddr 64 bit (EUI-64) datatype support.

Robert Haas and Victor Wagner traded patches to implement failover on the libpq connect level.

Peter Geoghegan sent in another revision of a patch to cap the number of tapes used by external sorts, add parallel B-tree index build sorting, and add a force_btree_randomaccess GUC for testing.

Amit Kapila sent in two more revisions of a patch to extend the bufmgr api to include hash index.

Ashutosh Sharma sent in a patch to add microvacuum support for hash indexes.

Amit Kapila sent in another revision of a patch to add group update clog.

Kyotaro HORIGUCHI sent in three more revisions of a patch to implement asynchronous execution.

Noah Misch sent in a patch to firmly recommend PG_GETARG_TEXT_PP() over PG_GETARG_TEXT_P().

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

Venkata B Nagothi sent in a patch to add a recovery_target_incomplete GUC.

Julian Markwort sent in another revision of a patch to allow specifying a password file location for libpq-based applications.

Karl O. Pinc and Gilles Darold traded patches to implement a pg_current_logfile() function.

Heikki Linnakangas and Kyotaro HORIGUCHI traded patches to implement a radix tree for character conversion.

Thomas Munro sent in another revision of a patch to add a replay_lag column to the pg_stat_replication view.

Claudio Freire sent in another revision of a patch to refetch buffers on backward scan and allow using more than 1GB work mem in VACUUM.

Peter Eisentraut sent in a patch to add NLS to the remaining bin programs which were moved from contrib a while ago.

Rushabh Lathia sent in another revision of a patch to implement gather merge.

Etsuro Fujita sent in another revision of a patch to push down more full joins to the postgres FDW.

Ashutosh Bapat sent in another revision of a patch to pgstat to avoid writing on SIGQUIT.

Peter Eisentraut sent in a patch to add a user-callable function to import operation system collations.

Michaël Paquier sent in a patch to apply XLR_INFO_MASK correctly when looking at WAL record information.

Peter Eisentraut sent in a patch to add rules to download raw UNICODE mapping files.

Peter Eisentraut sent in a patch to split up psql's \d modifiers column into more legible pieces.

Haribabu Kommi sent in another revision of a patch to add a pg_hba_file_settings view.

Michaël Paquier sent in another revision of a patch to add a wal_consistency GUC with associated machinery.

Mithun Cy sent in two revisions of a patch to add auto_prewarm.

Peter Eisentraut sent in a patch to save redundant code for pseudotype I/O functions.

Ashutosh Bapat sent in a patch to free unused paths in the case of partition-wise join for join between (declaratively) partitioned tables.

Amit Langote sent in another revision of a patch to implement declarative partitioning for tables.

Julien Rouhaud sent in a patch to remove pq_putmessage_hook and pq_flush_hook.

Magnus Hagander and Michaël Paquier traded patches to fix an issue where symlinks in the wrong place could cause streaming backups to fail.

Tomas Vondra sent in another revision of a patch to implement multivariate statistics.

Michaël Paquier sent in another revision of a patch to mention column name in error messages.

Kevin Grittner sent in another revision of a patch to construct and enable the use of delta relations in AFTER triggers.

Masahiko Sawada sent in another revision of a patch to support 2PC across FDWs.

Kyotaro HORIGUCHI sent in another revision of a patch to refactor tab completion in psql to use a more principled approach to grammar.