|Тема:||== PostgreSQL Weekly News - August 4, 2019 ==|
(см: обсуждение, исходный текст)
== PostgreSQL Weekly News - August 4, 2019 == == PostgreSQL Product News == barman 2.9, a backup and recovery manager for PostgreSQL, released. https://www.pgbarman.org/barman-2-9-released/ pg_probackup 2.1.5, a utility to manage backup and recovery of PostgreSQL database clusters, released. https://github.com/postgrespro/pg_probackup == PostgreSQL Jobs for August == http://archives.postgresql.org/pgsql-jobs/2019-08/ == PostgreSQL Local == The first Austrian pgDay, will take place September 6, 2019 at the Hilton Garden Inn in Wiener Neustadt. https://pgday.at/en/ PostgresOpen will be September 11th - 13th, 2019 in Orlando, Florida at the Rosen Centre Hotel. The CfP is open at https://2019.postgresopen.org/callforpapers/ https://2019.postgresopen.org/ PostgresConf South Africa 2019 will take place in Johannesburg on October 8-9, 2019 https://postgresconf.org/conferences/SouthAfrica2019 PostgreSQL Conference Europe 2019 will be held on October 15-18, 2019 in Milan, Italy. https://2019.pgconf.eu/ 2Q PGConf 2019 will be held December 4 & 5 in Chicago. The CFP is open through August 30, 2019. https://www.2qpgconf.com/ pgDay Paris 2020 will be held in Paris, France on March 26, 2020 at Espace Saint-Martin. http://2020.pgday.paris/ Nordic PGDay 2020 will be held in Helsinki, Finland at the Hilton Helsinki Strand Hotel on March 24, 2020. The CfP is open through December 31, 2019 at https://2020.nordicpgday.org/cfp/ == PostgreSQL in the News == Planet PostgreSQL: http://planet.postgresql.org/ PostgreSQL Weekly News is brought to you this week by David Fetter Submit news and announcements by Sunday at 3:00pm PST8PDT to . == Applied Patches == Tom Lane pushed: - Improve test coverage for LISTEN/NOTIFY. We had no actual end-to-end test of NOTIFY message delivery. In the core async.sql regression test, testing this is problematic because psql traditionally prints the PID of the sending backend, making the output unstable. We also have an isolation test script, but it likewise failed to prove that delivery worked, because isolationtester.c had no provisions for detecting/reporting NOTIFY messages. Hence, add such provisions to isolationtester.c, and extend async-notify.spec to include direct tests of basic NOTIFY functionality. I also added tests showing that NOTIFY de-duplicates messages normally, but not across subtransaction boundaries. (That's the historical behavior since we introduced subtransactions, though perhaps we ought to change it.) Patch by me, with suggestions/review by Andres Freund. Discussion: https://postgr.es/m/ https://git.postgresql.org/pg/commitdiff/b10f40bf0e4516d7832db8ccbe5f76319ad08682 - Fix busted logic for parallel lock grouping in TopoSort(). A "break" statement erroneously left behind by commit a1c1af2a1 caused TopoSort to do the wrong thing if a lock's wait list contained multiple members of the same locking group. Because parallel workers don't normally need any locks not already taken by their leader, this is very hard --- maybe impossible --- to hit in production. Still, if it did happen, the queries involved in an otherwise-resolvable deadlock would block until canceled. In addition to removing the bogus "break", add an Assert showing that the conflicting uses of the beforeConstraints array (for both counts and flags) don't overlap, and add some commentary explaining why not; because it's not obvious without explanation, IMHO. Original report and patch from Rui Hai Jiang; additional assert and commentary by me. Back-patch to 9.6 where the bug came in. Discussion: https://postgr.es/m/CAEri+mLd3bpHLyW+a9pSe1y= https://git.postgresql.org/pg/commitdiff/3420851a2c2d2ac49b8ba53ccec5d02aa1e6a272 - Fix pg_dump's handling of dependencies for custom opclasses. Since pg_dump doesn't treat the member operators and functions of operator classes/families (that is, the pg_amop and pg_amproc entries, not the underlying operators/functions) as separate dumpable objects, it missed their dependency information. I think this was safe when the code was designed, because the default object sorting rule emits operators and functions before opclasses, and there were no dependency types that could mess that up. However, the introduction of range types in 9.2 broke it: now a type can have a dependency on an opclass, allowing dependency rules to push the opclass before the type and hence before custom operators. Lacking any information showing that it shouldn't do so, pg_dump emitted the objects in the wrong order. Fix by teaching getDependencies() to translate pg_depend entries for pg_amop/amproc rows to look like dependencies for their parent opfamily. I added a regression test for this in HEAD/v12, but not further back; life is too short to fight with 002_pg_dump.pl. Per bug #15934 from Tom Gottfried. Back-patch to all supported branches. Discussion: https://postgr.es/m/ https://git.postgresql.org/pg/commitdiff/07b39083c2aca003c4b1f289d7dc2368b5e2297a - Mark advisory-lock functions as parallel restricted, not parallel unsafe. There seems no good reason not to allow a parallel leader to execute these functions. (The workers still can't, though. Although the code would work, any such lock would go away at worker exit, which is not the documented behavior of advisory locks.) Discussion: https://postgr.es/m/ https://git.postgresql.org/pg/commitdiff/4886da8327507dddd830786b0c7aaa9cfc480b4b - Add an isolation test to exercise parallel-worker deadlock resolution. Commit a1c1af2a1 added logic in the deadlock checker to handle lock grouping, but it was very poorly tested, as evidenced by the bug fixed in 3420851a2. Add a test case that exercises that a bit better (and catches the bug --- if you revert 3420851a2, this will hang). Since it's pretty hard to get parallel workers to take exclusive regular locks that their parents don't already have, this test operates by creating a deadlock among advisory locks taken in parallel workers. To make that happen, we must override the parallel-safety labeling of the advisory-lock functions, which we do by putting them in mislabeled, non-inlinable wrapper functions. We also have to remove the redundant PreventAdvisoryLocksInParallelMode checks in lockfuncs.c. That seems fine though; if some user accidentally does what this test is intentionally doing, not much harm will ensue. (If there are any remaining bugs that are reachable that way, they're probably reachable in other ways too.) Discussion: https://postgr.es/m/ https://git.postgresql.org/pg/commitdiff/da9456d22a7697ef2c5ba9dd1402d948b2ec7f09 - Allow functions-in-FROM to be pulled up if they reduce to constants. This allows simplification of the plan tree in some common usage patterns: we can get rid of a join to the function RTE. In principle we could pull up any immutable expression, but restricting it to Consts avoids the risk that multiple evaluations of the expression might cost more than we can save. (Possibly this could be improved in future --- but we've more or less promised people that putting a function in FROM guarantees single evaluation, so we'd have to tread carefully.) To do this, we need to rearrange when eval_const_expressions() happens for expressions in function RTEs. I moved it to inline_set_returning_functions(), which already has to iterate over every function RTE, and in consequence renamed that function to preprocess_function_rtes(). A useful consequence is that inline_set_returning_function() no longer has to do this for itself, simplifying that code. In passing, break out pull_up_simple_subquery's code that knows where everything that needs pullup_replace_vars() processing is, so that the new pull_up_constant_function() routine can share it. We'd gotten away with one-and-a-half copies of that code so far, since pull_up_simple_values() could assume that a lot of cases didn't apply to it --- but I don't think pull_up_constant_function() can make any simplifying assumptions. Might as well make pull_up_simple_values() use it too. (Possibly this refactoring should go further: maybe we could share some of the code to fill in the pullup_replace_vars_context struct? For now, I left it that the callers fill that completely.) Note: the one existing test case that this patch changes has to be changed because inlining its function RTEs would destroy the point of the test, namely to check join order. Alexander Kuzmenkov and Aleksandr Parfenov, reviewed by Antonin Houska and Anastasia Lubennikova, and whacked around some more by me Discussion: https://postgr.es/m/ https://git.postgresql.org/pg/commitdiff/7266d0997dd2a0632da38a594c78e25ff21df67e Thomas Munro pushed: - Avoid macro clash with LLVM 9. Early previews of LLVM 9 reveal that our Min() macro causes compiler errors in LLVM headers reached by the #include directives in llvmjit_inline.cpp. Let's just undefine it. Per buildfarm animal seawasp. Back-patch to 11. Reviewed-by: Fabien Coelho, Tom Lane Discussion: https://postgr.es/m/20190606173216.GA6306%40alvherre.pgsql https://git.postgresql.org/pg/commitdiff/a2a777d011971ace3a349a3f02b1bf6eeea07bf2 Michaël Paquier pushed: - Fix handling of expressions and predicates in REINDEX CONCURRENTLY. When copying the definition of an index rebuilt concurrently for the new entry, the index information was taken directly from the old index using the relation cache. In this case, predicates and expressions have some post-processing to prepare things for the planner, which loses some information including the collations added in any of them. This inconsistency can cause issues when attempting for example a table rewrite, and makes the new indexes rebuilt concurrently inconsistent with the old entries. In order to fix the problem, fetch expressions and predicates directly from the catalog of the old entry, and fill in IndexInfo for the new index with that. This makes the process more consistent with DefineIndex(), and the code is refactored with the addition of a routine to create an IndexInfo node. Reported-by: Manuel Rigger Author: Michael Paquier Discussion: https://postgr.es/m/ Backpatch-through: 12 https://git.postgresql.org/pg/commitdiff/7cce159349ccdb39ade07f869f08e4929ef2fe0b - Fix inconsistencies and typos in the tree. This is numbered take 8, and addresses again a set of issues with code comments, variable names and unreferenced variables. Author: Alexander Lakhin Discussion: https://postgr.es/m/ https://git.postgresql.org/pg/commitdiff/eb43f3d19324d7e5376b1f57fc2e5c142a6b5f3d - Fix memory leak coming from simple lists built in reindexdb. When building a list of relations for a parallel processing of a schema or a database (or just a single-entry list for the non-parallel case with the database name), the list is allocated and built on-the-fly for each database processed, leaking after one database-level reindex is done. This accumulates leaks when processing all databases, and could become a visible issue with thousands of relations. This is fixed by introducing a new routine in simple_list.c to free all the elements in a simple list made of strings or OIDs. The header of the list may be using a variable declaration or an allocated pointer, so we don't have a routine to free this part to keep the interface simple. Per report from coverity for an issue introduced by 5ab892c, and valgrind complains about the leak as well. The idea to introduce a new routine in simple_list.c is from Tom Lane. Author: Michael Paquier Reviewed-by: Tom Lane https://git.postgresql.org/pg/commitdiff/04cf0bfc90dfae89a794d2bdd88fe3b8e313798e - Remove orphaned structure member in pgcrypto. int_name has never been used for digest lookups since its introduction in e94dd6a. Author: Daniel Gustafsson Discussion: https://postgr.es/m/ https://git.postgresql.org/pg/commitdiff/652a8947d981db0367bcff5b123545eba0049878 - Fix handling of previous password hooks in passwordcheck. When piling up loading of modules using check_password_hook_type, loading passwordcheck would remove any trace of a previously-loaded hook. Unloading the module would also cause previous hooks to be entirely gone. Reported-by: Rafael Castro Author: Michael Paquier Reviewed-by: Daniel Gustafsson Discussion: https://postgr.es/m/ Backpatch-through: 9.4 https://git.postgresql.org/pg/commitdiff/b2a3d706b8d76b9d65e953942fc1ccafe892f692 - Fix format truncation issue from ECPG test. This fixes one warning generated by GCC and present in the test case array part of ECPG. This likely got missed in past fixes like 3a4b891 because the compilation of those tests is not done by default. Reported-by: Sergei Kornilov Discussion: https://postgr.es/m/-c.yandex.net https://git.postgresql.org/pg/commitdiff/a9f301df0e76c38d4544477c1b3e5e29d57904e6 Peter Eisentraut pushed: - Handle fsync failures in pg_receivewal and pg_recvlogical. It is not safe to simply report an fsync error and continue. We must exit the program instead. Reviewed-by: Michael Paquier <> Reviewed-by: Sehrope Sarkuni <> Discussion: https://www.postgresql.org/message-id/flat/ https://git.postgresql.org/pg/commitdiff/1e2fddfa33d3c7cc93ca3ee0f32852699bd3e012 - Run UTF8-requiring collation tests by default. The tests collate.icu.utf8 and collate.linux.utf8 were previously only run when explicitly selected via EXTRA_TESTS. They require a UTF8 database, because the error messages in the expected files refer to that, and they use some non-ASCII characters in the tests. Since users can select any locale and encoding for the regression test run, it was not possible to include these tests automatically. To fix, use psql's \if facility to check various prerequisites such as platform and the server encoding and quit the tests at the very beginning if the configuration is not adequate. We then need to maintain alternative expected files for these tests, but they are very tiny and never need to change after this. These two tests are now run automatically as part of the regression tests. Reviewed-by: Tom Lane <> Discussion: https://www.postgresql.org/message-id/flat/052295c2-a2e1-9a21-bd36-8fbff8686cf3%402ndquadrant.com https://git.postgresql.org/pg/commitdiff/f140007050a2ba874b85c4578d8417828f4b64b6 - Add error codes to some corruption log messages. In some cases we have elog(ERROR) while corruption is certain and we can give a clear error code ERRCODE_DATA_CORRUPTED or ERRCODE_INDEX_CORRUPTED. Author: Andrey Borodin <> Discussion: https://www.postgresql.org/message-id/flat/ https://git.postgresql.org/pg/commitdiff/fd6ec93bf890314ac694dc8a7f3c45702ecc1bbd Tomáš Vondra pushed: - Don't build extended statistics on inheritance trees. When performing ANALYZE on inheritance trees, we collect two samples for each relation - one for the relation alone, and one for the inheritance subtree (relation and its child relations). And then we build statistics on each sample, so for each relation we get two sets of statistics. For regular (per-column) statistics this works fine, because the catalog includes a flag differentiating statistics built from those two samples. But we don't have such flag in the extended statistics catalogs, and we ended up updating the same row twice, triggering this error: ERROR: tuple already updated by self The simplest solution is to disable extended statistics on inheritance trees, which is what this commit is doing. In the future we may need to do something similar to per-column statistics, but that requires adding a flag to the catalog - and that's not backpatchable. Moreover, the current selectivity estimation code only works with individual relations, so building statistics on inheritance trees would be pointless anyway. Author: Tomas Vondra Backpatch-to: 10- Discussion: https://postgr.es/m/ Reported-by: Justin Pryzby https://git.postgresql.org/pg/commitdiff/14ef15a22246ca17c949e7a9d1abe14c8874d743 Heikki Linnakangas pushed: - Print WAL position correctly in pg_rewind error message. This has been wrong ever since pg_rewind was added. The if-branch just above this, where we print the same error with an extra message supplied by XLogReadRecord() got this right, but the variable name was wrong in the else-branch. As a consequence, the error printed the WAL position as 0/0 if there was an error reading a WAL file. Backpatch to 9.5, where pg_rewind was added. https://git.postgresql.org/pg/commitdiff/d8b094dabb0fa16388340ca823d0a38285d2d6ce - Allow table AM's to use rd_amcache, too. The rd_amcache allows an index AM to cache arbitrary information in a relcache entry. This commit moves the cleanup of rd_amcache so that it can also be used by table AMs. Nothing takes advantage of that yet, but I'm sure it'll come handy for anyone writing new table AMs. Backpatch to v12, where table AM interface was introduced. Reviewed-by: Julien Rouhaud https://git.postgresql.org/pg/commitdiff/a29834beb1deeb0aa06742dd77ba1d21b444ca44 Andres Freund pushed: - Remove superfluous semicolon. Author: Andres Freund https://git.postgresql.org/pg/commitdiff/6384e87be28ee8d69ef11e49413b115506a3c6d3 - Remove superfluous newlines in function prototypes. These were introduced by pgindent due to fixe to broken indentation (c.f. 8255c7a5eeba8). Previously the mis-indentation of function prototypes was creatively used to reduce indentation in a few places. As that formatting only exists in master and REL_12_STABLE, it seems better to fix it in both, rather than having some odd indentation in v12 that somebody might copy for future patches or such. Author: Andres Freund Discussion: https://postgr.es/m/ Backpatch: 12- https://git.postgresql.org/pg/commitdiff/870b1d6800cc2173ab672449047efbc30bdc1b57 - Fix representation of hash keys in Hash/HashJoin nodes. In 5f32b29c1819 I changed the creation of HashState.hashkeys to actually use HashState as the parent (instead of HashJoinState, which was incorrect, as they were executed below HashState), to fix the problem of hashkeys expressions otherwise relying on slot types appropriate for HashJoinState, rather than HashState as would be correct. That reliance was only introduced in 12, which is why it previously worked to use HashJoinState as the parent (although I'd be unsurprised if there were problematic cases). Unfortunately that's not a sufficient solution, because before this commit, the to-be-hashed expressions referenced inner/outer as appropriate for the HashJoin, not Hash. That didn't have obvious bad consequences, because the slots containing the tuples were put into ecxt_innertuple when hashing a tuple for HashState (even though Hash doesn't have an inner plan). There are less common cases where this can cause visible problems however (rather than just confusion when inspecting such executor trees). E.g. "ERROR: bogus varno: 65000", when explaining queries containing a HashJoin where the subsidiary Hash node's hash keys reference a subplan. While normally hashkeys aren't displayed by EXPLAIN, if one of those expressions references a subplan, that subplan may be printed as part of the Hash node - which then failed because an inner plan was referenced, and Hash doesn't have that. It seems quite possible that there's other broken cases, too. Fix the problem by properly splitting the expression for the HashJoin and Hash nodes at plan time, and have them reference the proper subsidiary node. While other workarounds are possible, fixing this correctly seems easy enough. It was a pretty ugly hack to have ExecInitHashJoin put the expression into the already initialized HashState, in the first place. I decided to not just split inner/outer hashkeys inside make_hashjoin(), but also to separate out hashoperators and hashcollations at plan time. Otherwise we would have ended up having two very similar loops, one at plan time and the other during executor startup. The work seems to more appropriately belong to plan time, anyway. Reported-By: Nikita Glukhov, Alexander Korotkov Author: Andres Freund Reviewed-By: Tom Lane, in an earlier version Discussion: https://postgr.es/m/ Backpatch: 12- https://git.postgresql.org/pg/commitdiff/2abd7ae9b20bcd810d4f19d28aefb97048813825 Peter Geoghegan pushed: - Add sort support routine for the inet data type. Add sort support for inet, including support for abbreviated keys. Testing has shown that this reduces the time taken to sort medium to large inet/cidr inputs by ~50-60% in realistic cases. Author: Brandur Leach Reviewed-By: Peter Geoghegan, Edmund Horner Discussion: https://postgr.es/m/ https://git.postgresql.org/pg/commitdiff/71dcd7438664d81235c72337cbbbfa780f7a0630 - Bump catversion. Oversight in commit 71dcd743. https://git.postgresql.org/pg/commitdiff/a8d6a95eb992e942838e41029537564d81c4a50e Jeff Davis pushed: - Allow simplehash to use already-calculated hash values. Add _lookup_hash and _insert_hash functions for callers that have already calculated the hash value of the key. The immediate use case is for hash algorithms that write to disk in partitions. The hash value can be calculated once, used to perform a lookup, used to select the partition, then written to the partition along with the tuple. When the tuple is read back, the hash value does not need to be recalculated. Author: Jeff Davis Reviewed-by: Andres Freund Discussion: https://postgr.es/m/48abe675e1330f0c264ab2fe0d4ff23eb244f9ef.camel%40j-davis.com https://git.postgresql.org/pg/commitdiff/6ae4e8eae78e0781633f7b40a1b5cc189bc40923 == Pending Patches == Tom Lane sent in another revision of a patch to add support for ON UPDATE/DELETE actions on ALTER CONSTRAINT. Fabien COELHO sent in two more revisions of a patch to ensure that multi-statement strings in psql return all result sets. Simon Riggs sent in a patch to allow XLOG HEAP2 NEW CID while building snapshot. Jehan-Guillaume de Rorthais sent in a patch to add functions to walreceiver to get the timeline. Heikki Linnakangas sent in a patch to refactor xlogreaderstate callback. Vigneshwaran C sent in a patch to remove the unused ParsePrepareRecord function. Peter Eisentraut sent in another revision of a patch to run UTF8-requiring collation tests by default. Peter Eisentraut sent in another revision of a patch to use explicit_bzero. Konstantin Knizhnik sent in four more revisions of a patch to implement a built-in connection pooler. John Naylor and Binguo Bao traded patches to de-TOAST using an iterator. Jeevan Ladhe sent in three revisions of a patch to make have_error initialization more defensive. Melanie Plageman sent in another revision of a patch to avoid hash join batch explosions with extreme skew and weird stats. Kyotaro HORIGUCHI sent in two more revisions of a patch to add a WAL relief vent for replication slots. Richard Guo sent in two more revisions of a patch to execute grouping sets in parallel. Bruce Momjian sent in a patch to add a check for initialization vectors, a precursor to transparent data encryption. Tomáš Vondra sent in another revision of a patch to rework log sampling so it has a minimum level. Ibrar Ahmed sent in two more revisions of a patch to implement PERIODs via ranges. Ruijia and David Fetter traded patches to implement CORRESPONDING (BY). Amit Langote sent in two more revisions of a patch to refactor the layering of partition routing. Jeff Davis sent in a patch to make it possible to redact information from the logs. Yugo Nagata sent in another revision of a patch to implement incremental maintenance of materialized views. Anastasia Lubennikova sent in another revision of a patch to make storing duplicates more efficient in nbtrees. Konstantin Knizhnik sent in two more revisions of a patch to implement global temporary tables. Ashwin Agrawal sent in two more revisions of a patch to Remove HeapTuple dependency for predicate locking functions. Amit Kapila sent in another revision of a patch to clean up orphaned files using the undo log. Vigneshwaran C and Andres Freund traded patches to minimize redundant #include directives. Michaël Paquier sent in a patch to refactor the code that strips carriage returns and newlines from strings. Surafel Temesgen sent in another revision of a patch to implement FETCH FIRST ... PERCENT. Aleksey Kondratov sent in another revision of a patch to pg_rewind to add options to use restore_command from the command line or cluster configuration. John Naylor sent in another revision of a patch to handle UESCAPEs in the parser. Konstantin Knizhnik sent in another revision of a patch to implement auto_prepare. Vigneshwaran C and Thomas Munro traded patches to use FullTransactionId for two phase commit, add an SQL type xid8 to expose FullTransactionId to users, and use same in the pg_prepared_xacts view. Jeevan Chalke sent in a patch to implement a pg_combinebackup utility for incremental backups. Robert Haas sent in another revision of a patch to split uptoaster.c into three separate files, allow TOAST tables to be implemented using table AMs other than heap, create an API for inserting and deleting rows in TOAST tables, and rename the attribute-detoasting functions to reflect the fact that they're no longer specific to the heap AM. Julien Rouhaud, Tom Lane, and Nikita Glukhov traded patches to avoid GIN full scans for empty ALL keys, force GIN recheck more accurately, and avoid GIN full scan for non-empty ALL keys. Michaël Paquier sent in a patch to do some further refactoring of BuildIndexInfo. Sehrope Sarkuni sent in another revision of a patch to use the American spellings for "serialize" and "materalize." Martijn van Oosterhout sent in another revision of a patch to improve the performance of NOTIFY over many databases. Jesper Pedersen sent in another revision of a patch to implement index skip scans. Ibrar Ahmed sent in another revision of a patch to implement temporal query processing with range types. Floris Van Nee sent in a patch to optimize single tuple fetches from nbtree indexes. Peter Geoghegan sent in two more revisions of a patch to add a script which suggests OID ranges for new features. Ivan Panchenko sent in two revisions of a patch to fix a jsonb_plperl bug. Chapman Flack sent in another revision of a patch to improve documentation about our XML functionality. Tom Lane sent in another revision of a patch to prevent ALTER SYSTEM from making bad assumptions. Julien Rouhaud sent in another revision of a patch to surface queryId. Petr Jelínek sent in a patch to make compression pluggable. Tom Lane sent in a patch to check ports for Kerberos. Álvaro Herrera sent in another revision of a patch to fix an issue with default partition pruning.