PostgreSQL Weekly News - June 27, 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 - June 27, 2021
Date: 2021-06-28 11:45:22
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-announce

# PostgreSQL Weekly News - June 27, 2021

PostgreSQL 14 Beta 2 released.

The 12th PostgreSQL Conference Cuba (PostgresqlCUBA, @PgCuba) will take place on
November 18-19, 2021, at the Hotel Habana Libre. This event is part of the
TECNOGET conference, and we will host one track dedicated to PostgreSQL related
talks. For more details, reach out to cu AT postgresql DOT org.

# PostgreSQL Product News

JDBC 42.2.22

pgpoolAdmin 4.2.0, the administration tool for Pgpool-II,

pgCenter 0.9.0, a command-line admin tool for observing and troubleshooting
PostgreSQL, [released](

# PostgreSQL Jobs for June


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

- Work around portability issue with newer versions of mktime(). Recent glibc
versions have made mktime() fail if tm_isdst is inconsistent with the
prevailing timezone; in particular it fails for tm_isdst = 1 when the zone is
UTC. (This seems wildly inconsistent with the POSIX-mandated treatment of
"incorrect" values for the other fields of struct tm, so if you ask me it's a
bug, but I bet they'll say it's intentional.) This has been observed to cause
cosmetic problems when pg_restore'ing an archive created in a different
timezone. To fix, do mktime() using the field values from the archive, and if
that fails try again with tm_isdst = -1. This will give a result that's off
by the UTC-offset difference from the original zone, but that was true before,
too. It's not terribly critical since we don't do anything with the result
except possibly print it. (Someday we should flush this entire bit of logic
and record a standard-format timestamp in the archive instead. That's not
okay for a back-patched bug fix, though.) Also, guard our only other use of
mktime() by having initdb's build_time_t() set tm_isdst = -1 not 0. This case
could only have an issue in zones that are DST year-round; but I think some do
exist, or could in future. Per report from Wells Oliver. Back-patch to all
supported versions, since any of them might need to run with a newer glibc.

- Remove orphaned expected-result file. This should have been removed in
43e084197, which removed the corresponding spec file. Noted while fooling
about with the isolationtester.

- Update variant expected-result file. This should have been updated in
d2d8a229b, but it was overlooked. According to 31a877f18 which added it, this
file is meant to show the results you get under default_transaction_isolation
= serializable. We've largely lost track of that goal in other isolation
tests, but as long as we've got this one, it should be right. Noted while
fooling about with the isolationtester.

- Remove another orphan expected-result file. aborted-keyrevoke_2.out was
apparently needed when it was added (in commit 0ac5ad513) to handle the case
of serializable transaction mode. However, the output in serializable mode
actually matches the regular aborted-keyrevoke.out file, and AFAICT has done
so for a long time. There's no need to keep dragging this variant along.

- Update another variant expected-result file. This should have been updated in
533e9c6b0, but it was overlooked. Given the lack of complaints, I won't bother

- Improve SQLSTATE reporting in some replication-related code. I started out
with the goal of reporting ERRCODE_CONNECTION_FAILURE when walrcv_connect()
fails, but as I looked around I realized that whoever wrote this code was of
the opinion that errcodes are purely optional. That's not my understanding of
our project policy. Hence, make sure that an errcode is provided in each
ereport that (a) is ERROR or higher level and (b) isn't arguably an internal
logic error. Also fix some very dubious existing errcode assignments. While
this is not per policy, it's also largely cosmetic, since few of these cases
could get reported to applications. So I don't feel a need to back-patch.

- Fix plancache refcount leak after error in ExecuteQuery. When stuffing a plan
from the plancache into a Portal, one is not supposed to risk throwing an
error between GetCachedPlan and PortalDefineQuery; if that happens, the plan
refcount incremented by GetCachedPlan will be leaked. I managed to break this
rule while refactoring code in 9dbf2b7d7. There is no visible consequence
other than some memory leakage, and since nobody is very likely to trigger the
relevant error conditions many times in a row, it's not surprising we haven't
noticed. Nonetheless, it's a bug, so rearrange the order of operations to
remove the hazard. Noted on the way to looking for a better fix for bug
#17053. This mistake is pretty old, so back-patch to all supported branches.

- Centralize the logic for protective copying of utility statements. In the
"simple Query" code path, it's fine for parse analysis or execution of a
utility statement to scribble on the statement's node tree, since that'll just
be thrown away afterwards. However it's not fine if the node tree is in the
plan cache, as then it'd be corrupted for subsequent executions. Up to now
we've dealt with that by having individual utility-statement functions apply
copyObject() if they were going to modify the tree. But that's prone to
errors of omission. Bug #17053 from Charles Samborski shows that CREATE/ALTER
DOMAIN didn't get this memo, and can crash if executed repeatedly from plan
cache. In the back branches, we'll just apply a narrow band-aid for that, but
in HEAD it seems prudent to have a more principled fix that will close off the
possibility of other similar bugs in future. Hence, let's hoist the
responsibility for doing copyObject up into ProcessUtility from its children,
thus ensuring that it happens for all utility statement types. Also, modify
ProcessUtility's API so that its callers can tell it whether a copy step is
necessary. It turns out that in all cases, the immediate caller knows whether
the node tree is transient, so this doesn't involve a huge amount of code
thrashing. In this way, while we lose a little bit in the execute-from-cache
code path due to sometimes copying node trees that wouldn't be mutated anyway,
we gain something in the simple-Query code path by not copying throwaway node
trees. Statements that are complex enough to be expensive to copy are almost
certainly ones that would have to be copied anyway, so the loss in the cache
code path shouldn't be much. (Note that this whole problem applies only to
utility statements. Optimizable statements don't have the issue because we
long ago made the executor treat Plan trees as read-only. Perhaps someday we
will make utility statement execution act likewise, but I'm not holding my
breath.) Discussion:

- Improve version reporting in pgbench. Commit 547f04e73 caused pgbench to start
printing its version number, which seems like a fine idea, but it needs a bit
more work: * Print the server version number too, when different. * Print the
PG_VERSION string, not some reconstructed approximation. This patch copies
psql's well-tested code for the same purpose. Discussion:

- Fix misbehavior of DROP OWNED BY with duplicate polroles entries. Ordinarily,
a pg_policy.polroles array wouldn't list the same role more than once; but
CREATE POLICY does not prevent that. If we perform DROP OWNED BY on a role
that is listed more than once, RemoveRoleFromObjectPolicy either suffered an
assertion failure or encountered a tuple-updated-by-self error. Rewrite it to
cope correctly with duplicate entries, and add a CommandCounterIncrement call
to prevent the other problem. Per discussion, there's other cleanup that
ought to happen here, but this seems like the minimum essential fix. Per bug
#17062 from Alexander Lakhin. It's been broken all along, so back-patch to
all supported branches. Discussion:

- Provide feature-test macros for libpq features added in v14. We had a request
to provide a way to test at compile time for the availability of the new
pipeline features. More generally, it seems like a good idea to provide a way
to test via #ifdef for all new libpq API features. People have been using the
version from pg_config.h for that; but that's more likely to represent the
server version than the libpq version, in the increasingly-common scenario
where they're different. It's safer if libpq-fe.h itself is the source of
truth about what features it offers. Hence, establish a policy that starting
in v14 we'll add a suitable feature-is-present macro to libpq-fe.h when we add
new API there. (There doesn't seem to be much point in applying this policy
retroactively, but it's not too late for v14.) Tom Lane and Alvaro Herrera,
per suggestion from Boris Kolpackov. Discussion:

- Stabilize test case added by commit f61db909d. Buildfarm members ayu and tern
have sometimes shown a different plan than expected for this query. I'd been
unable to reproduce that before today, but I finally realized what is
happening. If there is a concurrent open transaction (probably an autovacuum
run in the buildfarm, but this can also be arranged manually), then the index
entries for the rows removed by the DELETE a few lines up are not killed
promptly, causing a change in the planner's estimate of the extremal value of
ft2.c1, which moves the rowcount estimate for "c1 > 1100" by enough to change
the join plan from nestloop to hash. To fix, change the query condition to
"c1 > 1000", causing the hash plan to be preferred whether or not a concurrent
open transaction exists. Since this UPDATE is tailored to be a no-op, nothing
else changes. Report:

- Restore the portal-level snapshot for simple expressions, too. Commits
84f5c2908 et al missed the need to cover plpgsql's "simple expression" code
path. If the first thing we execute after a COMMIT/ROLLBACK is one of those,
rather than a full-fledged SPI command, we must explicitly do
EnsurePortalSnapshotExists() to make sure we have an outer snapshot. Note
that it wouldn't be good enough to just push a snapshot for the duration of
the expression execution: what comes back might be toasted, so we'd better
have a snapshot protecting it. The test case demonstrating this fact cheats a
bit by marking a SQL function immutable even though it fetches from a table.
That's nothing that users haven't been seen to do, though. Per report from
Jim Nasby. Back-patch to v11, like the previous fix. Discussion:

- Use annotations to reduce instability of isolation-test results. We've long
contended with isolation test results that aren't entirely stable. Some test
scripts insert long delays to try to force stable results, which is not
terribly desirable; but other erratic failure modes remain, causing
unrepeatable buildfarm failures. I've spent a fair amount of time trying to
solve this by improving the server-side support code, without much success:
that way is fundamentally unable to cope with diffs that stem from chance
ordering of arrival of messages from different server processes. We can
improve matters on the client side, however, by annotating the test scripts
themselves to show the desired reporting order of events that might occur in
different orders. This patch adds three types of annotations to deal with (a)
test steps that might or might not complete their waits before the
isolationtester can see them waiting; (b) test steps in different sessions
that can legitimately complete in either order; and (c) NOTIFY messages that
might arrive before or after the completion of a step in another session. We
might need more annotation types later, but this seems to be enough to deal
with the instabilities we've seen in the buildfarm. It also lets us get rid
of all the long delays that were previously used, cutting more than a minute
off the runtime of the isolation tests. Back-patch to all supported branches,
because the buildfarm instabilities affect all the branches, and because it
seems desirable to keep isolationtester's capabilities the same across all
branches to simplify possible future back-patching of tests. Discussion:

- Improve display of query results in isolation tests. Previously,
isolationtester displayed SQL query results using some ad-hoc code that
clearly hadn't had much effort expended on it. Field values longer than 14
characters weren't separated from the next field, and usually caused
misalignment of the columns too. Also there was no visual separation of a
query's result from subsequent isolationtester output. This made test result
files confusing and hard to read. To improve matters, let's use libpq's
PQprint() function. Although that's long since unused by psql, it's still
plenty good enough for the purpose here. Like 741d7f104, back-patch to all
supported branches, so that this isn't a stumbling block for back-patching
isolation test changes. Discussion:

- Don't assume GSSAPI result strings are null-terminated. Our uses of
gss_display_status() and gss_display_name() assumed that the gss_buffer_desc
strings returned by those functions are null-terminated. It appears that they
generally are, given the lack of field complaints up to now. However, the
available documentation does not promise this, and some man pages for
gss_display_status() show examples that rely on the gss_buffer_desc.length
field instead of expecting null termination. Also, we now have a report that
on some implementations, clang's address sanitizer is of the opinion that the
byte after the specified length is undefined. Hence, change the code to rely
on the length field instead. This might well be cosmetic rather than fixing
any real bug, but it's hard to be sure, so back-patch to all supported
branches. While here, also back-patch the v12 changes that made pg_GSS_error
deal honestly with multiple messages available from gss_display_status. Per
report from Sudheer H R. Discussion:

- Doc: fix confusion about LEAKPROOF in syntax summaries. The syntax summaries
for CREATE FUNCTION and allied commands made it look like LEAKPROOF is an
alternative to IMMUTABLE/STABLE/VOLATILE, when of course it is an orthogonal
option. Improve that. Per gripe from aazamrafeeque0. Thanks to David
Johnston for suggestions. Discussion:

- Allow non-quoted identifiers as isolation test session/step names. For no
obvious reason, isolationtester has always insisted that session and step
names be written with double quotes. This is fairly tedious and does little
for test readability, especially since the names that people actually choose
almost always look like normal identifiers. Hence, let's tweak the lexer to
allow SQL-like identifiers not only double-quoted strings. (They're SQL-like,
not exactly SQL, because I didn't add any case-folding logic. Also there's no
provision for U&"..." names, not that anyone's likely to care.) There is one
incompatibility introduced by this change: if you write "foo""bar" with no
space, that used to be taken as two identifiers, but now it's just one
identifier with an embedded quote mark. I converted all the
src/test/isolation/ specfiles to remove unnecessary double quotes, but stopped
there because my eyes were glazing over already. Like 741d7f104, back-patch
to all supported branches, so that this isn't a stumbling block for
back-patching isolation test changes. Discussion:

- Further stabilize postgres_fdw test. The queries involving ft1_nopw don't
stably return the same row anymore. I surmise that an autovacuum hitting "S
1"."T 1" right after the updates introduced by f61db909d/5843659d0 freed some
space, changing where subsequent insertions get stored. It's only by good luck
that these results were stable before, though, since a LIMIT without ORDER BY
isn't well defined, and it's not like we've ever treated that table as
append-only in this test script. Since we only really care whether these
commands succeed or not, just replace `"SELECT *"` with "SELECT 1". Report:

- Doc: remove commit f560209c6 from v14 release notes. Now that this has been
back-patched, it's no longer a new feature for v14.

- Remove unnecessary failure cases in RemoveRoleFromObjectPolicy(). It's not
really necessary for this function to open or lock the relation associated
with the pg_policy entry it's modifying. The error checks it's making on the
rel are if anything counterproductive (e.g., if we don't want to allow
installation of policies on system catalogs, here is not the place to prevent
that). In particular, it seems just wrong to insist on an ownership check.
That has the net effect of forcing people to use superuser for DROP OWNED BY,
which surely is not an effect we want. Also there is no point in rebuilding
the dependencies of the policy expressions, which aren't being changed.
Lastly, locking the table also seems counterproductive; it's not helping to
prevent race conditions, since we failed to re-read the pg_policy row after
acquiring the lock. That means that concurrent DDL would likely result in
"tuple concurrently updated/deleted" errors; which is the same behavior this
code will produce, with less overhead. Per discussion of bug #17062.
Back-patch to all supported versions, as the failure cases this eliminates
seem just as undesirable in 9.6 as in HEAD. Discussion:

- Remove undesirable libpq dependency on stringinfo.c. Commit c0cb87fbb unwisely
introduced a dependency on the StringInfo machinery in fe-connect.c. We must
not use that in libpq, because it will do a summary exit(1) if it hits OOM,
and that is not appropriate behavior for a general-purpose library. The goal
of allowing arbitrary line lengths in service files doesn't seem like it's
worth a lot of effort, so revert back to the previous method of using a
stack-allocated buffer and failing on buffer overflow. This isn't an exact
revert though. I kept that patch's refactoring to have a single exit path, as
that seems cleaner than having each error path know what to do to clean up.
Also, I made the fixed-size buffer 1024 bytes not 256, just to push off the
need for an expandable buffer some more. There is more to do here; in
particular the lack of any mechanical check for this type of mistake now seems
pretty hazardous. But this fix gets us back to the level of robustness we had
in v13, anyway. Discussion:

- Doc: update v14 release notes for revert of commit c0cb87fbb.

- Remove memory leaks in isolationtester. specscanner.l leaked a kilobyte of
memory per token of the spec file. Apparently somebody thought that the
introductory code block would be executed once; but it's once per yylex()
call. A couple of functions in isolationtester.c leaked small amounts of
memory due to not bothering to free one-time allocations. Might as well
improve these so that valgrind gives this program a clean bill of health.
Also get rid of an ugly static variable. Coverity complained about one of the
one-time leaks, which led me to try valgrind'ing isolationtester, which led to
discovery of the larger leak.

Michaël Paquier pushed:

- Remove forced toast recompression in VACUUM FULL/CLUSTER. The extra checks
added by the recompression of toast data introduced in bbe0a81 is proving to
have a performance impact on VACUUM or CLUSTER even if no recompression is
done. This is more noticeable with more toastable columns that contain
non-NULL values. Improvements could be done to make those extra checks less
expensive, but that's not material for 14 at this stage, and we are not sure
either if the code path of VACUUM FULL/CLUSTER is adapted for this job. Per
discussion with several people, including Andres Freund, Robert Haas, Álvaro
Herrera, Tom Lane and myself. Discussion:

- Improve handling of dropped objects in pg_event_trigger_ddl_commands(). An
object found as dropped when digging into the list of objects returned by
pg_event_trigger_ddl_commands() could cause a cache lookup error, as the calls
grabbing for the object address and the type name would fail if the object was
missing. Those lookup errors could be seen with combinations of ALTER TABLE
sub-commands involving identity columns. The lookup logic is changed in this
code path to get a behavior similar to any other SQL-callable function by
ignoring objects that are not found, taking advantage of 2a10fdc. The
back-branches are not changed, as they require this commit that is too
invasive for stable branches. While on it, add test cases to exercise event
triggers with identity columns, and stress more cases with the event
ddl_command_end for relations. Author: Sven Klemm, Aleksander Alekseev,
Michael Paquier Discussion:

- doc: Apply markup <productname> to OpenSSL more consistently. Author: Daniel
Gustafsson Discussion:

- Fix pattern matching logic for logs in TAP tests of pgbench. The logic
checking for the format of per-thread logs used grep() with directly "$re",
which would cause the test to consider all the logs as a match without caring
about their format at all. Using "/$re/" makes grep() perform a regex test,
which is what we want here. While on it, improve some of the tests to be more
picky with the patterns expected and add more comments to describe the tests.
Issue discovered while digging into a separate patch. Author: Fabien Coelho,
Michael Paquier Discussion:
Backpatch-through: 11

- doc: Move remove_temp_files_after_crash to section for developer options. The
main goal of this option is to allow inspecting temporary files for debugging
purposes, so moving the parameter there is natural. Oversight in cd91de0.
Reported-by: Justin Pryzby Author: Euler Taveira Discussion:

- Add more debugging information with log checks in TAP tests of pgbench.
fairywren is not happy with the pattern checks introduced by c13585f. I am not
sure if this outlines a bug in pgbench or if the regex patterns used in the
tests are too restrictive for this buildfarm member's environment. This adds
more debugging information to show the log entries that do not match with the
expected pattern, to help in finding out what's happening. That seems like a
good addition in the long-term anyway as that may not be the only issue in
this area. Discussion:

- doc: Add acronyms for MITM and SNI. This adds MITM and SNI as acronyms, as the
documentation already had them marked up with <acronym>. While on it, make
sure to spell man-in-the-middle with dashes consistently, and add acronyms for
those new terms where appropriate. Author: Daniel Gustafsson Reviewed-by:
Michael Paquier Discussion:

- Cleanup some code related to pgbench log checks in TAP tests. This fixes a
couple of problems within the so-said code of this commit subject: - Replace
the use of open() with slurp_file(), fixing an issue reported by buildfarm
member fairywren whose perl installation keep around CRLF characters, causing
the matching patterns for the logs to fail. - Remove the eval block, which is
not really necessary. This set of issues has come into light after fixing a
different issue with c13585fe, and this is wrong since this code has been
introduced. Reported-by: Andrew Dunstan, and buildfarm member fairywren
Author: Michael Paquier Reviewed-by: Andrew Dunstan Discussion:
Backpatch-through: 11

- Remove some useless logs from the TAP tests of pgbench. 002_pgbench_no_server
was printing some array pointers instead of the actual contents of those
arrays for the expected outputs of stdout and stderr for a tested command.
This does not add any new information that can help with debugging as the test
names allow to track failure locations, if any. This commit simply removes
those logs as the rest of the printed information is redundant with
command_checks_all(). Per discussion with Andrew Dunstan and Álvaro Herrera.
Backpatch-through: 11

- Remove non-existing variable reference in MSVC's The version
string is grabbed from PACKAGE_VERSION in pg_config.h in the MSVC build since
8f4fb4c6, but an error message referenced a variable that existed before that.
This had no consequences except if one messes up enough with the version
number of the build. Author: Anton Voloshin Discussion:
Backpatch-through: 13

Bruce Momjian pushed:

- doc: add PG 14 relnote item about array function references. User-defined
objects that reference some built-in array functions will need to be recreated
in PG 14. Reported-by: Justin Pryzby Discussion:

- doc: PG 14 relnote updates. Reported-by: Justin Pryzby Discussion:

- doc: PG 14 relnotes fixes. Items related to logical replication attribution
and BRIN indexes Reported-by: Tomas Vondra, John Naylor Discussion:

- doc: add mention of +4GB windows file handling in PG14 relnotes. Reported-by:
Masahiko Sawada Discussion:

- doc: adjust PG 14 relnotes to be current.

Álvaro Herrera pushed:

- Fix logic bug in 1632ea43682f. I overlooked that one condition was logically
inverted. The fix is a little bit more involved than simply negating the
condition, to make the code easier to read. Fix some outdated comments left
by the same commit, while at it. Author: Masahiko Sawada
<sawada(dot)mshk(at)gmail(dot)com> Author: Álvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org>
Reviewed-by: Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> Discussion:

- Add test case for obsoleting slot with active walsender. The code to signal a
running walsender when its reserved WAL size grows too large is completely
uncovered before this commit; this adds coverage for that case. This test
involves sending SIGSTOP to walsender and walreceiver and running a checkpoint
while advancing WAL, then sending SIGCONT. There's no precedent for this
coding in Perl tests, and my reading of relevant manpages says it's likely to
fail on Windows. Because of this, this test is always skipped on that
platform. Author: Álvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> Discussion:

- Revert "Add test case for obsoleting slot with active walsender". This reverts
commit 09126984a263; the test case added there failed once in circumstances
that remain mysterious. It seems better to remove the test for now so that
14beta2 doesn't have random failures built in.

- Add test case for obsoleting slot with active walsender, take 2. The code to
signal a running walsender when its reserved WAL size grows too large is
completely uncovered before this commit; this adds coverage for that case.
This test involves sending SIGSTOP to walsender and walreceiver, then
advancing enough WAL for a checkpoint to trigger, then sending SIGCONT.
There's no precedent for STOP signalling in Perl tests, and my reading of
relevant manpages says it's likely to fail on Windows. Because of this, this
test is always skipped on that platform. This version fixes a couple of
rarely hit race conditions in the previous attempt 09126984a263; most notably,
both LOG string searches are loops, not just the second one; we acquire the
start-of-log position before STOP-signalling; and reference the correct
process name in the test description. All per Tom Lane. Author: Álvaro
Herrera <alvherre(at)alvh(dot)no-ip(dot)org> Discussion:

Noah Misch pushed:

- Remove pg_wait_for_backend_termination(). It was unable to wait on a backend
that had already left the procarray. Users tolerant of that limitation can
poll pg_stat_activity. Other users can employ the "timeout" argument of
pg_terminate_backend(). Reviewed by Bharath Rupireddy. Discussion:

- Copy-edit text for the pg_terminate_backend() "timeout" parameter. Revert the
pg_description entry to its v13 form, since those messages usually remain
shorter and don't discuss individual parameters. No catversion bump, since
pg_description content does not impair backend compatibility or application
compatibility. Justin Pryzby Discussion:

- Finish rename of PQtraceSetFlags() to PQsetTraceFlags(). Jie Zhang

Amit Kapila pushed:

- Fix decoding of speculative aborts. During decoding for speculative inserts,
we were relying for cleaning toast hash on confirmation records or next change
records. But that could lead to multiple problems (a) memory leak if there is
neither a confirmation record nor any other record after toast insertion for a
speculative insert in the transaction, (b) error and assertion failures if the
next operation is not an insert/update on the same table. The fix is to start
queuing spec abort change and clean up toast hash and change record during its
processing. Currently, we are queuing the spec aborts for both toast and main
table even though we perform cleanup while processing the main table's spec
abort record. Later, if we have a way to distinguish between the spec abort
record of toast and the main table, we can avoid queuing the change for spec
aborts of toast tables. Reported-by: Ashutosh Bapat Author: Dilip Kumar
Reviewed-by: Amit Kapila Backpatch-through: 9.6, where it was introduced

- Document a few caveats in synchronous logical replication. In a synchronous
logical setup, locking [user] catalog tables can cause deadlock. This is
because logical decoding of transactions can lock catalog tables to access
them so exclusively locking those in transactions can lead to deadlock. To
avoid this users must refrain from having exclusive locks on catalog tables.
Author: Takamichi Osumi Reviewed-by: Vignesh C, Amit Kapila Backpatch-through:
9.6 Discussion:

- Handle no replica identity index case in RelationGetIdentityKeyBitmap. Commit
e7eea52b2d has introduced a new function RelationGetIdentityKeyBitmap which
omits to handle the case where there is no replica identity index on a
relation. Author: Mark Dilger Reviewed-by: Takamichi Osumi, Amit Kapila

- Doc: Update caveats in synchronous logical replication. Reported-by: Simon
Riggs Author: Takamichi Osumi Reviewed-by: Amit Kapila Backpatch-through: 9.6

- Doc: Update logical replication message formats. Commits 9de77b5453 and
ac4645c015 missed to update the logical replication message formats section in
the docs. Author: Brar Piening Reviewed-by: Amit Kapila Discussion:

- Doc: Fix minor formatting issue in logicaldecoding.sgml. Author: Guillaume
Lelarge Discussion:

Alexander Korotkov pushed:

- Support for unnest(multirange) and cast multirange as an array of ranges. It
has been spotted that multiranges lack of ability to decompose them into
individual ranges. Subscription and proper expanded object representation
require substantial work, and it's too late for v14. This commit provides the
implementation of unnest(multirange) and cast multirange as an array of
ranges, which is quite trivial. unnest(multirange) is defined as a
polymorphic procedure. The catalog description of the cast underlying
procedure is duplicated for each multirange type because we don't have
anyrangearray polymorphic type to use here. Catversion is bumped.
Reported-by: Jonathan S. Katz Discussion:
Author: Alexander Korotkov Reviewed-by: Justin Pryzby, Jonathan S. Katz,
Zhihong Yu

- Add missing type name "multirange" in docs chapter title. Discussion:
Author: Pavel Stehule, Alexander Korotkov Reviewed-by: Justin Pryzby, Tom Lane

- Revert 29854ee8d1 due to buildfarm failures. Reported-by: Tom Lane Discussion:

Peter Geoghegan pushed:

- Remove unneeded field from VACUUM state. Bugfix commit 5fc89376 effectively
made the lock_waiter_detected field from vacuumlazy.c's global state struct
into private state owned by lazy_truncate_heap(). Finish this off by
replacing the struct field with a local variable.

- Support disabling index bypassing by VACUUM. Generalize the INDEX_CLEANUP
VACUUM parameter (and the corresponding reloption): make it into a ternary
style boolean parameter. It now exposes a third option, "auto". The "auto"
option (which is now the default) enables the "bypass index vacuuming"
optimization added by commit 1e55e7d1. "VACUUM (INDEX_CLEANUP TRUE)" is
redefined to once again make VACUUM simply do any required index vacuuming,
regardless of how few dead tuples are encountered during the first scan of the
target heap relation (unless there are exactly zero). This gives users a way
of opting out of the "bypass index vacuuming" optimization, if for whatever
reason that proves necessary. It is also expected to be used by PostgreSQL
developers as a testing option from time to time. "VACUUM (INDEX_CLEANUP
FALSE)" does the same thing as it always has: it forcibly disables both index
vacuuming and index cleanup. It's not expected to be used much in PostgreSQL
14. The failsafe mechanism added by commit 1e55e7d1 addresses the same
problem in a simpler way. INDEX_CLEANUP can now be thought of as a testing and
compatibility option. Author: Peter Geoghegan <pg(at)bowt(dot)ie> Reviewed-By:
Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> Reviewed-By: Justin Pryzby
<pryzby(at)telsasoft(dot)com> Discussion:

- Remove overzealous VACUUM failsafe assertions. The failsafe can trigger when
index processing is already disabled. This can happen when VACUUM's
INDEX_CLEANUP parameter is "off" and the failsafe happens to trigger. Remove
assertions that assume that index processing is directly tied to the failsafe.
Oversight in commit c242baa4, which made it possible for the failsafe to
trigger in a two-pass strategy VACUUM that has yet to make its first call to

- Add list of ignorable pgindent commits for git-blame. Add a
.git-blame-ignore-revs file with a list of pgindent, pgperlyidy, and
reformat-dat-files commit hashes. Postgres hackers that configure git to use
the ignore file will get git-blame output that avoids attributing line changes
to the ignored indent commits. This makes git-blame output much easier to
work with in practice. Author: Peter Geoghegan <pg(at)bowt(dot)ie> Reviewed-By: Tom
Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> Discussion:

Andrew Dunstan pushed:

- Further refinement of stuck_on_old_timeline recovery test. TestLib::perl2host
can take a file argument as well as a directory argument, so that code becomes
substantially simpler. Also add comments on why we're using forward slashes,
and why we're setting PERL_BADLANG=0. Discussion:

- Don't set a fast default for anything but a plain table. The fast default code
added in Release 11 omitted to check that the table a fast default was being
added to was a plain table. Thus one could be added to a foreign table, which
predicably blows up. Here we perform that check. In addition, on the back
branches, since some of these might have escaped into the wild, if we
encounter a missing value for an attribute of something other than a plain
table we ignore it. Fixes bug #17056 Backpatch to release 11, Reviewed by:
Andres Freund, Álvaro Herrera and Tom Lane

Heikki Linnakangas pushed:

- Fix outdated comment that talked about seek position of WAL file. Since commit
c24dcd0cfd, we have been using pg_pread() to read the WAL file, which doesn't
change the seek position (unless we fall back to the implementation in
src/port/pread.c). Update comment accordingly. Backpatch-through: 12, where
we started to use pg_pread()

- Tidy up GetMultiXactIdMembers()'s behavior on error. One of the error paths
left `*members` uninitialized. That's not a live bug, because most callers don't
look at `*members` when the function returns -1, but let's be tidy. One caller,
in heap_lock_tuple(), does "if (members != NULL) pfree(members)", but AFAICS
it never passes an invalid 'multi' value so it should not reach that error
case. The callers are also a bit inconsistent in their expectations.
heap_lock_tuple() pfrees the 'members' array if it's not-NULL, others pfree()
it if "nmembers >= 0", and others if "nmembers > 0". That's not a live bug
either, because the function should never return 0, but add an Assert for that
to make it more clear. I left the callers alone for now. I also moved the
line where we set `*nmembers`. It wasn't wrong before, but I like to do that
right next to the 'return' statement, to make it clear that it's always set on
return. Also remove one unreachable return statement after ereport(ERROR),
for brevity and for consistency with the similar if-block right after it.
Author: Greg Nancarrow with the additional changes by me Backpatch-through:
9.6, all supported versions

- Prevent race condition while reading relmapper file. Contrary to the comment
here, POSIX does not guarantee atomicity of a read(), if another process calls
write() concurrently. Or at least Linux does not. Add locking to
load_relmap_file() to avoid the race condition. Fixes bug #17064. Thanks to
Alexander Lakhin for the report and test case. Backpatch-through: 9.6, all
supported versions. Discussion:

- Another fix to relmapper race condition. In previous commit, I missed that
relmap_redo() was also not acquiring the RelationMappingLock. Thanks to Thomas
Munro for pointing that out. Backpatch-through: 9.6, like previous commit.

Tomáš Vondra pushed:

- Fix copying data into slots with FDW batching. Commit b676ac443b optimized
handling of tuple slots with bulk inserts into foreign tables, so that the
slots are initialized only once and reused for all batches. The data was
however copied into the slots only after the initialization, inserting
duplicate values when the slot gets reused. Fixed by moving the ExecCopySlot
outside the init branch. The existing postgres_fdw tests failed to catch this
due to inserting data into foreign tables without unique indexes, and then
checking only the number of inserted rows. This adds a new test with both a
unique index and a check of inserted values. Reported-by: Alexander Pyhalov

Fujii Masao pushed:

- Make archiver process handle barrier events. Commit d75288fb27 made WAL
archiver process an auxiliary process. An auxiliary process needs to handle
barrier events but the commit forgot to make archiver process do that.
Reported-by: Thomas Munro Author: Fujii Masao Reviewed-by: Thomas Munro

Peter Eisentraut pushed:

- amcheck: Fix code comments. Code comments were claiming that verify_heapam()
was checking privileges on the relation it was operating on, but it didn't
actually do that. Perhaps earlier versions of the patch did that, but now the
access is regulated by privileges on the function. Remove the wrong comments.

- Translation updates. Source-Git-URL:
git:// Source-Git-Hash:

- Remove redundant variable pageSize in gistinitpage. In gistinitpage, pageSize
variable looks redundant, instead just pass BLCKSZ. This will be consistent
with its peers BloomInitPage, brin_page_init and SpGistInitPage. Author:
Bharath Rupireddy <bharath(dot)rupireddy(at)enterprisedb(dot)com> Discussion:

- doc: Change reloption data type spelling for consistency. Use "floating point"
rather than "float4", like everywhere else in this context. Author:
Shinya11(dot)Kato(at)nttdata(dot)com Discussion:

PUBLICATION does not actually support copy_data option, so remove it from tab
completion. Also, reword the error message that is thrown when all the
publications from a subscription are specified to be dropped. Also, made few
doc and cosmetic adjustments. Author: Vignesh C <vignesh21(at)gmail(dot)com>
Reviewed-by: Bharath Rupireddy <bharath(dot)rupireddy(at)enterprisedb(dot)com>
Reviewed-by: Japin Li <japinli(at)hotmail(dot)com> Discussion:

- Put option listing back into alphabetical order.

- Error message refactoring. Take some untranslatable things out of the message
and replace by format placeholders, to reduce translatable strings and reduce
translation mistakes.

David Rowley pushed:

- Fix assert failure in expand_grouping_sets. linitial_node() fails in assert
enabled builds if the given pointer is not of the specified type. Here the
type is IntList. The code thought it should be expecting List, but it was
wrong. In the existing tests which run this code the initial list element is
always NIL. Since linitial_node() allows NULL, we didn't trigger any assert
failures in the existing regression tests. There is still some discussion as
to whether we need a few more tests in this area, but for now, since beta2 is
looming, fix the bug first. Bug: #17067 Discussion:
Reported-by: Yaoguang Chen

Andres Freund pushed:

- Use correct horizon when vacuuming catalog relations. In dc7420c2c92 I
(Andres) accidentally used RelationIsAccessibleInLogicalDecoding() as the sole
condition to use the non-shared catalog horizon in
GetOldestNonRemovableTransactionId(). That is incorrect, as
RelationIsAccessibleInLogicalDecoding() checks whether wal_level is logical.
The correct check, as done e.g. in GlobalVisTestFor(), is to check
IsCatalogRelation() and RelationIsAccessibleInLogicalDecoding(). The observed
misbehavior of this bug was that there could be an endless loop in
lazy_scan_prune(), because the horizons used in heap_page_prune() and the
individual tuple liveliness checks did not match. Likely there are other
potential consequences as well. A later commit will unify the determination
which horizon has to be used, and add additional assertions to make it easier
to catch a bug like this. Reported-By: Justin Pryzby <pryzby(at)telsasoft(dot)com>
Diagnosed-By: Matthias van de Meent <boekewurm+postgres(at)gmail(dot)com> Author:
Matthias van de Meent <boekewurm+postgres(at)gmail(dot)com> Discussion:

Joe Conway pushed:

- Stamp 14beta2.

Thomas Munro pushed:

- Prepare for forthcoming LLVM 13 API change. LLVM 13 (due out in September) has
changed the semantics of LLVMOrcAbsoluteSymbols(), so we need to bump some
reference counts to avoid a double-free that causes crashes and bad query
results. A proactive change seems necessary to avoid having a window of time
where our respective latest releases would interact badly. It's possible that
the situation could change before then, though. Thanks to Fabien Coelho for
monitoring bleeding edge LLVM and Andres Freund for tracking down the change.
Back-patch to 11, where the JIT code arrived. Discussion:

# Pending Patches

Nitin Jadhav sent in another revision of a patch to make a way to track the
progress of the operations performed during the startup process.

Yuzuko Hosoya sent in a patch to control whether autoanalyze runs for partition
operations via new GUCs autovacuum_analyze_attach_partition,
autovacuum_analyze_detach_partition, and autovacuum_analyze_drop_partition.

Peter Eisentraut sent in a patch to add an index OID macro argument to
DECLARE_INDEX. Instead of defining symbols such as AmOidIndexId explicitly,
include them as an argument of DECLARE_INDEX() and have generate the
way as the table OID symbols from the CATALOG() declaration

Filip Gospodinov sent in a patch to intended to fix a bug that manifested as the
shipped pkg-config file is broken for statically linking libpq because
libpgcommon and libpgport are missing. The fix adds those two missing private

Heikki Linnakangas sent in another revision of a patch to split xlog.c into
xlog.c and xlogrecovery.c. This moves the functions related to performing WAL
recovery into the new xlogrecovery.c source file, leaving xlog.c responsible for
maintaining the WAL buffers, coordinating the startup and switch from recovery
to normal operations, and other miscellaneous stuff that have always been in

Greg Nancarrow sent in two revisions of a patch to remove the useless int64
range checks on BIGINT sequence MINVALUE/MAXVALUE values.

Peter Geoghegan sent in three revisions of a patch to add a list of ignorable
pgindent commits for "git blame".

David Rowley sent in another revision of a patch to add a new hash table type
which has stable pointers, and use same to speed up SMgr.

Peter Smith and Ajin Cherian traded patches to add an option to support prepared
transactions to built-in logical replication.

Daniel Gustafsson and Michaël Paquier traded patches to document some SSL/TLS
related acronyms, and replace usage of SSL with SSL/TLS, the latter being more
accurate and up to date.

Simon Riggs sent in three revisions of a patch to add a documentation chapter on
hash indexes.

Atsushi Torikoshi sent in another revision of a patch to add a function to log
the complete query string and its plan for the query currently running on the
backend with the specified process ID.

Li Japin sent in a patch to make a minor code beautification in

Bertrand Drouvot sent in another revision of a patch to enable minimal logical
decoding on standbys.

Alexander Pyhalov sent in another revision of a patch to allow pushing CASE
expressions to foreign servers.

Peter Eisentraut sent in a patch to Make the Unicode makefile more
parallel-safe, Make process encodings in sorted order, remove
some whitespace in generated C output to get it in line with the coding style of
the rest of the project, simplify the code-generation code, and fix indentation
in generated output.

Maxim Orlov sent in another revision of a patch to fix parallel worker failed
assertion and coredump.

Vigneshwaran C sent in two more revisions of a patch to add schema level support

Mike Fiedler sent in a patch to emit the namespace in post-copy output.

Emre Hasegeli sent in a patch to decouple operator classes from index access

Jacob Champion sent in two revisions of a patch to decouple the SASL framework
from the SCRAM code, as it may be useful separately.

Yugo Nagata sent in another revision of a patch to address some Pgbench errors
by using the Variables structure for client variables. This is most important
when it is used to reset client variables during the repeating of transactions
after serialization/deadlock failures. Don't allocate Variable structs one by
one. Instead, add a constant margin each time it overflows. Also included, a fix
for pgbench errors and serialization/deadlock retries.

Jacob Champion, Michaël Paquier, and Tom Lane traded patches to make it possible
to use jsonapi with libpq.

Gurjeet Singh sent in a patch to add automatic notification for top transaction

Haiying Tang sent in another revision of a patch to support tab completion with
a query result for upper case inputs in psql.

Peter Eisentraut sent in a patch to make genbki error handling more useful by
counting the errors and error out at the end instead of just writing warnings
for invalid cross-catalog lookups.

Daniel Gustafsson sent in another revision of a patch to support NSS as a TLS
backend for libpq.

Andrey V. Lepikhov sent in a patch to inform genericcostestimate()'s index
selectivity value of the fact that in the case of an unique one-row btree
index, scan only one row can be returned.

Tomáš Vondra sent in another revision of a patch to make it possible to
replicate sequences in logical decoding.

Ranier Vilela sent in a patch to fix an uninitialized copy_data var in

Peter Eisentraut sent in another revision of a patch to improve error messages
about mismatching relkind by making the primary error message shorter and saying
more directly that what was attempted is not possible.

Peter Eisentraut sent in a patch to add tests for UNBOUNDED syntax ambiguity.

Simon Riggs sent in a patch to make pgbench use COPY FREEZE.

Justin Pryzby sent in a patch to avoid double parentheses, and fix a comment in
tablefunc.c to refer to the correct comment.

Craig Ringer sent in a patch to add some more PGDLLIMPORTs to expose some of the
things heretofore unavailable on Windows.

Amit Kapila sent in a patch to allow streaming the changes after speculative

Hayato Kuroda sent in a patch to make ECPG's new DECLARE STATEMENT work for

Bruce Momjian sent in another revision of a patch to implement cluster file

Jie Zhang sent in a patch to bring the generated sample postgresql.conf into
line with what's actually the default for `shared_buffers`.

Richard Guo sent in a patch to use each rel as both outer and inner for anti

Tom Lane sent in a patch to prevent any calls of abort() or exit() from inside

Alexander Korotkov sent in a patch to fix some small inconsistencies in the
catalog definition of multirange operators.

Julien Rouhaud sent in another revision of a patch to preserve param location
when generating a const node.

Andrey Borodin sent in another revision of a patch to reorganize the pglz
compression code.

Julien Rouhaud and Ranier Vilela traded patches to add a pg_get_query_def() to
deparse and print a rewritten SQL statement.

Tomáš Vondra sent in a PoC patch to do cardinality estimation using runtime

Browse pgsql-announce by date

  From Date Subject
Next Message PWN via PostgreSQL Announce 2021-07-05 09:35:30 PostgreSQL Weekly News - July 4, 2021
Previous Message Gilles Darold via PostgreSQL Announce 2021-06-28 00:10:47 pg_dumpbinary v2.4 released