PostgreSQL Weekly News - April 25, 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 - April 25, 2021
Date: 2021-04-26 01:17:46
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-announce

# PostgreSQL Weekly News - April 25, 2021

Person of the week: [](

# PostgreSQL Product News

JDBC 42.2.20 released

kubgres 1.1, a Kubernetes operator which makes it possible to deploy a cluster
of PostgreSQL pods with replication and failover configured, released.

pspg 4.6.1 a pager designed for PostgreSQL, released.

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

pg_log_statements 0.2, an extension that makes is possible to log statements for
specific server processes, released.

# PostgreSQL Jobs for April


# 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

Thomas Munro pushed:

- Explain postmaster's treatment of SIGURG. Add a few words of comment to
explain why SIGURG doesn't follow the dummy_handler pattern used for SIGUSR2,
since that might otherwise appear to be a bug. Discussion:

Michaël Paquier pushed:

- Replace magic constants for seek() calls in perl scripts. A couple of tests
have been using 0 as magic constant while SEEK_SET can be used instead. This
makes the code easier to understand, and more consistent with the changes done
in 3c5b068. Per discussion with Andrew Dunstan. Discussion:

- Fix typos and grammar in comments and docs. Author: Justin Pryzby Discussion:

- Add CURRENT_ROLE to list of roles for tab completion of GRANT in psql. This
compatibility has been added in 45b9805, but psql forgot the call. Author:
Wei Wang Reviewed-by: Aleksander Alekseev Discussion:

- doc: List compute_query_id in required config for pg_stat_statements. Not
enabling compute_query_id would disable pg_stat_statements even if the module
is listed in shared_preload_libraries, so add it to the minimum configuration
set as listed in its documentation. Author: Greg Nancarrow Reviewed-by:
Julien Rouhaud, Bharath Rupireddy Discussion:

- doc: Move parallel_leader_participation to its correct category.
parallel_leader_participation got introduced in e5253fd, where it was listed
under RESOURCES_ASYNCHRONOUS in guc.c, but the documentation did not reflect
that and listed it with the other planner-related options. This commit fixes
this inconsistency as the parameter is intended to be an asynchronous one.
While on it, reorganize a bit the section dedicated to asynchronous
parameters, backend_flush_after being moved first to do better in terms of
alphabetical order of the options listed. Reported-by: Yanliang Lei Author:
Bharath Rupireddy Discussion:

- Fix relation leak for subscribers firing triggers in logical replication.
Creating a trigger on a relation to which an apply operation is triggered
would cause a relation leak once the change gets committed, as the executor
would miss that the relation needs to be closed beforehand. This issue got
introduced with the refactoring done in 1375422c, where it becomes necessary
to track relations within es_opened_result_relations to make sure that they
are closed. We have discussed using ExecInitResultRelation() coupled with
ExecCloseResultRelations() for the relations in need of tracking by the apply
operations in the subscribers, which would simplify greatly the opening and
closing of indexes, but this requires a larger rework and reorganization of
the worker code, particularly for the tuple routing part. And that's not
really welcome post feature freeze. So, for now, settle down to the same
solution as TRUNCATE which is to fill in es_opened_result_relations with the
relation opened, to make sure that ExecGetTriggerResultRel() finds them and
that they get closed. The code is lightly refactored so as a relation is not
registered three times for each DML code path, making the whole a bit easier
to follow. Reported-by: Tang Haiying, Shi Yu, Hou Zhijie Author: Amit
Langote, Masahiko Sawada, Hou Zhijie Reviewed-by: Amit Kapila, Michael Paquier

- Remove use of [U]INT64_FORMAT in some translatable strings. %lld with (long
long), or %llu with (unsigned long long) are more adapted. This is similar to
3286065. Author: Kyotaro Horiguchi Discussion:

- Fix some comments in fmgr.c. Oversight in 2a0faed. Author: Hou Zhijie

- Add some forgotten LSN_FORMAT_ARGS() in xlogreader.c. 6f6f284 has introduced a
specific macro to make printf()-ing of LSNs easier. This takes care of what
looks like the remaining code paths that did not get the call. Author:
Michael Paquier Reviewed-by: Kyotaro Horiguchi, Tom Lane Discussion:

- Doc: Remove extraneous whitespaces with some tags. Author: Justin Pryzby

- Fix come comments in execMain.c. 1375422 has refactored this area of the
executor code, and some comments went out-of-sync. Author: Yukun Wang
Reviewed-by: Amul Sul Discussion:

Amit Kapila pushed:

- Fix test case added by commit f5fc2f5b23. In the new test after resetting the
stats, we were not waiting for the stats message to be delivered. Also, we
need to decode the results for the new test, otherwise, it will show the old
stats. In passing, a. Change docs added by commit f5fc2f5b23 as per
suggestion by Justin Pryzby. b. Bump the PGSTAT_FILE_FORMAT_ID as commit
f5fc2f5b23 changes the file format of stats. Reported-by: Tom Lane based on
buildfarm reports Author: Vignesh C, Justin Pryzby Reviewed-by: Amit Kapila

Peter Eisentraut pushed:

- Use correct format placeholder for pids. Should be signed, not unsigned.

- Don't use INT64_FORMAT inside message strings. Use %lld and cast to long long
int instead.

- Fix typo.

- doc: Improve hyphenation consistency.

- Use correct format placeholder for timeline IDs. Should be %u rather than %d.

- Add DISTINCT to information schema usage views. Since pg_depend can contain
duplicate entries, we need to eliminate those in information schema views that
build on pg_depend, using DISTINCT. Some of the older views already did that
correctly, but some of the more recently added ones didn't. (In some of these
views, it might not be possible to reproduce the issue because of how the
implementation happens to deduplicate dependencies while recording them, but
it seems better to keep this consistent in all cases.)

- Update config.guess and config.sub.

- Fix some trailing whitespace in documentation files.

- Fix incorrect format placeholder.

- doc: Fix typos. Author: Justin Pryzby <pryzby(at)telsasoft(dot)com>

- pg_amcheck: Use logging functions. This was already mostly done, but some
error messages were printed the long way.

- Use correct format placeholder for WSAGetLastError(). Some code thought this
was unsigned, but it's signed int.

- Factor out system call names from error messages. Instead, put them in via a
format placeholder. This reduces the number of distinct translatable messages
and also reduces the chances of typos during translation. We already did this
for the system call arguments in a number of cases, so this is just the same
thing taken a bit further. Discussion:

Peter Geoghegan pushed:

- Document LP_DEAD accounting issues in VACUUM. Document VACUUM's soft
assumption that any LP_DEAD items encountered during pruning will become
LP_UNUSED items before VACUUM finishes up. This is integral to the accounting
used by VACUUM to generate its final report on the table to the stats
collector. It also affects how VACUUM determines which heap pages are
truncatable. In both cases VACUUM is concerned with the likely contents of
the page in the near future, not the current contents of the page. This state
of affairs created the false impression that VACUUM's dead tuple accounting
had significant difference with similar accounting used during ANALYZE. There
were and are no substantive differences, at least when the soft assumption
completely works out. This is far clearer now. Also document cases where
things don't quite work out for VACUUM's dead tuple accounting. It's possible
that a significant number of LP_DEAD items will be left behind by VACUUM, and
won't be recorded as remaining dead tuples in VACUUM's statistics collector
report. This behavior dates back to commit a96c41fe, which taught VACUUM to
run without index and heap vacuuming at the user's request. The failsafe
mechanism added to VACUUM more recently by commit 1e55e7d1 takes the same
approach to dead tuple accounting. Reported-By: Masahiko Sawada
<sawada(dot)mshk(at)gmail(dot)com> Discussion:

- amcheck: MAXALIGN() nbtree special area offset. This isn't strictly
necessary, but in theory it might matter if in the future the width of the
nbtree special area changes -- its total size might not be an even number of
MAXALIGN() quantums, even with padding. PageInit() MAXALIGN()s all special
area offsets, but amcheck uses the offset to perform initial basic validation
of line pointers, so we don't rely on the offset from the page header. The
real reason to do this is to set a good example for new code that adds amcheck
coverage for other index AMs. Reported-By: Bharath Rupireddy
<bharath(dot)rupireddyforpostgres(at)gmail(dot)com> Discussion:

Magnus Hagander pushed:

- Fix typo in comment. Author: Julien Rouhaud Backpatch-through: 11 Discussion:

- Mention that toplevel is part of pg_stat_statements key. While at it, also
document that toplevel is always true if pg_stat_statements.track is set to
top. Author: Julien Rouhaud Reported-By: Fujii Masao Discussion:

Andrew Dunstan pushed:

- Avoid unfortunate IPC::Run path caching in PostgresNode. Commit b34ca595ab
provided for installation-aware instances of PostgresNode. However, it turns
out that IPC::Run works against this by caching the path to a binary and not
consulting the path again, even if it has changed. We work around this by
calling Postgres binaries with the installed path rather than just a bare name
to be looked up in the environment path, if there is an installed path. For
the common case where there is no installed path we continue to use the bare
command name. Diagnosis and solution from Mark Dilger Discussion:

- Only ever test for non- addresses on Windows in PostgresNode. This
has been found to cause hangs where tcp usage is forced. Alexey Kodratov
Backpatch to all live branches

- fix silly perl error in commit d064afc720.

- Make PostgresNode version aware. A new PostgresVersion object type is created
and this is used in PostgresNode using the output of `pg_config --version` and
the result stored in the PostgresNode object. This object can be compared to
other PostgresVersion objects, or to a number or string. PostgresNode is
currently believed to be compatible with versions down to release 12, so
PostgresNode will issue a warning if used with a version prior to that. No
attempt has been made to deal with incompatibilities in older versions - that
remains work to be undertaken in a subsequent development cycle. Based on
code from Mark Dilger and Jehan-Guillaume de Rorthais. Discussion:

- Make PostgresVersion code a bit more robust and simple. per gripe from Alvaro

- Teach PostgresVersion all the ways to mark non-release code. As well as
'devel' provides for 'alphaN' 'betaN' and 'rcN', so teach
PostgresVersion about those. Also stash the version string instead of trying
to reconstruct it during stringification. Discussion:

- Provide pg_amcheck with an --install-missing option. This will install amcheck
in the database if not present. The default schema is for the extension is
pg_catalog, but this can be overridden by providing a value for the option.
Mark Dilger, slightly editorialized by me. (rather divergent) Discussion:

Tom Lane pushed:

- Fix planner failure in some cases of sorting by an aggregate. An oversight
introduced by the incremental-sort patches caused "could not find pathkey item
to sort" errors in some situations where a sort key involves an aggregate or
window function. The basic problem here is that
find_em_expr_usable_for_sorting_rel isn't properly modeling what
prepare_sort_from_pathkeys will do later. Rather than hoping we can keep
those functions in sync, let's refactor so that they actually share the code
for identifying a suitable sort expression. With this refactoring, tlist.c's
tlist_member_ignore_relabel is unused. I removed it in HEAD but left it in
place in v13, in case any extensions are using it. Per report from Luc
Vlaming. Back-patch to v13 where the problem arose. James Coleman and Tom
Lane Discussion:

- Rename find_em_expr_usable_for_sorting_rel. I didn't particularly like this
function name, as it fails to express what's going on. Also, returning the
sort expression alone isn't too helpful --- typically, a caller would also
need some other fields of the EquivalenceMember. But the sole caller really
only needs a bool result, so let's make it "bool
relation_can_be_sorted_early()". Discussion:

- Fix under-parenthesized XLogRecHasBlockRef() macro. Commit f003d9f87 left this
macro with inadequate (or, one could say, too much) parenthesization. Which
was catastrophic to the correctness of calls such as "if
(!XLogRecHasBlockRef(record, 1)) ...". There are only a few of those, which
perhaps explains why we didn't notice immediately (with our general weakness
of WAL replay testing being another factor). I found it by debugging
intermittent replay failures like 2021-04-08 14:33:30.191 EDT [29463] PANIC:
failed to locate backup block with ID 1 2021-04-08 14:33:30.191 EDT [29463]
CONTEXT: WAL redo at 0/95D3438 for SPGist/ADD_NODE: off 1; blkref #0: rel
1663/16384/25998, blk 1

- Improve WAL record descriptions for SP-GiST records. While tracking down the
bug fixed in the preceding commit, I got quite annoyed by the low quality of
spg_desc's output. Add missing fields, try to make the formatting consistent.

- Doc: document the tie-breaking behavior of the round() function. Back-patch to
v13; the table layout in older branches is unfriendly to adding such details.
Laurenz Albe Discussion:

- Don't crash on reference to an un-available system column. Adopt a more
consistent policy about what slot-type-specific getsysattr functions should do
when system attributes are not available. To wit, they should all throw the
same user-oriented error, rather than variously crashing or emitting
developer-oriented messages. This closes a identifiable problem in commits
a71cfc56b and 3fb93103a (in v13 and v12), so back-patch into those branches,
along with a test case to try to ensure we don't break it again. It is not
known that any of the former crash cases are reachable in HEAD, but this seems
like a good safety improvement in any case. Discussion:

Bruce Momjian pushed:

- adjust query id feature to use pg_stat_activity.query_id. Previously, it was
pg_stat_activity.queryid to match the pg_stat_statements queryid column. This
is an adjustment to patch 4f0b0966c8. This also adjusts some of the internal
function calls to match. Catversion bumped. Reported-by: Álvaro Herrera,
Julien Rouhaud Discussion:

- Fix interaction of log_line_prefix's query_id and log_statement. log_statement
is issued before query_id can be computed, so properly clear the value, and
document the interaction. Reported-by: Fujii Masao, Michael Paquier
Author: Julien Rouhaud

- doc: mention <link> can be inside of <command>, but not <xref>. This was
discussed in commit 9081bddbd. Reported-by: Peter Eisentraut Discussion:

Álvaro Herrera pushed:

- Don't add a redundant constraint when detaching a partition. On ALTER TABLE ..
DETACH CONCURRENTLY, we add a new table constraint that duplicates the
partition constraint. But if the partition already has another constraint
that implies that one, then that's unnecessary. We were already avoiding the
addition of a duplicate constraint if there was an exact 'equal' match -- this
just improves the quality of the check. Author: Justin Pryzby
<pryzby(at)telsasoft(dot)com> Reviewed-by: Álvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org>

- Add comment about extract_autovac_opts not holding lock. Per observation from
Tom Lane. Discussion:

- Fix relcache inconsistency hazard in partition detach. During queries coming
from ri_triggers.c, we need to omit partitions that are marked pending detach
-- otherwise, the RI query is tricked into allowing a row into the referencing
table whose corresponding row is in the detached partition. Which is bogus:
once the detach operation completes, the row becomes an orphan. However, the
code was not doing that in repeatable-read transactions, because relcache kept
a copy of the partition descriptor that included the partition, and used it in
the RI query. This commit changes the partdesc cache code to only keep
descriptors that aren't dependent on a snapshot (namely: those where no
detached partition exist, and those where detached partitions are included).
When a partdesc-without- detached-partitions is requested, we create one
afresh each time; also, those partdescs are stored in PortalContext instead of
CacheMemoryContext. find_inheritance_children gets a new output
`*detached_exist` boolean, which indicates whether any partition marked
pending-detach is found. Its "include_detached" input flag is changed to
"omit_detached", because that name captures desired the semantics more
naturally. CreatePartitionDirectory() and RelationGetPartitionDesc() arguments
are identically renamed. This was noticed because a buildfarm member that
runs with relcache clobbering, which would not keep the improperly cached
partdesc, broke one test, which led us to realize that the expected output of
that test was bogus. This commit also corrects that expected output. Author:
Amit Langote <amitlangote09(at)gmail(dot)com> Author: Álvaro Herrera
<alvherre(at)alvh(dot)no-ip(dot)org> Discussion:

- Fix uninitialized memory bug. Have interested callers of
find_inheritance_children set the detached_exist value to false prior to
calling it, so that that routine only has to set it true in the rare cases
where it is necessary. Don't touch it otherwise. Per buildfarm member
thorntail (which reported a UBSan failure here).

Etsuro Fujita pushed:

- Minor code cleanup in asynchronous execution support. This is cleanup for
commit 27e1f1456: * ExecAppendAsyncEventWait(), which was modified a bit
further by commit a8af856d3, duplicated the same nevents calculation.
Simplify the code a little bit to avoid the duplication. Update comments
there. * Add an assertion to ExecAppendAsyncRequest(). * Update a comment
about merging the async_capable options from input relations in
merge_fdw_options(), per complaint from Kyotaro Horiguchi. * Add a comment for
fetch_more_data_begin(). Author: Etsuro Fujita Discussion:

Fujii Masao pushed:

- doc: Fix obsolete description about pg_basebackup. Previously it was
documented that if using "-X none" option there was no guarantee that all
required WAL files were archived at the end of pg_basebackup when taking a
backup from the standby. But this limitation was removed by commit 52f8a59dd9.
Now, even when taking a backup from the standby, pg_basebackup can wait for
all required WAL files to be archived. Therefore this commit removes such
obsolete description from the docs. Also this commit adds new description
about the limitation when taking a backup from the standby, into the docs. The
limitation is that pg_basebackup cannot force the standbfy to switch to a new
WAL file at the end of backup, which may cause pg_basebackup to wait a long
time for the last required WAL file to be switched and archived, especially
when write activity on the primary is low. Back-patch to v10 where the issue
was introduced. Reported-by: Kyotaro Horiguchi Author: Kyotaro Horiguchi,
Fujii Masao Reviewed-by: Kyotaro Horiguchi, Fujii Masao Discussion:

- Reorder COMPRESSION option in gram.y and parsenodes.h into alphabetical order.
Commit bbe0a81db6 introduced "INCLUDING COMPRESSION" option in CREATE TABLE
command, but previously TableLikeOption in gram.y and parsenodes.h didn't
classify this new option in alphabetical order with the rest. Author: Fujii
Masao Reviewed-by: Michael Paquier Discussion:

Alexander Korotkov pushed:

- Mark multirange_constructor0() and multirange_constructor2() strict. These
functions shouldn't receive null arguments: multirange_constructor0() doesn't
have any arguments while multirange_constructor2() has a single array
argument, which is never null. But mark them strict anyway for the sake of
uniformity. Also, make checks for null arguments use elog() instead of
ereport() as these errors should normally be never thrown. And adjust
corresponding comments. Catversion is bumped. Reported-by: Peter Eisentraut

Noah Misch pushed:

- Make a test endure log_error_verbosity=verbose.

# Pending Patches

Peter Smith sent in a patch to rearrange the option list for CREATE

Bharath Rupireddy sent in another revision of a patch to add new table AMs for
multi- and single- inserts, and use same for CTAS, REFRESH MATERIALIZED VIEW,
and COPY.

Amul Sul sent in two more revisions of a patch to remove a redundant variable
from transformCreateStmt.

Pavel Stěhule sent in another revision of a patch to make it possible to log
seqscans in auto_explain.

David Rowley sent in another revision of a patch to reduce the number of contrib
build special cases on Windows.

Amul Sul sent in two more revisions of a patch to add a RelationGetSmgr inline

Masahiko Sawada sent in two more revisions of a patch to skip vmbuffer for
frozen tuple insertion.

Dagfinn Ilmari Mannsåker sent in a patch to create a function for stripping
RelabelType nodes off an expression, consolidating many repetitions of the same
pattern into one.

Masahiko Sawada and Amit Kapila traded patches to use HTAB for replication slot

Vigneshwaran C and Amit Kapila traded patches to update decoding stats during
replication slot release.

Bharath Rupireddy sent in a patch to use WaitLatches for lock waiting in
lazy_truncate_heap, in do_pg_stop_backup instead of a pg_usleep(), and for pre-
and post- auth delay.

Bharath Rupireddy sent in a patch to move the parallel_leader_participation GUC
to the resource consumption category.

Peter Smith sent in two more revisions of a patch to add support for prepared
transactions to built-in logical replication, and add prepare API support for
streaming transactions.

Dan Carter sent in a patch to add a GSSAPI ccache_name option to libpq.

Takamichi Osumi and Amit Kapila traded patches to fix an infelicity between
TRUNCATE and synchronous logical replication.

Amit Langote sent in a patch to fix a tupdesc leak in the pgoutput plugin.

Bharath Rupireddy sent in a patch to skip VACUUM/ANALYZE of repeated relations.

Pavel Stěhule sent in another revision of a patch to add a PSQL_WATCH_PAGER
setting to psql.

Masahiro Ikeda sent in another revision of a patch to make some performance
improvements in reporting WAL stats without introducing a new variable.

Fujii Masao sent in four more revisions of a patch to document the way TRUNCATE
works on foreign tables, and ensure that any ONLY clauses in the original query
not be passed to same.

Hou Zhijie sent in a patch to fix a test case with the wrong parallel safety

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

Simon Riggs sent in a patch to document the fact that altering a constraint to

Simon Riggs sent in a patch to reduce the lock level for CHECK constraints when
allowing them to be NOT VALID.

Daniil Zakhlystov sent in another revision of a patch to add zlib and zstd
streaming compression, and implement libpq compression.

Tom Lane sent in a patch to document Julian dates better.

Álvaro Herrera sent in a patch to add tab-complete for ALTER TABLE .. DETACH

Tom Stellard sent in a patch to jit to work around a potential data layout
mismatch on s390x.

Justin Pryzby sent in a patch to add COMPRESSION as a possibility when executing

Kyotaro HORIGUCHI sent in a patch to fix up some dubious messages.

Tang sent in a patch to use pg_strncasecmp to replace strncmp when comparing
things "pg_" in psql.

Tang sent in another revision of a patch to support tab completion for upper
case character inputs in psql.

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

Mark Dilger and Robert Haas traded patches to add toast pointer corruption
checks to pg_amcheck.

Bharath Rupireddy sent in a patch to MAXALIGN sizeof(BTPageOpaqueData) in

Ajin Cherian sent in another revision of a patch to skip empty transactions for
logical replication.

Alexander Pyhalov sent in another revision of a patch to push down restrictinfos
to CTEs, and attempt to prune partitions in the process.

Thomas Munro sent in a patch to add a new TAP test under src/test/recovery that
runs the regression tests with wal_consistency_checking=all.

Joel Jacobson sent in a patch to fix pg_identify_object_as_address for event

Takamichi Osumi sent in a patch to replace the check of
RelationIsLogicallyLogged in ReorderBufferProcessTXN with an Assert to the same

David Rowley sent in another revision of a patch to speed up NOT IN() with a set
of Consts by allowing hash tables to be used.

David Rowley and Yura Sokolov traded patches to use simplehash.h instead of
dynahash in SMgr.

Julien Rouhaud sent in a patch to fix some oversights in query_id calculation.

Tom Lane sent in a patch to make it required for callers of pq_getmessage to
provide an upper length bound, and installs the same sort of short-vs-long
message heuristic as libpq has in the server.

Browse pgsql-announce by date

  From Date Subject
Next Message Data Egret via PostgreSQL Announce 2021-04-28 15:09:43 has been updated
Previous Message JDBC Project via PostgreSQL Announce 2021-04-23 11:49:09 PostgreSQL JDBC 42.2.20 Released