16th June 2022: PostgreSQL 14.4 Released!

PostgreSQL Weekly News - October 17, 2021

Posted on 2021-10-18 by PWN
PWN

PostgreSQL Weekly News - October 17, 2021

PostgreSQL Product News

psycopg2 3.0.0, a Python connector for PostgreSQL, released

pg_partman 4.6.0, a management system for partitioned tables, released.

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

Percona Distribution for PostgreSQL Operator 1.0.0, a Kubernetes operator based on Crunchy Data's, for PostgreSQL, released.

PostgreSQL Jobs for October

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

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

Tom Lane pushed:

  • Doc: update testing recipe in src/test/perl/README. The previous text didn't provide any clear explanation of our policy around TAP test portability. The recipe for using perlbrew had some problems, too: it resulted in a non-shared libperl (preventing testing of plperl) and it caused some modules to be updated to current when the point of the recipe is to build an old environment. Discussion: https://postgr.es/m/E1mYY6Z-0006OL-QN@gemulon.postgresql.org https://git.postgresql.org/pg/commitdiff/3eb1f4d09745433c70ccac411cad24d0374b9c3b

  • Fix EXPLAIN of SEARCH BREADTH FIRST queries some more. Commit 3f50b8263 had an oversight: formerly, to deparse expressions attached to a plan node, it was only necessary to update the deparse_namespace ancestors list alongside calling set_deparse_plan. Now it's necessary to update the ancestors list first, because set_deparse_plan consults it, and one call site got that wrong. This error was masked in most cases because explain.c uses just one List object for the ancestors list, updating it in-place as the plan is scanned, so that we accidentally had the right List assigned to dpns->ancestors before it was needed. It would fail only if a WorkTableScan node were the first one that we tried to deparse a subexpression of. Per report from Markus Winand. Like the previous patch, back-patch to v14. Discussion: https://postgr.es/m/648B0505-AA57-42C2-A2DA-E551DE46FA15@winand.at https://git.postgresql.org/pg/commitdiff/39ae0ef8561362304ee512963aa51d5a705e5616

  • Make configure check for minimum required version of IPC::Run. Per the discussion around 3eb1f4d09, let's have configure verify that the available IPC::Run version is at least 0.79, the agreed-on minimum. It seems unlikely that this could bite anybody anymore, but it's useful as documentation. (Based on that, there's little need to back-patch.) For consistency, also supply a minimum version for the other Perl module we have an explicit check for, Time::HiRes. I used the version that ships with Perl 5.8.3. Discussion: https://postgr.es/m/E1mYY6Z-0006OL-QN@gemulon.postgresql.org https://git.postgresql.org/pg/commitdiff/4a235efddaa78ec78a47614ddc6161644e089290

  • Fix planner error with pulling up subquery expressions into function RTEs. If a function-in-FROM laterally references the output of some sub-SELECT earlier in the FROM clause, and we are able to flatten that sub-SELECT into the outer query, the expression(s) copied into the function RTE missed being processed by eval_const_expressions. This'd lead to trouble and probable crashes at execution if such expressions contained named-argument function call syntax or functions with defaulted arguments. The bug is masked if the query contains any explicit JOIN syntax, which may help explain why we'd not noticed. Per bug #17227 from Bernd Dorn. This is an oversight in commit 7266d0997, so back-patch to v13 where that came in. Discussion: https://postgr.es/m/17227-5a28ed1512189fa4@postgresql.org https://git.postgresql.org/pg/commitdiff/4d5f651f1d651c6fa79f9188e7b9a04654c7125a

  • Make pg_dump acquire lock on partitioned tables that are to be dumped. It was clearly the intent to do so all along, but the original coding fat-fingered this by checking the wrong array element. We fixed it in passing in 403a3d91c, but that later got reverted, and we forgot to keep this bug fix. Most of the time this'd be relatively harmless, since once we lock any of the partitioned table's leaf partitions, that would suffice to prevent major DDL on the partitioned table itself. However, a childless partitioned table would get dumped with no relevant lock whatsoever, possibly allowing dump failure or inconsistent output. Unlike 403a3d91c, there are no versioning concerns, since every server version that has partitioned tables will allow you to lock one. Back-patch to v10 where partitioned tables were introduced. Discussion: https://postgr.es/m/1018205.1634346327@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/e2ff7d9a83d4b489806281dc6dfce88510b40ad7

  • Avoid core dump in pg_dump when dumping from pre-8.3 server. Commit f0e21f2f6 missed adding a tgisinternal output column to getTriggers' query for pre-8.3 servers. Back-patch to v11, like that commit. https://git.postgresql.org/pg/commitdiff/40dfac4fc4776213a02291f13046d36e318f2629

Michaël Paquier pushed:

  • Clean up more code using "(expr) ? true : false". This is similar to fd0625c, taking care of any remaining code paths that are worth the cleanup. This also changes some cases using opposite expression patterns. Author: Justin Pryzby, Masahiko Sawada Discussion: https://postgr.es/m/CAD21AoCdF8dnUvr-BUWWGvA_XhKSoANacBMZb6jKyCk4TYfQ2Q@mail.gmail.com https://git.postgresql.org/pg/commitdiff/68f7c4b57a27dbcd3e93ba3ff7b0b49664b25e09

  • Add more $Test::Builder::Level in the TAP tests. Incrementing the level of the call stack reported is useful for debugging purposes as it allows to control which part of the test is exactly failing, especially if a test is structured with subroutines that call routines from Test::More. This adds more incrementations of $Test::Builder::Level where debugging gets improved (for example it does not make sense for some paths like pg_rewind where long subroutines are used). A note is added to src/test/perl/README about that, based on a suggestion from Andrew Dunstan and a wording coming from both of us. Usage of Test::Builder::Level has spread in 12, so a backpatch down to this version is done. Reviewed-by: Andrew Dunstan, Peter Eisentraut, Daniel Gustafsson Discussion: https://postgr.es/m/YV1CCFwgM1RV1LeS@paquier.xyz Backpatch-through: 12 https://git.postgresql.org/pg/commitdiff/f9c4cb686800d46ef9e9e90ed5133493b23962af

  • Fix tests of pg_upgrade across different major versions. This fixes a set of issues that cause different breakages or annoyances when using pg_upgrade's test.sh to do upgrades across different major versions: - test.sh is completely broken when using v14 as new version because of the removal of testtablespace/ as Makefile rule. Older versions of pg_regress don't support --make-tablespacedir, blocking the creation of the tablespace. In order to fix that, it is simple enough to create those directories in the script itself, but only do that when an old version is involved. This fix is needed on HEAD and REL_14_STABLE. - The script would fail when using PG <= v11 as old version because of WITH OIDS relations not supported in v12. In order to fix this, this steals a method from the buildfarm that uses a DO block to change all the relations marked as WITH OIDS, allowing pg_upgrade to pass. This is more portable than using ALTER TABLE queries on the relations causing issues. This is fixed down to v12, and authored originally by Andrew Dunstan. - Not using --extra-float-digits=0 with v11 as old version causes a lot of diffs in the dumps, making the whole unreadable. This gets only done when using v11 as old version. This is fixed down to v12. The buildfarm code uses that already. Note that the addition of --wal-segsize and --allow-group-access breaks the script when using v10 or older at initdb time as these got added in 11. 10 would be EOL'd next year and nobody has complained about those problems yet, so nothing is done about that. This means that this commit fixes upgrade tests using test.sh with v11 as minimum older version, up to HEAD, and that it is enough to apply this change down to 12. The old and new dumps still generate diffs, still require manual checks, and more could be done to reduce the noise, but this allows the tests to run with a rather minimal amount of them. I have tested this commit and test.sh with v11 as minimum across all the branches where this is applied. Note that this commit has no impact on the normal pg_upgrade test run with a simple "make check". Author: Justin Pryzby, Andrew Dunstan, Michael Paquier Discussion: https://postgr.es/m/20201206180248.GI24052@telsasoft.com Backpatch-through: 12 https://git.postgresql.org/pg/commitdiff/fa66b6dee0843d2bca5bf9c9b8b7be32defbffae

  • Fix use-after-free with multirange types in CREATE TYPE. The code was freeing the name of the multirange type function stored in the parse tree but it should not do that. Event triggers could for example look at such a corrupted parsed tree with a ddl_command_end event. Author: Alex Kozhemyakin, Sergey Shinderuk Reviewed-by: Peter Eisentraut, Michael Paquier Discussion: https://postgr.es/m/d5042d46-b9cd-6efb-219a-71ed0cf45bc8@postgrespro.ru Backpatch-through: 14 https://git.postgresql.org/pg/commitdiff/5b0e7fe1d67235a092be1132bc5c97f1d7f29aaf

Peter Geoghegan pushed:

Fujii Masao pushed:

  • Make autovacuum launcher more responsive to pg_log_backend_memory_contexts(). Previously when pg_log_backend_memory_contexts() sent the request to the autovacuum launcher, it could take more than several seconds to log its memory contexts. Because the function (HandleAutoVacLauncherInterrupts) to process any new interrupts that autovacuum launcher received didn't handle the request for logging of memory contexts. This commit changes the function so that it handles the request, to make autovacuum launcher more responsitve to pg_log_backend_memory_contexts(). Back-patch to v14 where pg_log_backend_memory_contexts() was added. Author: Koyu Tanigawa Reviewed-by: Bharath Rupireddy, Atsushi Torikoshi Discussion: https://postgr.es/m/0aae3e074face409b35153451be5cc11@oss.nttdata.com https://git.postgresql.org/pg/commitdiff/e3e29cec10d15bbcedc6b41887d8f4e138d719bd

Peter Eisentraut pushed:

Robert Haas pushed:

  • Refactor basebackup.c's _tarWriteDir() function. Sometimes, we replace a symbolic link that we find in the data directory with an actual directory within the tarfile that we create. _tarWriteDir was responsible both for making this substitution and also for writing the tar header for the resulting directory into the tar file. Make it do only the first of those things, and rename to convert_link_to_directory. Substantially larger refactoring of this source file is planned, but this little bit seemed to make sense to commit independently. Discussion: http://postgr.es/m/CA+Tgmobz6tuv5tr-WxURe5JA1vVcGz85k4kkvoWxcyHvDpEqFA@mail.gmail.com https://git.postgresql.org/pg/commitdiff/967a17fe2fa77b61061c8fb1183f64a5df4e080a

  • Refactor some end-of-recovery code out of StartupXLOG(). Create a new function PerformRecoveryXLogAction() and move the code which either writes an end-of-recovery record or requests a checkpoint there. Also create a new function CleanupAfterArchiveRecovery() to perform a few tasks that we want to do after we've actually exited archive recovery but before we start accepting new WAL writes. More refactoring of this file is planned, but this commit is just straightforward code movement to make StartupXLOG() a little bit shorter and a little bit easier to understand. Robert Haas and Amul Sul Discussion: http://postgr.es/m/CAAJ_b97abMuq=470Wahun=aS1PHTSbStHtrjjPaD-C0YQ1AqVw@mail.gmail.com https://git.postgresql.org/pg/commitdiff/6df1543abfed6f6a86b76a48fa11a6f019111c01

  • Postpone some end-of-recovery operations related to allowing WAL. CreateOverwriteContrecordRecord(), UpdateFullPageWrites(), PerformRecoveryXLogAction(), and CleanupAfterArchiveRecovery() are moved somewhat later in StartupXLOG(). This is preparatory work for a future patch that wants to allow recovery to end at one time and only later start to allow WAL writes. To do that, it's necessary to separate code that has to do with allowing WAL writes from other things that need to happen simply because recovery is ending, such as initializing shared memory data structures that depend on information that might not be accurate before redo is complete. This commit does not achieve that goal, but it is a step in that direction. For example, there are a few different bits of code that write things into WAL once we have finished recovery, and with this change, those bits of code are closer to each other than previously, with fewer unrelated bits of code interspersed. Robert Haas and Amul Sul Discussion: http://postgr.es/m/CAAJ_b97abMuq=470Wahun=aS1PHTSbStHtrjjPaD-C0YQ1AqVw@mail.gmail.com https://git.postgresql.org/pg/commitdiff/811051c2e7af1b030467760baf7ee0f4a22bc992

  • shm_mq: Update mq_bytes_written less often. Do not update shm_mq's mq_bytes_written until we have written an amount of data greater than 1/4th of the ring size, unless the caller of shm_mq_send(v) requests a flush at the end of the message. This reduces the number of calls to SetLatch(), and also the number of CPU cache misses, considerably, and thus makes shm_mq significantly faster. Dilip Kumar, reviewed by Zhihong Yu and Tomas Vondra. Some minor cosmetic changes by me. Discussion: http://postgr.es/m/CAFiTN-tVXqn_OG7tHNeSkBbN+iiCZTiQ83uakax43y1sQb2OBA@mail.gmail.com https://git.postgresql.org/pg/commitdiff/46846433a03dff4f2e08c8a161e54a842da360d6

Etsuro Fujita pushed:

Álvaro Herrera pushed:

Jeff Davis pushed:

Andrew Dunstan pushed: