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

PostgreSQL Weekly News - July 25, 2021

Posted on 2021-07-26 by PWN
PWN

PostgreSQL Weekly News - July 25, 2021

Person of the week

Austrian pgDay will take place September 17, 2021 at Schloss Schoenbrunn (Apothekertrakt) in Vienna. https://pgday.at/en/

PostgreSQL Product News

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

pg_validate_extugprade 1.0.0beta, a tool for testing various combinations of upgrade process for extensions, released

pspg 5.1.0 a pager designed for PostgreSQL, released. https://github.com/okbob/pspg/releases/tag/5.1.0

Database Lab 2.4, a tool for fast cloning of large PostgreSQL databases to build non-production environments, released: https://gitlab.com/postgres-ai/database-lab/-/releases

PostgreSQL Jobs for July

https://archives.postgresql.org/pgsql-jobs/2021-07/

PostgreSQL in the News

Planet PostgreSQL: https://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

Thomas Munro pushed:

Amit Kapila pushed:

Michaël Paquier pushed:

  • doc: Mention CASCADE/RESTRICT for DROP STATISTICS. This grammar has no effect as there are no dependencies on statistics, but it is supported by the parser. This is more consistent with the other DROP commands. Author: Vignesh C Discussion: https://postgr.es/m/CALDaNm1LA=yNmzcSfy+0oe6CEAgsxXRf_-UutE3ZncFi8QkFNQ@mail.gmail.com Backpatch-through: 10 https://git.postgresql.org/pg/commitdiff/d1216635610a9855a8ec139e55cd852f67d5ee81

  • Fix some issues with WAL segment opening for pg_receivewal --compress. The logic handling the opening of new WAL segments was fuzzy when using --compress if a partial, non-compressed, segment with the same base name existed in the repository storing those files. In this case, using --compress would cause the code to first check for the existence and the size of a non-compressed segment, followed by the opening of a new compressed, partial, segment. The code was accidentally working correctly on most platforms as the buildfarm has proved, except bowerbird where gzflush() could fail in this code path. It is wrong anyway to take the code path used pre-padding when creating a new partial, non-compressed, segment, so let's fix it. Note that this issue exists when users mix successive runs of pg_receivewal with or without compression, as discovered with the tests introduced by ffc9dda. While on it, this refactors the code so as code paths that need to know about the ".gz" suffix are down from four to one in walmethods.c, easing a bit the introduction of new compression methods. This addresses a second issue where log messages generated for an unexpected failure would not show the compressed segment name involved, which was confusing, printing instead the name of the non-compressed equivalent. Reported-by: Georgios Kokolatos Discussion: https://postgr.es/m/YPDLz2x3o1aX2wRh@paquier.xyz Backpatch-through: 10 https://git.postgresql.org/pg/commitdiff/7fbe0c8c4d4fe429ee1d6383706ea5ccb0f639d3

  • Re-enable TAP tests of pg_receivewal for ZLIB on Windows. This is a revert of 6cea447, that disabled those tests temporarily on Windows due to failures with bowerbird where gzflush() would fail when executed on a freshly-opened compressed and partial segment. This problem should be taken care of now thanks to 7fbe0c8, so let's see what the buildfarm has to say on Windows for those tests. Discussion: https://postgr.es/m/YPDLz2x3o1aX2wRh@paquier.xyz https://git.postgresql.org/pg/commitdiff/91d395f47aa92849b2556b1a4d6bc1ff34121a30

  • Disable TAP tests of pg_receivewal for ZLIB on Windows. This reverts commit 91d395f, to avoid running those tests on Windows. The tests are globally stable across all buildfarm members, except fairywren (crash of pg_receivewal) and bowerdird (SIGBREAK preventing the buildfarm run to complete). Those errors are rather strange, as other hosts with very similar characteristics are able to run those tests without breaking a sweat. For now, disable those tests on Windows to turn back the buildfarm to green. Per discussion with Andrew Dunstan. Discussion: https://postgr.es/m/9040d5ed-6462-66a4-07ac-2923785ae563@dunslane.net https://git.postgresql.org/pg/commitdiff/6a2c532c2230159b909382cfa58494ef9d6df703

  • Unify parsing logic for command-line integer options. Most of the integer options for command-line binaries now make use of a single routine able to do the job, fixing issues with the detection of sloppy values caused for example by the use of atoi(), that fails on strings beginning with numerical characters with junk trailing characters. This commit cuts down the number of strings requiring translation by 26 per my count, switching the code to have two error types for invalid and out-of-range values instead. Much more could be done here, with float or even int64 options, but int32 was the most appealing case as it is possible to rely on strtol() to do the job reliably. Note that there are some exceptions for now, like pg_ctl or pg_upgrade that use their own logging logic. A couple of negative TAP tests required some adjustments for the new errors generated. pg_dump and pg_restore tracked the maximum number of parallel jobs within the option parsing. The code is refactored a bit to track that in the code dedicated to parallelism instead. Author: Kyotaro Horiguchi, Michael Paquier Reviewed-by: David Rowley, Álvaro Herrera Discussion: https://postgr.es/m/CALj2ACXqdG9WhqVoJ9zYf-iZt7sgK7Szv5USs=he6NnWQ2ofTA@mail.gmail.com https://git.postgresql.org/pg/commitdiff/6f164e6d17616a157ea5d9e34dbb1b211c080c41

  • Add missing header declarations for pg_basebackup and pg_{dump,restore}. This fixes two compilation failures caused by 6f164e6. Interesting to see that missing <limits.h> dies not fail in Linux or even Windows. On MacOS, it fails, though. Per various buildfarm members. https://git.postgresql.org/pg/commitdiff/bc0cc68f8a1648029424e9300b2e4457acd474a6

Peter Eisentraut pushed:

Álvaro Herrera pushed:

Peter Geoghegan pushed:

Tom Lane pushed:

  • Fix corner-case uninitialized-variable issues in plpgsql. If an error was raised during our initial attempt to check whether a successfully-compiled expression is "simple", subsequent calls of exec_stmt_execsql would suppose that stmt->mod_stmt was already computed when it had not been. This could lead to assertion failures in debug builds; in production builds the effect would typically be to act as if INTO STRICT had been specified even when it had not been. Of course that only matters if the subsequent attempt to execute the expression succeeds, so that the problem can only be reached by fixing a failure in some referenced, inline-able SQL function and then retrying the calling plpgsql function in the same session. (There might be even-more-obscure ways to change the expression's behavior without changing the plpgsql function, but that one seems like the only one people would be likely to hit in practice.) The most foolproof way to fix this would be to arrange for exec_prepare_plan to not set expr->plan until we've finished the subsidiary simple-expression check. But it seems hard to do that without creating reference-count leak issues. So settle for documenting the hazard in a comment and fixing exec_stmt_execsql to test separately for whether it's computed stmt->mod_stmt. (That adds a test-and-branch per execution, but hopefully that's negligible in context.) In v11 and up, also fix exec_stmt_call which had a variant of the same issue. Per bug #17113 from Alexander Lakhin. Back-patch to all supported branches. Discussion: https://postgr.es/m/17113-077605ce00e0e7ec@postgresql.org https://git.postgresql.org/pg/commitdiff/d9809bf8694c17e05537c5dd96cde3e67c02d52a

  • Doc: improve documentation about exponentiation operator. Now that we're not having to wedge this into the straitjacket of the old operator table format, we can add another example to clarify the point about left-to-right associativity. Per suggestion from mdione at grulic.org.ar. https://postgr.es/m/162661954599.693.13700316547731859171@wrigleys.postgresql.org https://git.postgresql.org/pg/commitdiff/7fa1e1ef741964eeb50f33d7c72622658bb7e5f4

  • Fix failure of some headers to compile "standalone". Recently-added references to ParseState weren't covered by #include references, creating unwanted ordering dependencies for users of these headers. Oversight in commit 2bfb50b3d. Per headerscheck/cpluspluscheck. https://git.postgresql.org/pg/commitdiff/678f5448c2d86976a98b402ef14482a8ba3b159b

  • Remove configure-time thread safety checking (thread_test.c). This testing was useful when it was written, nigh twenty years ago, but it seems fairly pointless for any platform built in the last dozen or more years. (Compare also the comments at 8a2121185.) Also we now have reports that the test program itself fails under ThreadSanitizer. Rather than invest effort in fixing it, let's just drop it, and assume that the few people who still care already know they need to use --disable-thread-safety. Back-patch into v14, for consistency with 8a2121185. Discussion: https://postgr.es/m/CADhDkKzPSiNvA3Hyq+wSR_icuPmazG0cFe=YnC3U-CFcYLc8Xw@mail.gmail.com https://git.postgresql.org/pg/commitdiff/76fa3db33654e543b5c796e28c6fc5b505a19c2a

  • Make printf("%s", NULL) print "(null)" instead of crashing. We previously took a hard-line attitude that callers should never print a null string pointer, and doing so is worthy of an assertion failure or crash. However, we've long since flushed out any easy-to-find bugs of that nature. What remains is a lot of code that perhaps could fail that way in hard-to-reach corner cases. For example, in something as simple as ereport(ERROR, (errcode(ERRCODE_UNDEFINED_OBJECT), errmsg("constraint \"%s\" for table \"%s\" does not exist", conname, get_rel_name(relid)))); one must wonder whether it's completely guaranteed that get_rel_name cannot return NULL in this context. If such a situation did occur, the existing policy converts what might be a pretty minor bug into a server crash condition. This is not good for robustness. Hence, let's follow the lead of glibc and print "(null)" instead of failing. We should, of course, still consider it a bug if that behavior is reachable in ordinary use; but crashing seems less desirable than not crashing. This fix works across-the-board in v12 and up, where we always use src/port/snprintf.c. Before that, on most platforms we're at the mercy of the local libc, but it appears that Solaris 10 is the only supported platform where we'd still get a crash. Most other platforms such as *BSD, macOS, and Solaris 11 have adopted glibc's behavior at some point. (AIX and HPUX just print "" not "(null)", but that's close enough.) I've not checked what Windows' native printf would do, but it doesn't matter because we've long used snprintf.c on that platform. In v12 and up, also const-ify related code so that we're not casting away const on the constant string. This is just neatnik-ism, since next to no compilers will warn about that. Discussion: https://postgr.es/m/17098-b960f3616c861f83@postgresql.org https://git.postgresql.org/pg/commitdiff/3779ac62d709467fe6331c8f0285d42e7487a01c

  • Fix check for conflicting session- vs transaction-level locks. We have an implementation restriction that PREPARE TRANSACTION can't handle cases where both session-lifespan and transaction-lifespan locks are held on the same lockable object. (That's because we'd otherwise need to acquire a new PROCLOCK entry during post-prepare cleanup, which is an operation that might fail. The situation can only arise with odd usages of advisory locks, so removing the restriction is probably not worth the amount of effort it would take.) AtPrepare_Locks attempted to enforce this, but its logic was many bricks shy of a load, because it only detected cases where the session and transaction locks had the same lockmode. Locks of different modes on the same object would lead to the rather unhelpful message "PANIC: we seem to have dropped a bit somewhere". To fix, build a transient hashtable with one entry per locktag, not one per locktag + mode, and use that to detect conflicts. Per bug #17122 from Alexander Pyhalov. This bug is ancient, so back-patch to all supported branches. Discussion: https://postgr.es/m/17122-04f3c32098a62233@postgresql.org https://git.postgresql.org/pg/commitdiff/6310809c4aa146b3996a35524955c6c6943d241a

  • Get rid of artificial restriction on hash table sizes on Windows. The point of introducing the hash_mem_multiplier GUC was to let users reproduce the old behavior of hash aggregation, i.e. that it could use more than work_mem at need. However, the implementation failed to get the job done on Win64, where work_mem is clamped to 2GB to protect various places that calculate memory sizes using "long int". As written, the same clamp was applied to hash_mem. This resulted in severe performance regressions for queries requiring a bit more than 2GB for hash aggregation, as they now spill to disk and there's no way to stop that. Getting rid of the work_mem restriction seems like a good idea, but it's a big job and could not conceivably be back-patched. However, there's only a fairly small number of places that are concerned with the hash_mem value, and it turns out to be possible to remove the restriction there without too much code churn or any ABI breaks. So, let's do that for now to fix the regression, and leave the larger task for another day. This patch does introduce a bit more infrastructure that should help with the larger task, namely pg_bitutils.h support for working with size_t values. Per gripe from Laurent Hasson. Back-patch to v13 where the behavior change came in. Discussion: https://postgr.es/m/997817.1627074924@sss.pgh.pa.us Discussion: https://postgr.es/m/MN2PR15MB25601E80A9B6D1BA6F592B1985E39@MN2PR15MB2560.namprd15.prod.outlook.com https://git.postgresql.org/pg/commitdiff/28d936031a86d94806c6604480ff3f3f169b371c

Fujii Masao pushed:

John Naylor pushed:

David Rowley pushed:

  • Make nodeSort.c use Datum sorts for single column sorts. Datum sorts can be significantly faster than tuple sorts, especially when the data type being sorted is a pass-by-value type. Something in the region of 50-70% performance improvements appear to be possible. Just in case there's any confusion; the Datum sort is only used when the targetlist of the Sort node contains a single column, not when there's a single column in the sort key and multiple items in the target list. Author: Ronan Dunklau Reviewed-by: James Coleman, David Rowley, Ranier Vilela, Hou Zhijie Tested-by: John Naylor Discussion: https://postgr.es/m/3177670.itZtoPt7T5@aivenronan https://git.postgresql.org/pg/commitdiff/91e9e89dccdfdf4216953d3d8f5515dcdef177fb

Andres Freund pushed:

Pending Patches

Dilip Kumar sent in another revision of a patch to add TOAST compression method options.

Kyotaro HORIGUCHI sent in two more revisions of a patch to prevent xlog page-header errors during recovery.

Greg Nancarrow sent in another revision of a patch to add a new "client_connection" event and client connection trigger support.

Jacob Champion sent in another revision of a patch to support NSS as a libpq TLS backend.

Thomas Munro sent in a patch to support direct I/O on Solaris.

Andres Freund sent in two revisions of a WIP patch to optimize allocations by separating hot from cold paths, and improve slab performance by avoiding the need to iterate over all chunks when allocating a new block.

Daniel Gustafsson sent in a patch to disable OpenSSL EVP digest padding in pgcrypto, and add alternative output for OpenSSL 3 without legacy loaded.

Ranier Vilela sent in two revisions of a patch to avoid some strlen calls.

Masahiko Sawada sent in two more revisions of a patch to add errcontext to errors which occur when applying logical replication changes, add a pg_stat_logical_replication_error statistics view, and add a skip_xid option to ALTER SUBSCRIPTION.

Peter Smith sent in two more revisions of a patch to add prepare API support for streaming transactions.

Dipesh Pandit sent in two more revisions of a patch to mitigate directory scan for WAL archiver by maintaining the log segment number of current file which is being archived and incrementing it by '1' to get the next WAL file, rather than rescanning the directory by default.

Andres Freund and Yura Sokolov traded patches to improve dead tuple storage for lazy vacuum by Andres by adding a radix tree implementation, and Yura by adding a specialized vacuum tid map.

Denis Hirn sent in another revision of a patch to allow multiple linear recursive self-references in CTEs.

Peter Eisentraut sent in a patch to make the Unicode makefile parallel-safe.

Ronan Dunklau sent in another revision of a patch to tag and remove resjunk added for SortGroupClause, and teach sort and result nodes to perform a simplified version of a projection.

Filip Gospodinov sent in another revision of a patch to fix the pkg-config file for static linking by hard coding -lpgcommon and -lpgport in Libs.private.

Tomáš Vondra sent in another revision of a patch to implement logical decoding / replication of sequences.

Tomáš Vondra sent in another revision of a patch to handle Expr op Expr clauses in extended stats.

Tomáš Vondra sent in two more revisions of a patch to make GROUP BY more efficient.

David Rowley sent in another revision of a patch to make nodeSort.c do Datum sorts for single column sorts.

Peifeng Qiu sent in two revisions of a patch to add kerberos delegation support to libpq.

John Naylor sent in another revision of a patch to rewrite pg_verify_str for speed.

Hou Zhijie and Greg Nancarrow traded patches to enable users to declare a table's parallel data-modification safety, enable parallel select for insert, and add a pg_get_table_parallel_dml_safety(regclass) function returning records of (objid, classid, parallel_safety).

Ranier Vilela and Aleksander Alekseev traded patches to unshadow a couple of variables.

Simon Riggs sent in two more revisions of a patch to make it possible for hash indexes to back UNIQUE constraints and allow for multicolumn hash indexes.

Bruce Momjian sent in four more revisions of a patch to fix a bug in the arithmetic of fractional intervals.

Yugo Nagata sent in a patch to correct an error in the libpq documentation where the fact that the commands stored locally in the output buffer until it is flushed.

Yugo Nagata sent in another revision of a patch intended to fix a bug that manifested as using prepared BEGIN statement in a pipeline in pgbench could cause an error.

Richard Guo sent in another revision of a patch to fix up some infelicities in partition-wise JOINs.

David Rowley sent in another revision of a patch to add planner support for ORDER BY aggregates.

Ronan Dunklau and Ranier Vilela traded patches to fix postgres_fdw PathKey's handling by paying attention to the operator family being used for the sort.

Thomas Munro sent in two revisions of a patch to make the SIMD code more platform neutral, add ARM/NEON support for UTF-8 validation, and add POWER AltiVec support for UTF-8 validation.

Dean Rasheed sent in another revision of a patch to make exponentiation with negative exponents work with NUMERIC.

Peter Eisentraut sent in a patch to test query cancelation in psql.

Pavel Stěhule and Aleksander Alekseev traded patches to enhance the PL/pgsql debugging API to add returning the text value of variable content.

Vigneshwaran C sent in two more revisions of a patch to add schema-level granularity for PUBLICATIONs.

Jacob Champion sent in a patch to add indicators for wide Unicode code points which have been added since the Unicode 5.0.

Thomas Munro and Soumyadeep Chakraborty traded patches to Optimize ProcSendSignal() by changing from tracking target backends by pid to using pgprocno, which doesn't require scanning the ProcArray. Doing it this way also removes some special cases that need to be checked for during startup.

Andres Freund sent in a patch to pgbench to restrict to use pipelining only to do PQconsumeInput() when necessary.

Nitin Jadhav sent in two more revisions of a patch to show the progress of the operations performed during startup process.

Ajin Cherian sent in four more revisions of a patch to drop empty transactions from logical replication.

Thomas Munro sent in a patch to Teach pgindent about special file-local typenames.

Erik Rijkers sent in another revision of a patch to implement the JSON_TABLE part of the SQL/JSON spec.

Alexander Pyhalov sent in another revision of a patch to make it possible to push CASE expressions to foreign servers.

Zeng Wenjing sent in another revision of a patch to implement global temporary tables.

Justin Pryzby sent in another revision of a patch to implement ALTER TABLE ... ACCESS METHOD, and make it possible to specify the table access method of partitioned tables.

Hou Zhijie sent in a patch to add a quickselect facility, which can be used among other things to compute medians quickly.

Artur Zakirov sent in a patch intended to fix a bug that manifested as stored procedure triggered by logical replication is unable to use notification events by moving the SignalBackends() calls inside AtCommit_Notify().

Peter Eisentraut sent in a patch to pg_amcheck to fix block number parsing on command line so it correctly handles systems where sizeof(long)==4.

Haiying Tang sent in a patch to support tab-completion for single quoted input which contains an equals sign.

Fabien COELHO sent in another revision of a patch to add a SHOW_ALL_RESULTS option to psql.

Dean Rasheed and Tom Lane traded patches to allow for NUMERICs to have negative scales.

Michael Banck sent in another revision of a patch to add a new PGC_ADMINSET GUC context and a new pg_change_role_settings predefined role. These create an administrator which lies between superuser and user in its capabilities.

Greg Nancarrow sent in another revision of a patch to fix parallel worker failed assertion and coredump.

Andrey Borodin sent in a patch to avoid duplication in relcache and syscache callbacks.

Vigneshwaran C sent in another revision of a patch to include the actual datatype used in logical replication message descriptions.

Robert Haas sent in a patch to refactor some end-of-recovery code out of StartupXLOG(), postpone some end-of-recovery operations relating to allowing WAL, and create a XLogAcceptWrites() function with code from StartupXLOG().

Andrey Borodin sent in a patch to improve error reporting of ReadPageInternal() by ensuring that the unhelpful error "no record found" no longer occurs.

Tom Lane sent in a patch to remove the "long int"-related limit on hash table sizes.

Haiying Tang sent in another revision of a patch to put in a minor fix for a regress example.

Bharath Rupireddy sent in another revision of a patch to use a WaitLatch for {pre, post}_auth_delay.

Bharath Rupireddy sent in another revision of a patch to disallow RESTART option for CREATE SEQUENCE.

Julien Rouhaud sent in a patch to make pg_stat_statements tests immune to prepared statements invalidation.