PostgreSQL Weekly News - October 17, 2021

From: PWN via PostgreSQL Announce <announce-noreply(at)postgresql(dot)org>
To: PostgreSQL Announce <pgsql-announce(at)lists(dot)postgresql(dot)org>
Subject: PostgreSQL Weekly News - October 17, 2021
Date: 2021-10-18 07:38:45
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-announce

# PostgreSQL Weekly News - October 17, 2021

# PostgreSQL Product News

psycopg2 3.0.0, a Python connector for PostgreSQL,

pg_partman 4.6.0, a management system for partitioned tables,

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

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

# PostgreSQL Jobs for October


# 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 david(at)fetter(dot)org(dot)

# 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:

- 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.

- 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:

- 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:

- 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:

- 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.

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:

- 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:
Backpatch-through: 12

- 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 to do upgrades across different major versions: - 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 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 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:
Backpatch-through: 12

- 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:
Backpatch-through: 14

Peter Geoghegan pushed:

- amcheck: Skip unlogged relations in Hot Standby. Have verify_heapam.c treat
unlogged relations as if they were simply empty when in Hot Standby mode.
This brings it in line with verify_nbtree.c, which has handled unlogged
relations in the same way since bugfix commit 6754fe65a4. This was an
oversight in commit 866e24d47d, which extended contrib/amcheck to check heap
relations. In passing, lower the verbosity used when reporting that a
relation has been skipped like this, from NOTICE to DEBUG1. This is
appropriate because the skipping behavior is only an implementation detail,
needed to work around the fact that unlogged tables don't have smgr-level
storage for their main fork when in Hot Standby mode. Affected unlogged
relations should be considered "trivially verified", not skipped over. They
are verified in the same sense that a totally empty relation can be verified.
This behavior seems least surprising overall, since unlogged relations on a
replica will initially be empty if and when the replica is promoted and Hot
Standby ends. Author: Mark Dilger <mark(dot)dilger(at)enterprisedb(dot)com> Reviewed-By:
Peter Geoghegan <pg(at)bowt(dot)ie> Discussion:
Backpatch: 14-, where heapam verification was introduced.

- Doc: normalize vacuum_multixact_failsafe_age ID. Author: Pavel Luzanov
<p(dot)luzanov(at)postgrespro(dot)ru> Discussion:
Backpatch: 14-, where the failsafe was introduced.

- pg_amcheck: avoid unhelpful verification attempts. Avoid calling
contrib/amcheck functions with relations that are unsuitable for checking.
Specifically, don't attempt verification of temporary relations, or indexes
whose pg_index entry indicates that the index is invalid, or not ready. These
relations are not supported by any of the contrib/amcheck functions, for
reasons that are pretty fundamental. For example, the implementation of
REINDEX CONCURRENTLY can add its own "transient" pg_index entries, which has
rather unclear implications for the B-Tree verification functions, at least in
the general case -- so they just treat it as an error. It falls to the
amcheck caller (in this case pg_amcheck) to deal with the situation at a
higher level. pg_amcheck now simply treats these conditions as additional
"visibility concerns" when it queries system catalogs. This is a little
arbitrary. It seems to have the least problems among any of the available
alternatives. Author: Mark Dilger <mark(dot)dilger(at)enterprisedb(dot)com> Reported-By:
Alexander Lakhin <exclusion(at)gmail(dot)com> Reviewed-By: Peter Geoghegan
<pg(at)bowt(dot)ie> Reviewed-By: Robert Haas <robertmhaas(at)gmail(dot)com> Bug: #17212
Backpatch: 14-, where pg_amcheck was introduced.

- Remove unstable pg_amcheck tests. Recent pg_amcheck bugfix commit d2bf06db
added a test case that the buildfarm has shown to be non-portable. It doesn't
particularly seem worth keeping anyway. Remove it. Discussion:
Backpatch: 14-, just like the original commit.

- Remove obsolete nbtree deduplication comments. Follow up to commit 2903f140.

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:

Peter Eisentraut pushed:

- psql: More tests. Add some basic tests for command-line option handling and
help output, similar to what we have for other command-line programs. This
also creates a place to put some more one-off test cases later. Discussion:

- psql: Add test for handling of replication commands. Add a test for the clean
handling of unsupported replication command responses. This was once
accidentally broken, and it seems unusual enough that it's easy to forget when
testing manually. Discussion:

- psql: Fix test. The test didn't work on platforms where getopt() doesn't
support non-option arguments before options.

- psql: Fix some scan-build warnings. A repeated complaint was that scan-build
thought that if the \timing setting changes during processing of a query, the
post-processing might read garbage time values. This is probably not possible
right now, but it's not entirely inconceivable given the code structure. So
silence this warning with small restructuring that makes this more robust.
The other warnings were a few dead stores that are easy to remove.

- Fix incorrect format placeholder.

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:

- 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:

- 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:

- 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:

Etsuro Fujita pushed:

- postgres_fdw: Move comments about elog level in (sub)abort cleanup. The
comments were misplaced when adding postgres_fdw. Fix that by moving the
comments to more appropriate functions. Author: Etsuro Fujita
Backpatch-through: 9.6 Discussion:

Álvaro Herrera pushed:

- Change recently added test code for stability. The test code added with
ff9f111bce24 fails under valgrind, and probably other slow cases too, because
if (say) autovacuum runs in between and produces WAL of its own, the large
INSERT fails to account for that in the LSN calculations. Rewrite to use a DO
loop. Per complaint from Andres Freund Backpatch to all branches.

Jeff Davis pushed:

- Check criticalSharedRelcachesBuilt in GetSharedSecurityLabel(). An extension
may want to call GetSecurityLabel() on a shared object before the shared
relcaches are fully initialized. For instance, a ClientAuthentication_hook
might want to retrieve the security label on a role. Discussion:
Backpatch-through: 9.6

Andrew Dunstan pushed:

- Fix PostgresNode install_path sanity tests that fail on Windows. Backpatch to
14 where install_path was introduced.

Browse pgsql-announce by date

  From Date Subject
Next Message RapidLoop, Inc. via PostgreSQL Announce 2021-10-19 12:33:54 pgmetrics 1.12 released
Previous Message Microsoft Azure via PostgreSQL Announce 2021-10-18 00:14:04 PostgreSQL 14 available in production on Azure in Hyperscale (Citus)