PostgreSQL Weekly News - November 21, 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 - November 21, 2021
Date: 2021-11-23 08:49:07
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-announce

# PostgreSQL Weekly News - November 21, 2021

Nordic PGDay 2022 will be held in Helsinki, Finland at the Hilton Helsinki
Strand Hotel on March 22, 2022. The CfP is open through December 31, 2021

# PostgreSQL Product News

PGroonga 2.3.4 a full text search platform for all languages,

Pgpool-II 4.2.6, 4.1.9, 4.0.16, 3.7.21 and 3.6.28, a connection pooler and statement replication system for

Ora2Pg 23.0, a tool for migrating Oracle databases to PostgreSQL, released.

BigAnimal, a managed PostgreSQL database on Azure,

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

# PostgreSQL Jobs for November


# 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

Robert Haas pushed:

- Fix thinko in bbsink_throttle_manifest_contents. Report and diagnosis by
Dmitry Dolgov. Discussion:

- Move InitXLogInsert() call from InitXLOGAccess() to BaseInit(). At present,
there is an undocumented coding rule that you must call RecoveryInProgress(),
or do something else that results in a call to InitXLogInsert(), before trying
to write WAL. Otherwise, the WAL construction buffers won't be initialized,
resulting in failures. Since it's not good to rely on a status inquiry
function like RecoveryInProgress() having the side effect of initializing
critical data structures, instead do the initialization eariler, when the
backend first starts up. Patch by me. Reviewed by Nathan Bossart and Michael
Paquier. Discussion:

Amit Kapila pushed:

- Invalidate relcache when changing REPLICA IDENTITY index. When changing
REPLICA IDENTITY INDEX to another one, the target table's relcache was not
being invalidated. This leads to skipping update/delete operations during
apply on the subscriber side as the columns required to search corresponding
rows won't get logged. Author: Tang Haiying, Hou Zhijie Reviewed-by: Euler
Taveira, Amit Kapila Backpatch-through: 10 Discussion:

- Fix parallel operations that prevent oldest xmin from advancing. While
determining xid horizons, we skip over backends that are running Vacuum. We
also ignore Create Index Concurrently, or Reindex Concurrently for the
purposes of computing Xmin for Vacuum. But we were not setting the flags
corresponding to these operations when they are performed in parallel which
was preventing Xid horizon from advancing. The optimization related to
skipping Create Index Concurrently, or Reindex Concurrently operations was
implemented in PG-14 but the fix is the same for the Parallel Vacuum as well
so back-patched till PG-13. Author: Masahiko Sawada Reviewed-by: Amit Kapila
Backpatch-through: 13 Discussion:

Álvaro Herrera pushed:

- Fix headerscheck failure in replication/worker_internal.h. Broken by

Michaël Paquier pushed:

- Remove global variable "LastRec" in xlog.c. This variable is used only by
StartupXLOG() now, so let's make it local to simplify the code. Author: Amul
Sul Reviewed-by: Tom Lane, Michael Paquier Discussion:

- Add table to regression tests for binary-compatibility checks in pg_upgrade.
This commit adds to the main regression test suite a table with all the
in-core data types (some exceptions apply). This table is not dropped, so as
pg_upgrade would be able to check the binary compatibility of the types
tracked in the table. If a new type is added in core, this part of the tests
would need a refresh but the tests are designed to fail if that were to
happen. As this is useful for upgrades and that these rely on the objects
created in the regression test suite of the old version upgraded from, a
backpatch down to 12 is done, which is the last point where a binary
incompatible change has been done (7c15cef). This will hopefully be enough to
find out if something gets broken during the development of a new version of
Postgres, so as it is possible to take actions in pg_upgrade itself in this
case (like 0ccfc28 for sql_identifier). An area that is not covered yet is
related to external modules, which may create their own types. The testing
infrastructure of pg_upgrade is not integrated yet with the external modules
stored in core (src/test/modules/ or contrib/, all use the same database name
for their tests so there would be an overlap). This could be improved in the
future. Author: Justin Pryzby Reviewed-by: Jacob Champion, Peter Eisentraut,
Tom Lane, Michael Paquier Discussion:
Backpatch-through: 12

- Fix quoting of ACL item in table for upgrade binary compatibility checks. Per
buildfarm member prion, that runs the regression tests under a role name that
uses a hyphen. Issue introduced by 835bcba. Discussion:
Backpatch-through: 12

- Improve psql tab completion for transforms, domains and sequences. The
following improvements are done: - Addition of some tab completion for CREATE
DOMAIN. - Addition of some tab completion for CREATE TRANSFORM. - Addition of
type completion for CREATE SEQUENCE AS. Author: Ken Kato Reviewed-by: Kyotaro
Horiguchi, Michael Paquier Discussion:

Peter Eisentraut pushed:

- Fix incorrect format placeholders.

Daniel Gustafsson pushed:

- Doc: add see-also references to CREATE PUBLICATION. The "See also" section on
the reference page for CREATE PUBLICATION didn't match the cross references on
CREATE SUBSCRIPTION and their ALTER counterparts. Fixed by adding an xref to
the CREATE and ALTER SUBSCRIPTION pages. Backpatch down to v10 where CREATE
PUBLICATION was introduced. Author: Peter Smith <smithpb2250(at)gmail(dot)com>
Reviewed-by: Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> Discussion:
Backpatch-through: 10

- Improve publication error messages. Commit 81d5995b4b introduced more
fine-grained errormessages for incorrect relkinds for publication, while
unlogged and temporary tables were reported with using the same message. This
provides separate error messages for these types of relpersistence. Author:
Bharath Rupireddy <bharath(dot)rupireddyforpostgres(at)gmail(dot)com> Reviewed-by: Peter
Eisentraut <peter(dot)eisentraut(at)enterprisedb(dot)com> Reviewed-by: Jeevan Ladhe
<jeevan(dot)ladhe(at)enterprisedb(dot)com> Reviewed-by: Euler Taveira <euler(at)eulerto(dot)com>

Tom Lane pushed:

- Fix display of SQL-standard function's arguments in INSERT/SELECT. If a
SQL-standard function body contains an INSERT ... SELECT statement, any
function parameters referenced within the SELECT were always printed in $N
style, rather than using the parameter name if any. While not strictly
incorrect, this wasn't the intention, and it's inconsistent with the way that
such parameters would be printed in any other kind of statement. The cause is
that the recursion to get_query_def from get_insert_query_def neglected to
pass down the context->namespaces list, passing constant NIL instead. This is
a very ancient oversight, but AFAICT it had no visible consequences before
commit e717a9a18 added an outermost namespace with function parameters. We
don't allow INSERT ... SELECT as a sub-query, except in a top-level WITH
clause, where it couldn't contain any outer references that might need to
access upper namespaces. So although that's arguably a bug, I don't see any
point in changing it before v14. In passing, harden the code added to
get_parameter by e717a9a18 so that it won't crash if a PARAM_EXTERN Param
appears in an unexpected place. Per report from Erki Eessaar. Code fix by
me, regression test case by Masahiko Sawada. Discussion:

- Handle close() failures more robustly in pg_dump and pg_basebackup. Coverity
complained that applying get_gz_error after a failed gzclose, as we did in one
place in pg_basebackup, is unsafe. I think it's right: it's entirely likely
that the call is touching freed memory. Change that to inspect errno, as we do
for other gzclose calls. Also, be careful to initialize errno to zero
immediately before any gzclose() call where we care about the error status.
(There are some calls where we don't, because we already failed at some
previous step.) This ensures that we don't get a misleadingly irrelevant
error code if gzclose() fails in a way that doesn't set errno. We could work
harder at that, but it looks to me like all such cases are basically
can't-happen if we're not misusing zlib, so it's not worth the extra
notational cruft that would be required. Also, fix several places that simply
failed to check for close-time errors at all, mostly at some remove from the
close or gzclose itself; and one place that did check but didn't bother to
report the errno. Back-patch to v12. These mistakes are older than that, but
between the frontend logging API changes that happened in v12 and the fact
that frontend code can't rely on %m before that, the patch would need
substantial revision to work in older branches. It doesn't quite seem worth
the trouble given the lack of related field complaints. Patch by me; thanks
to Michael Paquier for review. Discussion:

- Clean up error handling in pg_basebackup's walmethods.c. The error handling
here was a mess, as a result of a fundamentally bad design (relying on errno
to keep its value much longer than is safe to assume) as well as a lot of just
plain sloppiness, both as to noticing errors at all and as to reporting the
correct errno. Moreover, the recent addition of LZ4 compression broke things
completely, because liblz4 doesn't use errno to report errors. To improve
matters, keep the error state in the DirectoryMethodData or TarMethodData
struct, and add a string field so we can handle cases that don't set errno.
(The tar methods already had a version of this, but it can be done more
efficiently since all these cases use a constant error string.) Make the dir
and tar methods handle errors in basically identical ways, which they didn't
before. This requires copying errno into the state struct in a lot of places,
which is a bit tedious, but it has the virtue that we can get rid of ad-hoc
code to save and restore errno in a number of places ... not to mention that
it fixes other places that should've saved/restored errno but neglected to.
In passing, fix some pointlessly static buffers to be ordinary local
variables. There remains an issue about exactly how to handle errors from
fsync(), but that seems like material for its own patch. While the LZ4
problems are new, all the rest of this is fixes for old bugs, so backpatch to
v10 where walmethods.c was introduced. Patch by me; thanks to Michael Paquier
for review. Discussion:

- Add a planner support function for starts_with(). This fills in some gaps in
planner support for starts_with() and the equivalent ^@ operator: * A
condition such as "textcol ^@ constant" can now use a regular btree index, not
only an SP-GiST index, so long as the index's collation is C. (This works
just like "textcol LIKE 'foo%'".) * "starts_with(textcol, constant)" can be
optimized the same as "textcol ^@ constant". * Fixed-prefix LIKE and regex
patterns are now more like starts_with() in another way: if you apply one to
an SPGiST-indexed column, you'll get an index condition using ^@ rather than
two index conditions with >= and <. Per a complaint from Shay Rojansky.
Patch by me; thanks to Nathan Bossart for review. Discussion:

- Provide a variant of simple_prompt() that can be interrupted by ^C. Up to now,
you couldn't escape out of psql's \password command by typing control-C (or
other local spelling of SIGINT). This is pretty user-unfriendly, so improve
it. To do so, we have to modify the functions provided by pg_get_line.c; but
we don't want to mess with psql's SIGINT handler setup, so provide an API that
lets that handler cause the cancel to occur. This relies on the assumption
that we won't do any major harm by longjmp'ing out of fgets(). While that's
obviously a little shaky, we've long had the same assumption in the main input
loop, and few issues have been reported. psql has some other simple_prompt()
calls that could usefully be improved the same way; for now, just deal with
\password. Nathan Bossart, minor tweaks by me Discussion:

- Use appropriate -Wno-warning switches when compiling bitcode. We use "clang"
to compile bitcode files for LLVM inlining. That might be different from the
build's main C compiler, so it needs its own set of compiler flags. To
simplify configure, we don't bother adding any -W switches to that flag set;
there's little need since the main build will show us any warnings. However,
if we don't want to see unwanted warnings, we still have to add any
-Wno-warning switches we'd normally use with clang. This escaped notice
before commit 9ff47ea41, which tried to add
-Wno-compound-token-split-by-macro; buildfarm animals using mismatched CC and
CLANG still showed those warnings. I'm not sure why we never saw any effects
from the lack of -Wno-unused-command-line-argument (maybe that's only
activated by -Wall?). clang does not currently support -Wno-format-truncation
or -Wno-stringop-truncation, although in the interests of future-proofing and
consistency I included tests for those. Back-patch to v11 where we started
building bitcode files. Discussion:

- Allow psql's other uses of simple_prompt() to be interrupted by ^C. This fills
in the work left un-done by 5f1148224. \prompt can be canceled out of now,
and so can password prompts issued during \connect. (We don't need to do
anything for password prompts issued during startup, because we aren't yet
trapping SIGINT at that point.) Nathan Bossart Discussion:

- Fix SP-GiST scan initialization logic for binary-compatible cases. Commit
ac9099fc1 rearranged the logic in spgGetCache() that determines the index's
attType (nominal input data type) and leafType (actual type stored in leaf
index tuples). Turns out this broke things for the case where (a) the actual
input data type is different from the nominal type, (b) the opclass's config
function leaves leafType defaulted, and (c) the opclass has no "compress"
function. (b) caused us to assign the actual input data type as leafType, and
then since that's not attType, we complained that a "compress" function is
required. For non-polymorphic opclasses, condition (a) arises in
binary-compatible cases, such as using SP-GiST text_ops for a varchar column,
or using any opclass on a domain over its nominal input type. To fix, use
attType for leafType when the index's declared column type is different from
but binary-compatible with attType. Do this only in the defaulted-leafType
case, to avoid overriding any explicit selection made by the opclass. Per bug
#17294 from Ilya Anfimov. Back-patch to v14. Discussion:

- Doc: update some things relevant to minimum Test::More version. Oversights in
commit 405f32fc4. Also, add a tip (discovered the hard way) about getting
Test::More 0.98 to pass its regression tests on recent Linux platforms.

- pg_receivewal, pg_recvlogical: allow canceling initial password prompt.
Previously it was impossible to terminate these programs via control-C while
they were prompting for a password. We can fix that trivially for their
initial password prompts, by moving setup of the SIGINT handler from just
before to just after their initial GetConnection() calls. This fix doesn't
permit escaping out of later re-prompts, but those should be exceedingly rare,
since the user's password or the server's authentication setup would have to
have changed meanwhile. We considered applying a fix similar to commit
46d665bc2, but that seemed more complicated than it'd be worth. Moreover,
this way is back-patchable, which that wasn't. The misbehavior exists in all
supported versions, so back-patch to all. Tom Lane and Nathan Bossart

Andres Freund pushed:

- Initialize backend status reporting during bootstrap. This allows a later
commit to reduce the number of branches in performance sensitive functions
during normal running, compared to a very minor saving during bootstrapping.
Author: Melanie Plageman <melanieplageman(at)gmail(dot)com> Reviewed-By: Andres
Freund <andres(at)anarazel(dot)de> Discussion:

Andrew Dunstan pushed:

- Require version 0.98 of Test::More for TAP tests. This means that the subtest
feature will be available for use. We expect that this change will make
prairiedog go red until it is updated, but other buildfarm animals should be
fine. Discussion:

Browse pgsql-announce by date

  From Date Subject
Next Message PWN via PostgreSQL Announce 2021-11-29 09:37:51 PostgreSQL Weekly News - November 28, 2021
Previous Message United States PostgreSQL Association via PostgreSQL Announce 2021-11-18 20:40:57 PGConf NYC 2021 is in just two weeks!