PostgreSQL Weekly News - July 4, 2021

Posted on 2021-07-05 by PWN

PostgreSQL Weekly News - July 4, 2021

Congratulations to the new PostgreSQL committers, Daniel Gustafsson and John Naylor!

PostgreSQL Product News

pg_dumpbinary 2.4, a program used to dump a PostgreSQL database in binary format, released.

PostgreSQL Jobs for July

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

Michaël Paquier pushed:

Thomas Munro pushed:

Amit Kapila pushed:

Peter Eisentraut pushed:

Andrew Dunstan pushed:

Peter Geoghegan pushed:

Tom Lane pushed:

  • Don't depend on -fwrapv semantics in pgbench's random() function. Instead use the common/int.h functions to check for integer overflow in a more C-standard-compliant fashion. This is motivated by recent failures on buildfarm member moonjelly, where it appears that development-tip gcc is optimizing without regard to the -fwrapv switch. Presumably that's a gcc bug that will be fixed soon, but we might as well install cleaner coding here rather than wait. (This does not address the question of whether we'll ever be able to get rid of using -fwrapv. Testing shows that this spot is the only place where doing so creates visible regression test failures, but unfortunately that proves very little.) Back-patch to v12. The common/int.h functions exist in v11, but that branch doesn't use them in any client-side code. I judge that this case isn't interesting enough in the real world to take even a small risk of issues from being the first such use. Tom Lane and Fabien Coelho Discussion:

  • Don't use abort(3) in libpq's fe-print.c. Causing a core dump on out-of-memory seems pretty unfriendly, and surely is far outside the expected behavior of a general-purpose library. Just print an error message (as we did already) and return. These functions unfortunately don't have an error return convention, but code using them is probably just looking for a quick-n-dirty print method and wouldn't bother to check anyway. Although these functions are semi-deprecated, it still seems appropriate to back-patch this. In passing, also back-patch b90e6cef1, just to reduce cosmetic differences between the branches. Discussion:

  • Doc: further updates for RELEASE_CHANGES process notes. Mention expectations for email notifications of appropriate lists when a branch is made or retired. (I've been doing that informally for years, but it's better to have it written down.)

  • Remove libpq's use of abort(3) to handle mutex failure cases. Doing an abort() seems all right in development builds, but not in production builds of general-purpose libraries. However, the functions that were doing this lack any way to report a failure back up to their callers. It seems like we can just get away with ignoring failures in production builds, since (a) no such failures have been reported in the dozen years that the code's been like this, and (b) failure to enforce mutual exclusion during fe-auth.c operations would likely not cause any problems anyway in most cases. (The OpenSSL callbacks that use this macro are obsolete, so even less likely to cause interesting problems.) Possibly a better answer would be to break compatibility of the pgthreadlock_t callback API, but in the absence of field problem reports, it doesn't really seem worth the trouble. Discussion:

  • Add a build-time check that libpq doesn't call exit() or abort(). Directly exiting or aborting seems like poor form for a general-purpose library. Now that libpq liberally uses bits out of src/common/, it's very easy to accidentally include code that would do something unwanted like calling exit(1) after OOM --- see for example 8ec00dc5c. Hence, add a simple cross-check that no such calls have made it into The cross-check depends on nm(1) being available and being able to work on a shared library, which probably isn't true everywhere. But we can just make the test silently do nothing if nm fails. As long as the check is effective on common platforms, that should be good enough. (By the same logic, I've not worried about providing an equivalent test in MSVC builds.) Discussion:

  • Fix bogus logic for reporting which hash partition conflicts. Commit efbfb6424 added logic for reporting exactly which existing partition conflicts when complaining that a new hash partition's modulus isn't compatible with the existing ones. However, it misunderstood the partitioning data structure, and would select the wrong partition in some cases, or crash outright due to fetching a bogus table OID in other cases. Per bug #17076 from Alexander Lakhin. Fix by Amit Langote; some further work on the code comments by me. Discussion:

  • Fix portability fallout from commit dc227eb82. Give up on trying to mechanically forbid abort() within libpq. Even though there are no such calls in the source code, we've now seen three different scenarios where build toolchains silently insert such calls: gcc does it for profiling, some platforms implement assert() using it, and icc does so for no visible reason. Checking for accidental use of exit() seems considerably more important than checking for abort(), so we'll settle for doing that for now. Also, filter out __cxa_atexit() to avoid a false match. It seems that OpenBSD inserts a call to that despite the fact that libpq contains no C++ code. Discussion:

  • Improve build-time check that libpq doesn't call exit(). Further fixes for commit dc227eb82. Per suggestion from Peter Eisentraut, use a stamp-file to control when the check is run, avoiding repeated executions during "make all". Also, remove "-g" switch for nm: it's useless and some versions of nm consider it to conflict with "-u". (Thanks to Noah Misch for running down that portability issue.) Discussion:

  • Add --clobber-cache option to initdb, for CCA testing. Commit 4656e3d66 replaced the "#define CLOBBER_CACHE_ALWAYS" testing mechanism with a GUC, which has been a great help for doing cache-clobber testing in more efficient ways; but there is a gap in the implementation. The only way to do cache-clobber testing during an initdb run is to use the old method with #define, because one can't set the GUC from outside. Improve this by adding a switch to initdb for the purpose. (Perhaps someday we should let initdb pass through arbitrary "-c NAME=VALUE" switches. Quoting difficulties dissuaded me from attempting that right now, though.) Back-patch to v14 where 4656e3d66 came in. Discussion:

  • Don't try to print data type names in slot_store_error_callback(). The existing code tried to do syscache lookups in an already-failed transaction, which is problematic to say the least. After some consideration of alternatives, the best fix seems to be to just drop type names from the error message altogether. The table and column names seem like sufficient localization. If the user is unsure what types are involved, she can check the local and remote table definitions. Having done that, we can also discard the LogicalRepTypMap hash table, which had no other use. Arguably, LOGICAL_REP_MSG_TYPE replication messages are now obsolete as well; but we should probably keep them in case some other use emerges. (The complexity of removing something from the replication protocol would likely outweigh any savings anyhow.) Masahiko Sawada and Bharath Rupireddy, per complaint from Andres Freund. Back-patch to v10 where this code originated. Discussion:

  • Further restrict the scope of no-exit()-in-libpq test. Disable this check altogether in --enable-coverage builds, because newer versions of gcc insert exit() as well as abort() calls for that. Also disable it on AIX and Solaris, because those platforms tend to provide facilities such as libldap as static libraries, which then get included in libpq's shlib. We can't expect such libraries to honor our coding rules. (That platform list might need additional tweaking, but I think this is enough to keep the buildfarm happy.) Per reports from Jacob Champion and Noah Misch. Discussion:

Noah Misch pushed:

Álvaro Herrera pushed:

Alexander Korotkov pushed:

Fujii Masao pushed:

David Rowley pushed:

  • Improve various places that double the size of a buffer. Several places were performing a tight loop to determine the first power of 2 number that's > or

    = the required memory. Instead of using a loop for that, we can use pg_nextpower2_32 or pg_nextpower2_64. When we need a power of 2 number equal to or greater than a given amount, we just pass the amount to the nextpower2 function. When we need a power of 2 greater than the amount, we just pass the amount + 1. Additionally, in tsearch there were a couple of locations that were performing a while loop when a simple "if" would have done. In both of these locations only 1 item is being added, so the loop could only have ever iterated once. Changing the loop into an if statement makes the code very slightly more optimal as the condition is checked once rather than twice. There are quite a few remaining locations that increase the size of the buffer in the following form: while (reqsize >= buflen) { buflen *= 2; buf = repalloc(buf, buflen); } These are not touched in this commit. repalloc will error out for sizes larger than MaxAllocSize. Changing these to use pg_nextpower2_32 would remove the chance of that error being raised. It's unclear from the code if the sizes could ever become that large, so err on the side of caution. Discussion: Reviewed-by: Zhihong Yu

  • Cleanup some aggregate code in the executor. Here we alter the code that calls build_pertrans_for_aggref() so that the function no longer needs to special-case whether it's dealing with an aggtransfn or an aggcombinefn. This allows us to reuse the build_aggregate_transfn_expr() function and just get rid of the build_aggregate_combinefn_expr() completely. All of the special case code that was in build_pertrans_for_aggref() has been moved up to the calling functions. This saves about a dozen lines of code in nodeAgg.c and a few dozen more in parse_agg.c Also, rename a few variables in nodeAgg.c to try to make it more clear that we're working with either a aggtransfn or an aggcombinefn. Some of the old names would have you believe that we were always working with an aggtransfn. Discussion:

  • Doc: mention that VACUUM can't utilize over 1GB of RAM. Document that setting maintenance_work_mem to values over 1GB has no effect on VACUUM. Reported-by: Martín Marqués Author: Laurenz Albe Discussion: Backpatch-through: 9.6, oldest supported release

Heikki Linnakangas pushed:

Bruce Momjian pushed:

Pending Patches

Anna Akenteva sent in another revision of a patch to write the visibility map during CLUSTER/VACUUM FULL.

Julien Rouhaud sent in another revision of a patch to Expose get_query_def().

Arne Roland sent in another revision of a patch to rename triggers of partitioned tables.

Peter Eisentraut sent in a patch to remove redundant initializations.

Andrew Dunstan sent in a patch to clean up

Masahiko Sawada sent in a patch to add ALTER SUBSCRIPTION SET SKIP TRANSACTION, add errcontext to errors of the applying logical replication changes, and add a pg_stat_logical_replication_error statistics view.

Bharath Rupireddy, Amit Kapila, and Álvaro Herrera traded patches to refactor function parse_subscription_options. Instead of using multiple parameters in parse_subscription_options function signature, use the struct SubOpts that encapsulate all the subscription options and their values. It will be useful for future work where we need to add other options in the subscription. Also, use bitmaps to pass the supported and retrieve the specified options.

Jeff Davis sent in a patch to add a synchronous_replication_interrupt GUC.

Daniel Gustafsson sent in two revisions of a patch to fix the sscanf limit in pg_basebackup and pg_dump, and fix bug in TOC file error message printing by unshadowing a variable name.

Dean Rasheed sent in three revisions of a patch to intended to fix a bug that manifested as overflows in multiplication of NUMERICs.

Michaël Paquier sent in another revision of a patch to add wal_compression=zstd.

Magnus Hagander sent in two more revisions of a patch to add PROXY protocol support.

Peter Eisentraut sent in another revision of a patch to psql to get it to display multiple result sets instead of silently dropping all but the first, and make it possible to return dynamic result sets from procedures.

Ranier Vilela sent in a patch to prevent choosing an invalid number of partitions in src/backend/executor/nodeAgg.c.

Aleksey Kondratov sent in a patch to enable providing restore_command as a command line option to pg_rewind.

David Christensen sent in a patch to expand the possible units for size, and refactor pg_size_pretty and pg_size_bytes to allow for supported unit expansion.

Andrey V. Lepikhov sent in a patch to add an '--ignore-errors' option to pg_regress.

Jacob Champion sent in another revision of a patch to rework the sslfiles Makefile target.

Josef Šimánek sent in two revisions of a patch to prevent BRIN index updates from blocking HOT updates.

Michaël Paquier sent in another revision of a patch to speed up pg_checksums in cases where the checksum is already set.

Anastasia Lubennikova and Yura Sokolov traded patches to make the smgr API extensible via new hooks smgr_hook, smgr_init_hook, and smgr_shutdown_hook.

John Naylor and Heikki Linnakangas traded patches to rewrite pg_utf8_verifystr() for speed.

Dean Rasheed sent in a patch to make numeric x^y work for negative x.

Georgios Kokolatos sent in a patch to teach pg_receivewal to use lz4 compression.

Dean Rasheed sent in two revisions of a patch to relax the constraints on numeric scale to allow, for example, NUMERIC(3,-3), which rounds values to the nearest thousand and hold values up to 999000, or NUMERIC(3,6) which could hold "micro" quantities up to 0.000999.

Thomas Munro and John Naylor traded patches to accelerate tuple sorting for common types.

Masahiko Sawada sent in another revision of a patch to make it possible to have transactions involving multiple postgres foreign servers.

Andrey V. Lepikhov sent in a patch to choose async append subplans at the initial execution stage.

Takashi Menjo sent in another revision of a patch to map WAL segment files on PMEM as WAL buffers.

Yugo Nagata sent in two more revisions of a patch to fix conn_duration in pgbench.

Simon Riggs sent in a patch to enhance getRecordTimestamp() in PITR to include all record types that contain times, add handling for checkpoints, end of recovery and prepared xact record types, and allow the option of recovery_target_use_origin_time = off (default) | on.

Vigneshwaran C sent in another revision of a patch to ensure that error message include hint messages for redundant options error.

Vigneshwaran C sent in another revision of a patch to identify missing publications from publishers during CREATE/ALTER SUBSCRIPTION.

Álvaro Herrera sent in a patch to clarify error messages when Perl's system() fails.

Antonin Houska sent in another revision of a patch to clean up up orphaned files using undo logs.

Jacob Champion sent in another revision of a patch to pull the general SASL framework out of the SCRAM code.

David Rowley sent in a patch to record a Bitmapset of non-pruned partitions.

Fabien COELHO sent in three more revisions of a patch to fix a bug in pgbench logging which was caused by some recent changes to time logic.

David Christensen and Fabien COELHO traded patches to add a multiconnect option to pgbench.

Bharath Rupireddy sent in two more revisions of a patch to tighten up batch_size, fetch_size options against non-numeric values in the postgres_fdw.

Rahila Syed sent in a patch to add column filtering to logical replication.

Mark Dilger sent in two more revisions of a patch to add a default roles for each of managing logical replication, host security operations, network security operations, and database operations, and Allow SET and ALTER SYSTEM SET by non-superusers.

Yugo Nagata sent in another revision of a patch intended to fix a bug that manifested as pgbench serialization and deadlock errors.

Amit Langote sent in a patch to explicitly track RT indexes of relations to check permissions.

Richard Guo sent in two more revisions of a patch to use each rel as both outer and inner for anti joins.

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

Paul A Jungwirth sent in three more revisions of a patch to implement SQL standard application time.

Gurjeet Singh sent in a patch to add automatic notification of top transaction IDs.

David Rowley sent in a patch to allow some window functions to finish execution early.

Greg Smith sent in a patch to improve some pgbench workloads in the arenas of INSERT workload, FK indexes, and filler.

Euler Taveira de Oliveira sent in another revision of a patch to add SSL/TLS-related acronyms MITM and SNI, and change usages of SSL to the more modern and general SSL/TLS.

Kyotaro HORIGUCHI sent in two revisions of a patch to fix a bug that manifested as ECPG pre-compiling CREATE AS EXECUTE to an un-compilable C statement by avoiding touching the parse tree structure and use ExecuteStmt.type to notify whether the returned string is a statment name or a full statement.

Euler Taveira de Oliveira and Peter Smith traded patches to implement row filtering for logical replication.

Ranier Vilela sent in a patch to fix an uninitialized variable access in src/backend/utils/mmgr/freepage.c.

Fabien COELHO sent in five more revisions of a patch to replace rand48 with a better PRNG.

Haotian Wu sent in another revision of a patch to add a --drop-cascade option pg_dump/restore.

Pavel Stěhule sent in another revision of a patch to implement schema variables.

Hayato Kuroda sent in another revision of a patch to fix bugs in ECPG's DECLARE STATEMENT, DEALLOCATE, and DESCRIBE.

Justin Pryzby sent in another revision of a patch to make pg_ls_* show directories and shared filesets.

Fabien COELHO sent in another revision of a patch to factor out psql's echo code.

Vigneshwaran C sent in a patch ensure that logical replication relations get the needed cache invalidation when there is a change in schema by adding a callback for schema changes.

Tom Lane sent in two more revisions of a patch to avoid catalogue accesses in conversion_error_calls.

Tom Lane sent in another revision of a patch to remove the 64k rangetable limit.

Andy Fan sent in a patch to add not null attrs for RelOptInfo.

Peter Eisentraut sent in a patch to use EVP_EncryptFinal_ex() and EVP_DecryptFinal_ex() for compatibility with OpenSSL 3.0.

Gilles Darold sent in another revision of a patch to add hooks at the XactCommand level.

Zhihong Yu sent in a patch to remove a redundant check for transaction in progress in check_safe_enum_use.

Tatsuo Ishii sent in another revision of a patch to use COPY FREEZE in pgbench.

David Rowley sent in a patch to better document the locking behavior when attaching and removing partitions.