PostgreSQL Weekly News - January 24, 2021

Posted on 2021-01-25 by PWN

PostgreSQL Weekly News - January 24, 2021

Person of the week:

PostgreSQL Product News

sqlite_fdw 1.3.1 released.

InfluxDB fdw 0.3 released

griddb_fdw 1.3 released.

pg_activity 2.0.0, a top-like application for PostgreSQL server activity monitoring, released.

PostgreSQL Jobs for January

PostgreSQL in the News

Planet PostgreSQL:

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áš Vondra pushed:

Heikki Linnakangas pushed:

Michaël Paquier pushed:

  • Refactor option handling of CLUSTER, REINDEX and VACUUM. This continues the work done in b5913f6. All the options of those commands are changed to use hex values rather than enums to reduce the risk of compatibility bugs when introducing new options. Each option set is moved into a new structure that can be extended with more non-boolean options (this was already the case of VACUUM). The code of REINDEX is restructured so as manual REINDEX commands go through a single routine from utility.c, like VACUUM, to ease the allocation handling of option parameters when a command needs to go through multiple transactions. This can be used as a base infrastructure for future patches related to those commands, including reindex filtering and tablespace support. Per discussion with people mentioned below, as well as Alvaro Herrera and Peter Eisentraut. Author: Michael Paquier, Justin Pryzby Reviewed-by: Alexey Kondratov, Justin Pryzby Discussion:

  • Fix ALTER DEFAULT PRIVILEGES with duplicated objects. Specifying duplicated objects in this command would lead to unique constraint violations in pg_default_acl or "tuple already updated by self" errors. Similarly to GRANT/REVOKE, increment the command ID after each subcommand processing to allow this case to work transparently. A regression test is added by tweaking one of the existing queries of privileges.sql to stress this case. Reported-by: Andrus Author: Michael Paquier Reviewed-by: Álvaro Herrera Discussion: Backpatch-through: 9.5

  • Add regression test for DROP OWNED BY with default ACLs. DROP OWNED BY has a specific code path to remove ACLs stored in pg_default_acl when cleaning up shared dependencies that had no coverage with the existing tests. This issue has been found while digging into the bug fixed by 21378e1. As ALTER DEFAULT PRIVILEGES impacts the ACLs of all objects created while the default permissions are visible, the test uses a transaction rollback to isolate the test and avoid any impact with other sessions running in parallel. Reviewed-by: Álvaro Herrera Discussion:

  • Switch "cl /?" to "cl /help" in MSVC scripts for platform detection. "cl /?" produces a different output if run on a real or a virtual drive (this can be set with a simple subst command), causing an error in the MSVC scripts if building on a virtual drive because the platform to use cannot be detected. "cl /help", on the contrary, produces a consistent output if used on a real or virtual drive. Changing to "/help" allows the compilation to work with a virtual drive as long as the top of the code repository is part of the drive, without impacting the build on real drives. Reported-by: Robert Grange Author: Juan José Santamaría Flecha Discussion:

  • Move SSL information callback earlier to capture more information. The callback for retrieving state change information during connection setup was only installed when the connection was mostly set up, and thus didn't provide much information and missed all the details related to the handshake. This also extends the callback with SSL_state_string_long() to print more information about the state change within the SSL object handled. While there, fix some comments which were incorrectly referring to the callback and its previous location in fe-secure.c. Author: Daniel Gustafsson Discussion:

  • Introduce SHA1 implementations in the cryptohash infrastructure. With this commit, SHA1 goes through the implementation provided by OpenSSL via EVP when building the backend with it, and uses as fallback implementation KAME which was located in pgcrypto and already shaped for an integration with a set of init, update and final routines. Structures and routines have been renamed to make things consistent with the fallback implementations of MD5 and SHA2. uuid-ossp has used for ages a shortcut with pgcrypto to fetch a copy of SHA1 if needed. This was built depending on the build options within ./configure, so this cleans up some code and removes the build dependency between pgcrypto and uuid-ossp. Note that this will help with the refactoring of HMAC, as pgcrypto offers the option to use MD5, SHA1 or SHA2, so only the second option was missing to make that possible. Author: Michael Paquier Reviewed-by: Heikki Linnakangas Discussion:

Fujii Masao pushed:

  • postgres_fdw: Add function to list cached connections to foreign servers. This commit adds function postgres_fdw_get_connections() to return the foreign server names of all the open connections that postgres_fdw established from the local session to the foreign servers. This function also returns whether each connection is valid or not. This function is useful when checking all the open foreign server connections. If we found some connection to drop, from the result of function, probably we can explicitly close them by the function that upcoming commit will add. This commit bumps the version of postgres_fdw to 1.1 since it adds new function. Author: Bharath Rupireddy, tweaked by Fujii Masao Reviewed-by: Zhijie Hou, Alexey Kondratov, Zhihong Yu, Fujii Masao Discussion:

  • doc: Add note about the server name of postgres_fdw_get_connections() returns. Previously the document didn't mention the case where postgres_fdw_get_connections() returns NULL in server_name column. Users might be confused about why NULL was returned. This commit adds the note that, in postgres_fdw_get_connections(), the server name of an invalid connection will be NULL if the server is dropped. Suggested-by: Zhijie Hou Author: Bharath Rupireddy Reviewed-by: Zhijie Hou, Fujii Masao Discussion:

Peter Eisentraut pushed:

Magnus Hagander pushed:

Robert Haas pushed:

Tom Lane pushed:

  • Add bytea equivalents of ltrim() and rtrim(). We had bytea btrim() already, but for some reason not the other two. Joel Jacobson Discussion:

  • Narrow the scope of a local variable. This is better style and more symmetrical with the other if-branch. This likely should have been included in 9de77b545 (which created the opportunity), but it was overlooked. Japin Li Discussion:

  • Avoid crash with WHERE CURRENT OF and a custom scan plan. execCurrent.c's search_plan_tree() assumed that ForeignScanStates and CustomScanStates necessarily have a valid ss_currentRelation. This is demonstrably untrue for postgres_fdw's remote join and remote aggregation plans, and non-leaf custom scans might not have an identifiable scan relation either. Avoid crashing by ignoring such nodes when the field is null. This solution will lead to errors like 'cursor "foo" is not a simply updatable scan of table "bar"' in cases where maybe we could have allowed WHERE CURRENT OF to work. That's not an issue for postgres_fdw's usages, since joins or aggregations would render WHERE CURRENT OF invalid anyway. But an otherwise-transparent upper level custom scan node might find this annoying. When and if someone cares to expend work on such a scenario, we could invent a custom-scan-provider callback to determine what's safe. Report and patch by David Geier, commentary by me. It's been like this for awhile, so back-patch to all supported branches. Discussion:

  • Remove faulty support for MergeAppend plan with WHERE CURRENT OF. Somebody extended search_plan_tree() to treat MergeAppend exactly like Append, which is 100% wrong, because unlike Append we can't assume that only one input node is actively returning tuples. Hence a cursor using a MergeAppend across a UNION ALL or inheritance tree could falsely match a WHERE CURRENT OF query at a row that isn't actually the cursor's current output row, but coincidentally has the same TID (in a different table) as the current output row. Delete the faulty code; this means that such a case will now return an error like 'cursor "foo" is not a simply updatable scan of table "bar"', instead of silently misbehaving. Users should not find that surprising though, as the same cursor query could have failed that way already depending on the chosen plan. (It would fail like that if the sort were done with an explicit Sort node instead of MergeAppend.) Expand the clearly-inadequate commentary to be more explicit about what this code is doing, in hopes of forestalling future mistakes. It's been like this for awhile, so back-patch to all supported branches. Discussion:

  • Disable vacuum page skipping in selected test cases. By default VACUUM will skip pages that it can't immediately get exclusive access to, which means that even activities as harmless and unpredictable as checkpoint buffer writes might prevent a page from being processed. Ordinarily this is no big deal, but we have a small number of test cases that examine the results of VACUUM's processing and therefore will fail if the page of interest is skipped. This seems to be the explanation for some rare buildfarm failures. To fix, add the DISABLE_PAGE_SKIPPING option to the VACUUM commands in tests where this could be an issue. In passing, remove a duplicated query in pageinspect/sql/page.sql. Back-patch as necessary (some of these cases are as old as v10). Discussion:

  • Further tweaking of PG_SYSROOT heuristics for macOS. It emerges that in some phases of the moon (perhaps to do with directory entry order?), xcrun will report that the SDK path is /Library/Developer/CommandLineTools/SDKs/MacOSX.sdk which is normally a symlink to a version-numbered sibling directory. Our heuristic to skip non-version-numbered pathnames was rejecting that, which is the wrong thing to do. We'd still like to end up with a version-numbered PG_SYSROOT value, but we can have that by dereferencing the symlink. Like the previous fix, back-patch to all supported versions. Discussion:

  • Fix pull_varnos' miscomputation of relids set for a PlaceHolderVar. Previously, pull_varnos() took the relids of a PlaceHolderVar as being equal to the relids in its contents, but that fails to account for the possibility that we have to postpone evaluation of the PHV due to outer joins. This could result in a malformed plan. The known cases end up triggering the "failed to assign all NestLoopParams to plan nodes" sanity check in createplan.c, but other symptoms may be possible. The right value to use is the join level we actually intend to evaluate the PHV at. We can get that from the ph_eval_at field of the associated PlaceHolderInfo. However, there are some places that call pull_varnos() before the PlaceHolderInfos have been created; in that case, fall back to the conservative assumption that the PHV will be evaluated at its syntactic level. (In principle this might result in missing some legal optimization, but I'm not aware of any cases where it's an issue in practice.) Things are also a bit ticklish for calls occurring during deconstruct_jointree(), but AFAICS the ph_eval_at fields should have reached their final values by the time we need them. The main problem in making this work is that pull_varnos() has no way to get at the PlaceHolderInfos. We can fix that easily, if a bit tediously, in HEAD by passing it the planner "root" pointer. In the back branches that'd cause an unacceptable API/ABI break for extensions, so leave the existing entry points alone and add new ones with the additional parameter. (If an old entry point is called and encounters a PHV, it'll fall back to using the syntactic level, again possibly missing some valid optimization.) Back-patch to v12. The computation is surely also wrong before that, but it appears that we cannot reach a bad plan thanks to join order restrictions imposed on the subquery that the PlaceHolderVar came from. The error only became reachable when commit 4be058fe9 allowed trivial subqueries to be collapsed out completely, eliminating their join order restrictions. Per report from Stephan Springl. Discussion:

  • Improve new wording of libpq's connection failure messages. "connection to server so-and-so failed:" seems clearer than the previous wording "could not connect to so-and-so:" (introduced by 52a10224e), because the latter suggests a network-level connection failure. We're now prefixing this string to all types of connection failures, for instance authentication failures; so we need wording that doesn't imply a low-level error. Per discussion with Robert Haas. Discussion:

  • Doc: remove misleading claim in documentation of PQreset(). This text claimed that the reconnection would occur "to the same server", but there is no such guarantee in the code, nor would insisting on that be an improvement. Back-patch to v10 where multi-host connection strings were added. Discussion:

  • Re-allow DISTINCT in pl/pgsql expressions. I'd omitted this from the grammar in commit c9d529848, figuring that it wasn't worth supporting. However we already have one complaint, so it seems that judgment was wrong. It doesn't require a huge amount of code, so add it back. (I'm still drawing the line at UNION/INTERSECT/EXCEPT though: those'd require an unreasonable amount of grammar refactoring, and the single-result-row restriction makes them near useless anyway.) Also rethink the documentation: this behavior is a property of all pl/pgsql expressions, not just assignments. Discussion:

  • Avoid redundantly prefixing PQerrorMessage for a connection failure. libpq's error messages for connection failures pretty well stand on their own, especially since commits 52a10224e/27a48e5a1. Prefixing them with 'could not connect to database "foo"' or the like is just redundant, and perhaps even misleading if the specific database name isn't relevant to the failure. (When it is, we trust that the backend's error message will include the DB name.) Indeed, psql hasn't used any such prefix in a long time. So, make all our other programs and documentation examples agree with psql's practice. Discussion:

  • Doc: improve directions for building on macOS. In light of recent discussions, we should instruct people to install Apple's command line tools; installing Xcode is secondary. Also, fix sample command for finding out the default sysroot, as we now know that the command originally recommended can give a result that doesn't match your OS version. Also document the workaround to use if you really don't want configure to select a sysroot at all. Discussion:

  • Suppress bison warning in ecpg grammar. opt_distinct_clause is only used in PLpgSQL_Expr, which ecpg ignores, so it needs to ignore opt_distinct_clause too. My oversight in 7cd9765f9; reported by Bruce Momjian. Discussion:

  • Update ecpg's connect-test1 for connection-failure message changes. I should have updated this in commits 52a10224e and follow-ons, but I missed it because it's not run by default, and none of the buildfarm runs it either. Maybe we should try to improve that situation. Discussion:

  • Doc: update example connection-failure messages in the documentation. Now that the dust has more or less settled on 52a10224e and follow-ons, make sure the examples in the documentation are up-to-date.

  • Doc: clean up contrib/pageinspect's GIST function documentation. I came to fix the overwidth-PDF-page warnings seen in the buildfarm, but stayed long enough to copy-edit some nearby text.

  • Add a simple test for contrib/auto_explain. This module formerly had zero test coverage. Discussion:

Bruce Momjian pushed:

Amit Kapila pushed:

Thomas Munro pushed:

Pending Patches

Craig Ringer sent in a patch to cross-reference comments on signal handling logic.

Joel Jacobson sent in a patch to implement, which parses catalog references out of catalogs.sgml.

Bertrand Drouvot sent in another revision of a patch to enable minimal logical decoding on standbys.

Justin Pryzby sent in another revision of a patch to g_dump: make CLUSTER ON a separate dump object since it needs to be restored after any child indexes are restored and attached.

John Naylor sent in another revision of a patch to make it possible to truncate timestamps on a wider selection of intervals.

Robert Haas sent in a patch to remove CheckpointLock.

Craig Ringer sent in a patch to add ProcessInterrupts_hook.

Atsushi Torikoshi sent in a patch to fix a corner case where the TOAST condition was wrong for the column size.

Kyotaro HORIGUCHI and Noah Misch traded patches to test for snapshot too old and wal_level=minimal, avoid using RelationNeedsWAL to identify relation persistence, and keep page-LSN updated while WAL-skipping.

Heikki Linnakangas sent in two more revisions of a patch to move a few ResourceOwnerEnlarge() calls for safety and clarity, make resowners more easily extensible, and make hash_resource_elem work better for the case where there's a 64-bit input.

Michail Nikolaev sent in a patch to add full support for indexing LP_DEAD hint bits on standbys.

Vigneshwaran C sent in another revision of a patch to add schema-level support for PUBLICATIONs.

Masahiko Sawada sent in two more revisions of a patch to introduce an IndexAM API for choosing the index vacuum strategy, choose index vacuum strategy based on same, and skip B-tree bulkdelete if the index doesn't grow.

Craig Ringer sent in a patch to add more docs on what (not) to do in extension code.

Atsushi Torikoshi sent in two more revisions of a patch to add a waitstart field to the pg_lock_status view and supporting function.

Heikki Linnakangas and Jürgen Purtz traded patches to add a chapter on architecture to the tutorial.

Justin Pryzby and Aleksey Kondratov traded patches to make it possible for REINDEX, CLUSTER and VACUUM FULL to change tablespaces on the fly.

Pavel Stěhule sent in three more revisions of a patch to implement schema variables.

Peter Smith sent in three more revisions of a patch to make it possible to use multiple background workers for tablesync.

Dilip Kumar sent in three more revisions of a patch to make pg_is_wal_replay_paused return the status of recovery pause.

Amit Langote sent in five revisions of a patch to make get_partition_for_tuple() usable in broader contexts, and use same to avoid using the SPI interface for some referential integrity checks.

Takamichi Osumi sent in another revision of a patch to make stronger safeguards for archive recovery.

Bruce Momjian sent in another revision of a patch to add key management.

Buzhen (步真) sent in a patch to change from a FIFO strategy to LRU to sweep a valid cache.

Kyotaro HORIGUCHI sent in another revision of a patch to make it possible to specify a CRL (certificate revocation list) directory, bringing it into line with the implementation of X509_STORE_load_locations.

Mark G sent in a patch to make the gaps array static in heapam.c.

Jie Zhang sent in a patch to make the output of pg_dump -?/--help more legible.

Michaël Paquier sent in a patch to paint some PG_USED_FOR_ASSERTS_ONLY in inline functions of ilist.h and bufpage.h.

David Fetter sent in another revision of a patch to surface popcount (count_set_bits) to SQL.

Greg Sabino Mullane sent in another revision of a patch to help psql's \df choose among the possible functions by supplying input argument types.

Justin Pryzby sent in another revision of a patch to implement CREATE TABLE (LIKE .. INCLUDING ACCESS METHOD).

Li Japin sent in a patch to use an array of booleans rather than a character pointer to denote NULLness of parameters in SPI.

Julien Rouhaud sent in another revision of a patch to add queryId to the pg_catalog.pg_stat_activity view.

Stephen Frost sent in two more revisions of a patch to change the default of checkpoint_completion_target to 0.9.

Dilip Kumar sent in another revision of a patch to implement custom compression methods for tables.

Stephen Frost and Craig Ringer traded patches to add a docs section for obsoleted and renamed functions and settings.

Vigneshwaran C sent in another revision of a patch to make it possible to print backtraces of postgres processes that are part of the instance in which pg_print_callstack() is called. This capability is guarded by a GUC.

Dmitry Dolgov, Pavel Stěhule, and Dian M Fay traded patches to use the generic type subscripting infrastructure for JSONB.

Daniel Gustafsson and Jacob Champion traded patches to support NSS as a libpq TLS backend.

Daniel Gustafsson sent in another revision of a patch to support checksum enable/disable in a running cluster.

Tom Lane sent in a patch to fix detection of pwritev support for OSX.

Hou Zhijie sent in another revision of a patch to add a nullif case for eval_const_expressions.

Masahiro Ikeda sent in a patch to refactor the variable names of global statistics messages to make it clear that they are messages.

Peter Smith sent in another revision of a patch to implement logical decoding of two-phase transactions.

Justin Pryzby sent in another revision of a patch to make INSERT SELECT use a BulkInsertState.

Alexander Korotkov sent in another revision of a patch to fix an infelicity between phrase search and multi-lexeme tokens.

Alexander Korotkov sent in a patch to make everything after the json ** operator use strict mode, as the lax mode produces surprises.

Nikita Glukhov and Erik Rijkers traded patches to implement SQL/JSON functions per the standard.

Anastasia Lubennikova sent in two more revisions of a patch intended to fix a bug that manifested as pg_upgrade fails with non-standard ACL by adding some extra checks to cover that situation.

David Rowley sent in a patch to correctly figure out the pages backwards scan with heap_setscanlimits().

Nikita Glukhov sent in another revision of a patch to implement JSON_TABLE per the standard.

Kyotaro HORIGUCHI sent in another revision of a patch to refactor the stats collector to use shared memory instead of files for temporary storage.

Julien Rouhaud sent in another revision of a patch to add a new COLLATION option to REINDEX.

David Rowley sent in another revision of a patch to make it possible to scan ranges of TIDs short of the entire relation, as sequential scan does.

Kyotaro HORIGUCHI sent in another revision of a patch to add a new pg_waitlsn() function.

Daniel Gustafsson sent in a patch to refactor the library-specific SSL test setup into a separate module in preparation for supporting TLS implementations other than OpenSSL.

Masahiko Sawada sent in a patch to add a bound check for encoded itermpointers before bsearch() in lazy_tid_reaped(), and inlines lazy_tid_reaped().

Pavel Stěhule sent in three revisions of a patch to add a plan cache for CALL.

Amul Sul sent in another revision of a patch to implement ALTER SYSTEM READ {ONLY,WRITE}.

Matthias van de Meent sent in a patch to Update postgres_fdw to import partitions when named in IMPORT SCHEMA LIMIT TO.

Vigneshwaran C sent in a patch to identify missing publications from publisher during CREATE/ALTER SUBSCRIPTION.

Álvaro Herrera sent in another revision of a patch to add tracing capability to libpq.

Peter Eisentraut and Tom Lane traded patches to add primary keys to the system catalogs.

Denis Laxalde sent in a patch to disable background workers during servers start in pg_upgrade.

Greg Nancarrow sent in another revision of a patch to make it possible to parallelize the execution of INSERT (INTO ... SELECT ...).

Álvaro Herrera sent in another revision of a patch to add batch/pipelining support for libpq.

Andrey Borodin sent in another revision of a patch to reorganize the pglz compression code to be more efficient, speeding it up by a factor of about 1.4.

Yugo Nagata sent in another revision of a patch to implement incremental maintenance of materialized views.

Tomáš Vondra sent in two more revisions of a patch to implement extended statistics for expressions.

Heikki Linnakangas sent in another revision of a patch to pg_rewind which makes it fetch small files according to new size.

Tomáš Vondra sent in another revision of a patch to make it possible to use non-volatile memory, if available, for WAL buffers.

Heikki Linnakangas sent in another revision of a patch to refactor the LogicalTapeSet/LogicalTape interface to make all the tape functions, like LogicalTapeRead and LogicalTapeWrite, take a LogicalTape as argument instead of LogicalTapeSet+tape number, replace the old polyphase merge algorithm with a simple balanced k-way merge, and fix the sizing of tape read buffers.

Tomáš Vondra sent in another revision of a patch to implement BRIN multi-range indexes.

Masahiro Ikeda sent in two more revisions of a patch to add columns to the pg_stat_wal view to track WAL I/O activity, and a new GUC, track_wal_io_timing, to enable collecting this, as the overhead could be large.

Jim Finnerty sent in another revision of a patch to prepare to implement 64-bit xids by adding a way to have 64-bit GUCs, using XID_FMT to format xids, and using ClogPageNumber in place of int for type safety.

Michaël Paquier sent in another revision of a patch to refactor the HMAC implementations into common/.

Amit Kapila and Bharath Rupireddy traded patches to fix the ALTER PUBLICATION...DROP TABLE behavior by initializing pubactions when RelationSyncEntry gets invalidated.

Mark Rofail sent in three more revisions of a patch to make it possible to check that each element of an array matches a primary key in (in general) a different table.

Thomas Munro sent in another revision of a patch to Use pg_pwrite() in pg_test_fsync.

Tomáš Vondra sent in a patch to test TOAST visibility.

Andy Fan sent in a patch to build some implied pruning quals to extend the use case of planning time partition pruning and init partition pruning.