PostgreSQL Weekly News - January 2, 2022

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 - January 2, 2022
Date: 2022-01-05 12:42:27
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-announce

# PostgreSQL Weekly News - January 2, 2022

# PostgreSQL Product News

Pgpool-II 4.2.7, 4.1.10, 4.0.17, and 3.7.22 a connection pooler and statement
replication system for PostgreSQL,

parquet_s3_fdw 0.2.1, a foreign data wrapper for parquet files on S3,

Database Lab 3.0, a tool for fast cloning of large PostgreSQL databases to build
non-production environments,

sqlite_fdw 2.1.1

DynamoDB FDW 1.1.0 [released](

pg_query_rewrite 0.0.3, a rewriter for certain types of PostgreSQL statements,

InfluxDB fdw 1.1.1 released

pgspider v2.0, a cluster engine for distributed data based on PostgreSQL foreign
data wrappers, [released](

pg_builder 2.0.0 a PHP query builder for PostgreSQL,

JDBC FDW 0.1.0 [released](

griddb_fdw 2.1.1 released.

# PostgreSQL Jobs for January


# PostgreSQL Local

Nordic PGDay 2022 will be held in Helsinki, Finland at the Hilton Helsinki
Strand Hotel on March 22, 2022. [here](

[pgDay Paris 2022]( will be held in Paris,
France on March 24, 2022.

FOSDEM PGDay 2022 will be held on line, on Feb 5-6, 2022.

Citus Con, a virtual global developer event, is happening April 12-13, 2022. The
[CFP]( is now open.

# 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

Peter Eisentraut pushed:

- doc: More documentation on regular expressions and SQL standard. Reviewed-by:
Gilles Darold <gilles(at)darold(dot)net> Discussion:

- pg_dump: Refactor getIndexes(). Rearrange the version-dependent pieces in the
new more modular style. Discussion:

- Fix typo in code comment. Reported-by: Kevin Zheng <1642644905(at)qq(dot)com>

- Fix incorrect format placeholders.

- Remove unused include. "fmgr.h" was used for load_external_function(), added
by a05dc4d7fd57d4ae084c1f0801973e5c1a1aa26e, removed by

- Remove unused include. "utils/builtins.h" was used for pg_strtouint64(), added
by cff440d368690f94fbda1a475277e90ea2263843, removed by

- Fix incorrect format placeholders.

John Naylor pushed:

- Add fast path for validating UTF-8 text. Our previous validator used a
traditional algorithm that performed comparison and branching one byte at a
time. It's useful in that we always know exactly how many bytes we have
validated, but that precision comes at a cost. Input validation can show up
prominently in profiles of COPY FROM, and future improvements to COPY FROM
such as parallelism or faster line parsing will put more pressure on input
validation. Hence, add fast paths for both ASCII and multibyte UTF-8: Use
bitwise operations to check 16 bytes at a time for ASCII. If that fails, use a
"shift-based" DFA on those bytes to handle the general case, including
multibyte. These paths are relatively free of branches and thus robust against
all kinds of byte patterns. With these algorithms, UTF-8 validation is several
times faster, depending on platform and the input byte distribution. The
previous coding in pg_utf8_verifystr() is retained for short strings and for
when the fast path returns an error. Review, performance testing, and
additional hacking by: Heikki Linakangas, Vladimir Sitnikov, Amit Khandekar,
Thomas Munro, and Greg Stark Discussion:

Tom Lane pushed:

- Add a \getenv command to psql. \getenv fetches the value of an environment
variable into a psql variable. This is the inverse of the \setenv command
that was added over ten years ago. We'd not seen a compelling use-case for
\getenv at the time, but upcoming regression test refactoring provides a
sufficient reason to add it now. Discussion:

- Remove dynamic translation of regression test scripts, step 1. pg_regress has
long had provisions for dynamically substituting path names into regression
test scripts and result files, but use of that feature has always been a
serious pain in the neck, mainly because updating the result files requires
tedious manual editing. Let's get rid of that in favor of passing down the
paths in environment variables. In addition to being easier to maintain, this
way is capable of dealing with path names that require escaping at runtime,
for example paths containing single-quote marks. (There are other stumbling
blocks in the way of actually building in a path that looks like that, but
removing this one seems like a good thing to do.) The key coding rule that
makes that possible is to concatenate pieces of a dynamically-variable string
using psql's \set command, and then use the :'variable' notation to quote and
escape the string for the next level of interpretation. In hopes of making
this change more transparent to "git blame", I've split it into two steps.
This commit adds the necessary pg_regress.c support and changes all the
`*.source` files in-place so that they no longer require any dynamic
translation. The next commit will just "git mv" them into the regular sql/
and expected/ directories. Discussion:

- Remove dynamic translation of regression test scripts, step 2. "git mv" all
the input/*.source and output/*.source files into the corresponding sql/ and
expected/ directories. Then remove the pg_regress and Makefile infrastructure
associated with dynamic translation. Discussion:

- Merge dblink's paths test script into its main test. There's no longer any
reason to fire up a separate psql run to create these functions. (Some
refactoring in the main regression tests is also called for, but that will
take more thought.) Discussion:

- Add missing EmitWarningsOnPlaceholders() calls. Extensions that define any
custom GUCs should call EmitWarningsOnPlaceholders after doing so, to help
catch misspellings. Many of our contrib modules hadn't gotten the memo on
that, though. Also add such calls to src/test/modules extensions that have
GUCs. While these aren't really user-facing, they should illustrate good
practice not faulty practice. Shinya Kato Discussion:

- Add help & tab-complete support for psql's \getenv. I forgot about these
details in 33d3eeadb :-(. Noted by Christoph Berg. Discussion:

- Rethink handling of settings with a prefix reserved by an extension. Commit
75d22069e made SET print a warning if you tried to set an unrecognized
parameter within namespace previously reserved by an extension. It seems
better for that to be an outright error though, for the same reason that we
don't let you set unrecognized unqualified parameter names. In any case, the
preceding implementation was inefficient and erroneous. Perform the check in
a more appropriate spot, and be more careful about prefix-match cases.

- Rename EmitWarningsOnPlaceholders() to MarkGUCPrefixReserved(). This seems
like a clearer name for what it does now. Provide a compatibility macro so
that extensions don't have to convert to the new name right away. Discussion:

- Revert changes about warnings/errors for placeholders. Revert commits
5609cc01c, 2ed8a8cc5, and 75d22069e until we have a less broken idea of how
this should work in parallel workers. Per buildfarm. Discussion:

- Fix issues in pgarch's new directory-scanning logic. The arch_filenames[]
array elements were one byte too small, so that a maximum-length filename
would get corrupted if another entry were made after it. (Noted by Thomas
Munro, fix by Nathan Bossart.) Move these arrays into a palloc'd struct, so
that we aren't wasting a few kilobytes of static data in each non-archiver
process. Add a binaryheap_reset() call to make it plain that we start the
directory scan with an empty heap. I don't think there's any live bug of that
sort, but it seems fragile, and this is very cheap insurance. Cleanup for
commit beb4e9ba1, so no back-patch needed. Discussion:

- Minor cleanup/optimization in pg_dump. In the wake of commits 05649b88c and
5209c0ba0, findComments() and findSecLabels() no longer use their "Archive
`*fout"` arguments, so get rid of those. While doing that, I noticed that
there's no very good reason why dumpCompositeTypeColComments() should be doing
its own query to fetch the column names of the composite type, when the
calling function has just fetched the same data. Tweak it to use that query
result. This probably doesn't save a lot for most people, because since
5209c0ba0 we won't get into this code at all unless the composite type has at
least one comment. Nonetheless, it's a wasted query.

- pg_dump: make dumpPublication et al. less unlike sibling functions.
dumpPublication, dumpPublicationNamespace, dumpPublicationTable, and
dumpSubscription failed to check dataOnly. This is just a latent bug, because
pg_backup_archiver.c would filter out the ArchiveEntry later; but they're
wasting cycles in data-only dumps, and the omission might become a live bug
someday. In any case, it's not good to have some dumpFoo functions do this
and some not. On the same reasoning, make dumpPublicationNamespace follow the
same pattern as every other dumpFoo function for checking the
DUMP_COMPONENT_DEFINITION flag. (Since 5209c0ba0, we wouldn't even get here
if that flag isn't set, so checking it is just pro forma right now. But it
might not be so forever.) Since this is just cosmetic and/or future-proofing,
no need for back-patch.

- pg_dump: minor performance improvements from eliminating sub-SELECTs. Get rid
of the "username_subquery" mechanism in favor of doing local lookups of role
names from role OIDs. The PG backend isn't terribly smart about scalar
SubLinks in SELECT output lists, so this offers a small performance
improvement, at least in installations with more than a couple of users. In
any case the old method didn't make for particularly readable SQL code. While
at it, I removed the various custom warning messages about failing to find an
object's owner, in favor of just fatal'ing in the local lookup function.
AFAIK there is no reason any longer to treat that as anything but a
catalog-corruption case, and certainly no reason to make translators deal with
a dozen different messages where one would do. (If it turns out that fatal()
is indeed a bad idea, we can back off to issuing pg_log_warning() and
returning an empty string, resulting in the same behavior as before, except
more consistent.) Also drop an entirely unnecessary sub-SELECT to check on
the pg_depend status of a sequence relation: we already have a LEFT JOIN to
fetch the row of interest in the FROM clause. Discussion:

- pg_dump: avoid unsafe function calls in getPolicies(). getPolicies() had the
same disease I fixed in other places in commit e3fcbbd62, i.e., it was calling
pg_get_expr() for expressions on tables that we don't necessarily have lock
on. To fix, restrict the query to only collect interesting rows, rather than
doing the filtering on the client side. Like the previous patch, apply to
HEAD only for now. Discussion:

- Fix index-only scan plans when not all index columns can be returned. If an
index has both returnable and non-returnable columns, and one of the
non-returnable columns is an expression using a Var that is in a returnable
column, then a query returning that expression could result in an index-only
scan plan that attempts to read the non-returnable column, instead of
recomputing the expression from the returnable column as intended. To fix,
redefine the "indextlist" list of an IndexOnlyScan plan node as containing
null Consts in place of any non-returnable columns. This solves the problem by
preventing setrefs.c from falsely matching to such entries. The executor is
happy since it only cares about the exposed types of the entries, and
ruleutils.c doesn't care because a correct plan won't reference those entries.
I considered some other ways to prevent setrefs.c from doing the wrong thing,
but this way seems good since (a) it allows a very localized fix, (b) it makes
the indextlist structure more compact in many cases, and (c) the indextlist is
now a more faithful representation of what the index AM will actually produce,
viz. nulls for any non-returnable columns. This is easier to hit since we
introduced included columns, but it's possible to construct failing examples
without that, as per the added regression test. Hence, back-patch to all
supported branches. Per bug #17350 from Louis Jachiet. Discussion:

Amit Kapila pushed:

- Move index vacuum routines to vacuum.c. An upcoming patch moves parallel
vacuum code out of vacuumlazy.c. This code restructuring will allow both lazy
vacuum and parallel vacuum to use index vacuum functions. Author: Masahiko
Sawada Reviewed-by: Hou Zhijie, Amit Kapila Discussion:

- Move parallel vacuum code to vacuumparallel.c. This commit moves parallel
vacuum related code to a new file commands/vacuumparallel.c so that any table
AM supporting indexes can utilize parallel vacuum in order to call index AM
callbacks (ambulkdelete and amvacuumcleanup) with parallel workers. Another
reason for this refactoring is that the parallel vacuum isn't specific to heap
so it doesn't make sense to keep this code in heap/vacuumlazy.c. Author:
Masahiko Sawada, based on suggestion from Andres Freund Reviewed-by: Hou
Zhijie, Amit Kapila, Haiying Tang Discussion:

- Fix compilation error introduced by commit 8e1fae1938. Author: Masahiko Sawada

Michaël Paquier pushed:

path related to this flavor of ALTER TABLE was checking that the relation to
detach has to be a normal table or a partitioned table, which would fail if
using the command with a different relation kind. Views, sequences and
materialized views cannot be part of a partition tree, so these would cause
the command to fail anyway, but the assertion was triggered. Foreign tables
can be part of a partition tree, and again the assertion would have failed.
The simplest solution is just to remove this assertion, so as we get the same
failure as the non-concurrent code path. While on it, add a regression test
in postgres_fdw for the concurrent partition detach of a foreign table, as per
a suggestion from Alexander Lakhin. Issue introduced in 71f4c8c.
Reported-by: Alexander Lakhin Author: Michael Paquier, Alexander Lakhin
Reviewed-by: Peter Eisentraut, Kyotaro Horiguchi Discussion:
Backpatch-through: 14

- Correct comment and some documentation about REPLICA_IDENTITY_INDEX.
catalog/pg_class.h was stating that REPLICA_IDENTITY_INDEX with a dropped
index is equivalent to REPLICA_IDENTITY_DEFAULT. The code tells a different
story, as it is equivalent to REPLICA_IDENTITY_NOTHING. The behavior exists
since the introduction of replica identities, and fe7fd4e even added tests for
this case but I somewhat forgot to fix this comment. While on it, this commit
reorganizes the documentation about replica identities on the ALTER TABLE
page, and a note is added about the case of dropped indexes with
REPLICA_IDENTITY_INDEX. Author: Michael Paquier, Wei Wang Reviewed-by: Euler
Taveira Discussion:
Backpatch-through: 10

- Fix incorrect field count in pg_control_checkpoint(). 18 columns are generated
in this function, but we had enough space for 19 of them. Introduced by
4b0d28d. Author: Bharath Rupireddy Reviewed-by: Justin Pryzby, Euler Taveira

Bruce Momjian pushed:

- doc: clarify when expression indexes evaluate their expressions. Only non-HOT
updates evaluate the index expression. Reported-by: Chris Lowder Discussion:
Backpatch-through: 10

Fujii Masao pushed:

- postgres_fdw: Allow postgres_fdw.application_name to include escape sequences.
application_name that used when postgres_fdw establishes a connection to a
foreign server can be specified in either or both a connection parameter of a
server object and GUC postgres_fdw.application_name. This commit allows those
parameters to include escape sequences that begins with % character. Then
postgres_fdw replaces those escape sequences with status information. For
example, %d and %u are replaced with user name and database name in local
server, respectively. This feature enables us to add information more easily
to track remote transactions or queries, into application_name of a remote
connection. Author: Hayato Kuroda Reviewed-by: Kyotaro Horiguchi, Masahiro
Ikeda, Hou Zhijie, Fujii Masao Discussion:

- postgres_fdw: Revert unstable tests for postgres_fdw.application_name. Commit
6e0cb3dec1 added the tests that check that escape sequences in
postgres_fdw.application_name setting are replaced with status information
expectedly. But they were unstable and caused some buildfarm members to report
the failure. This commit reverts those unstable tests.

Thomas Munro pushed:

- Fix overly generic name in with.sql test. Avoid the name "test". In the 10
branch, this could clash with alter_table.sql, as seen in the build farm.
That other instance was already renamed in later branches by commit 2cf8c7aa,
but it's good to future-proof the name here too. Back-patch to 10.
Reviewed-by: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> Discussion:

Daniel Gustafsson pushed:

- Revert b2a459edf "Fix GRANTED BY support in REVOKE ROLE statements". The
reverted commit attempted to fix SQL specification compliance for the cases
which 6aaaa76bb left. This however broke existing behavior which takes
precedence over spec compliance so revert. The introduced tests are left after
the revert since the codepath isn't well covered. Per bug report 17346.
Backpatch down to 14 where it was introduced. Reported-by: Andrew Bille
<andrewbille(at)gmail(dot)com> Discussion:

Álvaro Herrera pushed:

- Small cleanups related to PUBLICATION framework code. Discussion:

Andres Freund pushed:

- ci: Add continuous integration for github repositories via cirrus-ci.
Currently FreeBSD, Linux, macOS and Windows (Visual Studio) are tested. The
main goal of this integration is to make it easier to test in-development
patches across multiple platforms. This includes improving the testing done
automatically by cfbot [1] for commitfest entries. It is *not* the goal to
supersede the buildfarm. cirrus-ci [2] was chosen because it was already in
use for cfbot, allows using full VMs, has good OS coverage and allows
accessing the full test results without authentication (like a github
account). It might be worth adding support for further CI providers,
particularly ones supporting other git forges, in the future. To keep CI
times tolerable, most platforms use pre-generated images. Some platforms use
containers, others use full VMs. For instructions on how to enable the CI
integration in a repository and further details, see src/tools/ci/README [1]
[]( [2]
[]( Author: Andres Freund
<andres(at)anarazel(dot)de> Author: Thomas Munro <tmunro(at)postgresql(dot)org> Author:
Melanie Plageman <melanieplageman(at)gmail(dot)com> Reviewed-By: Melanie Plageman
<melanieplageman(at)gmail(dot)com> Reviewed-By: Justin Pryzby <pryzby(at)telsasoft(dot)com>
Reviewed-By: Thomas Munro <tmunro(at)postgresql(dot)org> Reviewed-By: Peter
Eisentraut <peter(dot)eisentraut(at)enterprisedb(dot)com> Discussion:

Magnus Hagander pushed:

- Fix typo. Reported-By: Eric Mutta Backpatch-through: 10 Discussion:

Browse pgsql-announce by date

  From Date Subject
Next Message Michel Pelletier via PostgreSQL Announce 2022-01-10 14:59:55 pgsodium 2.0.0: Modern cryptography for PostgreSQL
Previous Message Alexey Borzov via PostgreSQL Announce 2022-01-02 21:25:11 pg_builder 2.0.0 and pg_wrapper 2.0.0 packages for PHP released