PostgreSQL Weekly News - July 18, 2021

# PostgreSQL Weekly News - July 18, 2021

[Person of the week](https://postgresql.life/post/jean-christophe_arnu/)

# PostgreSQL Jobs for July

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

# 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

Michaël Paquier pushed:

- pageinspect: Improve page_header() for pages of 32kB. ld_upper, ld_lower,
pd_special and the page size have been using smallint as return type, which
could cause those fields to return negative values in certain cases for builds
configures with a page size of 32kB. Bump pageinspect to 1.10. page_header()
is able to handle the correct return type of those fields at runtime when
using an older version of the extension, with some tests are added to cover
that. Author: Quan Zongliang Reviewed-by: Michael Paquier, Bharath Rupireddy
Discussion:
[https://postgr.es/m/8b8ec36e-61fe-14f9-005d-07bc85aa4eed@yeah.net](https://postgr.es/m/8b8ec36e-61fe-14f9-005d-07bc85aa4eed@yeah.net)
[https://git.postgresql.org/pg/commitdiff/127404fbe28455d6e8183fa58f3b7aefeba8f909](https://git.postgresql.org/pg/commitdiff/127404fbe28455d6e8183fa58f3b7aefeba8f909)

- Fix issues with Windows' stat() for files pending on deletion. The code
introduced by bed9075 to enhance the stat() implementation on Windows for file
sizes larger than 4GB fails to properly detect files pending for deletion with
its method based on NtQueryInformationFile() or
GetFileInformationByHandleEx(), as proved by Alexander Lakhin in a custom TAP
test of his own. The method used in the implementation of open() to sleep and
loop when when failing on ERROR_ACCESS_DENIED (EACCES) is showing much more
stability, so switch to this method. This could still lead to issues if the
permission problem stays around for much longer than the timeout of 1 second
used, but that should (hopefully) never happen in performance-critical paths.
Still, there could be a point in increasing the timeouts for the sake of
machines that handle heavy loads. Note that WIN32's open() now uses
microsoft_native_stat() as it should be similar to stat() when working around
issues with concurrent file deletions. I have spent some time testing this
patch with pgbench in combination of the SQL functions from genfile.c, as well
as running the TAP test provided on the thread with MSVC builds, and this
looks much more stable than the previous method. Author: Alexander Lakhin
Reviewed-by: Tom Lane, Michael Paquier, Justin Pryzby Discussion:
[https://postgr.es/m/c3427edf-d7c0-ff57-90f6-b5de3bb62709@gmail.com](https://postgr.es/m/c3427edf-d7c0-ff57-90f6-b5de3bb62709@gmail.com)
Backpatch-through: 14
[https://git.postgresql.org/pg/commitdiff/54fb8c7ddf152629021cab3ac3596354217b7d81](https://git.postgresql.org/pg/commitdiff/54fb8c7ddf152629021cab3ac3596354217b7d81)

- Revert "Fix issues with Windows' stat() for files pending on deletion". This
reverts commit 54fb8c7, as per the issues reported by fairywren when it comes
to MinGW because of the lack of microsoft_native_stat() there. Using just
stat() for MSVC is not sufficient to take care of the concurrency problems
with files pending on deletion. It may be possible to paint some __MINGW64__
in the code to switch to a different implementation of stat() in this build
context, but I am not sure either if relying on the implementation of stat()
in MinGW to take care of the problems we are trying to fix is enough or not.
So this needs more study. Discussion:
[https://postgr.es/m/YOvOlfRrIO0yGtgw@paquier.xyz](https://postgr.es/m/YOvOlfRrIO0yGtgw@paquier.xyz)
Backpatch-through: 14
[https://git.postgresql.org/pg/commitdiff/2c9b46c090e76c62f24563b9be2c34e6b92e9329](https://git.postgresql.org/pg/commitdiff/2c9b46c090e76c62f24563b9be2c34e6b92e9329)

- Install properly fe-auth-sasl.h. The internals of the frontend-side callbacks
for SASL are visible in libpq-int.h, but the header was not getting installed.
This would cause compilation failures for applications playing with the
internals of libpq. Issue introduced in 9fd8557. Author: Mikhail Kulagin
Reviewed-by: Jacob Champion Discussion:
[https://postgr.es/m/05ce01d777cb$40f31d60$c2d95820$@postgrespro.ru](https://postgr.es/m/05ce01d777cb$40f31d60$c2d95820$@postgrespro.ru)
[https://git.postgresql.org/pg/commitdiff/6c9c2831668345122fd0f92280b30f3bbe2dd4e6](https://git.postgresql.org/pg/commitdiff/6c9c2831668345122fd0f92280b30f3bbe2dd4e6)

- Remove unnecessary assertion in postmaster.c. A code path asserted that the
archiver was dead, but a check made that impossible to happen. Author:
Bharath Rupireddy Discussion:
[https://postgr.es/m/CALj2ACW=CYE1ars+2XyPTEPq0wQvru4c0dPZ=Nrn3EqNBkksvQ@mail.gmail.com](https://postgr.es/m/CALj2ACW=CYE1ars+2XyPTEPq0wQvru4c0dPZ=Nrn3EqNBkksvQ@mail.gmail.com)
Backpatch-throgh: 14
[https://git.postgresql.org/pg/commitdiff/dc2db1eac365b97c9129393acfe11102859f9e23](https://git.postgresql.org/pg/commitdiff/dc2db1eac365b97c9129393acfe11102859f9e23)

- Add TAP tests for ZLIB compression for pg_receivewal. There is a non-trivial
amount of code that handles ZLIB compression in pg_receivewal, from basics
like the format name, the calculation of the start streaming position and of
course the compression itself, but there was no automated coverage for it.
This commit introduces a set of conditional tests (if the build supports ZLIB)
to cover the creation of ZLIB-compressed WAL segments, the handling of the
partial, compressed, WAL segments and the compression operation in itself.
Note that there is an extra phase checking the validity of the generated files
by using directly a gzip command, passed down by the Makefile of
pg_receivewal. This part is skipped if the command cannot be found, something
likely going to happen on Windows with MSVC except if one sets the variable
GZIP_PROGRAM in the environment of the test. This set of tests will become
handy for upcoming patches that add more options for the compression methods
used by pg_receivewal, like LZ4, to make sure that no existing facilities are
broken. Author: Georgios Kokolatos Reviewed-by: Gilles Darold, Michael
Paquier Discussion:
[https://postgr.es/m/07BK3Mk5aEOsTwGaY77qBVyf9GjoEzn8TMgHLyPGfEFPIpTEmoQuP2P4c7teesjSg-LPeUafsp1flnPeQYINMSMB_UpggJDoduB5EDYBqaQ=@protonmail.com](https://postgr.es/m/07BK3Mk5aEOsTwGaY77qBVyf9GjoEzn8TMgHLyPGfEFPIpTEmoQuP2P4c7teesjSg-LPeUafsp1flnPeQYINMSMB_UpggJDoduB5EDYBqaQ=@protonmail.com)
[https://git.postgresql.org/pg/commitdiff/ffc9ddaea33f6dfd3dfa95828a0970fbb617bf8a](https://git.postgresql.org/pg/commitdiff/ffc9ddaea33f6dfd3dfa95828a0970fbb617bf8a)

- Fix portability issue with gzip in TAP test of pg_receivewal. The OpenBSD
implementation of gzip considers only files suffixed by "Z", "gz", "z", "tgz"
or "taz" as valid targets, discarding anything else and making a command using
--test exit with an error code of 512 if anything invalid is found. The test
introduced in ffc9dda tested a WAL segment suffixed as .gz.partial, enough to
make the test fail. Testing only a full segment is fine enough in terms of
coverage, so simplify the code by discarding the .gz.partial segment in this
check. This should be enough to make the test pass with OpenBSD environments.
Per report from curculio. Discussion:
[https://postgr.es/m/YPAdf9r5aJbDoHoq@paquier.xyz](https://postgr.es/m/YPAdf9r5aJbDoHoq@paquier.xyz)
[https://git.postgresql.org/pg/commitdiff/0da3c1bc3f7261d5157f5b86ade88e8b379f8686](https://git.postgresql.org/pg/commitdiff/0da3c1bc3f7261d5157f5b86ade88e8b379f8686)

- Disable tests involving ZLIB on Windows for pg_receivewal. As reported by
buildfarm member bowerbird, those tests are unstable on Windows. The failure
produced there points to a problem with gzflush(), that fails to sync a file
freshly-opened, with a gzFile properly opened. While testing this myself with
MSVC, I bumped into a different error where a file could simply not be opened,
so this makes me rather doubtful that testing this area on Windows is a good
idea if this finishes with random concurrency failures. This requires more
investigation, and keeping this buildfarm member red is not a good thing in
the long-term, so for now this just disables this set of tests on Windows.
Discussion:
[https://postgr.es/m/YPDLz2x3o1aX2wRh@paquier.xyz](https://postgr.es/m/YPDLz2x3o1aX2wRh@paquier.xyz)
[https://git.postgresql.org/pg/commitdiff/6cea447e6a10cd7ef511470e809a894a013e6a18](https://git.postgresql.org/pg/commitdiff/6cea447e6a10cd7ef511470e809a894a013e6a18)

Heikki Linnakangas pushed:

- Remove dead assignment to local variable. This should have been removed in
commit 7e30c186da, which split the loop into two. Only the first loop uses the
'from' variable; updating it in the second loop is bogus. It was never read
after the first loop, so this was harmless and surely optimized away by the
compiler, but let's be tidy. Backpatch to all supported versions. Author:
Ranier Vilela Discussion:
[https://www.postgresql.org/message-id/CAEudQAoWq%2BAL3BnELHu7gms2GN07k-np6yLbukGaxJ1vY-zeiQ%40mail.gmail.com](https://www.postgresql.org/message-id/CAEudQAoWq%2BAL3BnELHu7gms2GN07k-np6yLbukGaxJ1vY-zeiQ%40mail.gmail.com)
[https://git.postgresql.org/pg/commitdiff/4c64b51dc51f8ff7e3e51eebfe8d10469a577df1](https://git.postgresql.org/pg/commitdiff/4c64b51dc51f8ff7e3e51eebfe8d10469a577df1)

- In psql \copy from, send data to server in larger chunks. Previously, we would
send each line as a separate CopyData message. That's pretty wasteful if the
table is narrow, as each CopyData message has 5 bytes of overhead. For
efficiency, buffer up and pack 8 kB of input data into each CopyData message.
The server also sends each line as a separate CopyData message in COPY TO
STDOUT, and that's similarly wasteful. But that's documented in the FE/BE
protocol description, so changing that would be a wire protocol break.
Reviewed-by: Aleksander Alekseev Discussion:
[https://www.postgresql.org/message-id/40b2cec0-d0fb-3191-2ae1-9a3fe16a7e48%40iki.fi](https://www.postgresql.org/message-id/40b2cec0-d0fb-3191-2ae1-9a3fe16a7e48%40iki.fi)
[https://git.postgresql.org/pg/commitdiff/eec57115e4c866f26bdc8bcbe3e2e7be4c6d0450](https://git.postgresql.org/pg/commitdiff/eec57115e4c866f26bdc8bcbe3e2e7be4c6d0450)

Peter Eisentraut pushed:

- doc: Fix typo in function prototype.
[https://git.postgresql.org/pg/commitdiff/5b60cf35f566697030a2895dfe27dde2e34dd370](https://git.postgresql.org/pg/commitdiff/5b60cf35f566697030a2895dfe27dde2e34dd370)

- Fix lack of message pluralization.
[https://git.postgresql.org/pg/commitdiff/55b2a2340758da8ff11357d719d169f264ac7112](https://git.postgresql.org/pg/commitdiff/55b2a2340758da8ff11357d719d169f264ac7112)

- Fix some nonstandard C code indentation in grammar file.
[https://git.postgresql.org/pg/commitdiff/9aa8268faa0ec2904f55e85be5ec7b365c98edd1](https://git.postgresql.org/pg/commitdiff/9aa8268faa0ec2904f55e85be5ec7b365c98edd1)

- doc: Spell checking.
[https://git.postgresql.org/pg/commitdiff/46111fb7b57876a87eb0630ec12ad30950e38a39](https://git.postgresql.org/pg/commitdiff/46111fb7b57876a87eb0630ec12ad30950e38a39)

Tom Lane pushed:

- Replace RelationOpenSmgr() with RelationGetSmgr(). The idea behind this patch
is to design out bugs like the one fixed by commit 9d523119f. Previously,
once one did RelationOpenSmgr(rel), it was considered okay to access
rel->rd_smgr directly for some not-very-clear interval. But since that
pointer will be cleared by relcache flushes, we had bugs arising from
overreliance on a previous RelationOpenSmgr call still being effective. Now,
very little code except that in rel.h and relcache.c should ever touch the
rd_smgr field directly. The normal coding rule is to use RelationGetSmgr(rel)
and not expect the result to be valid for longer than one smgr function call.
There are a couple of places where using the function every single time seemed
like overkill, but they are now annotated with large warning comments. Amul
Sul, after an idea of mine. 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/f10f0ae420ee62400876ab34dca2c09c20dcd030](https://git.postgresql.org/pg/commitdiff/f10f0ae420ee62400876ab34dca2c09c20dcd030)

- Probe for preadv/pwritev in a more macOS-friendly way. Apple's mechanism for
dealing with functions that are available in only some OS versions confuses
AC_CHECK_FUNCS, and therefore AC_REPLACE_FUNCS. We can use AC_CHECK_DECLS
instead, so long as we enable -Werror=unguarded-availability-new. This allows
people compiling for macOS to control whether or not preadv/pwritev are used
by setting MACOSX_DEPLOYMENT_TARGET, rather than supplying a back-rev SDK.
(Of course, the latter still works, too.) James Hilliard Discussion:
[https://postgr.es/m/20210122193230.25295-1-james.hilliard1@gmail.com](https://postgr.es/m/20210122193230.25295-1-james.hilliard1@gmail.com)
[https://git.postgresql.org/pg/commitdiff/f014b1b9bb8eda4e82c1805969dbae2b07b7d54d](https://git.postgresql.org/pg/commitdiff/f014b1b9bb8eda4e82c1805969dbae2b07b7d54d)

- Rename debug_invalidate_system_caches_always to debug_discard_caches. The name
introduced by commit 4656e3d66 was agreed to be unreasonably long. To match
this change, rename initdb's recently-added --clobber-cache option to
--discard-caches. Discussion:
[https://postgr.es/m/1374320.1625430433@sss.pgh.pa.us](https://postgr.es/m/1374320.1625430433@sss.pgh.pa.us)
[https://git.postgresql.org/pg/commitdiff/d68a00391214be2020e49be4b55f761d47a5c229](https://git.postgresql.org/pg/commitdiff/d68a00391214be2020e49be4b55f761d47a5c229)

- Copy a Param's location field when replacing it with a Const. This allows
Param substitution to produce just the same result as writing a constant value
literally would have done. While it hardly matters so far as the current core
code is concerned, extensions might take more interest in node location
fields. Julien Rouhaud Discussion:
[https://postgr.es/m/20170311220932.GJ15188@nol.local](https://postgr.es/m/20170311220932.GJ15188@nol.local)
[https://git.postgresql.org/pg/commitdiff/be850f1822e4b54d1d570eefa8a7242788011634](https://git.postgresql.org/pg/commitdiff/be850f1822e4b54d1d570eefa8a7242788011634)

- Ensure HAVE_DECL_XXX macros in MSVC builds match those in Unix. Autoconf's
AC_CHECK_DECLS() always defines HAVE_DECL_whatever as 1 or 0, but some of the
entries in msvc/Solution.pm showed such symbols as "undef" instead of 0. Fix
that for consistency. There's no live bug in current usages AFAICS, but it's
not hard to imagine one creeping in if more-complex #if tests get added.
Back-patch to v13, which is as far back as Solution.pm contains this data.
The inconsistency still exists in the manually-filled pg_config_ext.h.win32
files of older branches; but as long as the problem is only latent, it doesn't
seem worth the trouble to clean things up there. Discussion:
[https://postgr.es/m/3185430.1626133592@sss.pgh.pa.us](https://postgr.es/m/3185430.1626133592@sss.pgh.pa.us)
[https://git.postgresql.org/pg/commitdiff/e529b2dc37ac80ccebd76cdbb14966d3b40819c9](https://git.postgresql.org/pg/commitdiff/e529b2dc37ac80ccebd76cdbb14966d3b40819c9)

- Replace explicit PIN entries in pg_depend with an OID range test. As of v14,
pg_depend contains almost 7000 "pin" entries recording the OIDs of built-in
objects. This is a fair amount of bloat for every database, and it adds time
to pg_depend lookups as well as initdb. We can get rid of all of those
entries in favor of an OID range check, i.e. "OIDs below FirstUnpinnedObjectId
are pinned". (template1 and the public schema are exceptions. Those
exceptions are now wired into IsPinnedObject() instead of initdb's code for
filling pg_depend, but it's the same amount of cruft either way.) The
contents of pg_shdepend are modified likewise. Discussion:
[https://postgr.es/m/3737988.1618451008@sss.pgh.pa.us](https://postgr.es/m/3737988.1618451008@sss.pgh.pa.us)
[https://git.postgresql.org/pg/commitdiff/a49d081235997c67e8aab7a523b17e8d1cb93184](https://git.postgresql.org/pg/commitdiff/a49d081235997c67e8aab7a523b17e8d1cb93184)

- Doc: document the current-transaction-modes GUCs. We had documentation of
default_transaction_isolation et al, but for some reason not of
transaction_isolation et al. AFAICS this is just an ancient oversight, so
repair. Per bug #17077 from Yanliang Lei. Discussion:
[https://postgr.es/m/17077-ade8e166a01e1374@postgresql.org](https://postgr.es/m/17077-ade8e166a01e1374@postgresql.org)
[https://git.postgresql.org/pg/commitdiff/665c5855e5820531f500dad50f29f377af537ecc](https://git.postgresql.org/pg/commitdiff/665c5855e5820531f500dad50f29f377af537ecc)

Thomas Munro pushed:

- Add PSQL_WATCH_PAGER for psql's \watch command. Allow a pager to be used by
the \watch command. This works but isn't very useful with traditional pagers
like "less", so use a different environment variable. The popular open source
tool "pspg" (also by Pavel) knows how to display the output if you set
PSQL_WATCH_PAGER="pspg --stream". To make \watch react quickly when the user
quits the pager or presses ^C, and also to increase the accuracy of its timing
and decrease the rate of useless context switches, change the main loop of the
\watch command to use sigwait() rather than a sleeping/polling loop, on Unix.
Supported on Unix only for now (like pspg). Author: Pavel Stehule
<pavel(dot)stehule(at)gmail(dot)com> Author: Thomas Munro <thomas(dot)munro(at)gmail(dot)com>
Discussion:
[https://postgr.es/m/CAFj8pRBfzUUPz-3gN5oAzto9SDuRSq-TQPfXU_P6h0L7hO%2BEhg%40mail.gmail.com](https://postgr.es/m/CAFj8pRBfzUUPz-3gN5oAzto9SDuRSq-TQPfXU_P6h0L7hO%2BEhg%40mail.gmail.com)
[https://git.postgresql.org/pg/commitdiff/7c09d2797ecdf779e5dc3289497be85675f3d134](https://git.postgresql.org/pg/commitdiff/7c09d2797ecdf779e5dc3289497be85675f3d134)

- Portability fixes for sigwait. Build farm animals running ancient HPUX and
Solaris have a non-standard sigwait() from draft versions of POSIX, so they
didn't like commit 7c09d279. To avoid the problem in general, only try to use
sigwait() if it's declared by <signal.h> and matches the expected declaration.
To select the modern declaration on Solaris (even in non-threaded programs),
move -D_POSIX_PTHREAD_SEMANTICS into the right place to affect all translation
units. Also fix the error checking. Modern sigwait() doesn't set errno.
Thanks to Tom Lane for help with this. Discussion:
[https://postgr.es/m/3187588.1626136248%40sss.pgh.pa.us](https://postgr.es/m/3187588.1626136248%40sss.pgh.pa.us)
[https://git.postgresql.org/pg/commitdiff/5865e064abfbbe11ebfc09881be009c0f69b4dc2](https://git.postgresql.org/pg/commitdiff/5865e064abfbbe11ebfc09881be009c0f69b4dc2)

David Rowley pushed:

- Fix theoretical bug in tuplesort. This fixes a theoretical bug in tuplesort.c
which, if a bounded sort was used in combination with a byval Datum sort
(tuplesort_begin_datum), when switching the sort to a bounded heap in
make_bounded_heap(), we'd call free_sort_tuple(). The problem was that when
sorting Datums of a byval type, the tuple is NULL and free_sort_tuple() would
free the memory for it regardless of that. This would result in a crash.
Here we fix that simply by adding a check to see if the tuple is NULL before
trying to disassociate and free any memory belonging to it. The reason this
bug is only theoretical is that nowhere in the current code base do we do
tuplesort_set_bound() when performing a Datum sort. However, let's backpatch a
fix for this as if any extension uses the code in this way then it's likely to
cause problems. Author: Ronan Dunklau Discussion:
[https://postgr.es/m/CAApHDvpdoqNC5FjDb3KUTSMs5dg6f+XxH4Bg_dVcLi8UYAG3EQ@mail.gmail.com](https://postgr.es/m/CAApHDvpdoqNC5FjDb3KUTSMs5dg6f+XxH4Bg_dVcLi8UYAG3EQ@mail.gmail.com)
Backpatch-through: 9.6, oldest supported version
[https://git.postgresql.org/pg/commitdiff/41469253e970b539a4ae75226dd4f226b7b2bc8c](https://git.postgresql.org/pg/commitdiff/41469253e970b539a4ae75226dd4f226b7b2bc8c)

- Robustify tuplesort's free_sort_tuple function. 41469253e went to the trouble
of removing a theoretical bug from free_sort_tuple by checking if the tuple
was NULL before freeing it. Let's make this a little more robust by also
setting the tuple to NULL so that should we be called again we won't end up
doing a pfree on the already pfree'd tuple. Per advice from Tom Lane.
Discussion:
[https://postgr.es/m/3188192.1626136953@sss.pgh.pa.us](https://postgr.es/m/3188192.1626136953@sss.pgh.pa.us)
Backpatch-through: 9.6, same as 41469253e
[https://git.postgresql.org/pg/commitdiff/5bd38d2f2846c7e387d04a16c4372da0de7b1221](https://git.postgresql.org/pg/commitdiff/5bd38d2f2846c7e387d04a16c4372da0de7b1221)

- Remove useless range checks on INT8 sequences. There's no point in checking if
an INT8 sequence has a seqmin and seqmax value is outside the range of the
minimum and maximum values for an int64 type. These both use the same
underlying types so an INT8 certainly cannot be outside the minimum and
maximum values supported by int64. This code is fairly harmless and it seems
likely that most compilers would optimize it out anyway, never-the-less, let's
remove it replacing it with a small comment to mention why the check is not
needed. Author: Greg Nancarrow, with the comment revised by David Rowley
Discussion:
[https://postgr.es/m/CAJcOf-c9KBUZ8ow_6e%3DWSfbbEyTKfqV%3DVwoFuODQVYMySHtusw%40mail.gmail.com](https://postgr.es/m/CAJcOf-c9KBUZ8ow_6e%3DWSfbbEyTKfqV%3DVwoFuODQVYMySHtusw%40mail.gmail.com)
[https://git.postgresql.org/pg/commitdiff/e0271d5f1e871dd61efc26bda8a0b556c7935901](https://git.postgresql.org/pg/commitdiff/e0271d5f1e871dd61efc26bda8a0b556c7935901)

- Change the name of the Result Cache node to Memoize. "Result Cache" was never
a great name for this node, but nobody managed to come up with another name
that anyone liked enough. That was until David Johnston mentioned "Node
Memoization", which Tom Lane revised to just "Memoize". People seem to like
"Memoize", so let's do the rename. Reviewed-by: Justin Pryzby Discussion:
[https://postgr.es/m/20210708165145.GG1176@momjian.us](https://postgr.es/m/20210708165145.GG1176@momjian.us)
Backpatch-through: 14, where Result Cache was introduced
[https://git.postgresql.org/pg/commitdiff/83f4fcc65503c5d4e5d5eefc8e7a70d3c9a6496f](https://git.postgresql.org/pg/commitdiff/83f4fcc65503c5d4e5d5eefc8e7a70d3c9a6496f)

Amit Kapila pushed:

- Add support for prepared transactions to built-in logical replication. To add
support for streaming transactions at prepare time into the built-in logical
replication, we need to do the following things: `*` Modify the output plugin
(pgoutput) to implement the new two-phase API callbacks, by leveraging the
extended replication protocol. `*` Modify the replication apply worker, to
properly handle two-phase transactions by replaying them on prepare. `*` Add a
new SUBSCRIPTION option "two_phase" to allow users to enable two-phase
transactions. We enable the two_phase once the initial data sync is over. We
however must explicitly disable replication of two-phase transactions during
replication slot creation, even if the plugin supports it. We don't need to
replicate the changes accumulated during this phase, and moreover, we don't
have a replication connection open so we don't know where to send the data
anyway. The streaming option is not allowed with this new two_phase option.
This can be done as a separate patch. We don't allow to toggle two_phase
option of a subscription because it can lead to an inconsistent replica. For
the same reason, we don't allow to refresh the publication once the two_phase
is enabled for a subscription unless copy_data option is false. Author: Peter
Smith, Ajin Cherian and Amit Kapila based on previous work by Nikhil Sontakke
and Stas Kelvich Reviewed-by: Amit Kapila, Sawada Masahiko, Vignesh C, Dilip
Kumar, Takamichi Osumi, Greg Nancarrow Tested-By: Haiying Tang Discussion:
[https://postgr.es/m/02DA5F5E-CECE-4D9C-8B4B-418077E2C010@postgrespro.ru](https://postgr.es/m/02DA5F5E-CECE-4D9C-8B4B-418077E2C010@postgrespro.ru)
Discussion:
[https://postgr.es/m/CAA4eK1+opiV4aFTmWWUF9h_32=HfPOW9vZASHarT0UA5oBrtGw@mail.gmail.com](https://postgr.es/m/CAA4eK1+opiV4aFTmWWUF9h_32=HfPOW9vZASHarT0UA5oBrtGw@mail.gmail.com)
[https://git.postgresql.org/pg/commitdiff/a8fd13cab0ba815e9925dc9676e6309f699b5f72](https://git.postgresql.org/pg/commitdiff/a8fd13cab0ba815e9925dc9676e6309f699b5f72)

Magnus Hagander pushed:

- Clarify description of pg_stat_statements columns. Reported-By: Peter
Eisentraut Backpatch-through: 14 Discussion:
[https://postgr.es/m/8f5e63b8-e8ed-0f80-d8c4-68222624c200@enterprisedb.com](https://postgr.es/m/8f5e63b8-e8ed-0f80-d8c4-68222624c200@enterprisedb.com)
[https://git.postgresql.org/pg/commitdiff/b4deefc39b933b9808645667117f2d8208092794](https://git.postgresql.org/pg/commitdiff/b4deefc39b933b9808645667117f2d8208092794)

John Naylor pushed:

- Remove unused function parameter in get_qual_from_partbound. Commit 0563a3a8b
changed how partition constraints were generated such that this function no
longer computes the mapping of parent attnos to child attnos. This is an
external function that extensions could use, so this is potentially a breaking
change. No external callers are known, however, and this will make it simpler
to write such callers in the future. Author: Hou Zhijie Reviewed-by: David
Rowley, Michael Paquier, Soumyadeep Chakraborty Discussion:
[https://www.postgresql.org/message-id/flat/OS0PR01MB5716A75A45BE46101A1B489894379(at)OS0PR01MB5716(dot)jpnprd01(dot)prod(dot)outlook(dot)com](https://www.postgresql.org/message-id/flat/OS0PR01MB5716A75A45BE46101A1B489894379(at)OS0PR01MB5716(dot)jpnprd01(dot)prod(dot)outlook(dot)com)
[https://git.postgresql.org/pg/commitdiff/c203dcddf997180000bc574a60313f3437e35af9](https://git.postgresql.org/pg/commitdiff/c203dcddf997180000bc574a60313f3437e35af9)

Dean Rasheed pushed:

- Improve reporting of "conflicting or redundant options" errors. When reporting
"conflicting or redundant options" errors, try to ensure that errposition() is
used, to help the user identify the offending option. Formerly, errposition()
was invoked in less than 60% of cases. This patch raises that to over 90%, but
there remain a few places where the ParseState is not readily available. Using
errdetail() might improve the error in such cases, but that is left as a task
for the future. Additionally, since this error is thrown from over 100 places
in the codebase, introduce a dedicated function to throw it, reducing code
duplication. Extracted from a slightly larger patch by Vignesh C. Reviewed by
Bharath Rupireddy, Alvaro Herrera, Dilip Kumar, Hou Zhijie, Peter Smith,
Daniel Gustafsson, Julien Rouhaud and me. Discussion:
[https://postgr.es/m/CALDaNm33FFSS5tVyvmkoK2cCMuDVxcui=gFrjti9ROfynqSAGA@mail.gmail.com](https://postgr.es/m/CALDaNm33FFSS5tVyvmkoK2cCMuDVxcui=gFrjti9ROfynqSAGA@mail.gmail.com)
[https://git.postgresql.org/pg/commitdiff/2bfb50b3df11399ed80347dd03bfaf8cd5acf962](https://git.postgresql.org/pg/commitdiff/2bfb50b3df11399ed80347dd03bfaf8cd5acf962)

- Improve error checking of CREATE COLLATION options. Check for conflicting or
redundant options, as we do for most other commands. Specifying any option
more than once is at best redundant, and quite likely indicates a bug in the
user's code. While at it, improve the error for conflicting locale options by
adding detail text (the same as for CREATE DATABASE). Bharath Rupireddy,
reviewed by Vignesh C. Some additional hacking by me. Discussion:
[https://postgr.es/m/CALj2ACWtL6fTLdyF4R_YkPtf1YEDb6FUoD5DGAki3rpD+sWqiA@mail.gmail.com](https://postgr.es/m/CALj2ACWtL6fTLdyF4R_YkPtf1YEDb6FUoD5DGAki3rpD+sWqiA@mail.gmail.com)
[https://git.postgresql.org/pg/commitdiff/ba620760c4c8ca90ff83ecf7e4d46f5ec4dabd7b](https://git.postgresql.org/pg/commitdiff/ba620760c4c8ca90ff83ecf7e4d46f5ec4dabd7b)

Alexander Korotkov pushed:

- Fix small inconsistencies in catalog definition of multirange operators. This
commit fixes the description of a couple of multirange operators and oprjoin
for another multirange operator. The change of oprjoin is more cosmetic since
both old and new functions return the same constant. These cosmetic changes
don't worth catalog incompatibility between 14beta2 and 14beta3. So,
catversion isn't bumped. Discussion:
[https://postgr.es/m/CAPpHfdv9OZEuZDqOQoUKpXhq%3Dmc-qa4gKCPmcgG5Vvesu7%3Ds1w%40mail.gmail.com](https://postgr.es/m/CAPpHfdv9OZEuZDqOQoUKpXhq%3Dmc-qa4gKCPmcgG5Vvesu7%3Ds1w%40mail.gmail.com)
Backpatch-throgh: 14
[https://git.postgresql.org/pg/commitdiff/768ea9bcf98120eef01a6deea9c5c6997b153ab1](https://git.postgresql.org/pg/commitdiff/768ea9bcf98120eef01a6deea9c5c6997b153ab1)

- Support for unnest(multirange). It has been spotted that multiranges lack of
ability to decompose them into individual ranges. Subscription and proper
expanded object representation require substantial work, and it's too late for
v14. This commit provides the implementation of unnest(multirange), which is
quite trivial. unnest(multirange) is defined as a polymorphic procedure.
Catversion is bumped. Reported-by: Jonathan S. Katz Discussion:
[https://postgr.es/m/flat/60258efe-bd7e-4886-82e1-196e0cac5433%40postgresql.org](https://postgr.es/m/flat/60258efe-bd7e-4886-82e1-196e0cac5433%40postgresql.org)
Author: Alexander Korotkov Reviewed-by: Justin Pryzby, Jonathan S. Katz,
Zhihong Yu, Tom Lane Reviewed-by: Alvaro Herrera
[https://git.postgresql.org/pg/commitdiff/9e3c217bd98da803709872a8612577d88a39329a](https://git.postgresql.org/pg/commitdiff/9e3c217bd98da803709872a8612577d88a39329a)

- Forgotten catversion bump for 9e3c217bd9.
[https://git.postgresql.org/pg/commitdiff/f157db862225a7bfe041ca3f7b73e913e2a8d8d6](https://git.postgresql.org/pg/commitdiff/f157db862225a7bfe041ca3f7b73e913e2a8d8d6)

Daniel Gustafsson pushed:

- docs: fix inconsistencies in markup and case. Ensure to properly mark up
function parameters in text with <parameter>, avoid using <acronym> for terms
which aren't acronyms and properly place the ", and" in a value list. The
acronym removal is a follow-up to commit fb72a7b8c3 which removed it for
minmax-multi. In passing, also fix an incorrectly cased word. Author:
Ekaterina Kiryanova <e(dot)kiryanova(at)postgrespro(dot)ru> Reviewed-by: Laurenz Albe
<laurenz(dot)albe(at)cybertec(dot)at> Discussion:
[https://postgr.es/m/c050ecbc-80b2-b360-3c1d-9fe6a6a11bb5@postgrespro.ru](https://postgr.es/m/c050ecbc-80b2-b360-3c1d-9fe6a6a11bb5@postgrespro.ru)
Backpatch-through: v14
[https://git.postgresql.org/pg/commitdiff/830ef61bd8d0ac4c89c21a895047c1b3a6b202f3](https://git.postgresql.org/pg/commitdiff/830ef61bd8d0ac4c89c21a895047c1b3a6b202f3)

Álvaro Herrera pushed:

- Advance old-segment horizon properly after slot invalidation. When some slots
are invalidated due to the max_slot_wal_keep_size limit, the old segment
horizon should move forward to stay within the limit. However, in commit
c6550776394e we forgot to call KeepLogSeg again to recompute the horizon after
invalidating replication slots. In cases where other slots remained, the
limits would be recomputed eventually for other reasons, but if all slots were
invalidated, the limits would not move at all afterwards. Repair. Backpatch
to 13 where the feature was introduced. Author: Kyotaro Horiguchi
<horikyota(dot)ntt(at)gmail(dot)com> Reported-by: Marcin Krupowicz <mk(at)071(dot)ovh>
Discussion:
[https://postgr.es/m/17103-004130e8f27782c9@postgresql.org](https://postgr.es/m/17103-004130e8f27782c9@postgresql.org)
[https://git.postgresql.org/pg/commitdiff/ead9e51e82363a0e492d56aee83ed11b3759a615](https://git.postgresql.org/pg/commitdiff/ead9e51e82363a0e492d56aee83ed11b3759a615)

- Preserve firing-on state when cloning row triggers to partitions. When
triggers are cloned from partitioned tables to their partitions, the
'tgenabled' flag (origin/replica/always/disable) was not propagated. Make it
so that the flag on the trigger on partition is initially set to the same
value as on the partitioned table. Add a test case to verify the behavior.
Backpatch to 11, where this appeared in commit 86f575948c77. Author: Álvaro
Herrera <alvherre(at)alvh(dot)no-ip(dot)org> Reported-by: Justin Pryzby
<pryzby(at)telsasoft(dot)com> Discussion:
[https://postgr.es/m/20200930223450.GA14848@telsasoft.com](https://postgr.es/m/20200930223450.GA14848@telsasoft.com)
[https://git.postgresql.org/pg/commitdiff/df80fa2ee50478391445cef03e42c1b3d64fccca](https://git.postgresql.org/pg/commitdiff/df80fa2ee50478391445cef03e42c1b3d64fccca)

- Fix pg_dump for disabled triggers on partitioned tables. pg_dump failed to
preserve the 'enabled' flag (which can be not only disabled, but also REPLICA
or ALWAYS) for partitions which had it changed from their respective parents.
Attempt to handle that by including a definition for such triggers in the
dump, but replace the standard CREATE TRIGGER line with an ALTER TRIGGER line.
Backpatch to 11, where these triggers can exist. In branches 11 and 12, pick
up a few test lines from commit b9b408c48724 to verify that pg_upgrade is okay
with these arrangements. Co-authored-by: Justin Pryzby <pryzby(at)telsasoft(dot)com>
Co-authored-by: Álvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> Discussion:
[https://postgr.es/m/20200930223450.GA14848@telsasoft.com](https://postgr.es/m/20200930223450.GA14848@telsasoft.com)
[https://git.postgresql.org/pg/commitdiff/f0e21f2f61675f4e56ae53d32ea54d587a7c2257](https://git.postgresql.org/pg/commitdiff/f0e21f2f61675f4e56ae53d32ea54d587a7c2257)

- Make new replication slot test code less racy. The new test code added in
ead9e51e8236 is racy -- it hinges on shared-memory state, which changes before
the WARNING message is logged. Put it the other way around. Backpatch to 13.
Author: Álvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> Discussion:
[https://postgr.es/m/202107161809.zclasccpfcg3@alvherre.pgsql](https://postgr.es/m/202107161809.zclasccpfcg3@alvherre.pgsql)
[https://git.postgresql.org/pg/commitdiff/8589299e03fff012e0bbb9716693750a0d68eef7](https://git.postgresql.org/pg/commitdiff/8589299e03fff012e0bbb9716693750a0d68eef7)

# Pending Patches

David Rowley sent in another revision of a patch to track non-pruned partitions
in a Bitmapset added to the RelOptInfo struct, and allow ordered partition scans
in more cases.

Hayato Kuroda sent in another revision of a patch to fix DEALLOCATE and DESCRIBE
in ECPG.

David Rowley sent in another revision of a patch to speed up transaction
completion faster after many relations are accessed in a transaction.

Hou Zhijie sent in another revision of a patch to make it possible to use
parallel operations in things that write.

Rahila Syed sent in two more revisions of a patch to make it possible to filter
logical replication by column.

Álvaro Herrera sent in a patch intended to fix a bug that manifested as
partition check not updated when insert into a partition.

Jeevan Ladhe sent in two revisions of a patch to specify in error messages which
DBs had errors in pg_upgrade.

Magnus Hagander sent in another revision of a patch to add support for PROXY
protocol.

Bharath Rupireddy sent in a patch to use WaitLatch for {pre, post}_auth_delay
instead of pg_usleep.

Vigneshwaran C sent in three more revisions of a patch to add schema level
granularity to PUBLICATIONs.

John Naylor sent in four more revisions of a patch to speed up verifying UTF-8.

David Rowley sent in four more revisions of a patch to add proper planner
support for ORDER BY / DISTINCT aggregates.

Ronan Dunklau, David Rowley, and Ranier Vilela traded patches to use the
optimized single-datum tuplesort in ExecSort.

Masahiro Ikeda sent in two revisions of a patch to fix some mistaken comments in
heap_prune_chain().

Alexander Lakhin sent in another revision of a patch intended to fix a bug
that manifested as more time spent in "delete pending" on Win32.

Tom Lane sent in another revision of a patch to reduce memory consumption for
pending inval messages.

Peter Smith sent in a patch to avoid unnecessary calls to PGserverVersion.

Craig Ringer sent in a patch to teach pgflex.pl and pgbision.pl to read
buildenv.pl for tool names, and support extra preprocessor definitions in
config.pl.

Maxim Orlov sent in another revision of a patch to fix a parallel worker failed
assertion and core dump.

Tom Lane sent in another revision of a patch intended to fix a bug that
manifested as merge join on tables with different DB collation behind
postgres_fdw fails.

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

Ian Barwick sent in a patch to document pg_encoding_to_char() and
pg_char_to_encoding().

Fabien COELHO sent in another revision of a patch to psql to factor out the echo
code.

Heikki Linnakangas sent in another revision of a patch to refactor
LogicalTapeSet/LogicalTape interface in a way that stops requiring knowing how
many tapes will be created up front, and replace the polyphase merge algorithm
with a simple balanced k-way merge.

Heikki Linnakangas sent in another revision of a patch to move a few
ResourceOwnerEnlarge() calls for safety and clarity, make resowners more easily
extensible, and optimize the hash function used in same.

Daniil Zakhlystov sent in another revision of a patch to add zlib and zstd
streaming compression generally, then use this infrastructure to add them to
libpq.

Zhihong Yu sent in two revisions of a patch to pfree() a palloc()ed string.

Ajin Cherian sent in another revision of a patch to skip empty transactions for
logical replication.

Kyotaro HORIGUCHI sent in another revision of a patch to be strict in checking
numeric parameters on command line, in environment variables, and document the
effect of those changes on PGCTLTIMEOUT.

Hou Zhijie sent in a patch to avoid repeated calls to PQfnumber in pg_dump.

Gilles Darold sent in two more revisions of a patch to allows user-defined code
to be executed at the start of any command through a xact registered callback.

Peter Smith sent in another revision of a patch to add prepare API support for
streaming transactions.

Huailing Liu sent in a patch to remove a confusing SI inval.

Kyotaro HORIGUCHI sent in another revision of a patch to remove read_page
callback from XLogReadRecord.

Heikki Linnakangas sent in another revision of a patch to add amcheck for GIN.

enis Hirn sent in another revision of a patch to allow multiple linear recursive
self-references in common table expressions.

Haiying Tang sent in a patch to tighten up the help output for the things that
psql tab-completes.

Vigneshwaran C sent in another revision of a patch to identify missing
publications from publishers during CREATE/ALTER SUBSCRIPTION.

Arne Roland sent in another revision of a patch to rename triggers on
partitioned tables recursively.

Andrey V. Lepikhov sent in another revision of a patch to teach optimizer to
consider partitionwise joins of non-partitioned table with each partition of
partitioned table, and disallow asymmetric machinery for joining of two
partitioned (or appended) relations because it could cause huge consumption of
CPU and memory during reparameterization of the NestLoop path.

Bharath Rupireddy sent in another revision of a patch to disambiguate error
messages that use "non-negative".

Magnus Hagander sent in a patch to tag tarballs with their git revision.

Andrey V. Lepikhov sent in another revision of a patch to remove inner joins of
a relation to itself if it can be proven that the join can be replaced with a
scan.

Ryohei Takahashi sent in two revisions of a patch to speed up COMMIT PREPARED.

Mark Dilger sent in a patch to stop ignoring failures on file close.

Justin Pryzby sent in two more revisions of a patch to add new metacommands \dn+
to show the size of each schema and \dA+ for AMs.

Aleksander Alekseev sent in another revision of a patch to refactor procarray.

Yugo Nagata sent in another revision of a patch to fix bugs that manifested as
pgbench errors and serialization/deadlock retries.

Justin Pryzby sent in another revision of a patch to support ALTER TABLE ...
ACCESS METHOD and allow specifying access methods of partitioned tables to be
inherited by partitions.

Dilip Kumar added a missing bbsink_forward_end_archive(sink) to
src/backend/replication/basebackup_gzip.c.

Kyotaro HORIGUCHI sent in a patch to fix a number of repeated typos in the
char-mapping tables.

Bertrand Drouvot sent in another revision of a patch to implement minimal
logical decoding on standbys.

Li Japin and Amit Kapila traded patches to disallow setting a replication slot
name to be an empty string.

Ranier Vilela sent in a patch to un-shadow some variables, and reduce some
-Wsign-compare warnings from modern compilers.

Vigneshwaran C sent in another revision of a patch to include the actual
datatype used in logical replication message description.

James Coleman sent in another revision of a patch to allow parallel LATERAL
subqueries with LIMIT/OFFSET.

Dinesh Chemuduru sent in a patch to add new diagnostics, PG_PARSE_SQL_STATEMENT,
and PG_PARSE_SQL_STATEMENT_POSITION, to PL/pgsql.

Andrew Dunstan sent in three more revisions of a patch to clean up
PostgresNode.pm.

Álvaro Herrera and Ranier Vilela traded patches to remove a pointless strlen
from slot.c.

Kyotaro HORIGUCHI sent in two more revisions of a patch to make FPI_FOR_HINT
follow standard FPI emitting policy.

Yugo Nagata sent in a patch intended to fix a bug that manifested as by
preparing commands at the start of a script instead of at the first execution of
the command.

Soumyadeep Chakraborty sent in another revision of a patch to make
ProcSendSignal() more efficient by referring target processes using pgprocno in
order to avoid scanning ProcArray and keeping track of the startup process.

Yugo Nagata and Ranier Vilela traded patches intended to fix a bug that
manifested as corruption of WAL page header is never reported.

Atsushi Torikoshi sent in a patch to document the necessity for superuser
privileges to execute pg_import_system_collations().

Peter Smith sent in a patch to prevent potential buffer overruns when using
strcpy to gid buffer.

Atsushi Torikoshi sent in another revision of a patch to add a function to log
the complete query string and its plan for the query currently running on the
backend with the specified process ID.