PostgreSQL Weekly News - September 26, 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 - September 26, 2021
Date: 2021-09-27 07:58:29
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-announce

# PostgreSQL Weekly News - September 26, 2021

PostgreSQL 14 Release Candidate 1 released.

# PostgreSQL Product News

JDBC 42.2.24 released

check_pgbackrest 2.1, a Nagios-compatible monitor for pgBackRest, released.

sqlite_fdw 2.1.0 [released](

# PostgreSQL Jobs for September


# 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áš Vondra pushed:

- Disallow extended statistics on system columns. Since introduction of extended
statistics, we've disallowed references to system columns. So for example
CREATE STATISTICS s ON ctid FROM t; would fail. But with extended statistics
on expressions, it was possible to work around this limitation quite easily
CREATE STATISTICS s ON (ctid::text) FROM t; This is an oversight in
a4d75c86bf, fixed by adding a simple check. Backpatch to PostgreSQL 14, where
support for extended statistics on expressions was introduced.
Backpatch-through: 14 Discussion:

- Free memory after building each statistics object. Until now, all extended
statistics on a given relation were built in the same memory context, without
resetting. Some of the memory was released explicitly, but not all of it - for
example memory allocated while detoasting values is hard to free. This is how
it worked since extended statistics were introduced in PostgreSQL 10, but
adding support for extended stats on expressions made the issue somewhat worse
as it increases the number of statistics to build. Fixed by adding a memory
context which gets reset after building each statistics object (all the
statistics kinds included in it). Resetting it after building each statistics
kind would be even better, but it would require more invasive changes and
copying of results, making it harder to backpatch. Backpatch to PostgreSQL
10, where extended statistics were introduced. Author: Justin Pryzby
Reported-by: Justin Pryzby Reviewed-by: Tomas Vondra Backpatch-through: 10

- Release memory allocated by dependency_degree. Calculating degree of a
functional dependency may allocate a lot of memory - we have released mot of
the explicitly allocated memory, but e.g. detoasted varlena values were left
behind. That may be an issue, because we consider a lot of dependencies (all
combinations), and the detoasting may happen for each one again. Fixed by
calling dependency_degree() in a dedicated context, and resetting it after
each call. We only need the calculated dependency degree, so we don't need to
copy anything. Backpatch to PostgreSQL 10, where extended statistics were
introduced. Backpatch-through: 10 Discussion:

Tom Lane pushed:

- Doc: minor improvements for "Formatting" section. Add more-specific links into
the source tree.

- Fix misevaluation of STABLE parameters in CALL within plpgsql. Before commit
84f5c2908, a STABLE function in a plpgsql CALL statement's argument list would
see an up-to-date snapshot, because exec_stmt_call would push a new snapshot.
I got rid of that because the possibility of the snapshot disappearing within
COMMIT made it too hard to manage a snapshot across the CALL statement.
That's fine so far as the procedure itself goes, but I forgot to think about
the possibility of STABLE functions within the CALL argument list. As things
now stand, those'll be executed with the Portal's snapshot as ActiveSnapshot,
keeping them from seeing updates more recent than Portal startup. (VOLATILE
functions don't have a problem because they take their own snapshots; which
indeed is also why the procedure itself doesn't have a problem. There are no
STABLE procedures.) We can fix this by pushing a new snapshot transiently
within ExecuteCallStmt itself. Popping the snapshot before we get into the
procedure proper eliminates the management problem. The possibly-useless extra
snapshot-grab is slightly annoying, but it's no worse than what happened
before 84f5c2908. Per bug #17199 from Alexander Nawratil. Back-patch to v11,
like the previous patch. Discussion:

- Doc: extend warnings about collation-mismatch hazards in postgres_fdw. Be a
little more vocal about the risks of remote collations not matching local
ones. Actually fixing these risks seems hard, and I've given up on the idea
that it might be back-patchable. So the best we can do for the back branches
is add documentation. Per discussion of bug #16583 from Jiří Fejfar.

- Avoid unnecessary division in interval_cmp_value(). Splitting the time field
into days and microseconds is pretty useless when we're just going to
recombine those values. It's unclear if anyone will notice the speedup in
real-world cases, but a cycle shaved is a cycle earned. Discussion:

Álvaro Herrera pushed:

- Doc: add glossary term for "auxiliary process". Add entries for existing
processes not documented, too, and adjust existing definitions for
consistency. Per question from Bharath Rupireddy. Author: Justin Pryzby
<pryzby(at)telsasoft(dot)com> Author: Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org>

- Document XLOG_INCLUDE_XID a little better. I noticed that commit 0bead9af484c
left this flag undocumented in XLogSetRecordFlags, which led me to discover
that the flag doesn't actually do what the one comment on it said it does.
Improve the situation by adding some more comments. Backpatch to 14, where
the aforementioned commit appears. Author: Álvaro Herrera
<alvherre(at)alvh(dot)no-ip(dot)org> Discussion:

Andres Freund pushed:

- pgstat: Split out relation stats handling from AtEO[Sub]Xact_PgStat() etc. An
upcoming patch will add additional work to these functions. To avoid the
functions getting too complicated / doing too many things at once, split out
sub-tasks into their own functions. Author: Andres Freund
<andres(at)anarazel(dot)de> Discussion:

- pgstat: Prepare to use mechanism for truncated rels also for droppped rels.
The upcoming shared memory stats patch drops stats for dropped objects in a
transactional manner, rather than removing them later as part of vacuum. This
means that stats for DROP inside a transaction needs to handle aborted
(sub-)transactions similar to TRUNCATE: The stats up to the DROP should be
restored. Rename the existing infrastructure in preparation. Author: Andres
Freund <andres(at)anarazel(dot)de> Discussion:

Peter Geoghegan pushed:

- Remove overzealous index deletion assertion. A broken HOT chain is not an
unexpected condition, even when the offset number points past the end of the
page's line pointer array. heap_prune_chain() does not (and never has) treated
this condition as unexpected, so derivative code in heap_index_delete_tuples()
shouldn't do so either. Oversight in commit 4228817449. The assertion can
probably only fail on Postgres 14 and master. Earlier releases don't have
commit 3c3b8a4b, which taught VACUUM to truncate the line pointer array of
heap pages. Backpatch all the same, just to be consistent. Author: Peter
Geoghegan <pg(at)bowt(dot)ie> Reported-By: Alexander Lakhin <exclusion(at)gmail(dot)com>
Backpatch: 12-, just like commit 4228817449.

- Fix "single value strategy" index deletion issue. It is not appropriate for
deduplication to apply single value strategy when triggered by a bottom-up
index deletion pass. This wastes cycles because later bottom-up deletion
passes will overinterpret older duplicate tuples that deduplication actually
just skipped over "by design". It also makes bottom-up deletion much less
effective for low cardinality indexes that happen to cross a meaningless
"index has single key value per leaf page" threshold. To fix, slightly narrow
the conditions under which deduplication's single value strategy is
considered. We already avoided the strategy for a unique index, since our
high level goal must just be to buy time for VACUUM to run (not to buy space).
We'll now also avoid it when we just had a bottom-up pass that reported
failure. The two cases share the same high level goal, and already overlapped
significantly, so this approach is quite natural. Oversight in commit
d168b666, which added bottom-up index deletion. Author: Peter Geoghegan
<pg(at)bowt(dot)ie> Discussion:
Backpatch: 14-, where bottom-up deletion was introduced.

- Document issue with heapam line pointer truncation. Checking that an offset
number isn't past the end of a heap page's line pointer array was just a
defensive sanity check for HOT-chain traversal code before commit 3c3b8a4b.
It's etrictly necessary now, though. Add comments that reference the issue to
code in heapam that needs to get it right. Per suggestion from Alexander
Lakhin. Discussion:

- nbtree README: Add note about latestRemovedXid. Point out that index tuple
deletion generally needs a latestRemovedXid value for the deletion operation's
WAL record. This is bound to be the most expensive part of the whole deletion
operation now that it takes place up front, during original execution. This
was arguably an oversight in commit 558a9165e08, which moved the work required
to generate these values from index deletion REDO routines to original
execution of index deletion operations.

- vacuumlazy.c: Remove obsolete 'onecall' comment. Remove obsolete reference to
lazy_vacuum()'s onecall argument. The function argument was removed by commit
3499df0dee. Also remove adjoining comment block that introduces the
wraparound failsafe concept. Talking about the failsafe here no longer makes
sense, since lazy_vacuum() (and related functions) are no longer the only
place where the failsafe might be triggered. This has been the case since
commit c242baa4a8 taught VACUUM to consider triggering the failsafe mechanism
during its initial heap scan.

- Update obsolete nbtree deletion comments. `_bt_delitems_delete`() is no longer
the high-level entry point used by index tuple deletion driven by index tuples
whose LP_DEAD bits are set (now called "simple index tuple deletion"). It
became a lower level routine that's only called by `_bt_delitems_delete_check`()
following commit d168b66682.

Michaël Paquier pushed:

- Introduce GUC shared_memory_size_in_huge_pages. This runtime-computed GUC
shows the number of huge pages required for the server's main shared memory
area, taking advantage of the work done in 0c39c29 and 0bd305e. This is
useful for users to estimate the amount of huge pages required for a server as
it becomes possible to do an estimation without having to start the server and
potentially allocate a large chunk of shared memory. The number of huge pages
is calculated based on the existing GUC huge_page_size if set, or by using the
system's default by looking at /proc/meminfo on Linux. There is nothing new
here as this commit reuses the existing calculation methods, and just exposes
this information directly to the user. The routine calculating the huge page
size is refactored to limit the number of files with platform-specific flags.
This new GUC's name was the most popular choice based on the discussion done.
This is only supported on Linux. I have taken the time to test the change on
Linux, Windows and MacOS, though for the last two ones large pages are not
supported. The first one calculates correctly the number of pages depending
on the existing GUC huge_page_size or the system's default. Thanks to Andres
Freund, Robert Haas, Kyotaro Horiguchi, Tom Lane, Justin Pryzby (and anybody
forgotten here) for the discussion. Author: Nathan Bossart Discussion:

- Fix places in in need of adaptation to the output of Msys perl.
Contrary to the output of native perl, Msys perl generates outputs with CRLFs
characters. There are already places in the TAP code where CRLFs (\r\n) are
automatically converted to LF (\n) on Msys, but we missed a couple of places
when running commands and using their output for comparison, that would lead
to failures. This problem has been found thanks to the test added in 5adb067
using TestLib::command_checks_all(), but after a closer look more code paths
were missing a filter. This is backpatched all the way down to prevent any
surprises if a new test is introduced in stable branches. Reviewed-by: Andrew
Dunstan, Álvaro Herrera Discussion:
Backpatch-through: 9.6

- Fix some issues with TAP tests for postgres -C. This addresses two issues with
the tests added in 0c39c292 for runtime GUCs: - Re-enable the test on Msys.
The test could fail because of \r\n generated by Msys perl. 0d91c52a has
taken care of this issue. - Allow the test to run in the context of a
privileged account. CIs running under privileged accounts would fail on
permission failures, as reported by Andres Freund. This issue is fixed by
wrapping the postgres command within pg_ctl as the latter will take care of
any permissions needed. The test checking a failure of postgres -C for a
runtime parameter with an instance running is removed, as pg_ctl produces an
unstable error code (no need for a CI to reproduce that). Discussion:

- doc: Add missing markup in CREATE EVENT TRIGGER page. Reported-by: rir
Backpatch-through: 9.6

- doc: Improve description of index vacuuming with GUCs. Index vacuums may
happen multiple times depending on the number of dead tuples stored, as of
maintenance_work_mem for a manual VACUUM. For autovacuum, this is controlled
by autovacuum_work_mem instead, if set. The documentation mentioned the
former, but not the latter in the context of autovacuum. Reported-by: Nikolai
Berkoff Author: Laurenz Albe, Euler Taveira Discussion:
Backpatch-through: 9.6

- Fix typos in docs. Author: Justin Pryzby Discussion:
Backpatch-through: 9.6

Amit Kapila pushed:

- Add parent table name in an error in reorderbuffer.c. This can help in
troubleshooting the cause of a particular error that can occur during
decoding. Author: Jeremy Schneider Reviewed-by: Amit Kapila Discussion:

- Invalidate all partitions for a partitioned table in publication.
Updates/Deletes on a partition were allowed even without replica identity
after the parent table was added to a publication. This would later lead to an
error on subscribers. The reason was that we were not invalidating the
partition's relcache and the publication information for partitions was not
getting rebuilt. Similarly, we were not invalidating the partitions' relcache
after dropping a partitioned table from a publication which will prohibit
Updates/Deletes on its partition without replica identity even without any
publication. Reported-by: Haiying Tang Author: Hou Zhijie and Vignesh C
Reviewed-by: Vignesh C and Amit Kapila Backpatch-through: 13 Discussion:

Peter Eisentraut pushed:

- Make use of PG_INT64_MAX/PG_INT64_MIN. This code was written before those
symbols were introduced, but now we can simplify it.

- Add missing $Test::Builder::Level settings. One of these was accidentally
removed by c50624c. The others are added by analogy. Discussion:

Fujii Masao pushed:

- postgres_fdw: Refactor transaction rollback code to avoid code duplication. In
postgres_fdw, pgfdw_xact_callback() and pgfdw_subxact_callback() callback
functions do almost the same thing to rollback remote toplevel- and
sub-transaction. But previously their such rollback logics were implemented
separately in each function and in different way. Which could decrease the
readability and maintainability of the code. To fix the issue, this commit
creates the common function to rollback remote transactions, and makes those
callback functions use it. Which allows us to avoid unnecessary code
duplication. Author: Fujii Masao Reviewed-by: Zhihong Yu, Bharath Rupireddy

Alexander Korotkov pushed:

- Split macros from visibilitymap.h into a separate header. That allows to
include just visibilitymapdefs.h from file.c, and in turn, remove include of
postgres.h from relcache.h. Reported-by: Andres Freund Discussion:
Author: Alexander Korotkov Reviewed-by: Andres Freund, Tom Lane, Alvaro
Herrera Backpatch-through: 13

John Naylor pushed:

- Add exception for unicode_east_asian_fw_table.h to cpluspluscheck.
unicode_east_asian_fw_table.h should not be compiled standalone, similarly to
unicode_combining_table.h, but cpluspluscheck did not get the memo. Oversight
in bab982161. Per report from Tom Lane

- Add exception for unicode_east_asian_fw_table.h to headerscheck also. Followup
to a315b19cc

Browse pgsql-announce by date

  From Date Subject
Next Message CYBERTEC PostgreSQL International GmbH via PostgreSQL Announce 2021-09-27 21:21:23 oracle_fdw 2.4.0 released
Previous Message Toshiba via PostgreSQL Announce 2021-09-25 15:13:27 SQLite fdw 2.1.0 released