PostgreSQL Weekly News - March 28, 2021

# PostgreSQL Weekly News - March 28, 2021

Person of the week: [https://postgresql.life/post/julien_riou/](https://postgresql.life/post/julien_riou/)

# PostgreSQL Product News

pspg 4.5.0 a pager designed for PostgreSQL, released.
[https://github.com/okbob/pspg/releases/tag/4.5.0](https://github.com/okbob/pspg/releases/tag/4.5.0)

pgAdmin4 5.1, a web- and native GUI control center for PostgreSQL, released.
[https://www.pgadmin.org/docs/pgadmin4/5.1/release_notes_5_1.html](https://www.pgadmin.org/docs/pgadmin4/5.1/release_notes_5_1.html)

# PostgreSQL Jobs for March

[https://archives.postgresql.org/pgsql-jobs/2021-03/](https://archives.postgresql.org/pgsql-jobs/2021-03/)

# PostgreSQL in the News

Planet PostgreSQL: [https://planet.postgresql.org/](https://planet.postgresql.org/)

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

Andrew Dunstan pushed:

- Don't run recover crash_temp_files test in Windows perl. This reverts commit
677271a3a125e294b33b891669f594a2c8cb36ce. "Unbreak recovery test on Windows"
The test hangs on Windows, and attempts to remedy the problem have proved
fragile at best. So we simply disable the test on Windows perl. (Msys perl
seems perfectly happy). Discussion:
[https://postgr.es/m/5b748470-7335-5439-e876-6a88c951e1c5@dunslane.net](https://postgr.es/m/5b748470-7335-5439-e876-6a88c951e1c5@dunslane.net)
[https://git.postgresql.org/pg/commitdiff/ef823873840c9f341239e18633bdb0116d8d2738](https://git.postgresql.org/pg/commitdiff/ef823873840c9f341239e18633bdb0116d8d2738)

- Allow for installation-aware instances of PostgresNode. Currently instances of
PostgresNode find their Postgres executables in the PATH of the caller. This
modification allows for instances that know the installation path they are
supposed to use, and the module adjusts the environment of methods that call
Postgres executables appropriately. This facility is activated by passing the
installation path to the constructor: my $node =
PostgresNode->get_new_node('mynode', installation_path =>
'/path/to/installation'); This makes a number of things substantially easier,
including . testing third party modules . testing different versions of
postgres together . testing different builds of postgres together Discussion:
[https://postgr.es/m/a94c74f9-6b71-1957-7973-a734ea3cbef1@dunslane.net](https://postgr.es/m/a94c74f9-6b71-1957-7973-a734ea3cbef1@dunslane.net)
Reviewed-By: Alvaro Herrera, Michael Paquier, Dagfinn Ilmari Mannsåker
[https://git.postgresql.org/pg/commitdiff/b34ca595abd697e716ce369ec1b58624bdd1c431](https://git.postgresql.org/pg/commitdiff/b34ca595abd697e716ce369ec1b58624bdd1c431)

Tom Lane pushed:

- Make compression.sql regression test independent of default. This test will
fail in "make installcheck" if the installation's default_toast_compression
setting is not 'pglz'. Make it robust against that situation. Dilip Kumar
Discussion:
[https://postgr.es/m/CAFiTN-t0w+Rc2U3S+y=7KWcLuOYNB5MfWeGdNa7+pg0UovVdcQ@mail.gmail.com](https://postgr.es/m/CAFiTN-t0w+Rc2U3S+y=7KWcLuOYNB5MfWeGdNa7+pg0UovVdcQ@mail.gmail.com)
[https://git.postgresql.org/pg/commitdiff/fd1ac9a548966786cf7978e590be816c55936a50](https://git.postgresql.org/pg/commitdiff/fd1ac9a548966786cf7978e590be816c55936a50)

- Bring configure support for LZ4 up to snuff. It's not okay to just shove the
pkg_config results right into our build flags, for a couple different reasons:
* This fails to maintain the separation between CPPFLAGS and CFLAGS, as well
as that between LDFLAGS and LIBS. (The CPPFLAGS angle is, I believe, the
reason for warning messages reported when building with MacPorts' liblz4.) *
If pkg_config emits anything other than -I/-D/-L/-l switches, it's highly
unlikely that we want to absorb those. That'd be more likely to break the
build than do anything helpful. (Even the -D case is questionable; but we're
doing that for libxml2, so I kept it.) Also, it's not okay to skip doing an
AC_CHECK_LIB probe, as evidenced by recent build failure on topminnow; that
should have been caught at configure time. Model fixes for this on
configure's libxml2 support. It appears that somebody overlooked an
autoheader run, too. Discussion:
[https://postgr.es/m/20210119190720.GL8560@telsasoft.com](https://postgr.es/m/20210119190720.GL8560@telsasoft.com)
[https://git.postgresql.org/pg/commitdiff/4d399a6fbeb720b34d33441330910b7d853f703d](https://git.postgresql.org/pg/commitdiff/4d399a6fbeb720b34d33441330910b7d853f703d)

- Fix assorted silliness in ATExecSetCompression(). It's not okay to scribble
directly on a syscache entry. Nor to continue accessing said entry after
releasing it. Also get rid of not-used local variables. Per valgrind
testing.
[https://git.postgresql.org/pg/commitdiff/ac897c483485d3858ada23ca49650a0f2742a50f](https://git.postgresql.org/pg/commitdiff/ac897c483485d3858ada23ca49650a0f2742a50f)

- Remove useless configure probe for <lz4/lz4.h>. This seems to have been just
copied-and-pasted from some other header checks. But our C code is entirely
unprepared to support such a header name, so it's only wasting cycles to look
for it. If we did need to support it, some #ifdefs would be required. (A
quick trawl at codesearch.debian.net finds some packages that reference
lz4/lz4.h; but they use *only* that spelling, and appear to be intending to
reference their own copy rather than a system-level installation of liblz4.
There's no evidence of freestanding installations that require this spelling.)
Discussion:
[https://postgr.es/m/457962.1616362509@sss.pgh.pa.us](https://postgr.es/m/457962.1616362509@sss.pgh.pa.us)
[https://git.postgresql.org/pg/commitdiff/2c75f8a612b207c7d36e5dc73317dc9ab6fb29d4](https://git.postgresql.org/pg/commitdiff/2c75f8a612b207c7d36e5dc73317dc9ab6fb29d4)

- Mostly-cosmetic adjustments of TOAST-related macros. The authors of bbe0a81db
hadn't quite got the idea that macros named like SOMETHING_4B_C were only
meant for internal endianness-related details in postgres.h. Choose more
legible names for macros that are intended to be used elsewhere. Rearrange
postgres.h a bit to clarify the separation between those internal macros and
ones intended for wider use. Also, avoid using the term "rawsize" for true
decompressed size; we've used "extsize" for that, because "rawsize" generally
denotes total Datum size including header. This choice seemed particularly
unfortunate in tests that were comparing one of these meanings to the other.
This patch includes a couple of not-purely-cosmetic changes: be sure that the
shifts aligning compression methods are unsigned (not critical today, but will
be when compression method 2 exists), and fix broken definition of
VARATT_EXTERNAL_GET_COMPRESSION (now VARATT_EXTERNAL_GET_COMPRESS_METHOD),
whose callers worked only accidentally. Discussion:
[https://postgr.es/m/574197.1616428079@sss.pgh.pa.us](https://postgr.es/m/574197.1616428079@sss.pgh.pa.us)
[https://git.postgresql.org/pg/commitdiff/aeb1631ed207cef2d80e20f79eb52c72f03bca7d](https://git.postgresql.org/pg/commitdiff/aeb1631ed207cef2d80e20f79eb52c72f03bca7d)

- Short-circuit slice requests that are for more than the object's size.
substring(), and perhaps other callers, isn't careful to pass a slice length
that is no more than the datum's true size. Since
toast_decompress_datum_slice's children will palloc the requested slice
length, this can waste memory. Also, close study of the liblz4 documentation
suggests that it is dependent on the caller to not ask for more than the
correct amount of decompressed data; this squares with observed misbehavior
with liblz4 1.8.3. Avoid these problems by switching to the normal
full-decompression code path if the slice request is >= datum's decompressed
size. Tom Lane and Dilip Kumar Discussion:
[https://postgr.es/m/507597.1616370729@sss.pgh.pa.us](https://postgr.es/m/507597.1616370729@sss.pgh.pa.us)
[https://git.postgresql.org/pg/commitdiff/063dd37ebc7644e8db6419565b50dca019e69e86](https://git.postgresql.org/pg/commitdiff/063dd37ebc7644e8db6419565b50dca019e69e86)

- Avoid possible crash while finishing up a heap rewrite. end_heap_rewrite was
not careful to ensure that the target relation is open at the smgr level
before performing its final smgrimmedsync. In ordinary cases this is no
problem, because it would have been opened earlier during the rewrite.
However a crash can be reproduced by re-clustering an empty table with
CLOBBER_CACHE_ALWAYS enabled. Although that exact scenario does not crash in
v13, I think that's a chance result of unrelated planner changes, and the
problem is likely still reachable with other test cases. The true proximate
cause of this failure is commit c6b92041d, which replaced a call to heap_sync
(which was careful about opening smgr) with a direct call to smgrimmedsync.
Hence, back-patch to v13. Amul Sul, per report from Neha Sharma; cosmetic
changes and test case by me. Discussion:
[https://postgr.es/m/CANiYTQsU7yMFpQYnv=BrcRVqK_3U3mtAzAsJCaqtzsDHfsUbdQ@mail.gmail.com](https://postgr.es/m/CANiYTQsU7yMFpQYnv=BrcRVqK_3U3mtAzAsJCaqtzsDHfsUbdQ@mail.gmail.com)
[https://git.postgresql.org/pg/commitdiff/9d523119fd38fd205cb9c8ea8e7cceeb54355818](https://git.postgresql.org/pg/commitdiff/9d523119fd38fd205cb9c8ea8e7cceeb54355818)

- Fix psql's \connect command some more. Jasen Betts reported yet another
unintended side effect of commit 85c54287a: reconnecting with "\c
service=whatever" did not have the expected results. The reason is that
starting from the output of PQconndefaults() effectively allows environment
variables (such as PGPORT) to override entries in the service file, whereas
the normal priority is the other way around. Not using PQconndefaults at all
would require yet a third main code path in do_connect's parameter setup, so I
don't really want to fix it that way. But we can have the logic effectively
ignore all the default values for just a couple more lines of code. This
patch doesn't change the behavior for "\c -reuse-previous=on
service=whatever". That remains significantly different from before
85c54287a, because many more parameters will be re-used, and thus not be
possible for service entries to replace. But I think this is (mostly?)
intentional. In any case, since libpq does not report where it got parameter
values from, it's hard to do differently. Per bug #16936 from Jasen Betts.
As with the previous patches, back-patch to all supported branches. (9.5 is
unfortunately now out of support, so this won't get fixed there.) Discussion:
[https://postgr.es/m/16936-3f524322a53a29f0@postgresql.org](https://postgr.es/m/16936-3f524322a53a29f0@postgresql.org)
[https://git.postgresql.org/pg/commitdiff/ea80138545043c0cfcff8405b15626796f2695fe](https://git.postgresql.org/pg/commitdiff/ea80138545043c0cfcff8405b15626796f2695fe)

Peter Geoghegan pushed:

- Recycle nbtree pages deleted during same VACUUM. Maintain a simple array of
metadata about pages that were deleted during nbtree VACUUM's current
btvacuumscan() call. Use this metadata at the end of btvacuumscan() to
attempt to place newly deleted pages in the FSM without further delay. It
might not yet be safe to place any of the pages in the FSM by then (they may
not be deemed recyclable), but we have little to lose and plenty to gain by
trying. In practice there is a very good chance that this will work out when
vacuuming larger indexes, where scanning the index naturally takes quite a
while. This commit doesn't change the page recycling invariants; it merely
improves the efficiency of page recycling within the confines of the existing
design. Recycle safety is a part of nbtree's implementation of what Lanin &
Shasha call "the drain technique". The design happens to use transaction IDs
(they're stored in deleted pages), but that in itself doesn't align the cutoff
for recycle safety to any of the XID-based cutoffs used by VACUUM (e.g.,
OldestXmin). All that matters is whether or not _other_ backends might be
able to observe various inconsistencies in the tree structure (that they
cannot just detect and recover from by moving right). Recycle safety is
purely a question of maintaining the consistency (or the apparent consistency)
of a physical data structure. Note that running a simple serial test case
involving a large range DELETE followed by a VACUUM VERBOSE will probably show
that any newly deleted nbtree pages are not yet reusable/recyclable. This is
expected in the absence of even one concurrent XID assignment. It is an old
implementation restriction. In practice it's unlikely to be the thing that
makes recycling remain unsafe, at least with larger indexes, where recycling
newly deleted pages during the same VACUUM actually matters. An important
high-level goal of this commit (as well as related recent commits e5d8a999 and
9f3665fb) is to make expensive deferred cleanup operations in index AMs rare
in general. If index vacuuming frequently depends on the next VACUUM
operation finishing off work that the current operation started, then the
general behavior of index vacuuming is hard to predict. This is relevant to
ongoing work that adds a vacuumlazy.c mechanism to skip index vacuuming in
certain cases. Anything that makes the real world behavior of index vacuuming
simpler and more linear will also make top-down modeling in vacuumlazy.c more
robust. Author: Peter Geoghegan <pg(at)bowt(dot)ie> Reviewed-By: Masahiko Sawada
<sawada(dot)mshk(at)gmail(dot)com> Discussion:
[https://postgr.es/m/CAH2-Wzk76_P=67iUscb1UN44-gyZL-KgpsXbSxq_bdcMa7Q+wQ@mail.gmail.com](https://postgr.es/m/CAH2-Wzk76_P=67iUscb1UN44-gyZL-KgpsXbSxq_bdcMa7Q+wQ@mail.gmail.com)
[https://git.postgresql.org/pg/commitdiff/9dd963ae2534e9614f0abeccaafbd39f1b93ff8a](https://git.postgresql.org/pg/commitdiff/9dd963ae2534e9614f0abeccaafbd39f1b93ff8a)

- nbtree VACUUM: Cope with buggy opclasses. Teach nbtree VACUUM to press on with
vacuuming in the event of a page deletion attempt that fails to "re-find" a
downlink for its child/target page. There is no good reason to treat this as
an irrecoverable error. But there is a good reason not to: pressing on at
this point removes any question of VACUUM not making progress solely due to
misbehavior from user-defined operator class code. Discussion:
[https://postgr.es/m/CAH2-Wzma5G9CTtMjbrXTwOym+U=aWg-R7=-htySuztgoJLvZXg@mail.gmail.com](https://postgr.es/m/CAH2-Wzma5G9CTtMjbrXTwOym+U=aWg-R7=-htySuztgoJLvZXg@mail.gmail.com)
[https://git.postgresql.org/pg/commitdiff/5b861baa550a369e04bf67fbe83f3a5a8c742fb4](https://git.postgresql.org/pg/commitdiff/5b861baa550a369e04bf67fbe83f3a5a8c742fb4)

Michaël Paquier pushed:

- Fix timeline assignment in checkpoints with 2PC transactions. Any transactions
found as still prepared by a checkpoint have their state data read from the
WAL records generated by PREPARE TRANSACTION before being moved into their new
location within pg_twophase/. While reading such records, the WAL reader uses
the callback read_local_xlog_page() to read a page, that is shared across
various parts of the system. This callback, since 1148e22a, has introduced an
update of ThisTimeLineID when reading a record while in recovery, which is
potentially helpful in the context of cascading WAL senders. This update of
ThisTimeLineID interacts badly with the checkpointer if a promotion happens
while some 2PC data is read from its record, as, by changing ThisTimeLineID,
any follow-up WAL records would be written to an timeline older than the
promoted one. This results in consistency issues. For instance, a subsequent
server restart would cause a failure in finding a valid checkpoint record,
resulting in a PANIC, for instance. This commit changes the code reading the
2PC data to reset the timeline once the 2PC record has been read, to prevent
messing up with the static state of the checkpointer. It would be tempting to
do the same thing directly in read_local_xlog_page(). However, based on the
discussion that has led to 1148e22a, users may rely on the updates of
ThisTimeLineID when a WAL record page is read in recovery, so changing this
callback could break some cases that are working currently. A TAP test
reproducing the issue is added, relying on a PITR to precisely trigger a
promotion with a prepared transaction still tracked. Per discussion with
Heikki Linnakangas, Kyotaro Horiguchi, Fujii Masao and myself. Author:
Soumyadeep Chakraborty, Jimmy Yih, Kevin Yeap Discussion:
[https://postgr.es/m/CAE-ML+_EjH_fzfq1F3RJ1=XaaNG=-Jz-i3JqkNhXiLAsM3z-Ew@mail.gmail.com](https://postgr.es/m/CAE-ML+_EjH_fzfq1F3RJ1=XaaNG=-Jz-i3JqkNhXiLAsM3z-Ew@mail.gmail.com)
Backpatch-through: 10
[https://git.postgresql.org/pg/commitdiff/595b9cba2ab0cdd057e02d3c23f34a8bcfd90a2d](https://git.postgresql.org/pg/commitdiff/595b9cba2ab0cdd057e02d3c23f34a8bcfd90a2d)

- Simplify TAP tests of kerberos with expected log file contents. The TAP tests
of kerberos rely on the logs generated by the backend to check various
connection scenarios. In order to make sure that a given test does not
overlap with the log contents generated by a previous test, the test suite
relied on a logic with the logging collector and a rotation of the log files
to ensure the uniqueness of the log generated with a wait phase. Parsing the
log contents for expected patterns is a problem that has been solved in a
simpler way by PostgresNode::issues_sql_like() where the log file is truncated
before checking for the contents generated, with the backend sending its
output to a log file given by pg_ctl instead. This commit switches the
kerberos test suite to use such a method, removing any wait phase and
simplifying the whole logic, resulting in less code. If a failure happens in
the tests, the contents of the logs are still showed to the user at the moment
of the failure thanks to like(), so this has no impact on debugging
capabilities. I have bumped into this issue while reviewing a different patch
set aiming at extending the kerberos test suite to check for multiple log
patterns instead of one now. Author: Michael Paquier Reviewed-by: Stephen
Frost, Bharath Rupireddy Discussion:
[https://postgr.es/m/YFXcq2vBTDGQVBNC@paquier.xyz](https://postgr.es/m/YFXcq2vBTDGQVBNC@paquier.xyz)
[https://git.postgresql.org/pg/commitdiff/11e1577a576fec6307aa0bfcde7333e63f907fa7](https://git.postgresql.org/pg/commitdiff/11e1577a576fec6307aa0bfcde7333e63f907fa7)

- Fix new TAP test for 2PC transactions and PITRs on Windows. The test added by
595b9cb forgot that on Windows it is necessary to set up pg_hba.conf (see
PostgresNode::set_replication_conf) with a specific entry or base backups
fail. Any node that requires to support replication just needs to pass down
allows_streaming at initialization. This updates the test to do so. Simplify
things a bit while on it. Per buildfarm member fairywren. Any Windows hosts
running this test would have failed, and I have reproduced the problem as
well. Backpatch-through: 10
[https://git.postgresql.org/pg/commitdiff/992d353a190c551db39bcab2dec0ecf14fbc7a40](https://git.postgresql.org/pg/commitdiff/992d353a190c551db39bcab2dec0ecf14fbc7a40)

- Fix concurrency issues with WAL segment recycling on Windows. This commit is
mostly a revert of aaa3aed, that switched the routine doing the internal
renaming of recycled WAL segments to use on Windows a combination of
CreateHardLinkA() plus unlink() instead of rename(). As reported by several
users of Postgres 13, this is causing concurrency issues when manipulating WAL
segments, mostly in the shape of the following error: LOG: could not rename
file "pg_wal/000000XX000000YY000000ZZ": Permission denied This moves back to
a logic where a single rename() (well, pgrename() for Windows) is used. This
issue has proved to be hard to hit when I tested it, facing it only once with
an archive_command that was not able to do its work, so it is
environment-sensitive. The reporters of this issue have been able to confirm
that the situation improved once we switched back to a single rename(). In
order to check things, I have provided to the reporters a patched build based
on 13.2 with aaa3aed reverted, to test if the error goes away, and an
unpatched build of 13.2 to test if the error still showed up (just to make
sure that I did not mess up my build process). Extra thanks to Fujii Masao
for pointing out what looked like the culprit commit, and to all the reporters
for taking the time to test what I have sent them. Reported-by: Andrus, Guy
Burgess, Yaroslav Pashinsky, Thomas Trenz Reviewed-by: Tom Lane, Andres Freund
Discussion:
[https://postgr.es/m/3861ff1e-0923-7838-e826-094cc9bef737@hot.ee](https://postgr.es/m/3861ff1e-0923-7838-e826-094cc9bef737@hot.ee)
Discussion:
[https://postgr.es/m/16874-c3eecd319e36a2bf@postgresql.org](https://postgr.es/m/16874-c3eecd319e36a2bf@postgresql.org)
Discussion:
[https://postgr.es/m/095ccf8d-7f58-d928-427c-b17ace23cae6@burgess.co.nz](https://postgr.es/m/095ccf8d-7f58-d928-427c-b17ace23cae6@burgess.co.nz)
Discussion:
[https://postgr.es/m/16927-67c570d968c99567%40postgresql.org](https://postgr.es/m/16927-67c570d968c99567%40postgresql.org)
Discussion:
[https://postgr.es/m/YFBcRbnBiPdGZvfW@paquier.xyz](https://postgr.es/m/YFBcRbnBiPdGZvfW@paquier.xyz)
Backpatch-through: 13
[https://git.postgresql.org/pg/commitdiff/909b449e00fc2f71e1a38569bbddbb6457d28485](https://git.postgresql.org/pg/commitdiff/909b449e00fc2f71e1a38569bbddbb6457d28485)

- Add per-index stats information in verbose logs of autovacuum. Once a
relation's autovacuum is completed, the logs include more information about
this relation state if the threshold of log_autovacuum_min_duration (or its
relation option) is reached, with for example contents about the statistics of
the VACUUM operation for the relation, WAL and system usage. This commit adds
more information about the statistics of the relation's indexes, with one line
of logs generated for each index. The index stats were already calculated,
but not printed in the context of autovacuum yet. While on it, some
refactoring is done to keep track of the index statistics directly within
LVRelStats, simplifying some routines related to parallel VACUUMs. Author:
Masahiko Sawada Reviewed-by: Michael Paquier, Euler Taveira Discussion:
[https://postgr.es/m/CAD21AoAy6SxHiTivh5yAPJSUE4S=QRPpSZUdafOSz0R+fRcM6Q@mail.gmail.com](https://postgr.es/m/CAD21AoAy6SxHiTivh5yAPJSUE4S=QRPpSZUdafOSz0R+fRcM6Q@mail.gmail.com)
[https://git.postgresql.org/pg/commitdiff/5aed6a1fc214913de9ac69c1717dc64a2483e16d](https://git.postgresql.org/pg/commitdiff/5aed6a1fc214913de9ac69c1717dc64a2483e16d)

- Reword slightly logs generated for index stats in autovacuum. Using "remain"
is confusing, as it implies that the index file can shrink. Instead, use "in
total". Per discussion with Peter Geoghegan. Discussion:
[https://postgr.es/m/CAH2-WzkYgHZzpGOwR14CScJsjaQpvJrEkEfkh_=wGhzLb=yVdQ@mail.gmail.com](https://postgr.es/m/CAH2-WzkYgHZzpGOwR14CScJsjaQpvJrEkEfkh_=wGhzLb=yVdQ@mail.gmail.com)
[https://git.postgresql.org/pg/commitdiff/99dd75fb99baa9188971cf47779ed8d7a5e6eb29](https://git.postgresql.org/pg/commitdiff/99dd75fb99baa9188971cf47779ed8d7a5e6eb29)

- Sanitize the term "combo CID" in code comments. Combo CIDs were referred in
the code comments using different terms across various places of the code, so
unify a bit the term used with what is currently in use in some of the
READMEs. Author: "Hou, Zhijie" Discussion:
[https://postgr.es/m/1d42865c91404f46af4562532fdbea31@G08CNEXMBPEKD05.g08.fujitsu.local](https://postgr.es/m/1d42865c91404f46af4562532fdbea31@G08CNEXMBPEKD05.g08.fujitsu.local)
[https://git.postgresql.org/pg/commitdiff/a1999a01bb56c5f5451116abe61b892b2eec5e49](https://git.postgresql.org/pg/commitdiff/a1999a01bb56c5f5451116abe61b892b2eec5e49)

Noah Misch pushed:

- Make a test endure log_error_verbosity=verbose. Back-patch to v13, which
introduced the test code in question.
[https://git.postgresql.org/pg/commitdiff/e3f4aec027891f794328050e62c9bbbe4ae02811](https://git.postgresql.org/pg/commitdiff/e3f4aec027891f794328050e62c9bbbe4ae02811)

- Merge similar algorithms into roles_is_member_of(). The next commit would have
complicated two or three algorithms, so take this opportunity to consolidate.
No functional changes. Reviewed by John Naylor. Discussion:
[https://postgr.es/m/20201228043148.GA1053024@rfd.leadboat.com](https://postgr.es/m/20201228043148.GA1053024@rfd.leadboat.com)
[https://git.postgresql.org/pg/commitdiff/f687bf61ed4dc75ec074c387f848147da2097e13](https://git.postgresql.org/pg/commitdiff/f687bf61ed4dc75ec074c387f848147da2097e13)

- Add "pg_database_owner" default role. Membership consists, implicitly, of the
current database owner. Expect use in template databases. Once
pg_database_owner has rights within a template, each owner of a database
instantiated from that template will exercise those rights. Reviewed by John
Naylor. Discussion:
[https://postgr.es/m/20201228043148.GA1053024@rfd.leadboat.com](https://postgr.es/m/20201228043148.GA1053024@rfd.leadboat.com)
[https://git.postgresql.org/pg/commitdiff/a14a0118a1fecf4066e53af52ed0f188607d0c4b](https://git.postgresql.org/pg/commitdiff/a14a0118a1fecf4066e53af52ed0f188607d0c4b)

Fujii Masao pushed:

- pgbench: Improve error-handling in \sleep command. This commit improves
pgbench \sleep command so that it handles the following three cases more
properly. (1) When only one argument was specified in \sleep command and
it's not a number, previously pgbench reported a confusing error message
like "unrecognized time unit, must be us, ms or s". This commit fixes this
so that more proper error message like "invalid sleep time, must be an
integer" is reported. (2) When two arguments were specified in \sleep command
and the first argument was not a number, previously pgbench treated
that argument as the sleep time 0. No error was reported in this case.
This commit fixes this so that an error is thrown in this case. (3) When
a variable was specified as the first argument in \sleep command and the
variable stored non-digit value, previously pgbench treated that argument
as the sleep time 0. No error was reported in this case. This commit fixes
this so that an error is thrown in this case. Author: Kota Miyake
Reviewed-by: Hayato Kuroda, Alvaro Herrera, Fujii Masao Discussion:
[https://postgr.es/m/23b254daf20cec4332a2d9168505dbc9@oss.nttdata.com](https://postgr.es/m/23b254daf20cec4332a2d9168505dbc9@oss.nttdata.com)
[https://git.postgresql.org/pg/commitdiff/8c6eda2d1c926be76baa79c28521275323bd26fd](https://git.postgresql.org/pg/commitdiff/8c6eda2d1c926be76baa79c28521275323bd26fd)

- pg_waldump: Fix bug in per-record statistics. pg_waldump --stats=record
identifies a record by a combination of the RmgrId and the four bits of the
xl_info field of the record. But XACT records use the first bit of those four
bits for an optional flag variable, and the following three bits for the
opcode to identify a record. So previously the same type of XACT record could
have different four bits (three bits are the same but the first one bit is
different), and which could cause pg_waldump --stats=record to show two lines
of per-record statistics for the same XACT record. This is a bug. This commit
changes pg_waldump --stats=record so that it processes only XACT record
differently, i.e., filters the opcode out of xl_info and uses a combination of
the RmgrId and those three bits as the identifier of a record, only for XACT
record. For other records, the four bits of the xl_info field are still used.
Back-patch to all supported branches. Author: Kyotaro Horiguchi Reviewed-by:
Shinya Kato, Fujii Masao Discussion:
[https://postgr.es/m/2020100913412132258847@highgo.ca](https://postgr.es/m/2020100913412132258847@highgo.ca)
[https://git.postgresql.org/pg/commitdiff/51893c8463501fc9a38e39cc097773dbdfb9db82](https://git.postgresql.org/pg/commitdiff/51893c8463501fc9a38e39cc097773dbdfb9db82)

- Change the type of WalReceiverWaitStart wait event from Client to IPC.
Previously the type of this wait event was Client. But while this wait event
is being reported, walreceiver process is waiting for the startup process to
set initial data for streaming replication. It's not waiting for any activity
on a socket connected to a user application or walsender. So this commit
changes the type for WalReceiverWaitStart wait event to IPC. Author: Fujii
Masao Reviewed-by: Kyotaro Horiguchi Discussion:
[https://postgr.es/m/cdacc27c-37ff-f1a4-20e2-ce19933abfcc@oss.nttdata.com](https://postgr.es/m/cdacc27c-37ff-f1a4-20e2-ce19933abfcc@oss.nttdata.com)
[https://git.postgresql.org/pg/commitdiff/1e3e8b51bda8ddd59984230f876f199c9ce3166a](https://git.postgresql.org/pg/commitdiff/1e3e8b51bda8ddd59984230f876f199c9ce3166a)

- Log when GetNewOidWithIndex() fails to find unused OID many times.
GetNewOidWithIndex() generates a new OID one by one until it finds one not in
the relation. If there are very long runs of consecutive existing OIDs,
GetNewOidWithIndex() needs to iterate many times in the loop to find unused
OID. Since TOAST table can have a large number of entries and there can be
such long runs of OIDs, there is the case where it takes so many iterations to
find new OID not in TOAST table. Furthermore if all (i.e., 2^32) OIDs are
already used, GetNewOidWithIndex() enters something like busy loop and repeats
the iterations until at least one OID is marked as unused. There are some
reported troubles caused by a large number of iterations in
GetNewOidWithIndex(). For example, when inserting a billion of records into
the table, all the backends doing that insertion operation got hang with 100%
CPU usage at some point. Previously there was no easy way to detect that
GetNewOidWithIndex() failed to find unused OID many times. So, for example,
gdb full backtrace of hanged backends needed to be taken, in order to
investigate that trouble. This is inconvenient and may not be available in
some production environments. To provide easy way for that, this commit makes
GetNewOidWithIndex() log that it iterates more than GETNEWOID_LOG_THRESHOLD
but have not yet found OID unused in the relation. Also this commit makes it
repeat logging with exponentially increasing intervals until it iterates more
than GETNEWOID_LOG_MAX_INTERVAL, and makes it finally repeat logging every
GETNEWOID_LOG_MAX_INTERVAL unless an unused OID is found. Those macro
variables are used not to fill up the server log with the similar messages.
In the discusion at pgsql-hackers, there was another idea to report the lots
of iterations in GetNewOidWithIndex() via wait event. But since
GetNewOidWithIndex() traverses indexes to find unused OID and which will do
I/O, acquire locks, etc, which will overwrite the wait event and reset it to
nothing once done. So that idea doesn't work well, and we didn't adopt it.
Author: Tomohiro Hiramitsu Reviewed-by: Tatsuhito Kasahara, Kyotaro Horiguchi,
Tom Lane, Fujii Masao Discussion:
[https://postgr.es/m/16722-93043fb459a41073@postgresql.org](https://postgr.es/m/16722-93043fb459a41073@postgresql.org)
[https://git.postgresql.org/pg/commitdiff/7fbcee1b2d5f1012c67942126881bd492e95077e](https://git.postgresql.org/pg/commitdiff/7fbcee1b2d5f1012c67942126881bd492e95077e)

- Rename wait event WalrcvExit to WalReceiverExit. Commit de829ddf23 added wait
event WalrcvExit. But its name is not consistent with other wait events like
WalReceiverMain or WalReceiverWaitStart, etc. So this commit renames
WalrcvExit to WalReceiverExit. Author: Fujii Masao Reviewed-by: Thomas Munro
Discussion:
[https://postgr.es/m/cced9995-8fa2-7b22-9d91-3f22a2b8c23c@oss.nttdata.com](https://postgr.es/m/cced9995-8fa2-7b22-9d91-3f22a2b8c23c@oss.nttdata.com)
[https://git.postgresql.org/pg/commitdiff/84007043fc1b1be68dad5d0a78269347c12094b6](https://git.postgresql.org/pg/commitdiff/84007043fc1b1be68dad5d0a78269347c12094b6)

- Improve connection denied error message during recovery. Previously when an
archive recovery or a standby was starting and reached the consistent recovery
state but hot_standby was configured to off, the error message when a client
connectted was "the database system is starting up", which was needless
confusing and not really all that accurate either. This commit improves the
connection denied error message during recovery, as follows, so that the users
immediately know that their servers are configured to deny those connections.
- If hot_standby is disabled, the error message "the database system is not
accepting connections" and the detail message "Hot standby mode is
disabled." are output when clients connect while an archive recovery or a
standby is running. - If hot_standby is enabled, the error message "the
database system is not yet accepting connections" and the detail message
"Consistent recovery state has not been yet reached." are output when
clients connect until the consistent recovery state is reached and
postmaster starts accepting read only connections. This commit doesn't change
the connection denied error message of "the database system is starting up"
during normal server startup and crash recovery. Because it's still suitable
for those situations. Author: James Coleman Reviewed-by: Alvaro Herrera,
Andres Freund, David Zhang, Tom Lane, Fujii Masao Discussion:
[https://postgr.es/m/CAAaqYe8h5ES_B=F_zDT+Nj9XU7YEwNhKhHA2RE4CFhAQ93hfig@mail.gmail.com](https://postgr.es/m/CAAaqYe8h5ES_B=F_zDT+Nj9XU7YEwNhKhHA2RE4CFhAQ93hfig@mail.gmail.com)
[https://git.postgresql.org/pg/commitdiff/df9384492b89aac370ab9d12eb89375aeb38a1d4](https://git.postgresql.org/pg/commitdiff/df9384492b89aac370ab9d12eb89375aeb38a1d4)

- Fix bug in WAL replay of COMMIT_TS_SETTS record. Previously the WAL replay of
COMMIT_TS_SETTS record called TransactionTreeSetCommitTsData() with the
argument write_xlog=true, which generated and wrote new COMMIT_TS_SETTS
record. This should not be acceptable because it's during recovery. This
commit fixes the WAL replay of COMMIT_TS_SETTS record so that it calls
TransactionTreeSetCommitTsData() with write_xlog=false and doesn't generate
new WAL during recovery. Back-patch to all supported branches. Reported-by:
lx zou <zoulx1982(at)163(dot)com> Author: Fujii Masao Reviewed-by: Alvaro Herrera
Discussion:
[https://postgr.es/m/16931-620d0f2fdc6108f1@postgresql.org](https://postgr.es/m/16931-620d0f2fdc6108f1@postgresql.org)
[https://git.postgresql.org/pg/commitdiff/438fc4a39c3905b7af88bb848bc5aeb1308a017d](https://git.postgresql.org/pg/commitdiff/438fc4a39c3905b7af88bb848bc5aeb1308a017d)

Robert Haas pushed:

- More code cleanup for configurable TOAST compression. Remove unused macro. Fix
confusion about whether a TOAST compression method is identified by an OID or
a char. Justin Pryzby Discussion:
[http://postgr.es/m/20210321235544.GD4203@telsasoft.com](http://postgr.es/m/20210321235544.GD4203@telsasoft.com)
[https://git.postgresql.org/pg/commitdiff/226e2be3876d0bda3dc33d16dfa0bed246b7b74f](https://git.postgresql.org/pg/commitdiff/226e2be3876d0bda3dc33d16dfa0bed246b7b74f)

- docs: Fix omissions related to configurable TOAST compression. Previously, the
default_toast_compression GUC was not documented, and neither was pg_dump's
new --no-toast-compression option. Justin Pryzby and Robert Haas Discussion:
[http://postgr.es/m/20210321235544.GD4203@telsasoft.com](http://postgr.es/m/20210321235544.GD4203@telsasoft.com)
[https://git.postgresql.org/pg/commitdiff/24f0e395ac5892cd12e8914646fe921fac5ba23d](https://git.postgresql.org/pg/commitdiff/24f0e395ac5892cd12e8914646fe921fac5ba23d)

- Error on invalid TOAST compression in CREATE or ALTER TABLE. The previous
coding treated an invalid compression method name as equivalent to the
default, which is certainly not right. Justin Pryzby Discussion:
[http://postgr.es/m/20210321235544.GD4203@telsasoft.com](http://postgr.es/m/20210321235544.GD4203@telsasoft.com)
[https://git.postgresql.org/pg/commitdiff/a4d5284a10b5096585f1bbf1bf723954e9d6c2e0](https://git.postgresql.org/pg/commitdiff/a4d5284a10b5096585f1bbf1bf723954e9d6c2e0)

- Improve pg_amcheck's TAP test 003_check.pl. Disable autovacuum, because we
don't want it to run against intentionally corrupted tables. Also, before
corrupting the tables, run pg_amcheck and ensure that it passes. Otherwise, if
something unexpected happens when we check the corrupted tables, it's not so
clear whether it would have also happened before we corrupted them. Mark
Dilger Discussion:
[http://postgr.es/m/AA5506CE-7D2A-42E4-A51D-358635E3722D@enterprisedb.com](http://postgr.es/m/AA5506CE-7D2A-42E4-A51D-358635E3722D@enterprisedb.com)
[https://git.postgresql.org/pg/commitdiff/87d90ac61fa113ffc886efcdb391c522c1982991](https://git.postgresql.org/pg/commitdiff/87d90ac61fa113ffc886efcdb391c522c1982991)

- Tidy up more loose ends related to configurable TOAST compression. Change the
default_toast_compression GUC to be an enum rather than a string. Earlier,
uncommitted versions of the patch supported using CREATE ACCESS METHOD to add
new compression methods to a running system, but that idea was dropped before
commit. So, we can simplify the GUC handling as well, which has the nice side
effect of improving the error messages. While updating the documentation to
reflect the new GUC type, also move it back to the right place in the list. I
moved this while revising what became commit
24f0e395ac5892cd12e8914646fe921fac5ba23d, but apparently the intended ordering
is "alphabetical" rather than "whatever Robert thinks looks nice." Rejigger
things to avoid having access/toast_compression.h depend on utils/guc.h, so
that we don't end up with every file that includes it also depending on
something largely unrelated. Move a few inline functions back into the C
source file partly to help reduce dependencies and partly just to avoid
clutter. A few very minor cosmetic fixes. Original patch by Justin Pryzby,
but very heavily edited by me, and reverse reviewed by him and also reviewed
by by Tom Lane. Discussion:
[http://postgr.es/m/CA+TgmoYp=GT_ztUCeZg2i4hkHAQv8o=-nVJ1-TKWTG1zQOmOpg@mail.gmail.com](http://postgr.es/m/CA+TgmoYp=GT_ztUCeZg2i4hkHAQv8o=-nVJ1-TKWTG1zQOmOpg@mail.gmail.com)
[https://git.postgresql.org/pg/commitdiff/e5595de03ec6ce60afde980ae05e9353a1501fdf](https://git.postgresql.org/pg/commitdiff/e5595de03ec6ce60afde980ae05e9353a1501fdf)

- Fix interaction of TOAST compression with expression indexes. Before, trying
to compress a value for insertion into an expression index would crash. Dilip
Kumar, with some editing by me. Report by Jaime Casanova. Discussion:
[http://postgr.es/m/CAJKUy5gcs0zGOp6JXU2mMVdthYhuQpFk=S3V8DOKT=LZC1L36Q@mail.gmail.com](http://postgr.es/m/CAJKUy5gcs0zGOp6JXU2mMVdthYhuQpFk=S3V8DOKT=LZC1L36Q@mail.gmail.com)
[https://git.postgresql.org/pg/commitdiff/5db1fd7823a1a12e2bdad98abc8e102fd71ffbda](https://git.postgresql.org/pg/commitdiff/5db1fd7823a1a12e2bdad98abc8e102fd71ffbda)

Tomáš Vondra pushed:

- Move bsearch_arg to src/port. Until now the bsearch_arg function was used only
in extended statistics code, so it was defined in that code. But we already
have qsort_arg in src/port, so let's move it next to it.
[https://git.postgresql.org/pg/commitdiff/bfa2cee784125047771db2768fcf7f04d8bd6bb4](https://git.postgresql.org/pg/commitdiff/bfa2cee784125047771db2768fcf7f04d8bd6bb4)

- Pass all scan keys to BRIN consistent function at once. This commit changes
how we pass scan keys to BRIN consistent function. Instead of passing them one
by one, we now pass all scan keys for a given attribute at once. That makes
the consistent function a bit more complex, as it has to loop through the
keys, but it does allow more elaborate opclasses that can use multiple keys to
eliminate ranges much more effectively. The existing BRIN opclasses (minmax,
inclusion) don't really benefit from this change. The primary purpose is to
allow future opclases to benefit from seeing all keys at once. This does
change the BRIN API, because the signature of the consistent function changes
(a new parameter with number of scan keys). So this breaks existing opclasses,
and will require supporting two variants of the code for different PostgreSQL
versions. We've considered supporting two variants of the consistent, but
we've decided not to do that. Firstly, there's another patch that moves
handling of NULL values from the opclass, which means the opclasses need to be
updated anyway. Secondly, we're not aware of any out-of-core BRIN opclasses,
so it does not seem worth the extra complexity. Bump catversion, because of
pg_proc changes. Author: Tomas Vondra <tomas(dot)vondra(at)postgresql(dot)org>
Reviewed-by: Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> Reviewed-by: Mark Dilger
<hornschnorter(at)gmail(dot)com> Reviewed-by: Alexander Korotkov
<aekorotkov(at)gmail(dot)com> Reviewed-by: John Naylor <john(dot)naylor(at)enterprisedb(dot)com>
Reviewed-by: Nikita Glukhov <n(dot)gluhov(at)postgrespro(dot)ru> Discussion:
[https://postgr.es/m/c1138ead-7668-f0e1-0638-c3be3237e812@2ndquadrant.com](https://postgr.es/m/c1138ead-7668-f0e1-0638-c3be3237e812@2ndquadrant.com)
[https://git.postgresql.org/pg/commitdiff/a1c649d889bdf6e74e9382e1e28574d7071568de](https://git.postgresql.org/pg/commitdiff/a1c649d889bdf6e74e9382e1e28574d7071568de)

- Move IS [NOT] NULL handling from BRIN support functions. The handling of IS
[NOT] NULL clauses is independent of an opclass, and most of the code was
exactly the same in both minmax and inclusion. So instead move the code from
support procedures to the AM. This simplifies the code - especially the
support procedures - quite a bit, as they don't need to care about NULL values
and flags at all. It also means the IS [NOT] NULL clauses can be evaluated
without invoking the support procedure. Author: Tomas Vondra
<tomas(dot)vondra(at)postgresql(dot)org> Author: Nikita Glukhov <n(dot)gluhov(at)postgrespro(dot)ru>
Reviewed-by: Nikita Glukhov <n(dot)gluhov(at)postgrespro(dot)ru> Reviewed-by: Mark Dilger
<hornschnorter(at)gmail(dot)com> Reviewed-by: Alexander Korotkov
<aekorotkov(at)gmail(dot)com> Reviewed-by: Masahiko Sawada
<masahiko(dot)sawada(at)enterprisedb(dot)com> Reviewed-by: John Naylor
<john(dot)naylor(at)enterprisedb(dot)com> Discussion:
[https://postgr.es/m/c1138ead-7668-f0e1-0638-c3be3237e812@2ndquadrant.com](https://postgr.es/m/c1138ead-7668-f0e1-0638-c3be3237e812@2ndquadrant.com)
[https://git.postgresql.org/pg/commitdiff/72ccf55cb99c6450dfb77f2f8f4a28b5c049ef7a](https://git.postgresql.org/pg/commitdiff/72ccf55cb99c6450dfb77f2f8f4a28b5c049ef7a)

- Optimize allocations in bringetbitmap. The bringetbitmap function allocates
memory for various purposes, which may be quite expensive, depending on the
number of scan keys. Instead of allocating them separately, allocate one bit
chunk of memory an carve it into smaller pieces as needed - all the pieces
have the same lifespan, and it saves quite a bit of CPU and memory overhead.
Author: Tomas Vondra <tomas(dot)vondra(at)postgresql(dot)org> Reviewed-by: Alvaro Herrera
<alvherre(at)alvh(dot)no-ip(dot)org> Reviewed-by: Mark Dilger <hornschnorter(at)gmail(dot)com>
Reviewed-by: Alexander Korotkov <aekorotkov(at)gmail(dot)com> Reviewed-by: Masahiko
Sawada <masahiko(dot)sawada(at)enterprisedb(dot)com> Reviewed-by: John Naylor
<john(dot)naylor(at)enterprisedb(dot)com> Discussion:
[https://postgr.es/m/c1138ead-7668-f0e1-0638-c3be3237e812@2ndquadrant.com](https://postgr.es/m/c1138ead-7668-f0e1-0638-c3be3237e812@2ndquadrant.com)
[https://git.postgresql.org/pg/commitdiff/8e4b332e88b8339408a3aa8c62bc93d96b67c808](https://git.postgresql.org/pg/commitdiff/8e4b332e88b8339408a3aa8c62bc93d96b67c808)

- Use correct spelling of statistics kind. A couple error messages and comments
used 'statistic kind', not the correct 'statistics kind'. Fix and backpatch
all the way back to 10, where extended statistics were introduced.
Backpatch-through: 10
[https://git.postgresql.org/pg/commitdiff/a5f002ad9a2ddb501148a12281efbaacec6f6397](https://git.postgresql.org/pg/commitdiff/a5f002ad9a2ddb501148a12281efbaacec6f6397)

- Convert Typ from array to list in bootstrap. It's a bit easier and more
convenient to free and reload a List, compared to a plain array. This will be
helpful when allowing catalogs to contain composite types. Author: Justin
Pryzby Reviewed-by: Dean Rasheed, Tomas Vondra Discussion:
[https://postgr.es/m/ad7891d2-e90c-b446-9fe2-7419143847d7%40enterprisedb.com](https://postgr.es/m/ad7891d2-e90c-b446-9fe2-7419143847d7%40enterprisedb.com)
[https://git.postgresql.org/pg/commitdiff/e1a5e65703ce884529340819f6268d24f43ef8f7](https://git.postgresql.org/pg/commitdiff/e1a5e65703ce884529340819f6268d24f43ef8f7)

- Allow composite types in catalog bootstrap. When resolving types during
catalog bootstrap, try to reload the pg_type contents if a type is not found.
That allows catalogs to contain composite types, e.g. row types for other
catalogs. Author: Justin Pryzby Reviewed-by: Dean Rasheed, Tomas Vondra
Discussion:
[https://postgr.es/m/ad7891d2-e90c-b446-9fe2-7419143847d7%40enterprisedb.com](https://postgr.es/m/ad7891d2-e90c-b446-9fe2-7419143847d7%40enterprisedb.com)
[https://git.postgresql.org/pg/commitdiff/79f6a942bdb958fbd7ef6870d5bf2e3cefb65da5](https://git.postgresql.org/pg/commitdiff/79f6a942bdb958fbd7ef6870d5bf2e3cefb65da5)

- Remove unnecessary pg_amproc BRIN minmax entries. The BRIN minmax opclasses
included amproc entries with mismatching left and right types, but those
happen to be unnecessary. The opclasses only need cross-type operators, not
cross-type support procedures. Discovered when trying to define equivalent
BRIN operator families in an extension. Catversion bump, because of pg_amproc
changes. Author: Tomas Vondra Reviewed-by: Alvaro Herrera Discussion:
[https://postgr.es/m/78c357ab-3395-8433-e7b3-b2cfcc9fdc23%40enterprisedb.com](https://postgr.es/m/78c357ab-3395-8433-e7b3-b2cfcc9fdc23%40enterprisedb.com)
[https://git.postgresql.org/pg/commitdiff/a68dfa27d42fb7b7611fd1206d2356fc124ed390](https://git.postgresql.org/pg/commitdiff/a68dfa27d42fb7b7611fd1206d2356fc124ed390)

- Support the old signature of BRIN consistent function. Commit a1c649d889
changed the signature of the BRIN consistent function by adding a new required
parameter. Treating the parameter as optional, which would make the change
backwards incompatibile, was rejected with the justification that there are
few out-of-core extensions, so it's not worth adding making the code more
complex, and it's better to deal with that in the extension. But after
further thought, that would be rather problematic, because pg_upgrade simply
dumps catalog contents and the same version of an extension needs to work on
both PostgreSQL versions. Supporting both variants of the consistent function
(with 3 or 4 arguments) makes that possible. The signature is not the only
thing that changed, as commit 72ccf55cb9 moved handling of IS [NOT] NULL keys
from the support procedures. But this change is backward compatible - handling
the keys in exension is unnecessary, but harmless. The consistent function
will do a bit of unnecessary work, but it should be very cheap. This also
undoes most of the changes to the existing opclasses (minmax and inclusion),
making them use the old signature again. This should make backpatching
simpler. Catversion bump, because of changes in pg_amproc. Author: Tomas
Vondra <tomas(dot)vondra(at)postgresql(dot)org> Author: Nikita Glukhov
<n(dot)gluhov(at)postgrespro(dot)ru> Reviewed-by: Mark Dilger <hornschnorter(at)gmail(dot)com>
Reviewed-by: Alexander Korotkov <aekorotkov(at)gmail(dot)com> Reviewed-by: Masahiko
Sawada <masahiko(dot)sawada(at)enterprisedb(dot)com> Reviewed-by: John Naylor
<john(dot)naylor(at)enterprisedb(dot)com> Discussion:
[https://postgr.es/m/c1138ead-7668-f0e1-0638-c3be3237e812@2ndquadrant.com](https://postgr.es/m/c1138ead-7668-f0e1-0638-c3be3237e812@2ndquadrant.com)
[https://git.postgresql.org/pg/commitdiff/a681e3c107aa97eb554f118935c4d2278892c3dd](https://git.postgresql.org/pg/commitdiff/a681e3c107aa97eb554f118935c4d2278892c3dd)

- BRIN bloom indexes. Adds a BRIN opclass using a Bloom filter to summarize the
range. Indexes using the new opclasses allow only equality queries (similar to
hash indexes), but that works fine for data like UUID, MAC addresses etc. for
which range queries are not very common. This also means the indexes work for
data that is not well correlated to physical location within the table, or
perhaps even entirely random (which is a common issue with existing BRIN
minmax opclasses). It's possible to specify opclass parameters with the usual
Bloom filter parameters, i.e. the desired false-positive rate and the expected
number of distinct values per page range. CREATE TABLE t (a int); CREATE
INDEX ON t USING brin (a int4_bloom_ops(false_positive_rate = 0.05,
n_distinct_per_range = 100)); The opclasses do not operate on the indexed
values directly, but compute a 32-bit hash first, and the Bloom filter is
built on the hash value. Collisions should not be a huge issue though, as the
number of distinct values in a page ranges is usually fairly small. Bump
catversion, due to various catalog changes. Author: Tomas Vondra
<tomas(dot)vondra(at)postgresql(dot)org> Reviewed-by: Alvaro Herrera
<alvherre(at)alvh(dot)no-ip(dot)org> Reviewed-by: Alexander Korotkov
<aekorotkov(at)gmail(dot)com> Reviewed-by: Sokolov Yura <y(dot)sokolov(at)postgrespro(dot)ru>
Reviewed-by: Nico Williams <nico(at)cryptonector(dot)com> Reviewed-by: John Naylor
<john(dot)naylor(at)enterprisedb(dot)com> Discussion:
[https://postgr.es/m/c1138ead-7668-f0e1-0638-c3be3237e812@2ndquadrant.com](https://postgr.es/m/c1138ead-7668-f0e1-0638-c3be3237e812@2ndquadrant.com)
Discussion:
[https://postgr.es/m/5d78b774-7e9c-c94e-12cf-fef51cc89b1a%402ndquadrant.com](https://postgr.es/m/5d78b774-7e9c-c94e-12cf-fef51cc89b1a%402ndquadrant.com)
[https://git.postgresql.org/pg/commitdiff/77b88cd1bb9041a735f24072150cacfa06c699a3](https://git.postgresql.org/pg/commitdiff/77b88cd1bb9041a735f24072150cacfa06c699a3)

- BRIN minmax-multi indexes. Adds BRIN opclasses similar to the existing minmax,
except that instead of summarizing the page range into a single [min,max]
range, the summary consists of multiple ranges and/or points, allowing gaps.
This allows more efficient handling of data with poor correlation to physical
location within the table and/or outlier values, for which the regular minmax
opclassed tend to work poorly. It's possible to specify the number of values
kept for each page range, either as a single point or an interval boundary.
CREATE TABLE t (a int); CREATE INDEX ON t USING brin (a
int4_minmax_multi_ops(values_per_range=16)); When building the summary, the
values are combined into intervals with the goal to minimize the "covering"
(sum of interval lengths), using a support procedure computing distance
between two values. Bump catversion, due to various catalog changes. Author:
Tomas Vondra <tomas(dot)vondra(at)postgresql(dot)org> Reviewed-by: Alvaro Herrera
<alvherre(at)alvh(dot)no-ip(dot)org> Reviewed-by: Alexander Korotkov
<aekorotkov(at)gmail(dot)com> Reviewed-by: Sokolov Yura <y(dot)sokolov(at)postgrespro(dot)ru>
Reviewed-by: John Naylor <john(dot)naylor(at)enterprisedb(dot)com> Discussion:
[https://postgr.es/m/c1138ead-7668-f0e1-0638-c3be3237e812@2ndquadrant.com](https://postgr.es/m/c1138ead-7668-f0e1-0638-c3be3237e812@2ndquadrant.com)
Discussion:
[https://postgr.es/m/5d78b774-7e9c-c94e-12cf-fef51cc89b1a%402ndquadrant.com](https://postgr.es/m/5d78b774-7e9c-c94e-12cf-fef51cc89b1a%402ndquadrant.com)
[https://git.postgresql.org/pg/commitdiff/ab596105b55f1d7fbd5a66b66f65227d210b047d](https://git.postgresql.org/pg/commitdiff/ab596105b55f1d7fbd5a66b66f65227d210b047d)

- Fix alignment in BRIN minmax-multi deserialization. The deserialization failed
to ensure correct alignment, as it assumed it can simply point into the
serialized value. The serialization however ignores alignment and copies just
the significant bytes in order to make the result as small as possible. This
caused failures on systems that are sensitive to mialigned addresses, like
sparc, or with address sanitizer enabled. Fixed by copying the serialized
data to ensure proper alignment. While at it, fix an issue with serialization
on big endian machines, using the same store_att_byval/fetch_att trick as
extended statistics. Discussion:
[https://postgr.es/0c8c3304-d3dd-5e29-d5ac-b50589a23c8c%40enterprisedb.com](https://postgr.es/0c8c3304-d3dd-5e29-d5ac-b50589a23c8c%40enterprisedb.com)
[https://git.postgresql.org/pg/commitdiff/73b96bad4af8fd113a36e4633dd3312001c115dc](https://git.postgresql.org/pg/commitdiff/73b96bad4af8fd113a36e4633dd3312001c115dc)

- Fix ndistinct estimates with system attributes. When estimating the number of
groups using extended statistics, the code was discarding information about
system attributes. This led to strange situation that SELECT 1 FROM t
GROUP BY ctid; could have produced higher estimate (equal to
pg_class.reltuples) than SELECT 1 FROM t GROUP BY a, b, ctid; with
extended statistics on (a,b). Fixed by retaining information about the system
attribute. Backpatch all the way to 10, where extended statistics were
introduced. Author: Tomas Vondra Backpatch-through: 10
[https://git.postgresql.org/pg/commitdiff/33e52ad9a32929a6d14dfd98a8440d57028f2e3e](https://git.postgresql.org/pg/commitdiff/33e52ad9a32929a6d14dfd98a8440d57028f2e3e)

- Reduce duration of stats_ext regression tests. The regression tests of
extended statistics were taking a fair amount of time, due to using fairly
large data sets with a couple thousand rows. So far this was fine, but with
tests for statistics on expressions the duration would get a bit excessive.
So reduce the size of some of the tests that will be used to test expressions,
to keep the duration under control. Done in a separate commit before adding
the statistics on expressions, to make it clear which estimates are expected
to change. Author: Tomas Vondra Discussion:
[https://postgr.es/m/ad7891d2-e90c-b446-9fe2-7419143847d7%40enterprisedb.com](https://postgr.es/m/ad7891d2-e90c-b446-9fe2-7419143847d7%40enterprisedb.com)
[https://git.postgresql.org/pg/commitdiff/98376c18f12e562421b5c77e619248e8b7aae3c6](https://git.postgresql.org/pg/commitdiff/98376c18f12e562421b5c77e619248e8b7aae3c6)

- Extended statistics on expressions. Allow defining extended statistics on
expressions, not just just on simple column references. With this commit,
expressions are supported by all existing extended statistics kinds, improving
the same types of estimates. A simple example may look like this: CREATE
TABLE t (a int); CREATE STATISTICS s ON mod(a,10), mod(a,20) FROM t;
ANALYZE t; The collected statistics are useful e.g. to estimate queries with
those expressions in WHERE or GROUP BY clauses: `SELECT * FROM t WHERE
mod(a,10) = 0 AND mod(a,20) = 0;` SELECT 1 FROM t GROUP BY mod(a,10),
mod(a,20); This introduces new internal statistics kind 'e' (expressions)
which is built automatically when the statistics object definition includes
any expressions. This represents single-expression statistics, as if there was
an expression index (but without the index maintenance overhead). The
statistics is stored in pg_statistics_ext_data as an array of composite types,
which is possible thanks to 79f6a942bd. CREATE STATISTICS allows building
statistics on a single expression, in which case in which case it's not
possible to specify statistics kinds. A new system view pg_stats_ext_exprs
can be used to display expression statistics, similarly to pg_stats and
pg_stats_ext views. ALTER TABLE ... ALTER COLUMN ... TYPE now treats indexes
the same way it treats indexes, i.e. it drops and recreates the statistics.
This means all statistics are reset, and we no longer try to preserve at least
the functional dependencies. This should not be a major issue in practice, as
the functional dependencies actually rely on per-column statistics, which were
always reset anyway. Author: Tomas Vondra Reviewed-by: Justin Pryzby, Dean
Rasheed, Zhihong Yu Discussion:
[https://postgr.es/m/ad7891d2-e90c-b446-9fe2-7419143847d7%40enterprisedb.com](https://postgr.es/m/ad7891d2-e90c-b446-9fe2-7419143847d7%40enterprisedb.com)
[https://git.postgresql.org/pg/commitdiff/a4d75c86bf15220df22de0a92c819ecef9db3849](https://git.postgresql.org/pg/commitdiff/a4d75c86bf15220df22de0a92c819ecef9db3849)

- Stabilize stats_ext test with other collations. The tests used string
concatenation to test statistics on expressions, but that made the tests
locale-dependent, e.g. because the ordering of '11' and '1X' depends on the
collation. This affected both the estimated and actual row couts, breaking
some of the tests. Fixed by replacing the string concatenation with upper()
function call, so that the text values contain only digits. Discussion:
[https://postgr.es/m/b650920b-2767-fbc3-c87a-cb8b5d693cbf%40enterprisedb.com](https://postgr.es/m/b650920b-2767-fbc3-c87a-cb8b5d693cbf%40enterprisedb.com)
[https://git.postgresql.org/pg/commitdiff/2a058e938c73bfb85bbc9fa93dea74788043ca6c](https://git.postgresql.org/pg/commitdiff/2a058e938c73bfb85bbc9fa93dea74788043ca6c)

Bruce Momjian pushed:

- Add macro RelationIsPermanent() to report relation permanence. Previously, to
check relation permanence, the Relation's Form_pg_class structure member
relpersistence was compared to the value RELPERSISTENCE_PERMANENT ("p"). This
commit adds the macro RelationIsPermanent() and is used in appropirate places
to simplify the code. This matches other `RelationIs*` macros. This macro will
be used in more places in future cluster file encryption patches. Discussion:
[https://postgr.es/m/20210318153134.GH20766@tamriel.snowman.net](https://postgr.es/m/20210318153134.GH20766@tamriel.snowman.net)
[https://git.postgresql.org/pg/commitdiff/95d77149c53545a74e0c84717cf8f925b8f6d632](https://git.postgresql.org/pg/commitdiff/95d77149c53545a74e0c84717cf8f925b8f6d632)

Amit Kapila pushed:

- Fix dangling pointer reference in stream_cleanup_files. We can't access the
entry after it is removed from dynahash. Author: Peter Smith Discussion:
[https://postgr.es/m/CAHut+Ps-pL++f6CJwPx2+vUqXuew=Xt-9Bi-6kCyxn+Fwi2M7w@mail.gmail.com](https://postgr.es/m/CAHut+Ps-pL++f6CJwPx2+vUqXuew=Xt-9Bi-6kCyxn+Fwi2M7w@mail.gmail.com)
[https://git.postgresql.org/pg/commitdiff/4b82ed6eca41220e50d4712ab929c20030b30d35](https://git.postgresql.org/pg/commitdiff/4b82ed6eca41220e50d4712ab929c20030b30d35)

- Revert "Enable parallel SELECT for "INSERT INTO ... SELECT ...".". To allow
inserts in parallel-mode this feature has to ensure that all the constraints,
triggers, etc. are parallel-safe for the partition hierarchy which is costly
and we need to find a better way to do that. Additionally, we could have used
existing cached information in some cases like indexes, domains, etc. to
determine the parallel-safety. List of commits reverted, in reverse
chronological order: ed62d3737c Doc: Update description for parallel insert
reloption. c8f78b6161 Add a new GUC and a reloption to enable inserts in
parallel-mode. c5be48f092 Improve FK trigger parallel-safety check added by
05c8482f7f. e2cda3c20a Fix use of relcache TriggerDesc field introduced by
commit 05c8482f7f. e4e87a32cc Fix valgrind issue in commit 05c8482f7f.
05c8482f7f Enable parallel SELECT for "INSERT INTO ... SELECT ...".
Discussion:
[https://postgr.es/m/E1lMiB9-0001c3-SY@gemulon.postgresql.org](https://postgr.es/m/E1lMiB9-0001c3-SY@gemulon.postgresql.org)
[https://git.postgresql.org/pg/commitdiff/26acb54a1368bf3706294400abca85b15c9233a6](https://git.postgresql.org/pg/commitdiff/26acb54a1368bf3706294400abca85b15c9233a6)

Peter Eisentraut pushed:

- Add bit_count SQL function. This function for bit and bytea counts the set
bits in the bit or byte string. Internally, we use the existing popcount
functionality. For the name, after some discussion, we settled on bit_count,
which also exists with this meaning in MySQL, Java, and Python. Author: David
Fetter <david(at)fetter(dot)org> Discussion:
[https://www.postgresql.org/message-id/flat/20201230105535(dot)GJ13234(at)fetter(dot)org](https://www.postgresql.org/message-id/flat/20201230105535(dot)GJ13234(at)fetter(dot)org)
[https://git.postgresql.org/pg/commitdiff/a6715af1e72da289474011be1e2d536f991eda34](https://git.postgresql.org/pg/commitdiff/a6715af1e72da289474011be1e2d536f991eda34)

- pgcrypto: Check for error return of px_cipher_decrypt(). This has previously
not been a problem (that anyone ever reported), but in future OpenSSL versions
(3.0.0), where legacy ciphers are/can be disabled, this is the place where
this is reported. So we need to catch the error here, otherwise the
higher-level functions would return garbage. The nearby encryption code
already handled errors similarly. Reviewed-by: Daniel Gustafsson
<daniel(at)yesql(dot)se> Discussion:
[https://www.postgresql.org/message-id/9e9c431c-0adc-7a6d-9b1a-915de1ba3fe7@enterprisedb.com](https://www.postgresql.org/message-id/9e9c431c-0adc-7a6d-9b1a-915de1ba3fe7@enterprisedb.com)
[https://git.postgresql.org/pg/commitdiff/22e1943f13b66df22ea4f8d15836411ba259263a](https://git.postgresql.org/pg/commitdiff/22e1943f13b66df22ea4f8d15836411ba259263a)

- Improve an error message. Make it the same as another nearby message.
[https://git.postgresql.org/pg/commitdiff/1509c6fc29c07d13c9a590fbd6f37c7576f58ba6](https://git.postgresql.org/pg/commitdiff/1509c6fc29c07d13c9a590fbd6f37c7576f58ba6)

- Add date_bin function. Similar to date_trunc, but allows binning by an
arbitrary interval rather than just full units. Author: John Naylor
<john(dot)naylor(at)enterprisedb(dot)com> Reviewed-by: David Fetter <david(at)fetter(dot)org>
Reviewed-by: Isaac Morland <isaac(dot)morland(at)gmail(dot)com> Reviewed-by: Tom Lane
<tgl(at)sss(dot)pgh(dot)pa(dot)us> Reviewed-by: Artur Zakirov <zaartur(at)gmail(dot)com> Discussion:
[https://www.postgresql.org/message-id/flat/CACPNZCt4buQFRgy6DyjuZS-2aPDpccRkrJBmgUfwYc1KiaXYxg(at)mail(dot)gmail(dot)com](https://www.postgresql.org/message-id/flat/CACPNZCt4buQFRgy6DyjuZS-2aPDpccRkrJBmgUfwYc1KiaXYxg(at)mail(dot)gmail(dot)com)
[https://git.postgresql.org/pg/commitdiff/49ab61f0bdc93984a8d36b602f6f2a15f09ebcc7](https://git.postgresql.org/pg/commitdiff/49ab61f0bdc93984a8d36b602f6f2a15f09ebcc7)

- Fix stray double semicolons. Reported-by: John Naylor
<john(dot)naylor(at)enterprisedb(dot)com>
[https://git.postgresql.org/pg/commitdiff/37c99d304dcbf12ab581ff031f394af93b750895](https://git.postgresql.org/pg/commitdiff/37c99d304dcbf12ab581ff031f394af93b750895)

- doc: Fix typo. Reported-by: Erik Rijkers <er(at)xs4all(dot)nl>
[https://git.postgresql.org/pg/commitdiff/5173e428928ce890be3e3d809b2d23d5f3c7da2f](https://git.postgresql.org/pg/commitdiff/5173e428928ce890be3e3d809b2d23d5f3c7da2f)

- Rename a parse node to be more general. A WHERE clause will be used for row
filtering in logical replication. We already have a similar node: 'WHERE
(condition here)'. Let's rename the node to a generic name and use it for row
filtering too. Author: Euler Taveira <euler(dot)taveira(at)enterprisedb(dot)com>
Discussion:
[https://www.postgresql.org/message-id/flat/CAHE3wggb715X+mK_DitLXF25B=jE6xyNCH4YOwM860JR7HarGQ(at)mail(dot)gmail(dot)com](https://www.postgresql.org/message-id/flat/CAHE3wggb715X+mK_DitLXF25B=jE6xyNCH4YOwM860JR7HarGQ(at)mail(dot)gmail(dot)com)
[https://git.postgresql.org/pg/commitdiff/91d1f2d302108f49006eedb8053522236dde77cc](https://git.postgresql.org/pg/commitdiff/91d1f2d302108f49006eedb8053522236dde77cc)

- Trim some extra whitespace in parser file.
[https://git.postgresql.org/pg/commitdiff/f2c7ce64ae9ba292c1846ae864cef1b8b37af1f3](https://git.postgresql.org/pg/commitdiff/f2c7ce64ae9ba292c1846ae864cef1b8b37af1f3)

- Improve consistency of SQL code capitalization.
[https://git.postgresql.org/pg/commitdiff/8df2f371141ea267627364cd00e1791054d82d7e](https://git.postgresql.org/pg/commitdiff/8df2f371141ea267627364cd00e1791054d82d7e)

Stephen Frost pushed:

- Change checkpoint_completion_target default to 0.9. Common recommendations are
that the checkpoint should be spread out as much as possible, provided we
avoid having it take too long. This change updates the default to 0.9 (from
0.5) to match that recommendation. There was some debate about possibly
removing the option entirely but it seems there may be some corner-cases where
having it set much lower to try to force the checkpoint to be as fast as
possible could result in fewer periods of time of reduced performance due to
kernel flushing. General agreement is that the "spread more" is the preferred
approach though and those who need to tune away from that value are much less
common. Reviewed-By: Michael Paquier, Peter Eisentraut, Tom Lane, David
Steele, Nathan Bossart Discussion:
[https://postgr.es/m/20201207175329.GM16415%40tamriel.snowman.net](https://postgr.es/m/20201207175329.GM16415%40tamriel.snowman.net)
[https://git.postgresql.org/pg/commitdiff/bbcc4eb2e08fb6e4535c7f84b2c00f3ad508bb9b](https://git.postgresql.org/pg/commitdiff/bbcc4eb2e08fb6e4535c7f84b2c00f3ad508bb9b)

- doc: Define TLS as an acronym. Commit c6763156589 added an acronym reference
for "TLS" but the definition was never added. Author: Daniel Gustafsson
Reviewed-by: Michael Paquier Backpatch-through: 9.6 Discussion:
[https://postgr.es/m/27109504-82DB-41A8-8E63-C0498314F5B0@yesql.se](https://postgr.es/m/27109504-82DB-41A8-8E63-C0498314F5B0@yesql.se)
[https://git.postgresql.org/pg/commitdiff/b64654d6c450eb9fb04c6e3456915790510af482](https://git.postgresql.org/pg/commitdiff/b64654d6c450eb9fb04c6e3456915790510af482)

Michael Meskes pushed:

- Add DECLARE STATEMENT command to ECPG. This command declares a SQL identifier
for a SQL statement to be used in other embedded SQL statements. The
identifier is linked to a connection. Author: Hayato Kuroda
<kuroda(dot)hayato(at)fujitsu(dot)com> Reviewed-by: Shawn Wang <shawn(dot)wang(dot)pg(at)gmail(dot)com>
Discussion:
[https://www.postgresql.org/message-id/flat/TY2PR01MB24438A52DB04E71D0E501452F5630(at)TY2PR01MB2443(dot)jpnprd01(dot)prod(dot)outlook(dot)com](https://www.postgresql.org/message-id/flat/TY2PR01MB24438A52DB04E71D0E501452F5630(at)TY2PR01MB2443(dot)jpnprd01(dot)prod(dot)outlook(dot)com)
[https://git.postgresql.org/pg/commitdiff/ad8305a43d1890768a613d3fb586b44f17360f29](https://git.postgresql.org/pg/commitdiff/ad8305a43d1890768a613d3fb586b44f17360f29)

- Need to step forward in the loop to get to an end.
[https://git.postgresql.org/pg/commitdiff/65c2ec6f30d9c0878a9ef83e0ec9a53e6b82d9d8](https://git.postgresql.org/pg/commitdiff/65c2ec6f30d9c0878a9ef83e0ec9a53e6b82d9d8)

Álvaro Herrera pushed:

- Remove StoreSingleInheritance reimplementation. I introduced this duplicate
code in commit 8b08f7d4820f for no good reason. Remove it, and backpatch to
11 where it was introduced. Author: Álvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org>
[https://git.postgresql.org/pg/commitdiff/a24ae3d7b9efb3b113c0d53030aa99de0d41b40a](https://git.postgresql.org/pg/commitdiff/a24ae3d7b9efb3b113c0d53030aa99de0d41b40a)

- Rework HeapTupleHeader macros to reuse itemptr.h. The original definitions
pointlessly disregarded existing ItemPointer macros that do the same thing.
Reported-by: Michael Paquier <michael(at)paquier(dot)xyz> Discussion:
[https://postgr.es/m/20210222201557.GA32655@alvherre.pgsql](https://postgr.es/m/20210222201557.GA32655@alvherre.pgsql)
[https://git.postgresql.org/pg/commitdiff/4669cacbd4b4b1baa1b7f2ea53d461433a1b6276](https://git.postgresql.org/pg/commitdiff/4669cacbd4b4b1baa1b7f2ea53d461433a1b6276)

- Let ALTER TABLE Phase 2 routines manage the relation pointer. Struct
AlteredRelationInfo gains a new Relation member, to be used only by Phase 2
(ATRewriteCatalogs); this allows ATExecCmd() subroutines open and close the
relation internally. A future commit will use this facility to implement an
ALTER TABLE subcommand that closes and reopens the relation across transaction
boundaries. (It is possible to keep the relation open past phase 2 to be used
by phase 3 instead of having to reopen it that point, but there are some minor
complications with that; it's not clear that there is much to be won from
doing that, though.) Author: Álvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org>
Discussion:
[https://postgr.es/m/20200803234854.GA24158@alvherre.pgsql](https://postgr.es/m/20200803234854.GA24158@alvherre.pgsql)
[https://git.postgresql.org/pg/commitdiff/cd03c6e94b09ff402cbc3ce8da5587f09f0b5e58](https://git.postgresql.org/pg/commitdiff/cd03c6e94b09ff402cbc3ce8da5587f09f0b5e58)

- Add comments for AlteredTableInfo->rel. The prior commit which introduced it
was pretty squalid in terms of code documentation, so add some comments.
[https://git.postgresql.org/pg/commitdiff/cc121d5596964f8aac93607e6f14607184558b16](https://git.postgresql.org/pg/commitdiff/cc121d5596964f8aac93607e6f14607184558b16)

- Document lock obtained during partition detach. On partition detach, we
acquire a SHARE lock on all tables that reference the partitioned table that
we're detaching a partition from, but failed to document this fact. My
oversight in commit f56f8f8da6af. Repair. Backpatch to 12. Author: Álvaro
Herrera <alvherre(at)alvh(dot)no-ip(dot)org> Discussion:
[https://postgr.es/m/20210325180244.GA12738@alvherre.pgsql](https://postgr.es/m/20210325180244.GA12738@alvherre.pgsql)
[https://git.postgresql.org/pg/commitdiff/650d623530c884c087c565f1d3b8cd76f8fe2b95](https://git.postgresql.org/pg/commitdiff/650d623530c884c087c565f1d3b8cd76f8fe2b95)

- ALTER TABLE ... DETACH PARTITION ... CONCURRENTLY. Allow a partition be
detached from its partitioned table without blocking concurrent queries, by
running in two transactions and only requiring ShareUpdateExclusive in the
partitioned table. Because it runs in two transactions, it cannot be used in
a transaction block. This is the main reason to use dedicated syntax: so that
users can choose to use the original mode if they need it. But also, it
doesn't work when a default partition exists (because an exclusive lock would
still need to be obtained on it, in order to change its partition constraint.)
In case the second transaction is cancelled or a crash occurs, there's ALTER
TABLE .. DETACH PARTITION .. FINALIZE, which executes the final steps. The
main trick to make this work is the addition of column
pg_inherits.inhdetachpending, initially false; can only be set true in the
first part of this command. Once that is committed, concurrent transactions
that use a PartitionDirectory will include or ignore partitions so marked: in
optimizer they are ignored if the row is marked committed for the snapshot; in
executor they are always included. As a result, and because of the way
PartitionDirectory caches partition descriptors, queries that were planned
before the detach will see the rows in the detached partition and queries that
are planned after the detach, won't. A CHECK constraint is created that
duplicates the partition constraint. This is probably not strictly necessary,
and some users will prefer to remove it afterwards, but if the partition is
re-attached to a partitioned table, the constraint needn't be rechecked.
Author: Álvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> Reviewed-by: Amit Langote
<amitlangote09(at)gmail(dot)com> Reviewed-by: Justin Pryzby <pryzby(at)telsasoft(dot)com>
Discussion:
[https://postgr.es/m/20200803234854.GA24158@alvherre.pgsql](https://postgr.es/m/20200803234854.GA24158@alvherre.pgsql)
[https://git.postgresql.org/pg/commitdiff/71f4c8c6f74ba021e55d35b1128d22fb8c6e1629](https://git.postgresql.org/pg/commitdiff/71f4c8c6f74ba021e55d35b1128d22fb8c6e1629)

# Pending Patches

Kyotaro HORIGUCHI sent in another revision of a patch to make async replica wait
for the lsn to be replayed.

Bharath Rupireddy sent in a patch to remove extra memset calls in BloomInitPage,
GinInitPage, and SpGistInitPage.

Hou Zhijie sent in another revision of a patch to avoid CommandCounterIncrement
in RI trigger when INSERT INTO referencing table.

Amul Sul sent in another revision of a patch to build the infrastructure that
will support ALTER SYSTEM READ {ONLY | WRITE}.

Amit Langote sent in another revision of a patch to fix an infelicity between
UPDATE ... RETURNING and moving tuples between partitions.

Greg Nancarrow sent in another revision of a patch to enable parallel INSERT and
or SELECT for INSERT INTO.

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

Tom Lane sent in another revision of a patch to allow an alias to be attached
directly to a JOIN ... USING per the SQL standard.

David Oksman sent in a patch to implement ALTER TABLE ... RENAME COLUMN IF
EXISTS.

Andrei Zubkov sent in two revisions of a patch to add statement entry timestamps
to pg_stat_statements.

Thomas Munro sent in another revision of a patch to add PSQL_WATCH_PAGER for
psql's \watch command.

Thomas Munro sent in four more revisions of a patch to detect dropped
connections while running queries.

Fujii Masao sent in a patch intended to fix a bug that manifested as Failed
assertion on standby while shutdown by making the startup process
call ShutdownRecoveryTransactionEnvironment() when it exits.

Peter Eisentraut sent in another revision of a patch to add a
result_format_auto_binary_types setting.

Jan Wieck sent in three more revisions of a patch to fix pg_upgrade to preserve
datdba.

Bertrand Drouvot sent in four more revisions of a patch to implement minimal
logical decoding on standbys.

Pavel Stěhule sent in two more revisions of a patch to implement schema
variables.

Marcus Wanner sent in two more revisions of a patch to add an xid argument to
the filter_prepare callback for output plugins.

Amul Sul sent in two more revisions of a patch to add an RelationGetSmgr()
inline function.

Peter Smith and Amit Kapila traded patches to add logical decoding of two-phase
transactions.

Euler Taveira de Oliveira and Peter Eisentraut traded patches to add row
filtering for logical replication.

Kyotaro HORIGUCHI sent in another revision of a patch to change the stats
collector's temporary storage from files to shared memory.

Masahiro Ikeda and Fujii Masao traded patches to make the WAL receiver report
WAL statistics.

Bruce Momjian and Julien Rouhaud traded patches to expose queryid in
pg_stat_activity, log_line_prefix, and verbose explain.

Atsushi Torikoshi sent in four more revisions of a patch to add a function,
pg_get_backend_memory_contexts(), which does what it says on the label.

Daniel Gustafsson sent in two more revisions of a patch to support NSS as a
libpq TLS backend.

Michaël Paquier and Jeevan Chalke traded patches to log authenticated identity
from all auth backends.

Stephen Frost sent in another revision of a patch to use a WaitLatch for
vacuum/autovacuum sleeping.

Stephen Frost sent in three more revisions of a patch to add a documentation
stub for the now obsolete recovery.conf.

Justin Pryzby sent in another revision of a patch to add an optional ACCESS
METHOD to CREATE TABLE ... LIKE.

Takayuki Tsunakawa sent in two more revisions of a patch to speed up COPY FROM
for the case of remote partitions.

Amit Langote sent in another revision of a patch to create foreign key triggers
in partitioned tables, and use this to enforce foreign key correctly during
cross-partition updates.

David Rowley sent sent in two more revisions of a patch to add a Result Cache
executor node.

Li Japin sent in another revision of a patch to implement ALTER SUBSCRIPTION ...
ADD/DROP PUBLICATION.

Tomáš Vondra sent in a patch to fix up an opclass storage type.

Fujii Masao sent in another revision of a patch to rename WalrcvExit wait_event
to WalReceiverExit.

Andrey V. Lepikhov sent in another revision of a patch to implement global
snapshots.

Atsushi Torikoshi sent in two more revisions of a patch to add plan type to
pg_stat_statements.

Denis Hirn sent in a patch to allow multiple recursive self-references in WITH
RECURSIVE.

Masahiro Ikeda and Fujii Masao traded patches to get pgstat to avoid writing on
SIGQUIT.

Kyotaro HORIGUCHI sent in another revision of a patch to protect syscache from
bloating with negative cache entries.

Hou Zhijie sent in another revision of a patch to add a nullif case for
eval_const_expressions.

Mark Dilger and Robert Haas traded patches to add a pg_amcheck contrib
application.

Daniel Gustafsson sent in another revision of a patch to refactor the SSL test
harness to allow for multiple libraries.

Pavel Stěhule sent in two more revisions of a patch to add routine labels.

Thomas Munro sent in four more revisions of a patch to make all SLRU buffer
sizes configurable.

Peter Geoghegan and Masahiko Sawada traded patches to centralize state for each
VACUUM, break lazy_scan_heap() up into functions, remove the tupgone special
case from vacuumlazy.c, and skip index vacuuming in some cases.

Kyotaro HORIGUCHI sent in another revision of a patch to implement in-place
table persistence change and add a new command, ALTER TABLE ALL IN TABLESPACE
SET LOGGED/UNLOGGED, to go with it.

Ashutosh Bapat sent in another revision of a patch to fix a memory leak in
decoding speculative inserts with TOAST.

Ekaterina Sokolova sent in another revision of a patch to add extra statistics
to explain for Nested Loop.

Pavel Borisov sent in two more revisions of a patch to implement covering
SP-GiST indexes, i.e. support for INCLUDE columns.

Marcus Wanner add a concurrent_abort callback for the output plugin.

Joel Jacobson sent in another revision of a patch to add views pg_permissions
and pg_ownerships.

Bharath Rupireddy sent in another revision of a patch to make the error messages
while adding tables to publications a bit more informative and consistent.

Kyotaro HORIGUCHI sent in another revision of a intended to fix a bug that
manifested as Walsender may fail to send wal to the end.

Jim Finnerty sent in another revision of a patch to add a capability to have
64-bit GUCs, use XID_FMT to format xids, and use ClogPageNumber in place of int
for type safety.

Sven Klemm sent in a patch to allow CustomScan nodes to signal whether they
support projection.

Andrew Dunstan and Nikita Glukhov traded patches to implement the JSON_TABLE
part of SQL/JSON.

Andrew Dunstan and Nikita Glukhov traded patches to implement the functions part
of SQL/JSON.

Amit Langote and Tom Lane traded patches to make updates in inheritance trees
scale better by overhauling how updates compute new tuples, and revise how
inherited update/delete are handled.

David Steele sent in two revisions of a patch to document the fact that backup
labels may need to be opened in binary mode on Windows.

Cai Mengjuan sent in a patch to update walrcv->flushedUpto each time when
requesting xlog streaming.

Andrew Dunstan sent in another revision of a patch to allow matching the whole
DN from a client certificate.

Masahiro Ikeda sent in a patch to improve the performance of reporting wal
stats.

Tomáš Vondra sent in a patch to show applied extended statistics in explain.

Noah Misch sent in another revision of a patch to add a public schema default
ACL.

Lætitia Avrot sent in two revisions of a patch to make it possible to dump only
functions using pg_dump.

Noah Misch sent in another revision of a patch to accept slightly-filled pages
for tuples larger than fillfactor.

Álvaro Herrera sent in two more revisions of a patch to add tracing capability
to libpq.

Kazutaka Onishi sent in another revision of a patch to make TRUNCATE on foreign
tables work.

Andrew Dunstan sent in another revision of a patch to implement global temporary
tables.

Yoan SULTAN sent in a patch to make it possible for pg_stat_statements to track
the most recent statement.

David Rowley sent in another revision of a patch to get better results from
valgrind leak tracking.