PostgreSQL Weekly News - December 6, 2020

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 - December 6, 2020
Date: 2020-12-07 14:16:29
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-announce

# PostgreSQL Weekly News - December 6, 2020

Person of the week:

# PostgreSQL Product News

check_pgactivity 2.5, a Nagios remote agent for monitoring PostgreSQL, released.

WAL-G 0.2.19, a backup management system for PostgreSQL written in Go, released.

# PostgreSQL Jobs for December


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

- Fix recently-introduced breakage in psql's \connect command. Through my
misreading of what the existing code actually did, commits 85c54287a et al.
broke psql's behavior for the case where "\c connstring" provides a password
in the connstring. We should use that password in such a case, but as of
85c54287a we ignored it (and instead, prompted for a password). Commit
94929f1cf fixed that in HEAD, but since I thought it was cleaning up a
longstanding misbehavior and not one I'd just created, I didn't back-patch it.
Hence, back-patch the portions of 94929f1cf having to do with password
management. In addition to fixing the introduced bug, this means that "\c
-reuse-previous=on connstring" will allow re-use of an existing connection's
password if the connstring doesn't change user/host/port. That didn't happen
before, but it seems like a bug fix, and anyway I'm loath to have significant
differences in this code across versions. Also fix an error with the same
root cause about whether or not to override a connstring's setting of
client_encoding. As of 85c54287a we always did so; restore the previous
behavior of overriding only when stdin/stdout are a terminal and there's no
environment setting of PGCLIENTENCODING. (I find that definition a bit
surprising, but right now doesn't seem like the time to revisit it.) Per bug
#16746 from Krzysztof Gradek. As with the previous patch, back-patch to all
supported branches. Discussion:

- Fix miscomputation of direct_lateral_relids for join relations. If a
PlaceHolderVar is to be evaluated at a join relation, but its value is only
needed there and not at higher levels, we neglected to update the joinrel's
direct_lateral_relids to include the PHV's source rel. This causes problems
because join_is_legal() then won't allow joining the joinrel to the PHV's
source rel at all, leading to "failed to build any N-way joins" planner
failures. Per report from Andreas Seltenreich. Back-patch to 9.5 where the
problem originated. Discussion:

- Prevent parallel index build in a standalone backend. This can't work if
there's no postmaster, and indeed the code got an assertion failure trying.
There should be a check on IsUnderPostmaster gating the use of parallelism, as
the planner has for ordinary parallel queries. Commit 40d964ec9 got this
right, so follow its model of checking IsUnderPostmaster at the same place
where we check for max_parallel_maintenance_workers == 0. In general, new
code implementing parallel utility operations should do the same. Report and
patch by Yulin Pei, cosmetically adjusted by me. Back-patch to v11 where this
code came in. Discussion:

- Remove configure-time probe for DocBook DTD. Checking for DocBook being
installed was valuable when we were on the OpenSP docs toolchain, because that
was rather hard to get installed fully. Nowadays, as long as you have xmllint
and xsltproc installed, you're good, because those programs will fetch the
DocBook files off the net at need. Moreover, testing this at configure time
means that a network access may well occur whether or not you have any
interest in building the docs later. That can be slow (typically 2 or 3
seconds, though much higher delays have been reported), and it seems not very
nice to be doing an off-machine access without warning, too. Hence, drop the
PGAC_CHECK_DOCBOOK probe, and adjust related documentation. Without that
macro, there's not much left of config/docbook.m4 at all, so I just removed
it. Back-patch to v11, where we started to use xmllint in the
PGAC_CHECK_DOCBOOK probe. Discussion:
[]( Discussion:

- Fix missing outfuncs.c support for IncrementalSortPath. For debugging
purposes, Path nodes are supposed to have outfuncs support, but this was
overlooked in the original incremental sort patch. While at it, clean up a
couple other minor oversights, as well as bizarre choice of return type for
create_incremental_sort_path(). (All the existing callers just cast it to
"Path *" immediately, so they don't care, but some future caller might care.)
outfuncs.c fix by Zhijie Hou, the rest by me Discussion:

- Ensure that expandTableLikeClause() re-examines the same table. As it stood,
expandTableLikeClause() re-did the same relation_openrv call that
transformTableLikeClause() had done. However there are scenarios where this
would not find the same table as expected. We hold lock on the LIKE source
table, so it can't be renamed or dropped, but another table could appear
before it in the search path. This explains the odd behavior reported in bug
#16758 when cloning a table as a temp table of the same name. This case
worked as expected before commit 502898192 introduced the need to open the
source table twice, so we should fix it. To make really sure we get the same
table, let's re-open it by OID not name. That requires adding an OID field to
struct TableLikeClause, which is a little nervous-making from an ABI
standpoint, but as long as it's at the end I don't think there's any serious
risk. Per bug #16758 from Marc Boeren. Like the previous patch, back-patch
to all supported branches. Discussion:

- Fix missed step in removal of useless RESULT RTEs in the planner. Commit
4be058fe9 forgot that the append_rel_list would already be populated at the
time we remove useless result RTEs, and it might contain PlaceHolderVars that
need to be adjusted like the ones in the main parse tree. This could lead to
"no relation entry for relid N" failures later on, when the planner tries to
do something with an unadjusted PHV. Per report from Tom Ellis. Back-patch
to v12 where the bug came in. Discussion:

Fujii Masao pushed:

- Improve log message about termination of background workers. Previously the
shutdown of a background worker that uses die() as SIGTERM signal handler
produced the log message "terminating connection due to administrator
command". This log message was confusing because a background worker is not a
connection. This commit improves that log message to "terminating background
worker XXX due to administrator command" (XXX is replaced with the name of the
background worker). This is the same log message as another SIGTERM signal
handler bgworker_die() for a background worker reports. Author: Bharath
Rupireddy Reviewed-by: Fujii Masao Discussion:

- Fix typo in comment. Author: Haiying Tang <tanghy(dot)fnst(at)cn(dot)fujitsu(dot)com>

- doc: Add additional index entries for progress reporting views. In the docs,
the index entries for progress reporting views link to the "Viewing
Statistics" section, but previously they did not link to the dedicated section
(e.g., "ANALYZE Progress Reporting") for each view. This was inconvenient when
finding the section describing the detailed information about each view, from
the index. This commit adds additional index entries linking to those
dedicated sections. Author: Fujii Masao Reviewed-by: Shinya Kato Discussion:

- Allow restore_command parameter to be changed with reload. This commit changes
restore_command from PGC_POSTMASTER to PGC_SIGHUP. As the side effect of this
commit, restore_command can be reset to empty during archive recovery. In this
setting, archive recovery tries to replay only WAL files available in pg_wal
directory. This is the same behavior as when the command that always fails is
specified in restore_command. Note that restore_command still must be
specified (not empty) when starting archive recovery, even after applying this
commit. This is necessary as the safeguard to prevent users from forgetting to
specify restore_command and starting archive recovery. Thanks to Peter
Eisentraut, Michael Paquier, Andres Freund, Robert Haas and Anastasia
Lubennikova for discussion. Author: Sergei Kornilov Reviewed-by: Kyotaro
Horiguchi, Fujii Masao Discussion:

- Track total number of WAL records, FPIs and bytes generated in the cluster.
Commit 6b466bf5f2 allowed pg_stat_statements to track the number of WAL
records, full page images and bytes that each statement generated. Similarly
this commit allows us to track the cluster-wide WAL statistics counters. New
columns wal_records, wal_fpi and wal_bytes are added into the pg_stat_wal
view, and reports the total number of WAL records, full page images and bytes
generated in the , respectively. Author: Masahiro Ikeda Reviewed-by: Amit
Kapila, Movead Li, Kyotaro Horiguchi, Fujii Masao Discussion:

Heikki Linnakangas pushed:

- Remove leftover comments, left behind by removal of WITH OIDS. Author: Amit
Langote Discussion:

- docs: ulink all references to RFC's. Make sure that the first mentions of
RFC's are ulinked to their entry, and subsequent ones are marked as
acronyms. This makes references to RFC's consistent across the documentation.
Author: Daniel Gustafsson Discussion:

- Fix pg_rewind bugs when rewinding a standby server. If the target is a standby
server, its WAL doesn't end at the last checkpoint record, but at
minRecoveryPoint. We must scan all the WAL from the last common checkpoint all
the way up to minRecoveryPoint for modified pages, and also consider that
portion when determining whether the server needs rewinding. Backpatch to all
supported versions. Author: Ian Barwick and me Discussion:

- Fix race conditions in newly-added test. Buildfarm has been failing
sporadically on the new test. I was able to reproduce this by adding a random
0-10 s delay in the walreceiver, just before it connects to the primary.
There's a race condition where node_3 is promoted before it has fully caught
up with node_1, leading to diverged timelines. When node_1 is later
reconfigured as standby following node_3, it fails to catch up: LOG: primary
server contains no more WAL on requested timeline 1 LOG: new timeline 2
forked off current database system timeline 1 before current recovery point
0/30000A0 That's the situation where you'd need to use pg_rewind, but in this
case it happens already when we are just setting up the actual pg_rewind
scenario we want to test, so change the test so that it waits until node_3 is
connected and fully caught up before promoting it, so that you get a clean,
controlled failover. Also rewrite some of the comments, for clarity. The
existing comments detailed what each step in the test did, but didn't give a
good overview of the situation the steps were trying to create. For reasons I
don't understand, the test setup had to be written slightly differently in 9.6
and 9.5 than in later versions. The 9.5/9.6 version needed node 1 to be
reinitialized from backup, whereas in later versions it could be shut down and
reconfigured to be a standby. But even 9.5 should support "clean switchover",
where primary makes sure that pending WAL is replicated to standby on
shutdown. It would be nice to figure out what's going on there, but that's
independent of pg_rewind and the scenario that this test tests. Discussion:

Michaël Paquier pushed:

- Refactor parsing rules for option lists of EXPLAIN, VACUUM and ANALYZE. Those
three commands have been using the same grammar rules to handle a a list of
parenthesized options. This refactors the code so as they use the same
parsing rules, shaving some code. A future commit will make use of those
option parsing rules for more utility commands, like REINDEX and CLUSTER.
Author: Alexey Kondratov, Justin Pryzby Discussion:

- doc: Remove more notes about compatibilities with past versions. This is a
follow-up of the work done in fa42c2e, that did not include all the fixes
previously agreed on. The contents removed here can be confusing to the
reader as they refer to rather old server versions. Author: Stephen Frost,
Tom Lane, Heikki Linnakangas, Yaroslav Schekin Discussion:
Discussion: [](

- Move SHA2 routines to a new generic API layer for crypto hashes. Two new
routines to allocate a hash context and to free it are created, as these
become necessary for the goal behind this refactoring: switch the all
cryptohash implementations for OpenSSL to use EVP (for FIPS and also because
upstream does not recommend the use of low-level cryptohash functions for 20
years). Note that OpenSSL hides the internals of cryptohash contexts since
1.1.0, so it is necessary to leave the allocation to OpenSSL itself,
explaining the need for those two new routines. This part is going to require
more work to properly track hash contexts with resource owners, but this not
introduced here. Still, this refactoring makes the move possible. This
reduces the number of routines for all SHA2 implementations from twelve
(SHA{224,256,386,512} with init, update and final calls) to five (create,
free, init, update and final calls) by incorporating the hash type directly
into the hash context data. The new cryptohash routines are moved to a new
file, called cryptohash.c for the fallback implementations, with SHA2
specifics becoming a part internal to src/common/. OpenSSL specifics are part
of cryptohash_openssl.c. This infrastructure is usable for more hash types,
like MD5 or HMAC. Any code paths using the internal SHA2 routines are adapted
to report correctly errors, which are most of the changes of this commit. The
zones mostly impacted are checksum manifests, libpq and SCRAM. Note that
e21cbb4 was a first attempt to switch SHA2 to EVP, but it lacked the
refactoring needed for libpq, as done here. This patch has been tested on
Linux and Windows, with and without OpenSSL, and down to 1.0.1, the oldest
version supported on HEAD. Author: Michael Paquier Reviewed-by: Daniel
Gustafsson Discussion: [](

- Fix compilation warnings in cryptohash_openssl.c. These showed up with -O2.
Oversight in 87ae969. Author: Fujii Masao Discussion:

- Refactor CLUSTER and REINDEX grammar to use DefElem for option lists. This
changes CLUSTER and REINDEX so as a parenthesized grammar becomes possible for
options, while unifying the grammar parsing rules for option lists with the
existing ones. This is a follow-up of the work done in 873ea9e for VACUUM,
ANALYZE and EXPLAIN. This benefits REINDEX for a potential backend-side
filtering for collatable-sensitive indexes and TABLESPACE, while CLUSTER would
benefit from the latter. Author: Alexey Kondratov, Justin Pryzby Discussion:

- Change SHA2 implementation based on OpenSSL to use EVP digest routines. The
use of low-level hash routines is not recommended by upstream OpenSSL since
2000, and pgcrypto already switched to EVP as of 5ff4a67. This takes advantage
of the refactoring done in 87ae969 that has introduced the allocation and free
routines for cryptographic hashes. Since 1.1.0, OpenSSL does not publish the
contents of the cryptohash contexts, forcing any consumers to rely on OpenSSL
for all allocations. Hence, the resource owner callback mechanism gains a new
set of routines to track and free cryptohash contexts when using OpenSSL,
preventing any risks of leaks in the backend. Nothing is needed in the
frontend thanks to the refactoring of 87ae969, and the resowner knowledge is
isolated into cryptohash_openssl.c. Note that this also fixes a failure with
SCRAM authentication when using FIPS in OpenSSL, but as there have been few
complaints about this problem and as this causes an ABI breakage, no backpatch
is done. Author: Michael Paquier Reviewed-by: Daniel Gustafsson, Heikki
Linnakangas Discussion: [](
Discussion: [](

- Rename cryptohashes.c to cryptohashfuncs.c. 87ae969 has created two new files
called cryptohash{_openssl}.c in src/common/, whose names overlap with the
existing backend file called cryptohashes.c dedicated to the SQL wrappers for
SHA2 and MD5. This file is renamed to cryptohashfuncs.c to be more consistent
with the surroundings and reduce the confusion with the new cryptohash
interface of src/common/. Author: Michael Paquier Reviewed-by: Daniel
Gustafsson Discussion: [](

Álvaro Herrera pushed:

- Document concurrent indexes waiting on each other. Because regular CREATE
INDEX commands are independent, and there's no logical data dependency, it's
not immediately obvious that transactions held by concurrent index builds on
one table will block the second phase of concurrent index creation on an
unrelated table, so document this caveat. Backpatch this all the way back.
In branch master, mention that only some indexes are involved. Author: James
Coleman <jtc331(at)gmail(dot)com> Reviewed-by: David Johnston
<david(dot)g(dot)johnston(at)gmail(dot)com> Discussion:

- Avoid memcpy() with a NULL source pointer and count == 0. When memcpy() is
called on a pointer, the compiler is entitled to assume that the pointer is
not null, which can lead to optimizing nearby code in potentially undesirable
ways. We still want such optimizations (gcc's -fdelete-null-pointer-checks)
in cases where they're valid. Related: commit 13bba02271dc. Backpatch to
pg11, where this particular instance appeared. Reported-by: Ranier Vilela
<ranier(dot)vf(at)gmail(dot)com> Reported-by: Zhihong Yu <zyu(at)yugabyte(dot)com> Discussion:

Thomas Munro pushed:

- Free disk space for dropped relations on commit. When committing a transaction
that dropped a relation, we previously truncated only the first segment file
to free up disk space (the one that won't be unlinked until the next
checkpoint). Truncate higher numbered segments too, even though we unlink
them on commit. This frees the disk space immediately, even if other backends
have open file descriptors and might take a long time to get around to
handling shared invalidation events and closing them. Also extend the same
behavior to the first segment, in recovery. Back-patch to all supported
releases. Bug: #16663 Reported-by: Denis Patron <denis(dot)patron(at)previnet(dot)it>
Reviewed-by: Pavel Borisov <pashkin(dot)elfe(at)gmail(dot)com> Reviewed-by: Neil Chen
<carpenter(dot)nail(dot)cz(at)gmail(dot)com> Reviewed-by: David Zhang <david(dot)zhang(at)highgo(dot)ca>
Discussion: [](

- Use truncate(2) where appropriate. When truncating files by name, use
truncate(2). Windows hasn't got it, so keep our previous coding based on
ftruncate(2) as a fallback. Discussion:

Bruce Momjian pushed:

- pg_checksums: data_checksum_version is unsigned so use %u not %d. While the
previous behavior didn't generate a warning, we might as well use an accurate
*printf specification. Backpatch-through: 12

- docs: list single-letter options first in command-line summary. In a few
places, the long-version options were listed before the single-letter ones in
the command summary of a few commands. This didn't match other commands, and
didn't match the option ordering later in the same reference page.
Backpatch-through: 9.5

- doc: remove unnecessary blank before command option text. Backpatch-through:

Stephen Frost pushed:

- Add GSS information to connection authorized log message. GSS information (if
used) such as if the connection was authorized using GSS or if it was
encrypted using GSS, and perhaps most importantly, what the GSS principal used
for the authentication was, is extremely useful but wasn't being included in
the connection authorized log message. Therefore, add to the connection
authorized log message that information, in a similar manner to how we log SSL
information when SSL is used for a connection. Author: Vignesh C Reviewed-by:
Bharath Rupireddy Discussion:

Dean Rasheed pushed:

- Improve estimation of OR clauses using extended statistics. Formerly we only
applied extended statistics to an OR clause as part of the
clauselist_selectivity() code path for an OR clause appearing in an
implicitly-ANDed list of clauses. This meant that it could only use extended
statistics if all sub-clauses of the OR clause were covered by a single
extended statistics object. Instead, teach clause_selectivity() how to apply
extended statistics to an OR clause by handling its ORed list of sub-clauses
in a similar manner to an implicitly-ANDed list of sub-clauses, but with
different combination rules. This allows one or more extended statistics
objects to be used to estimate all or part of the list of sub-clauses. Any
remaining sub-clauses are then treated as if they are independent.
Additionally, to avoid double-application of extended statistics, this
introduces "extended" versions of clause_selectivity() and
clauselist_selectivity(), which include an option to ignore extended
statistics. This replaces the old clauselist_selectivity_simple() function
which failed to completely ignore extended statistics when called from the
extended statistics code. A known limitation of the current infrastructure is
that an AND clause under an OR clause is not treated as compatible with
extended statistics (because we don't build RestrictInfos for such sub-AND
clauses). Thus, for example, "(a=1 AND b=1) OR (a=2 AND b=2)" will currently
be treated as two independent AND clauses (each of which may be estimated
using extended statistics), but extended statistics will not currently be used
to account for any possible overlap between those clauses. Improving that is
left as a task for the future. Original patch by Tomas Vondra, with
additional improvements by me. Discussion:

Peter Eisentraut pushed:

- Small code simplifications. strVal() can be used in a couple of places instead
of coding the same thing by hand.

- Remove unnecessary grammar symbols. Instead of publication_name_list, we can
use name_list. We already refer to publications everywhere else by the 'name'
or 'name_list' symbols, so this only improves consistency. Reviewed-by:
Discussion: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>

- Convert elog(LOG) calls to ereport() where appropriate. User-visible log
messages should go through ereport(), so they are subject to translation.
Many remaining elog(LOG) calls are really debugging calls. Reviewed-by:
Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> Reviewed-by: Michael Paquier
<michael(at)paquier(dot)xyz> Reviewed-by: Noah Misch <noah(at)leadboat(dot)com> Discussion:

Amit Kapila pushed:

- Remove incorrect assertion in reorderbuffer.c. We start recording changes in
ReorderBufferTXN even before we reach SNAPBUILD_CONSISTENT state so that if
the commit is encountered after reaching that we should be able to send the
changes of the entire transaction. Now, while recording changes if the reorder
buffer memory has exceeded logical_decoding_work_mem then we can start
streaming if it is allowed and we haven't yet streamed that data. However, we
must not allow streaming to start unless the snapshot has reached
SNAPBUILD_CONSISTENT state. In passing, improve the comments atop
ReorderBufferResetTXN to mention the case when we need to continue streaming
after getting an error. Author: Amit Kapila Reviewed-by: Dilip Kumar

== Pending Patches ==

Tatsuro Yamada sent in another revision of a patch to add \dX (extended
statistics) to psql.

Andrey V. Lepikhov sent in another revision of a patch to remove unneeded

David G. Johnston sent in another revision of a patch to make some minor
cleanups and rewording of plpgsql docs.

Seino Yuki sent in another revision of a patch to add a new column, reset-time,
to `pg_stat_statements_info`.

Justin Pryzby sent in a patch to make \dt+ `pg_toast`.\* work in psql.

Justin Pryzby sent in another revision of a patch to make EXPLAIN show JIT
details in non-text format, even if zero.

Peter Eisentraut sent in another revision of a patch to pause recovery for
insufficient parameter settings.

Michaël Paquier sent in another revision of a patch to fix a bug that manifested
as `vac_update_datfrozenxid` will raise "wrong tuple length" if pg_database tuple
contains toast attribute.

Michaël Paquier sent in another revision of a patch to remove some references to
long-obsolete behavior.

Peter Geoghegan sent in another revision of a patch to teach heapam to support
bottom-up index deletion, pass down "logically unchanged index" hint, and teach
nbtree to use bottom-up index deletion.

Bertrand Drouvot sent in two revisions of a patch to prevent functions from
becoming orphaned by dropping their containing schemas.

Andrey V. Lepikhov sent in a patch to fix a wild overestimation of the cost of a
foreign join.

Konstantin Knizhnik sent in another revision of a patch to implement custom
compression for libpq.

Álvaro Herrera sent in a patch to improve operations spelled like INDEX

Peter Eisentraut sent in another revision of a patch to pageinspect to change
the block number arguments to bigint, as this actually fits uint32.

Aleksey Kondratov, Michaël Paquier, and Justin Pryzby traded patches to allow
CLUSTER and VACUUM FULL to change tablespace on the fly.

Bertrand Drouvot and Fujii Masao traded patches to log the standby recovery
conflict waits.

Justin Pryzby sent in two more revisions of a patch to make it possible for
INSERT SELECT to use a BulkInsertState.

Bharath Rupireddy sent in two more revisions of a patch to make it possible to
use parallel inserts in CREATE TABLE AS.

James Coleman sent in another revision of a patch to error if gather merge paths
aren't sufficiently sorted.

James Coleman sent in another revision of a patch to ensure that
`generate_useful_gather_paths` doesn't skip unsorted subpaths, enforce parallel
safety of pathkeys in `generate_useful_gather_paths`, disallow SRFs in proactive
sort, remove volatile expr target search, and document
`find_em_expr_usable_for_sorting_rel` in `prepare_sort_from_pathkeys`.

Craig Ringer sent in a patch to implement a TAP test utility module ``
to test things that involve logical sequence numbers.

Greg Nancarrow sent in another revision of a patch to add an `in_hot_standby`
reportable GUC and make `transaction_read_only` GUC reportable, and enhance the
connection parameter `target_session_attrs` to support new values:

Jürgen Purtz sent in another revision of a patch to put more explicit JOINs in
the tutorial.

Zhihong Yu sent in another revision of a patch to check nparts for defining

Hou Zhejie sent in a patch to replace lcons and `list_delete_first` in
`plan_union_children()`, with lappend and `list_delete_last`.

Laurenz Albe sent in a patch to discard the query buffer if editor is quit in
psql's \e.

Andres Freund sent in a patch to fix a JIT problem that came up with LLVM 12.

Fujii Masao sent in another revision of a patch to add basic statistics to the
`pg_stat_wal view`.

Browse pgsql-announce by date

  From Date Subject
Next Message fish's dotNET via PostgreSQL Announce 2020-12-10 14:40:53 Database .NET v31.5 released
Previous Message Psycopg Development Team via PostgreSQL Announce 2020-12-01 14:38:28 psycopg3: request for sponsorship