PostgreSQL Weekly News - October 31, 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 31, 2021
Date: 2021-11-01 08:47:52
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-announce

# PostgreSQL Weekly News - October 31, 2021

Happy Hallowe'en!

# PostgreSQL Product News

pg_statement_rollback v1.3, an extension that adds server side
transaction with rollback at statement level,

# 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

Michaël Paquier pushed:

- Add replication command READ_REPLICATION_SLOT. The command is supported for
physical slots for now, and returns the type of slot, its restart_lsn and its
restart_tli. This will be useful for an upcoming patch related to
pg_receivewal, to allow the tool to be able to stream from the position of a
slot, rather than the last WAL position flushed by the backend (as reported by
IDENTIFY_SYSTEM) if the archive directory is found as empty, which would be an
advantage in the case of switching to a different archive locations with the
same slot used to avoid holes in WAL segment archives. Author: Ronan Dunklau
Reviewed-by: Kyotaro Horiguchi, Michael Paquier, Bharath Rupireddy Discussion:

- Allow pg_receivewal to stream from a slot's restart LSN. Prior to this patch,
when running pg_receivewal, the streaming start point would be the current
location of the archives if anything is found in the local directory where WAL
segments are written, and pg_receivewal would fall back to the current WAL
flush location if there are no archives, as of the result of an
IDENTIFY_SYSTEM command. If for some reason the WAL files from pg_receivewal
were moved, it is better to try a restart where we left at, which is the
replication slot's restart_lsn instead of skipping right to the current flush
location, to avoid holes in the WAL backed up. This commit changes
pg_receivewal to use the following sequence of methods to determine the
starting streaming LSN: - Scan the local archives. - Use the slot's
restart_lsn, if supported by the backend and if a slot is defined. - Fallback
to the current flush LSN as reported by IDENTIFY_SYSTEM. To keep
compatibility with older server versions, we only attempt to use
READ_REPLICATION_SLOT if the backend version is at least 15, and fallback to
the older behavior of streaming from the current flush LSN if the command is
not supported. Some TAP tests are added to cover this feature. Author: Ronan
Dunklau Reviewed-by: Kyotaro Horiguchi, Michael Paquier, Bharath Rupireddy

- Fix overly-lax regex pattern in TAP test of READ_REPLICATION_SLOT. The case
checking for a NULL output when a slot does not exist was too lax, as it was
passing for any output generated by the query. This fixes the matching
pattern to be what it should be, matching only on "||". Oversight in b4ada4e.

- doc: Fix grammar in page of pg_receivewal. Introduced by f61e1dd. Author:
Kyotaro Horiguchi Discussion:

- Add test for copy of shared dependencies from template database. As 98ec35b
has proved, there has never been any coverage in this area of the code. This
commit adds a new TAP test with a template database that includes a small set
of shared dependencies copied to a new database. The test is added in
createdb, where we have never tested that -T generates a query with TEMPLATE,
either. Reviewed-by: Tom Lane Discussion:

- doc: Fix link to SELinux user guide in sepgsql page. Reported-by: Anton
Voloshin Discussion:
Backpatch-through: 10

- Add TAP test for archive_cleanup_command and recovery_end_command. This adds
tests checking for the execution of both commands. The recovery test is nicely adapted to that, as promotion is triggered already
twice there, and even if any of those commands fail they don't affect recovery
or promotion. A command success is checked using a file generated by an
"echo" command, that should be able to work in all the buildfarm environments,
even Msys (but we'll know soon about that). Command failure is tested with an
"echo" command that points to a path that does not exist, scanning the backend
logs to make sure that the failure happens. Both rely on the backend
triggering the commands from the root of the data folder, making its logic
more robust. Thanks to Neha Sharma for the extra tests on Windows. Author:
Amul Sul, Michael Paquier Reviewed-by: Andres Freund, Euler Taveira

- Speed up TAP tests of pg_receivewal. This commit improves the speed of those
tests by 25~30%, using some simple ideas to reduce the amount of data written
by pg_receivewal: - Use a segment size of 1MB. While reducing the amount of
data zeroed by pg_receivewal for the new segments, this improves the code
coverage with a non-default segment size. - In the last test involving a
slot's restart_lsn, generate a checkpoint to advance the redo LSN and the WAL
retained by the slot created, reducing the number of segments that need to be
archived. This counts for most of the gain. - Minimize the amount of data
inserted into the dummy table. Reviewed-by: Ronan Dunklau Discussion:

Heikki Linnakangas pushed:

- Clarify the logic in a few places in the new balanced merge code. In
selectnewtape(), use 'nOutputTapes' rather than 'nOutputRuns' in the check for
whether to start a new tape or to append a new run to an existing tape. Until
'maxTapes' is reached, nOutputTapes is always equal to nOutputRuns, so it
doesn't change the logic, but it seems more logical to compare # of tapes with
# of tapes. Also, currently maxTapes is never modified after the merging
begins, but written this way, the code would still work if it was. (Although
the nOutputRuns == nOutputTapes assertion would need to be removed and using
nOutputRuns % nOutputTapes to distribute the runs evenly across the tapes
wouldn't do a good job anymore). Similarly in mergeruns(), change to
USEMEM(state->tape_buffer_mem) to account for the memory used for tape
buffers. It's equal to availMem currently, but tape_buffer_mem is more direct
and future-proof. For example, if we changed the logic to only allocate half
of the remaining memory to tape buffers, USEMEM(state->tape_buffer_mem) would
still be correct. Coverity complained about these. Hopefully this patch helps
it to understand the logic better. Thanks to Tom Lane for initial analysis.

Robert Haas pushed:

- StartupXLOG: Call CleanupAfterArchiveRecovery after XLogReportParameters. This
does a better job grouping related operations together, since all of the WAL
records that we need to write prior to allowing WAL writes generally and
written by a single uninterrupted stretch of code. Since
CleanupAfterArchiveRecovery() just (1) runs recovery_end_command, (2) removes
non-parent xlog files, and (3) archives any final partial segment, this should
be safe, because all of those things are pretty much unrelated to the WAL
record written by XLogReportParameters(). Amul Sul, per a suggestion from me

- StartupXLOG: Don't repeatedly disable/enable local xlog insertion. All the
code that runs in the startup process to write WAL records before that's
allowed generally is now consecutive, so there's no reason to shut the
facility to write WAL locally off and then turn it on again three times in a
row. Unfortunately, this requires a slight kludge in the checkpointer, which
needs to separately enable writing WAL in order to write the checkpoint
record. Because that code might run in the same process as StartupXLOG() if we
are in single-user mode, we must save/restore the state of the
LocalXLogInsertAllowed flag. Hopefully, we'll be able to eliminate this wart
in further refactoring, but it's not too bad anyway. Amul Sul, with
modifications by me. Discussion:

- Remove useless code from CreateReplicationSlot. According to the comments, we
initialize sendTimeLineIsHistoric and sendTimeLine here for the benefit of
WalSndSegmentOpen. However, the only way that can happen is if
logical_read_xlog_page calls WALRead. And since logical_read_xlog_page
initializes the same global variables internally, we don't need to also do it
here. These initializations have been here since replication slots were
introduced in commit 858ec11858a914d4c380971985709b6d6b7dd6fc. They were
certainly useless at that time, too, because logical decoding didn't yet exist
then, and physical replication doesn't examine any WAL at the time of slot
creation. I haven't checked all the intermediate versions, but I suspect
there's no point at which this code ever did anything useful. To reduce
future confusion, remove the code. Since there's no functional defect, no
back-patch. Discussion:

- Add enable_timeout_every() to fire the same timeout repeatedly.
enable_timeout_at() and enable_timeout_after() can still be used when you want
to fire a timeout just once. Patch by me, per a suggestion from Tom Lane.

- Report progress of startup operations that take a long time. Users sometimes
get concerned whe they start the server and it emits a few messages and then
doesn't emit any more messages for a long time. Generally, what's happening is
either that the system is taking a long time to apply WAL, or it's taking a
long time to reset unlogged relations, or it's taking a long time to fsync the
data directory, but it's not easy to tell which is the case. To fix that, add
a new 'log_startup_progress_interval' setting, by default 10s. When an
operation that is known to be potentially long-running takes more than this
amount of time, we'll log a status update each time this interval elapses. To
avoid undesirable log chatter, don't log anything about WAL replay when in
standby mode. Nitin Jadhav and Robert Haas, reviewed by Amul Sul, Bharath
Rupireddy, Justin Pryzby, Michael Paquier, and Álvaro Herrera. Discussion:

- Initialize variable to placate compiler. Per Nathan Bossart. Discussion:

- When fetching WAL for a basebackup, report errors with a sensible TLI. The
previous code used ThisTimeLineID, which need not even be initialized here,
although it usually was in practice, because pg_basebackup issues
IDENTIFY_SYSTEM before calling BASE_BACKUP, and that initializes
ThisTimeLineID as a side effect. That's not really good enough, though, not
only because we shoudn't be counting on side effects like that, but also
because the TLI could change meanwhile. Fortunately, we have convenient access
to more meaningful TLI values, so use those instead. Because of the way this
logic is coded, the consequences of using a possibly-incorrect TLI here are no
worse than a slightly confusing error message, I don't want to take any risk
here, so no back-patch at least for now. Patch by me, reviewed by Kyotaro
Horiguchi and Michael Paquier Discussion:

- Fix race condition in startup progress reporting. Commit
9ce346eabf350a130bba46be3f8c50ba28506969 added startup progress reporting, but
begin_startup_progress_phase has a race condition: the timeout for the
previous phase might fire just before we reschedule the interrupt for the next
phase. To avoid the race, disable the timeout, clear the flag, and then
re-enable the timeout. Patch by me, reviewed by Nitin Jadhav. Discussion:

Thomas Munro pushed:

- Reject huge_pages=on if shared_memory_type=sysv. It doesn't work (it could,
but hasn't been implemented). Back-patch to 12, where shared_memory_type
arrived. Reported-by: Alexander Lakhin <exclusion(at)gmail(dot)com> Reviewed-by:
Alexander Lakhin <exclusion(at)gmail(dot)com> Discussion:

Daniel Gustafsson pushed:

- Ensure that slots are zeroed before use. The previous coding relied on the
memory for the slots being zeroed elsewhere, which while it was true in this
case is not an contract which is guaranteed to hold. Explicitly clear the
tts_isnull array to ensure that the slots are filled from a known state.
Backpatch to v14 where the catalog multi-inserts were introduced.
Reviewed-by: Michael Paquier <michael(at)paquier(dot)xyz> Discussion:
Backpatch-through: 14

- Fix VPATH builds for src/test/ssl targets. Commit b4c4a00ea refactored the
gist of the sslfiles target into a separate makefile in order to override
settings in The invocation of this this file didn't however
include the absolute path for VPATH builds, resulting in "make clean" failing.
Fix by providing the path to the new makefile. Reported-by: Andres Freund
<andres(at)anarazel(dot)de> Discussion:

- Fix typos in comments. Author: Peter Smith <smithpb2250(at)gmail(dot)com> Discussion:

Fujii Masao pushed:

- Improve HINT message that FDW reports when there are no valid options. The
foreign data wrapper's validator function provides a HINT message with list of
valid options for the object specified in CREATE or ALTER command, when the
option given in the command is invalid. Previously postgresql_fdw_validator()
and the validator functions for postgres_fdw and dblink_fdw worked in that way
even there were no valid options in the object, which could lead to the HINT
message with empty list (because there were no valid options). For example,
ALTER FOREIGN DATA WRAPPER postgres_fdw OPTIONS (format 'csv') reported the
following ERROR and HINT messages. This behavior was confusing. ERROR:
invalid option "format" HINT: Valid options in this context are: There is
no such issue in file_fdw. The validator function for file_fdw reports the
HINT message "There are no valid options in this context." instead in that
case. This commit improves postgresql_fdw_validator() and the validator
functions for postgres_fdw and dblink_fdw so that they do likewise. For
example, this change causes the above ALTER FOREIGN DATA WRAPPER command to
report the following messages. ERROR: invalid option "nonexistent"
HINT: There are no valid options in this context. Author: Kosei Masumura
Reviewed-by: Bharath Rupireddy, Fujii Masao Discussion:

Jeff Davis pushed:

- Allow GRANT on pg_log_backend_memory_contexts(). Remove superuser check,
allowing any user granted permissions on pg_log_backend_memory_contexts() to
log the memory contexts of any backend. Note that this could allow a
privileged non-superuser to log the memory contexts of a superuser backend,
but as discussed, that does not seem to be a problem. Reviewed-by: Nathan
Bossart, Bharath Rupireddy, Michael Paquier, Kyotaro Horiguchi, Andres Freund

- Grant memory views to pg_read_all_stats. Grant privileges on views
pg_backend_memory_contexts and pg_shmem_allocations to the role
pg_read_all_stats. Also grant on the underlying functions that those views
depend on. Author: Bharath Rupireddy <bharath(dot)rupireddyforpostgres(at)gmail(dot)com>
Reviewed-by: Nathan Bossart <bossartn(at)amazon(dot)com> Discussion:

Amit Kapila pushed:

- Allow publishing the tables of schema. A new option "FOR ALL TABLES IN SCHEMA"
in Create/Alter Publication allows one or more schemas to be specified, whose
tables are selected by the publisher for sending the data to the subscriber.
The new syntax allows specifying both the tables and schemas. For example:
system table "pg_publication_namespace" has been added, to maintain the
schemas that the user wants to publish through the publication. Modified the
output plugin (pgoutput) to publish the changes if the relation is part of
schema publication. Updates pg_dump to identify and dump schema publications.
Updates the \d family of commands to display schema publications and \dRp+
variant will now display associated schemas if any. Author: Vignesh C, Hou
Zhijie, Amit Kapila Syntax-Suggested-by: Tom Lane, Alvaro Herrera Reviewed-by:
Greg Nancarrow, Masahiko Sawada, Hou Zhijie, Amit Kapila, Haiying Tang, Ajin
Cherian, Rahila Syed, Bharath Rupireddy, Mark Dilger Tested-by: Haiying Tang

- Add tap tests for the schema publications. This adds additional tests for
commit 5a2832465f ("Allow publishing the tables of schema.). This allows
testing streaming of data in tables that are published via schema
publications. Author: Vignesh C, Haiying Tang Reviewed-by: Greg Nancarrow,
Hou Zhijie, Amit Kapila Discussion:

Magnus Hagander pushed:

- Clarify that --system reindexes system catalogs *only*. Make this more clear
both in the help message and docs. Reviewed-By: Michael Paquier
Backpatch-through: 9.6 Discussion:

Peter Geoghegan pushed:

- Further harden nbtree posting split code. Add more defensive checks around
posting list split code. These should detect corruption involving duplicate
table TIDs earlier and more reliably than any existing check. Follow up to
commit 8f72bbac. Discussion:
Backpatch: 13-, where nbtree deduplication was introduced.

- Fix ordering of items in nbtree error message. Oversight in commit a5213adf.
Backpatch: 13-, just like commit a5213adf.

- Remove obsolete nbtree LP_DEAD item comments. Comments above
`_bt_findinsertloc()` that talk about LP_DEAD items are now out of place. We
already discuss index tuple deletion at an earlier point in the same comment
block. Oversight in commit d168b666.

- Demote pg_unreachable() in heapam to an assertion. Commit d168b66682, which
overhauled index deletion, added a pg_unreachable() to the end of a sort
comparator used when sorting heap TIDs from an index page. This allows the
compiler to apply optimizations that assume that the heap TIDs from the index
AM must always be unique. That doesn't seem like a good idea now, given
recent reports of corruption involving duplicate TIDs in indexes on Postgres
14. Demote to an assertion, just in case. Backpatch: 14-, where index
deletion was overhauled.

Tom Lane pushed:

- Improve contrib/amcheck's tests for CREATE INDEX CONCURRENTLY. Commits
fdd965d07 and 3cd9c3b92 tested CREATE INDEX CONCURRENTLY by launching two
separate pgbench runs concurrently. This was needed so that only a single
client thread would run CREATE INDEX CONCURRENTLY, avoiding deadlock between
two CICs. However, there's a better way, which is to use an advisory lock to
prevent concurrent CICs. That's better in part because the test code is
shorter and more readable, but mostly because it automatically scales things
to launch an appropriate number of CICs relative to the number of INSERT
transactions. As committed, typically half to three-quarters of the CIC
transactions were pointless because the INSERT transactions had already
stopped. In passing, remove background_pgbench, which was added to support
these tests and isn't needed anymore. We can always put it back if we find a
use for it later. Back-patch to v12; older pgbench versions lack the
conditional-execution features needed for this method. Tom Lane and Andrey
Borodin Discussion:

- Speed up printing of integers in snprintf.c. Since the only possible divisors
are 8, 10, and 16, it doesn't cost much code space to replace the division
loop with three copies using constant divisors. On most machines, division by
a constant can be done a lot more cheaply than division by an arbitrary value.
A microbenchmark testing just snprintf("foo %d") with a 9-digit value showed
about a 2X speedup for me (tgl). Most of Postgres isn't too dependent on the
speed of snprintf, so that the effect in real-world cases is barely
measurable. Still, a cycle saved is a cycle earned. Arjan van de Ven

- Update time zone data files to tzdata release 2021e. DST law changes in Fiji,
Jordan, Palestine, and Samoa. Historical corrections for Barbados, Cook
Islands, Guyana, Niue, Portugal, and Tonga. Also, the Pacific/Enderbury zone
has been renamed to Pacific/Kanton. The following zones have been merged into
nearby, more-populous zones whose clocks have agreed since 1970: Africa/Accra,
America/Atikokan, America/Blanc-Sablon, America/Creston, America/Curacao,
America/Nassau, America/Port_of_Spain, Antarctica/DumontDUrville, and

- Test and document the behavior of initialization cross-refs in plpgsql. We had
a test showing that a variable isn't referenceable in its own initialization
expression, nor in prior ones in the same block. It *is* referenceable in
later expressions in the same block, but AFAICS there is no test case
exercising that. Add one, and also add some error cases. Also, document that
this is possible, since the docs failed to cover the point. Per question from
tomás at tuxteam. I don't feel any need to back-patch this, but we should
ensure we don't break it in future. Discussion:

Peter Eisentraut pushed:

- Remove unused chunk from standalone-profile.xsl. unused since

- uuid-ossp: Remove obsolete build connection with pgcrypto. unused since

- doc: Remove some obsolete pgcrypto documentation. The pgcrypto documentation
contained acknowledgments of used external code, but some of this code has
been moved to src/common/, so mentioning it with pgcrypto no longer makes
sense, so remove it.

- pg_dump: Refactor messages. This reduces the number of separate messages for

Browse pgsql-announce by date

  From Date Subject
Next Message Amazon Web Services via PostgreSQL Announce 2021-11-01 11:58:58 Babelfish for PostgreSQL
Previous Message Gilles Darold via PostgreSQL Announce 2021-10-24 23:20:28 pg_statement_rollback v1.3 released