PostgreSQL Weekly News - May 30, 2021

Posted on 2021-06-10 by PWN

PostgreSQL Weekly News - May 30, 2021

pgSCV, a Prometheus-compatible monitoring agent and metrics exporter for PostgreSQL, released.

Pgpool-II 4.2.3, 4.1.7, 4.0.14, 3.7.19 and 3.6.26, a connection pooler and statement replication system for PostgreSQL, released.

sqlite_fdw 1.2.0 released.

Crunchy PostgreSQL Operator 4.7.0, a system for deploying and managing open source PostgreSQL clusters on Kubernetes, released.

pgAdmin4 5.3, a web- and native GUI control center for PostgreSQL, released.

InfluxDB fdw 1.0.0 released

griddb_fdw 2.0 released.

PostgreSQL Product News

PostgreSQL Jobs for May

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

David Rowley pushed:

Tom Lane pushed:

  • Doc: move some catalogs.sgml entries to the right place. pg_statistic_ext_data.stxdexpr was listed under the wrong catalog, as was pg_stats_ext.exprs. Also there was a bogus entry for pg_statistic_ext_data.stxexprs. Apparently a merge failure in commit a4d75c86b. Guillaume Lelarge and Tom Lane Discussion:

  • Fix use of uninitialized variable in inline_function(). Commit e717a9a18 introduced a code path that bypassed the call of get_expr_result_type, which is not good because we need its rettupdesc result to pass to check_sql_fn_retval. We'd failed to notice right away because the code path in which check_sql_fn_retval uses that argument is fairly hard to reach in this context. It's not impossible though, and in any case inline_function would have no business assuming that check_sql_fn_retval doesn't need that value. To fix, move get_expr_result_type out of the if-block, which in turn requires moving the construction of the dummy FuncExpr out of it. Per report from Ranier Vilela. (I'm bemused by the lack of any compiler complaints...) Discussion:

  • Rethink definition of pg_attribute.attcompression. Redefine '\0' (InvalidCompressionMethod) as meaning "if we need to compress, use the current setting of default_toast_compression". This allows '\0' to be a suitable default choice regardless of datatype, greatly simplifying code paths that initialize tupledescs and the like. It seems like a more user-friendly approach as well, because now the default compression choice doesn't migrate into table definitions, meaning that changing default_toast_compression is usually sufficient to flip an installation's behavior; one needn't tediously issue per-column ALTER SET COMPRESSION commands. Along the way, fix a few minor bugs and documentation issues with the per-column-compression feature. Adopt more robust APIs for SetIndexStorageProperties and GetAttributeCompression. Bump catversion because typical contents of attcompression will now be different. We could get away without doing that, but it seems better to ensure v14 installations all agree on this. (We already forced initdb for beta2, anyway.) Discussion:

  • Reduce the range of OIDs reserved for Commit ab596105b increased FirstBootstrapObjectId from 12000 to 13000, but we've had some push-back about that. It's worrisome to reduce the daylight between there and FirstNormalObjectId, because the number of OIDs consumed during initdb for collation objects is hard to predict. We can improve the situation by abandoning the assumption that these OIDs must be globally unique. It should be sufficient for them to be unique per-catalog. (Any code that's unhappy about that is broken anyway, since no more than per-catalog uniqueness can be guaranteed once the OID counter wraps around.) With that change, the largest OID assigned during (starting from a base of 10000) is a bit under

  • This allows reverting FirstBootstrapObjectId to 12000 with reasonable confidence that that will be sufficient for many years to come. We are not, at this time, abandoning the expectation that hand-assigned OIDs (below 10000) are globally unique. Someday that'll likely be necessary, but the need seems years away still. This is late for v14, but it seems worth doing it now so that downstream software doesn't have to deal with the consequences of a change in FirstBootstrapObjectId. In any case, we already bought into forcing an initdb for beta2, so another catversion bump won't hurt. Discussion:

  • Doc: improve libpq service-file docs, avoid overspecifying pathnames. Clarify libpq.sgml's description of service file locations and semantics. Avoid use of backtick'ed pg_config calls to describe paths; that doesn't work on Windows, and even on Unix it's an idiom that not all readers may be instantly familiar with. Don't overspecify the locations of include files, instead writing only as much as you'd use in #include directives. The previous text in these places was incorrect for some installations, depending on where "postgresql" is in the install path. Our convention for referencing the user's home directory seems to be "~", so change the one place that spelled it "$HOME". install-windows.sgml follows the platform convention of spelling file paths with "\", so change the one place that used "/". Haiying Tang and Tom Lane Discussion:

Peter Geoghegan pushed:

Michaël Paquier pushed:

  • Disallow SSL renegotiation. SSL renegotiation is already disabled as of 48d23c72, however this does not prevent the server to comply with a client willing to use renegotiation. In the last couple of years, renegotiation had its set of security issues and flaws (like the recent CVE-2021-3449), and it could be possible to crash the backend with a client attempting renegotiation. This commit takes one extra step by disabling renegotiation in the backend in the same way as SSL compression (f9264d15) or tickets (97d3a0b0). OpenSSL 1.1.0h has added an option named SSL_OP_NO_RENEGOTIATION able to achieve that. In older versions there is an option called SSL3_FLAGS_NO_RENEGOTIATE_CIPHERS that was undocumented, and could be set within the SSL object created when the TLS connection opens, but I have decided not to use it, as it feels trickier to rely on, and it is not official. Note that this option is not usable in OpenSSL < 1.1.0h as the internal contents of the *SSL object are hidden to applications. SSL renegotiation concerns protocols up to TLSv1.2. Per original report from Robert Haas, with a patch based on a suggestion by Andres Freund. Author: Michael Paquier Reviewed-by: Daniel Gustafsson Discussion: Backpatch-through: 9.6

  • Fix memory leak when de-toasting compressed values in VACUUM FULL/CLUSTER. VACUUM FULL and CLUSTER can be used to enforce the use of the existing compression method of a toastable column if a value currently stored is compressed with a method that does not match the column's defined method. The code in charge of decompressing and recompressing toast values at rewrite left around the detoasted values, causing an accumulation of memory allocated in TopTransactionContext. When processing large relations, this could cause the system to run out of memory. The detoasted values are not needed once their tuple is rewritten, and this commit ensures that the necessary cleanup happens. Issue introduced by bbe0a81d. The comments of the area are reordered a bit while on it. Reported-by: Andres Freund Analyzed-by: Andres Freund Author: Michael Paquier Reviewed-by: Dilip Kumar Discussion:

  • Fix typo in heapam.c. Author: Hou Zhijie Discussion:

  • doc: Fix description of some GUCs in docs and postgresql.conf.sample. The following parameters have been imprecise, or incorrect, about their description (PGC_POSTMASTER or PGC_SIGHUP): - autovacuum_work_mem (docs, as of 9.6~) - huge_page_size (docs, as of 14~) - max_logical_replication_workers (docs, as of 10~) - max_sync_workers_per_subscription (docs, as of 10~) - min_dynamic_shared_memory (docs, as of 14~) - recovery_init_sync_method (postgresql.conf.sample, as of 14~) - remove_temp_files_after_crash (docs, as of 14~) - restart_after_crash (docs, as of 9.6~) - ssl_min_protocol_version (docs, as of 12~) - ssl_max_protocol_version (docs, as of 12~) This commit adjusts the description of all these parameters to be more consistent with the practice used for the others. Revewed-by: Justin Pryzby Discussion: Backpatch-through: 9.6

  • Fix MSVC scripts when building with GSSAPI/Kerberos. The deliverables of upstream Kerberos on Windows are installed with paths that do not match our MSVC scripts. First, the include folder was named "inc/" in our scripts, but the upstream MSIs use "include/". Second, the build would fail with 64-bit environments as the libraries are named differently. This commit adjusts the MSVC scripts to be compatible with the latest installations of upstream, and I have checked that the compilation was able to work with the 32-bit and 64-bit installations. Special thanks to Kondo Yuta for the help in investigating the situation in hamerkop, which had an incorrect configuration for the GSS compilation. Reported-by: Brian Ye Discussion: Backpatch-through: 9.6

Amit Kapila pushed:

Peter Eisentraut pushed:

Álvaro Herrera pushed:

Andrew Dunstan pushed:

Thomas Munro pushed:

Pending Patches

Fabien COELHO and Aleksander Alekseev traded patches to replace the rand48 pseudo-random number generator with something more appropriate to the 64-bit era.

Greg Nancarrow and Pavel Borisov traded patches to fix a parallel worker failed assertion and coredump.

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

Hou Zhijie and Amit Langote traded patches to skip partition tuple routing in the case where the partition key is constant.

Dilip Kumar, Tsutomu Yamada, and Kyotaro HORIGUCHI traded patches to fix a race condition in recovery.

Hou Zhijie sent in another revision of a patch to make it possible to parallelize INSERT ... SELECT.

Tom Lane sent in two revisions of a patch to fix an infelicity between CALL and procedures with output-only arguments.

Justin Pryzby sent in another revision of a patch to make WAL compression methods pluggable and default to lz4.

Andy Fan sent in a patch to use planner_rt_fetch instead of rt_fetch when root->simple_rte_array.

Ajin Cherian sent in five more revisions of a patch to skip empty transactions for logical replication.

Mark Dilger sent in another revision of a patch to delegate superuser tasks to new security roles.

Hou Zhijie, Bharath Rupireddy, and Tomáš Vondra traded patches to make sure postgres_fdw batching does not use too many parameters.

Bharath Rupireddy sent in a patch to provide TDE nonce size as an initdb option, add TDE nonce bytes to page pd_special structure, and adjust tests to account for configurable TDE nonce size.

Bharath Rupireddy sent in another revision of a patch to disambiguate error messages that use "non-negative."

Antonin Houska sent in a patch to narrow the cases where a concurrent UPDATE restarts heap_lock_tuple(), reducing unneeded calls to same.

Greg Sabino Mullane sent in a patch to speed up pg_checksums in cases where checksum already set by avoiding writing same when it's already set to the value expected.

Michaël Paquier sent in a patch intended to fix a bug that manifested as incorrect snapshots while promoting hot standby node when 2PC is used.

Bharath Rupireddy sent in two more revisions of a patch to check for duplicate options and error out if they're found in CREATE COLLATION.

Andrey V. Lepikhov sent in a patch to teach the optimizer to consider a partition-wise join of a non-partitioned table with each partition of a partitioned table.

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

Peter Eisentraut sent in a patch to fix RADIUS error reporting in hba file parsing.

Vigneshwaran C and Bharath Rupireddy traded patches to improve publication error messages.

Tom Lane sent in another revision of a patch to replace pg_depend PIN entries with a fixed range check.

Kyotaro HORIGUCHI sent in two revisions of a patch to change confusing 'bracket' use to clearer wording, and add test cases for trailing garbage of (multi)range types.

Bharath Rupireddy and Hou Zhijie traded patches to make it possible to use parallel inserts in CREATE TABLE AS.

Dilip Kumar sent in a patch to fix a memory leak in decoding speculative insert with toast.

Peter Geoghegan sent in a patch to Generalize VACUUM's INDEX_CLEANUP option in a way that lets users disable the index vacuum bypassing optimization added by commit 5100010e, as well as any similar optimizations that may be added in the future.

Paul Guo sent in another revision of a patch to fsync only the affected files/directories and use copy_file_range() for file copying in pg_rewind.

Takamichi Osumi sent in another revision of a patch to log the plans of long-running queries.

Daniel Gustafsson sent in another revision of a patch to support NSS as a libpq TLS backend.

Etsuro Fujita sent in a patch to fix rescans in asynchronous appends in the PostgreSQL FDW.

Vigneshwaran C sent in a patch to add alias types regpublication and regsubscription.

Fabien COELHO sent in a patch to reduce some duplication in psql's echo code.

Laurenz Albe sent in another revision of a patch to expand the docs on PostgreSQL extension coding and background worker development a little so that key topics like allocation, interrupt handling, exit callbacks, transaction callbacks, PG_TRY()/PG_CATCH(), resource owners, transaction and snapshot state, etc are at least briefly mentioned with a few pointers to where to learn more.

Tom Lane sent in a patch to reduce memory consumption for pending inval messages.

Andreas Karlsson sent in a patch to shrink GISTSTATE.

Yura Sokolov sent in a patch to clear empty space in a page.

Tomáš Vondra sent in a patch to revert part of the COPY FREEZE improvements, namely tweaking heap_multi_insert, and removing most of 39b66a91bd (except for the heap_xlog_multi_insert bit).

Thomas Munro sent in two revisions of a patch to support direct I/O on macOS.