November 21, 2024: PostgreSQL 17.2, 16.6, 15.10, 14.15, 13.18, and 12.22 Released!

PostgreSQL Weekly News - December 6, 2020

Posted on 2020-12-07 by PWN
PWN

PostgreSQL Weekly News - December 6, 2020

Person of the week: https://postgresql.life/post/stephane_schildknecht/

PostgreSQL Product News

check_pgactivity 2.5, a Nagios remote agent for monitoring PostgreSQL, released. https://github.com/OPMDG/check_pgactivity/releases/latest

WAL-G 0.2.19, a backup management system for PostgreSQL written in Go, released. https://github.com/wal-g/wal-g/releases

PostgreSQL Jobs for December

http://archives.postgresql.org/pgsql-jobs/2020-12/

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 david@fetter.org.

== Applied Patches ==

Tom Lane pushed:

  • Fix recently-introduced breakage in psql's \connect command. Through my misreading of what the existing code actually did, commits 85c54287a et al. broke psql's behavior for the case where "\c connstring" provides a password in the connstring. We should use that password in such a case, but as of 85c54287a we ignored it (and instead, prompted for a password). Commit 94929f1cf fixed that in HEAD, but since I thought it was cleaning up a longstanding misbehavior and not one I'd just created, I didn't back-patch it. Hence, back-patch the portions of 94929f1cf having to do with password management. In addition to fixing the introduced bug, this means that "\c -reuse-previous=on connstring" will allow re-use of an existing connection's password if the connstring doesn't change user/host/port. That didn't happen before, but it seems like a bug fix, and anyway I'm loath to have significant differences in this code across versions. Also fix an error with the same root cause about whether or not to override a connstring's setting of client_encoding. As of 85c54287a we always did so; restore the previous behavior of overriding only when stdin/stdout are a terminal and there's no environment setting of PGCLIENTENCODING. (I find that definition a bit surprising, but right now doesn't seem like the time to revisit it.) Per bug #16746 from Krzysztof Gradek. As with the previous patch, back-patch to all supported branches. Discussion: https://postgr.es/m/16746-44b30e2edf4335d4@postgresql.org https://git.postgresql.org/pg/commitdiff/7e5e1bba034ee02245e8a3833aa1f6ea7253b584

  • Fix miscomputation of direct_lateral_relids for join relations. If a PlaceHolderVar is to be evaluated at a join relation, but its value is only needed there and not at higher levels, we neglected to update the joinrel's direct_lateral_relids to include the PHV's source rel. This causes problems because join_is_legal() then won't allow joining the joinrel to the PHV's source rel at all, leading to "failed to build any N-way joins" planner failures. Per report from Andreas Seltenreich. Back-patch to 9.5 where the problem originated. Discussion: https://postgr.es/m/87blfgqa4t.fsf@aurora.ydns.eu https://git.postgresql.org/pg/commitdiff/b1738ff6ab73203cbbc02d7fb82941dbc061d301

  • Prevent parallel index build in a standalone backend. This can't work if there's no postmaster, and indeed the code got an assertion failure trying. There should be a check on IsUnderPostmaster gating the use of parallelism, as the planner has for ordinary parallel queries. Commit 40d964ec9 got this right, so follow its model of checking IsUnderPostmaster at the same place where we check for max_parallel_maintenance_workers == 0. In general, new code implementing parallel utility operations should do the same. Report and patch by Yulin Pei, cosmetically adjusted by me. Back-patch to v11 where this code came in. Discussion: https://postgr.es/m/HK0PR01MB22747D839F77142D7E76A45DF4F50@HK0PR01MB2274.apcprd01.prod.exchangelabs.com https://git.postgresql.org/pg/commitdiff/275b3411d9189b3974687766db06727d64d22979

  • Remove configure-time probe for DocBook DTD. Checking for DocBook being installed was valuable when we were on the OpenSP docs toolchain, because that was rather hard to get installed fully. Nowadays, as long as you have xmllint and xsltproc installed, you're good, because those programs will fetch the DocBook files off the net at need. Moreover, testing this at configure time means that a network access may well occur whether or not you have any interest in building the docs later. That can be slow (typically 2 or 3 seconds, though much higher delays have been reported), and it seems not very nice to be doing an off-machine access without warning, too. Hence, drop the PGAC_CHECK_DOCBOOK probe, and adjust related documentation. Without that macro, there's not much left of config/docbook.m4 at all, so I just removed it. Back-patch to v11, where we started to use xmllint in the PGAC_CHECK_DOCBOOK probe. Discussion: https://postgr.es/m/E2EE6B76-2D96-408A-B961-CAE47D1A86F0@yesql.se Discussion: https://postgr.es/m/A55A7FC9-FA60-47FE-98B5-139CDC57CE6E@gmail.com https://git.postgresql.org/pg/commitdiff/4823c4f6ac770ebd49b63b0ce64ecfe82987af57

  • Fix missing outfuncs.c support for IncrementalSortPath. For debugging purposes, Path nodes are supposed to have outfuncs support, but this was overlooked in the original incremental sort patch. While at it, clean up a couple other minor oversights, as well as bizarre choice of return type for create_incremental_sort_path(). (All the existing callers just cast it to "Path *" immediately, so they don't care, but some future caller might care.) outfuncs.c fix by Zhijie Hou, the rest by me Discussion: https://postgr.es/m/324c4d81d8134117972a5b1f6cdf9560@G08CNEXMBPEKD05.g08.fujitsu.local https://git.postgresql.org/pg/commitdiff/8286223f3d820c39f2d5f14222f7ccde53bdf502

  • Ensure that expandTableLikeClause() re-examines the same table. As it stood, expandTableLikeClause() re-did the same relation_openrv call that transformTableLikeClause() had done. However there are scenarios where this would not find the same table as expected. We hold lock on the LIKE source table, so it can't be renamed or dropped, but another table could appear before it in the search path. This explains the odd behavior reported in bug #16758 when cloning a table as a temp table of the same name. This case worked as expected before commit 502898192 introduced the need to open the source table twice, so we should fix it. To make really sure we get the same table, let's re-open it by OID not name. That requires adding an OID field to struct TableLikeClause, which is a little nervous-making from an ABI standpoint, but as long as it's at the end I don't think there's any serious risk. Per bug #16758 from Marc Boeren. Like the previous patch, back-patch to all supported branches. Discussion: https://postgr.es/m/16758-840e84a6cfab276d@postgresql.org https://git.postgresql.org/pg/commitdiff/f7f83a55bf6051818a0e4387d718867ecfa8561b

  • Fix missed step in removal of useless RESULT RTEs in the planner. Commit 4be058fe9 forgot that the append_rel_list would already be populated at the time we remove useless result RTEs, and it might contain PlaceHolderVars that need to be adjusted like the ones in the main parse tree. This could lead to "no relation entry for relid N" failures later on, when the planner tries to do something with an unadjusted PHV. Per report from Tom Ellis. Back-patch to v12 where the bug came in. Discussion: https://postgr.es/m/20201205173056.GF30712@cloudinit-builder https://git.postgresql.org/pg/commitdiff/e98c900993e89ad9278cdfbf0ba5495381a1faac

Fujii Masao pushed:

Heikki Linnakangas pushed:

Michaël Paquier pushed:

  • Refactor parsing rules for option lists of EXPLAIN, VACUUM and ANALYZE. Those three commands have been using the same grammar rules to handle a a list of parenthesized options. This refactors the code so as they use the same parsing rules, shaving some code. A future commit will make use of those option parsing rules for more utility commands, like REINDEX and CLUSTER. Author: Alexey Kondratov, Justin Pryzby Discussion: https://postgr.es/m/8a8f5f73-00d3-55f8-7583-1375ca8f6a91@postgrespro.ru https://git.postgresql.org/pg/commitdiff/873ea9ee692e7829614f913685db540b17998ba6

  • doc: Remove more notes about compatibilities with past versions. This is a follow-up of the work done in fa42c2e, that did not include all the fixes previously agreed on. The contents removed here can be confusing to the reader as they refer to rather old server versions. Author: Stephen Frost, Tom Lane, Heikki Linnakangas, Yaroslav Schekin Discussion: https://postgr.es/m/CAB8KJ=jYHgnxLLZSNJz7gBTck4TxomngCmGkw3nEMSNF0yL6wA@mail.gmail.com Discussion: https://postgr.es/m/1599765595731-0.post@n3.nabble.com https://git.postgresql.org/pg/commitdiff/8a17f44c1e7a9f3d2a9da97dc3eba4184a2a453c

  • Move SHA2 routines to a new generic API layer for crypto hashes. Two new routines to allocate a hash context and to free it are created, as these become necessary for the goal behind this refactoring: switch the all cryptohash implementations for OpenSSL to use EVP (for FIPS and also because upstream does not recommend the use of low-level cryptohash functions for 20 years). Note that OpenSSL hides the internals of cryptohash contexts since 1.1.0, so it is necessary to leave the allocation to OpenSSL itself, explaining the need for those two new routines. This part is going to require more work to properly track hash contexts with resource owners, but this not introduced here. Still, this refactoring makes the move possible. This reduces the number of routines for all SHA2 implementations from twelve (SHA{224,256,386,512} with init, update and final calls) to five (create, free, init, update and final calls) by incorporating the hash type directly into the hash context data. The new cryptohash routines are moved to a new file, called cryptohash.c for the fallback implementations, with SHA2 specifics becoming a part internal to src/common/. OpenSSL specifics are part of cryptohash_openssl.c. This infrastructure is usable for more hash types, like MD5 or HMAC. Any code paths using the internal SHA2 routines are adapted to report correctly errors, which are most of the changes of this commit. The zones mostly impacted are checksum manifests, libpq and SCRAM. Note that e21cbb4 was a first attempt to switch SHA2 to EVP, but it lacked the refactoring needed for libpq, as done here. This patch has been tested on Linux and Windows, with and without OpenSSL, and down to 1.0.1, the oldest version supported on HEAD. Author: Michael Paquier Reviewed-by: Daniel Gustafsson Discussion: https://postgr.es/m/20200924025314.GE7405@paquier.xyz https://git.postgresql.org/pg/commitdiff/87ae9691d25379785f8c0f81b06a14818cfd8c56

  • Fix compilation warnings in cryptohash_openssl.c. These showed up with -O2. Oversight in 87ae969. Author: Fujii Masao Discussion: https://postgr.es/m/cee3df00-566a-400c-1252-67c3701f918a@oss.nttdata.com https://git.postgresql.org/pg/commitdiff/91624c2ff8809145880383b0fa84be0ee98f55b5

  • Refactor CLUSTER and REINDEX grammar to use DefElem for option lists. This changes CLUSTER and REINDEX so as a parenthesized grammar becomes possible for options, while unifying the grammar parsing rules for option lists with the existing ones. This is a follow-up of the work done in 873ea9e for VACUUM, ANALYZE and EXPLAIN. This benefits REINDEX for a potential backend-side filtering for collatable-sensitive indexes and TABLESPACE, while CLUSTER would benefit from the latter. Author: Alexey Kondratov, Justin Pryzby Discussion: https://postgr.es/m/8a8f5f73-00d3-55f8-7583-1375ca8f6a91@postgrespro.ru https://git.postgresql.org/pg/commitdiff/b5913f6120792465f4394b93c15c2e2ac0c08376

  • Change SHA2 implementation based on OpenSSL to use EVP digest routines. The use of low-level hash routines is not recommended by upstream OpenSSL since 2000, and pgcrypto already switched to EVP as of 5ff4a67. This takes advantage of the refactoring done in 87ae969 that has introduced the allocation and free routines for cryptographic hashes. Since 1.1.0, OpenSSL does not publish the contents of the cryptohash contexts, forcing any consumers to rely on OpenSSL for all allocations. Hence, the resource owner callback mechanism gains a new set of routines to track and free cryptohash contexts when using OpenSSL, preventing any risks of leaks in the backend. Nothing is needed in the frontend thanks to the refactoring of 87ae969, and the resowner knowledge is isolated into cryptohash_openssl.c. Note that this also fixes a failure with SCRAM authentication when using FIPS in OpenSSL, but as there have been few complaints about this problem and as this causes an ABI breakage, no backpatch is done. Author: Michael Paquier Reviewed-by: Daniel Gustafsson, Heikki Linnakangas Discussion: https://postgr.es/m/20200924025314.GE7405@paquier.xyz Discussion: https://postgr.es/m/20180911030250.GA27115@paquier.xyz https://git.postgresql.org/pg/commitdiff/4f48a6fbe2b28d8281dbbfa2d334fa2ed8472734

  • Rename cryptohashes.c to cryptohashfuncs.c. 87ae969 has created two new files called cryptohash{_openssl}.c in src/common/, whose names overlap with the existing backend file called cryptohashes.c dedicated to the SQL wrappers for SHA2 and MD5. This file is renamed to cryptohashfuncs.c to be more consistent with the surroundings and reduce the confusion with the new cryptohash interface of src/common/. Author: Michael Paquier Reviewed-by: Daniel Gustafsson Discussion: https://postgr.es/m/X8hHhaQgbMbW+aGU@paquier.xyz https://git.postgresql.org/pg/commitdiff/bd94a9c04e04bb3b626e88981a50fcca2bd99d60

Álvaro Herrera pushed:

Thomas Munro pushed:

Bruce Momjian pushed:

Stephen Frost pushed:

Dean Rasheed pushed:

  • Improve estimation of OR clauses using extended statistics. Formerly we only applied extended statistics to an OR clause as part of the clauselist_selectivity() code path for an OR clause appearing in an implicitly-ANDed list of clauses. This meant that it could only use extended statistics if all sub-clauses of the OR clause were covered by a single extended statistics object. Instead, teach clause_selectivity() how to apply extended statistics to an OR clause by handling its ORed list of sub-clauses in a similar manner to an implicitly-ANDed list of sub-clauses, but with different combination rules. This allows one or more extended statistics objects to be used to estimate all or part of the list of sub-clauses. Any remaining sub-clauses are then treated as if they are independent. Additionally, to avoid double-application of extended statistics, this introduces "extended" versions of clause_selectivity() and clauselist_selectivity(), which include an option to ignore extended statistics. This replaces the old clauselist_selectivity_simple() function which failed to completely ignore extended statistics when called from the extended statistics code. A known limitation of the current infrastructure is that an AND clause under an OR clause is not treated as compatible with extended statistics (because we don't build RestrictInfos for such sub-AND clauses). Thus, for example, "(a=1 AND b=1) OR (a=2 AND b=2)" will currently be treated as two independent AND clauses (each of which may be estimated using extended statistics), but extended statistics will not currently be used to account for any possible overlap between those clauses. Improving that is left as a task for the future. Original patch by Tomas Vondra, with additional improvements by me. Discussion: https://postgr.es/m/20200113230008.g67iyk4cs3xbnjju@development https://git.postgresql.org/pg/commitdiff/25a9e54d2db31b8031c2d8166114b187e8347098

Peter Eisentraut pushed:

Amit Kapila pushed:

  • Remove incorrect assertion in reorderbuffer.c. We start recording changes in ReorderBufferTXN even before we reach SNAPBUILD_CONSISTENT state so that if the commit is encountered after reaching that we should be able to send the changes of the entire transaction. Now, while recording changes if the reorder buffer memory has exceeded logical_decoding_work_mem then we can start streaming if it is allowed and we haven't yet streamed that data. However, we must not allow streaming to start unless the snapshot has reached SNAPBUILD_CONSISTENT state. In passing, improve the comments atop ReorderBufferResetTXN to mention the case when we need to continue streaming after getting an error. Author: Amit Kapila Reviewed-by: Dilip Kumar Discussion: https://postgr.es/m/CAA4eK1KoOH0byboyYY40NBcC7Fe812trwTa+WY3jQF7WQWZbQg@mail.gmail.com https://git.postgresql.org/pg/commitdiff/8ae4ef4fb0e0331f02c4615182600546c8e5c4d4

== Pending Patches ==

Tatsuro Yamada sent in another revision of a patch to add \dX (extended statistics) to psql.

Andrey V. Lepikhov sent in another revision of a patch to remove unneeded self-joins.

David G. Johnston sent in another revision of a patch to make some minor cleanups and rewording of plpgsql docs.

Seino Yuki sent in another revision of a patch to add a new column, reset-time, to pg_stat_statements_info.

Justin Pryzby sent in a patch to make \dt+ pg_toast.* work in psql.

Justin Pryzby sent in another revision of a patch to make EXPLAIN show JIT details in non-text format, even if zero.

Peter Eisentraut sent in another revision of a patch to pause recovery for insufficient parameter settings.

Michaël Paquier sent in another revision of a patch to fix a bug that manifested as vac_update_datfrozenxid will raise "wrong tuple length" if pg_database tuple contains toast attribute.

Michaël Paquier sent in another revision of a patch to remove some references to long-obsolete behavior.

Peter Geoghegan sent in another revision of a patch to teach heapam to support bottom-up index deletion, pass down "logically unchanged index" hint, and teach nbtree to use bottom-up index deletion.

Bertrand Drouvot sent in two revisions of a patch to prevent functions from becoming orphaned by dropping their containing schemas.

Andrey V. Lepikhov sent in a patch to fix a wild overestimation of the cost of a foreign join.

Konstantin Knizhnik sent in another revision of a patch to implement custom compression for libpq.

Álvaro Herrera sent in a patch to improve operations spelled like INDEX CONCURRENTLY.

Peter Eisentraut sent in another revision of a patch to pageinspect to change the block number arguments to bigint, as this actually fits uint32.

Aleksey Kondratov, Michaël Paquier, and Justin Pryzby traded patches to allow CLUSTER and VACUUM FULL to change tablespace on the fly.

Bertrand Drouvot and Fujii Masao traded patches to log the standby recovery conflict waits.

Justin Pryzby sent in two more revisions of a patch to make it possible for INSERT SELECT to use a BulkInsertState.

Bharath Rupireddy sent in two more revisions of a patch to make it possible to use parallel inserts in CREATE TABLE AS.

James Coleman sent in another revision of a patch to error if gather merge paths aren't sufficiently sorted.

James Coleman sent in another revision of a patch to ensure that generate_useful_gather_paths doesn't skip unsorted subpaths, enforce parallel safety of pathkeys in generate_useful_gather_paths, disallow SRFs in proactive sort, remove volatile expr target search, and document find_em_expr_usable_for_sorting_rel in prepare_sort_from_pathkeys.

Craig Ringer sent in a patch to implement a TAP test utility module PG_LSN.pm to test things that involve logical sequence numbers.

Greg Nancarrow sent in another revision of a patch to add an in_hot_standby reportable GUC and make transaction_read_only GUC reportable, and enhance the connection parameter target_session_attrs to support new values: read-only/primary/standby/prefer-standby.

Jürgen Purtz sent in another revision of a patch to put more explicit JOINs in the tutorial.

Zhihong Yu sent in another revision of a patch to check nparts for defining index.

Hou Zhejie sent in a patch to replace lcons and list_delete_first in plan_union_children(), with lappend and list_delete_last.

Laurenz Albe sent in a patch to discard the query buffer if editor is quit in psql's \e.

Andres Freund sent in a patch to fix a JIT problem that came up with LLVM 12.

Fujii Masao sent in another revision of a patch to add basic statistics to the pg_stat_wal view.