PostgreSQL Weekly News - August 15, 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 - August 15, 2021
Date: 2021-08-15 20:41:46
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-announce

# PostgreSQL Weekly News - August 15, 2021

Security releases 13.4, 12.8, 11.13, 10.18, 9.6.23, and 14 Beta 3
Please upgrade ASAP. The 9.6 series will stop getting fixes on November 11,
2021. Plan major version upgrades now.

The [Code of Conduct
Committee]( is looking
for new members to serve a 1-3 year term.

# PostgreSQL Product News

pgbouncer 1.16.0, a connection pooler and more for PostgreSQL

# PostgreSQL Jobs for August


# 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

Bruce Momjian pushed:

- doc: mention pg_upgrade extension script. Since commit e462856a7a,
pg_upgrade automatically creates a script to update extensions, so mention
that instead of ALTER EXTENSION. Backpatch-through: 9.6

David Rowley pushed:

- Add POPCNT support for MSVC x86_64 builds. 02a6a54ec added code to make use of
the POPCNT instruction when available for many of our common platforms. Here
we do the same for MSVC for x86_64 machines. MSVC's intrinsic functions for
popcnt seem to differ from GCCs in that they always appear to emit the popcnt
instructions. In GCC the behavior will depend on if the source file was
compiled with -mpopcnt or not. For this reason, the MSVC intrinsic function
has been lumped into the `pg_popcount*_asm` function, however doing that sort of
invalidates the name of that function, so let's rename it to
`pg_popcount*_fast()`. Author: David Rowley Reviewed-by: John Naylor

- Use ExplainPropertyInteger for queryid in EXPLAIN. This saves a few lines of
code. Also add a comment to mention why we use ExplainPropertyInteger instead
of ExplainPropertyUInteger given that queryid is a uint64 type. Author: David
Rowley Reviewed-by: Julien Rouhaud Discussion:
Backpatch-through: 14, where this code was originally added

- Doc: Fix misleading statement about VACUUM memory limits. In ec34040af I added
a mention that there was no point in setting maintenance_work_limit to
anything higher than 1GB for vacuum, but that was incorrect as
ginInsertCleanup() also looks at what maintenance_work_mem is set to during
VACUUM and that's not limited to 1GB. Here I attempt to make it more clear
that the limitation is only around the number of dead tuple identifiers that
we can collect during VACUUM. I've also added a note to autovacuum_work_mem
to mention this limitation. I didn't do that in ec34040af as I'd had some
wrong-headed ideas about just limiting the maximum value for that GUC to 1GB.
Author: David Rowley Discussion:
Backpatch-through: 9.6, same as ec34040af

- Remove some special cases from MSVC build scripts. Here we add additional
parsing of Makefiles to determine when to add references to libpgport and
libpgcommon. We also remove the need for adding the current
contrib_extrasource by adding sine very basic logic to implement the Makefile
rules which add .l and .y files when they exist for a given .o file in the
Makefile. This is just some very basic additional parsing of Makefiles to try
to keep things more consistent between builds using make and MSVC builds. This
happens to work with how our current Makefiles are laid out, but it could
easily be broken in the future if someone chooses do something in the Makefile
that we don't have parsing support for. We will cross that bridge when we
come to it. Author: David Rowley Discussion:

- Fix incorrect hash table resizing code in simplehash.h. This fixes a bug in
simplehash.h which caused an incorrect size mask to be used when the hash
table grew to SH_MAX_SIZE (2^32). The code was incorrectly setting the size
mask to 0 when the hash tables reached the maximum possible number of buckets.
This would result always trying to use the 0th bucket causing an infinite
loop of trying to grow the hash table due to there being too many collisions.
Seemingly it's not that common for simplehash tables to ever grow this big as
this bug dates back to v10 and nobody seems to have noticed it before.
However, probably the most likely place that people would notice it would be
doing a large in-memory Hash Aggregate with something close to at least 2^31
groups. After this fix, the code now works correctly with up to within 98% of
2^32 groups and will fail with the following error when trying to insert any
more items into the hash table: ERROR: hash table size exceeded However,
the work_mem (or hash_mem_multiplier in newer versions) settings will
generally cause Hash Aggregates to spill to disk long before reaching that
many groups. The minimal test case I did took a work_mem setting of over
192GB to hit the bug. simplehash hash tables are used in a few other places
such as Bitmap Index Scans, however, again the size that the hash table can
become there is also limited to work_mem and it would take a relation of
around 16TB (2^31) pages and a very large work_mem setting to hit this. With
smaller work_mem values the table would become lossy and never grow large
enough to hit the problem. Author: Yura Sokolov Reviewed-by: David Rowley,
Ranier Vilela Discussion:
Backpatch-through: 10, where simplehash.h was added

Amit Kapila pushed:

- Fix typo in Author: Peter Smith Discussion:

Tom Lane pushed:

- Avoid determining regexp subexpression matches, when possible. Identifying the
precise match locations for parenthesized subexpressions is a fairly expensive
task given the way our regexp engine works, both at regexp compile time (where
we must create an optimized NFA for each parenthesized subexpression) and at
runtime (where determining exact match locations requires laborious search).
Up to now we've made little attempt to optimize this situation. This patch
identifies cases where we know at compile time that we won't need to know
subexpression match locations, and teaches the regexp compiler to not bother
creating per-subexpression regexps for parenthesis pairs that are not
referenced by backrefs elsewhere in the regexp. (To preserve semantics, we
obviously still have to pin down the match locations of backref references.)
Users could have obtained the same results before this by being careful to
write "non capturing" parentheses wherever possible, but few people bother
with that. Discussion:

- Let regexp_replace() make use of REG_NOSUB when feasible. If the replacement
string doesn't contain \1...\9, then we don't need sub-match locations, so we
can use the REG_NOSUB optimization here too. There's already a pre-scan of
the replacement string to look for backslashes, so extend that to check for
digits, and refactor to allow that to happen before we compile the regexp.
While at it, try to speed up the pre-scan by using memchr() instead of a
handwritten loop. It's likely that this is lost in the noise compared to the
regexp processing proper, but maybe not. In any case, this coding is shorter.
Also, add some test cases to improve the poor coverage of
appendStringInfoRegexpSubstr(). Discussion:

- Fix failure of btree_gin indexscans with "char" type and `</<=` operators. As a
result of confusion about whether the "char" type is signed or unsigned, scans
for index searches like "col < 'x'" or "col <= 'x'" would start at the middle
of the index not the left end, thus missing many or all of the entries they
should find. Fortunately, this is not a symptom of index corruption. It's
only the search logic that is broken, and we can fix it without unpleasant
side-effects. Per report from Jason Kim. This has been wrong since
btree_gin's beginning, so back-patch to all supported branches. Discussion:

- Add RISC-V spinlock support in s_lock.h. Like the ARM case, just use gcc's
`__sync_lock_test_and_set();` that will compile into AMOSWAP.W.AQ which does
what we need. At some point it might be worth doing some work on atomic ops
for RISC-V, but this should be enough for a creditable port. Back-patch to
all supported branches, just in case somebody wants to try them on RISC-V.
Marek Szuba Discussion:

- Un-break s_lock_test. Commit 80abbeba2 evidently didn't bother checking this
code. Also, list the generated executable in .gitignore (so it's been a REALLY
long time since anyone tried this). Noted while trying out RISC-V spinlock
patch. Given that this has been broken for 5 years and nobody noticed, it's
likely not worth back-patching.

Andres Freund pushed:

- Fix bogus assertion in BootstrapModeMain(). The assertion was always true, as
written, thanks to me "simplifying" it before commit. Per coverity and Tom

- Fix typo. Reported-By: Michael Paquier <michael(at)paquier(dot)xyz> Discussion:

- Remove support for background workers without BGWORKER_SHMEM_ACCESS.
Background workers without shared memory access have been broken on
EXEC_BACKEND / windows builds since shortly after background workers have been
introduced, without that being reported. Clearly they are not commonly used.
The problem is that bgworker startup requires to be attached to shared memory
in EXEC_BACKEND child processes. StartBackgroundWorker() detaches from shared
memory for unconnected workers, but at that point we already have initialized
subsystems referencing shared memory. Fixing this problem is not entirely
trivial, so removing the option to not be connected to shared memory seems the
best way forward. In most use cases the advantages of being connected to
shared memory far outweigh the disadvantages. As there have been no reports
about this issue so far, we have decided that it is not worth trying to
address the problem in the back branches. Per discussion with Alvaro Herrera,
Robert Haas and Tom Lane. Author: Andres Freund <andres(at)anarazel(dot)de>

Michaël Paquier pushed:

- Add call to object access hook at the end of table rewrite in ALTER TABLE.
table-level object access hook, which was inconsistent with SET TABLESPACE.
Note that contrary to SET TABLESPACE, the no-op case is left off for those
commands as this requires tracking if commands have been called, but they may
not execute a physical rewrite. Another thing worth noting is that the
physical file swap at the end of a rewrite does a couple of access calls for
internal objects created for the swap operation (internal objects are for
example skipped by the tests of sepgsql), but this does not trigger the hook
for the table on which the operation is done. f41872d, that added support for
SET LOGGED/UNLOGGED in ALTER TABLE, visibly forgot to consider that. Based on
what I checked, two regression tests of sepgsql in ddl.sql are going to log
more information with this test, something that buildfarm member rhinoceros
will tell soon enough. I am not completely sure of their format though, so
these are not refreshed yet. This is arguably a bug, but no backpatch is done
as this could cause a behavior change for anybody using object access hooks.
Reported-by: Jeff Davis Discussion:

- Fix regression test output of sepgsql. The difference is caused by 7b56584,
for the tests involving a table rewrite. Per buildfarm member rhinoceros.

- Add tab completion for DECLARE .. ASENSITIVE in psql. This option has been
introduced in dd13ad9. Author: Shinya Kato Discussion:

- Avoid unnecessary shared invalidations in ROLLBACK PREPARED. The performance
gain is minimal, but this makes the logic more consistent with
AtEOXact_Inval(). No other invalidation is needed in this case as PREPARE
takes already care of sending any local ones. Author: Liu Huailing
Reviewed-by: Tom Lane, Michael Paquier Discussion:

Daniel Gustafsson pushed:

- Remove unused regression test certificate server-ss. The server-ss certificate
was included in e39250c64 but was never used in the TLS regression tests so
remove. Author: Jacob Champion Discussion:

- Disable OpenSSL EVP digest padding in pgcrypto. The PX layer in pgcrypto is
handling digest padding on its own uniformly for all backend implementations.
Starting with OpenSSL 3.0.0, DecryptUpdate doesn't flush the last block in
case padding is enabled so explicitly disable it as we don't use it. This
will be backpatched to all supported version once there is sufficient testing
in the buildfarm of OpenSSL 3. Reviewed-by: Peter Eisentraut, Michael Paquier

- Add alternative output for OpenSSL 3 without legacy loaded. OpenSSL 3
introduced the concept of providers to support modularization, and moved the
outdated ciphers to the new legacy provider. In case it's not loaded in the
users openssl.cnf file there will be a lot of regress test failures, so add
alternative outputs covering those. Also document the need to load the legacy
provider in order to use older ciphers with OpenSSL-enabled pgcrypto. This
will be backpatched to all supported version once there is sufficient testing
in the buildfarm of OpenSSL 3. Reviewed-by: Michael Paquier Discussion:

- Fix sslsni connparam boolean check. The check for sslsni only checked for
existence of the parameter but not for the actual value of the param. This
meant that the SNI extension was always turned on. Fix by inspecting the
value of sslsni and only activate the SNI extension iff sslsni has been
enabled. Also update the docs to be more in line with how other boolean
params are documented. Backpatch to 14 where sslsni was first implemented.
Reviewed-by: Tom Lane Backpatch-through: 14, where sslni was added

Heikki Linnakangas pushed:

- Fix segfault during EvalPlanQual with mix of local and foreign partitions.
It's not sensible to re-evaluate a direct-modify Foreign Update or Delete
during EvalPlanQual. However, ExecInitForeignScan() can still get called if a
table mixes local and foreign partitions. EvalPlanQualStart() left the
es_result_relations array uninitialized in the child EPQ EState, but
ExecInitForeignScan() still expected to find it. That caused a segfault. Fix
by skipping the es_result_relations lookup during EvalPlanQual processing. To
make things a bit more robust, also skip the BeginDirectModify calls, and add
a runtime check that ExecForeignScan() is not called on direct-modify foreign
scans during EvalPlanQual processing. This is new in v14, commit 1375422c782.
Before that, EvalPlanQualStart() copied the whole ResultRelInfo array to the
EPQ EState. Backpatch to v14. Report and diagnosis by Andrey Lepikhov.

John Naylor pushed:

- Fix grammar mistake in hash index README. Dilip Kumar Discussion:

- Speed up generation of Unicode hash functions. Sets of Unicode keys are picky
about the primes used when generating a perfect hash function for them.
Callers can spend many seconds iterating through all the possible combinations
of candidate multipliers and seeds to find one that works. Unicode updates
typically happen only once a year, but it still makes development and testing
of Unicode scripts unnecessarily slow. To fix, iterate over the primes in the
innermost loop. This does not change any existing functions checked into the

Tomáš Vondra pushed:

- Use appropriate tuple descriptor in FDW batching. The FDW batching code was
using the same tuple descriptor both for all slots (regular and plan slots),
but that's incorrect - the subplan may use a different descriptor. Currently
this is benign, because batching is used only for INSERTs, and in that case
the descriptors always match. But that would change if we allow batching
UPDATEs. Fix by copying the appropriate tuple descriptor. Backpatch to 14,
where the FDW batching was implemented. Author: Amit Langote
Backpatch-through: 14, where FDW batching was added Discussion:

Thomas Munro pushed:

- Make EXEC_BACKEND more convenient on macOS. It's hard to disable ASLR on
current macOS releases, for testing with -DEXEC_BACKEND. You could already
set the environment variable PG_SHMEM_ADDR to something not likely to collide
with mappings created earlier in process startup. Let's also provide a
default value that works on current releases and architectures, for developer
convenience. As noted in the pre-existing comment, this is a horrible hack,
but -DEXEC_BACKEND is only used by Unix-based PostgreSQL developers for
testing some otherwise Windows-only code paths, so it seems excusable.
Back-patch to all supported branches. Reviewed-by: Tom Lane
<tgl(at)sss(dot)pgh(dot)pa(dot)us> Discussion:

Michael Meskes pushed:

- Fix connection handling for DEALLOCATE and DESCRIBE statements. After binding
a statement to a connection with DECLARE STATEMENT the connection was still
not used for DEALLOCATE and DESCRIBE statements. This patch fixes that, adds a
missing warning and cleans up the code. Author: Hayato Kuroda Reviewed-by:
Kyotaro Horiguchi, Michael Paquier Discussion:

Peter Eisentraut pushed:

- pg_amcheck: Message style and structuring improvements.

Browse pgsql-announce by date

  From Date Subject
Next Message Crunchy Data via PostgreSQL Announce 2021-08-16 21:42:05 PGO, the Crunchy Postgres Operator v5 Released: Fully Declarative Postgres
Previous Message PostgreSQL Global Development Group 2021-08-12 13:11:43 PostgreSQL 13.4, 12.8, 11.13, 10.18, 9.6.23, and 14 Beta 3 Released!