PostgreSQL Weekly News - October 3, 2021

# PostgreSQL Weekly News - October 3, 2021

PostgreSQL 14 released!
https://www.postgresql.org/about/news/postgresql-14-released-2318/

# PostgreSQL Product News

pgtt 2.6, an extension to implement global temporary tables, released.
https://github.com/darold/pgtt/releases/tag/v2.6

oracle_fdw 2.4.0 released.
https://laurenz.github.io/oracle_fdw

pgFormatter 5.1, a formatter/beautifier for SQL code, released.
https://github.com/darold/pgFormatter/blob/master/ChangeLog

# PostgreSQL Jobs for October

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

# 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

Thomas Munro pushed:

- Track LLVM 14 API changes. Only done on the master branch for now to fix build
farm animal seawasp (which tests bleeeding edge PostgreSQL with bleeding edge
LLVM). We can back-patch a consolidated fix closer to LLVM 14's release, once
its API has stopped moving around. Discussion:
[https://postgr.es/m/CA%2BhUKGL%3Dyg6qqgg6W6SAuvRQejditeoDNy-X3b9H_6Fnw8j5Wg%40mail.gmail.com](https://postgr.es/m/CA%2BhUKGL%3Dyg6qqgg6W6SAuvRQejditeoDNy-X3b9H_6Fnw8j5Wg%40mail.gmail.com)
[https://git.postgresql.org/pg/commitdiff/e6a7600202105919bffd62b3dfd941f4a94e082b](https://git.postgresql.org/pg/commitdiff/e6a7600202105919bffd62b3dfd941f4a94e082b)

Peter Geoghegan pushed:

- Remove unneeded nbtree latestRemovedXid comments. Discussing the low level
issue of nbtree VACUUM and recovery conflicts in btvacuumpage() now seems
inappropriate. The same issue is discussed in nbtxlog.h, as well as in a
comment block above `_bt_delitems_vacuum`(). The comment block made more sense
when it was part of a broader discussion of nbtree VACUUM "pin scans". These
were removed by commit 9f83468b.
[https://git.postgresql.org/pg/commitdiff/895267a3266484440c0b2f42f613bcff28844cc1](https://git.postgresql.org/pg/commitdiff/895267a3266484440c0b2f42f613bcff28844cc1)

- Enable deduplication in system catalog indexes. The "equality implies image
equality" opclass infrastructure disallowed deduplication in system catalog
indexes and TOAST indexes before now. That seemed like the right approach back
when the infrastructure was added by commit 612a1ab7, since ALTER INDEX cannot
set deduplicate_items to 'off' (due to an old implementation restriction).
But that decision now seems arbitrary at best. Remove special case handling
implementing this policy. No catversion bump, since existing catalog indexes
will still work. Author: Peter Geoghegan <pg(at)bowt(dot)ie> Discussion:
[https://postgr.es/m/CAH2-Wz=rYQHFaJ3WYBdK=xgwxKzaiGMSSrh-ZCREa-pS-7Zjew@mail.gmail.com](https://postgr.es/m/CAH2-Wz=rYQHFaJ3WYBdK=xgwxKzaiGMSSrh-ZCREa-pS-7Zjew@mail.gmail.com)
[https://git.postgresql.org/pg/commitdiff/2903f1404df37e11ecc303dbc164826c4717194b](https://git.postgresql.org/pg/commitdiff/2903f1404df37e11ecc303dbc164826c4717194b)

Michaël Paquier pushed:

- Fix typos and grammar in code comments. Several mistakes have piled in the
code comments over the time, including incorrect grammar, function names and
simple typos. This commit takes care of a portion of these. No backpatch is
done as this is only cosmetic. Author: Justin Pryzby Discussion:
[https://postgr.es/m/20210924215827.GS831@telsasoft.com](https://postgr.es/m/20210924215827.GS831@telsasoft.com)
[https://git.postgresql.org/pg/commitdiff/e767ddcd354b51fc4c12d6b02e268861bd871fbc](https://git.postgresql.org/pg/commitdiff/e767ddcd354b51fc4c12d6b02e268861bd871fbc)

- Refactor output file handling when forking syslogger under EXEC_BACKEND. A
forked logging collector in EXEC_BACKEND builds passes down file descriptors
(or HANDLEs in WIN32) through a command for files to be reopened (for stderr
and csvlog). Some of its logic was duplicated, and this commit refactors the
code with some wrapper routines for file reopening after forking and fd
grabbing when building the command for the fork. While on it, this simplifies
a use of "long" in the code, introduced by ab0ba6e to take care of a warning
related to MinGW-W64 when mapping a intptr_t to a printed value. "long" is
32-bit long on Windows, and interoperability of Win32 and Win64 ensures that
handles are always 32-bit significant, so we can just use "int" for the same
result. This also makes the new routines more symmetric. This change makes
easier the introduction of new log destinations in the logging collector, and
this is not the only piece of refactoring planned. I have tested this change
with EXEC_BACKEND on linux, macos, and of course MSVC (both Win32 and Win64),
but not MinGW so the buildfarm may have something to say here. Author:
Sehrope Sarkuni, Michael Paquier Discussion:
[https://postgr.es/m/CAH7T-aqswBM6JWe4pDehi1uOiufqe06DJWaU5=X7dDLyqUExHg@mail.gmail.com](https://postgr.es/m/CAH7T-aqswBM6JWe4pDehi1uOiufqe06DJWaU5=X7dDLyqUExHg@mail.gmail.com)
[https://git.postgresql.org/pg/commitdiff/5b0b699f748ead1b7414c58aaa7cf0ea83808147](https://git.postgresql.org/pg/commitdiff/5b0b699f748ead1b7414c58aaa7cf0ea83808147)

- doc: Fix some typos and markups. Author: Ekaterina Kiryanova Discussion:
[https://postgr.es/m/8a14e78f-6991-7a6e-4711-fe376635f2ad@postgrespro.ru](https://postgr.es/m/8a14e78f-6991-7a6e-4711-fe376635f2ad@postgrespro.ru)
Backpatch-through: 14
[https://git.postgresql.org/pg/commitdiff/c8dd2cb49405d2a39a714bd5adc31d39b8372a4e](https://git.postgresql.org/pg/commitdiff/c8dd2cb49405d2a39a714bd5adc31d39b8372a4e)

- Clarify use of "statistics objects" in the code. The code inconsistently used
"statistic object" or "statistics" where the correct term, as discussed, is
actually "statistics object". This improves the state of the code to be more
consistent. While on it, fix an incorrect error message introduced in
a4d75c8. This error should never happen, as the code states, but it would be
misleading. Author: Justin Pryzby Reviewed-by: Álvaro Herrera, Michael
Paquier Discussion:
[https://postgr.es/m/20210924215827.GS831@telsasoft.com](https://postgr.es/m/20210924215827.GS831@telsasoft.com)
Backpatch-through: 14
[https://git.postgresql.org/pg/commitdiff/070d2e19e40897d857f570f24888fc30727ed9c0](https://git.postgresql.org/pg/commitdiff/070d2e19e40897d857f570f24888fc30727ed9c0)

- pg_stat_statements: Add some tests for older versions still usable. When the
newest version is loaded, the backend would load objects from the oldest
complete SQL file (here 1.4) and then update to the latest version with
transition scripts (up to 1.9 currently). This provides some coverage for
upgrades of pg_stat_statements, but there is no test to show how things have
changed across each version. This adds a couple of tests for the upgrade
paths using objects from each version supported, stressing the objects whose
behaviors have changed across each version supported. Author: Erica Zhang
Reviewed-by: Julien Rouhaud, Michael Paquier Discussion:
[https://postgr.es/m/tencent_BBA974AFF61379F2345E782FD6C55891950A@qq.com](https://postgr.es/m/tencent_BBA974AFF61379F2345E782FD6C55891950A@qq.com)
[https://git.postgresql.org/pg/commitdiff/2b0da0365bec6c62cc9c5c317bab6cbee3d52ef4](https://git.postgresql.org/pg/commitdiff/2b0da0365bec6c62cc9c5c317bab6cbee3d52ef4)

Tom Lane pushed:

- Re-enable contrib/bloom's TAP tests. These tests were disabled back in 2018
(commit d3c09b9b1) because of failures observed in the buildfarm. I've not
been able to reproduce any failure on longfin's host, though, so I'm curious
whether or to what extent we've fixed the problem. Let's re-enable it (in
HEAD only) and see what blows up. Discussion:
[https://postgr.es/m/2769443.1632773967@sss.pgh.pa.us](https://postgr.es/m/2769443.1632773967@sss.pgh.pa.us)
[https://git.postgresql.org/pg/commitdiff/7d1aa6bf1c27bf7438179db446f7d1e72ae093d0](https://git.postgresql.org/pg/commitdiff/7d1aa6bf1c27bf7438179db446f7d1e72ae093d0)

- Fix instability in contrib/bloom TAP tests. It turns out that the instability
complained of in commit d3c09b9b1 has an embarrassingly simple explanation.
The test script waits for the standby to flush incoming WAL to disk, but it
should wait for the WAL to be replayed, since we are testing for the effects
of that to be visible. While at it, use wait_for_catchup instead of
reinventing that logic, and adjust $Test::Builder::Level to improve future
error reports. Back-patch to v12 where the necessary infrastructure came in
(cf. aforesaid commit). Also back-patch 7d1aa6bf1 so that the test will
actually get run. Discussion:
[https://postgr.es/m/2854602.1632852664@sss.pgh.pa.us](https://postgr.es/m/2854602.1632852664@sss.pgh.pa.us)
[https://git.postgresql.org/pg/commitdiff/6bc6bd47cf715c8717a8af3f617957045772d38b](https://git.postgresql.org/pg/commitdiff/6bc6bd47cf715c8717a8af3f617957045772d38b)

- Treat ETIMEDOUT as indicating a non-recoverable connection failure. Add
ETIMEDOUT to ALL_CONNECTION_FAILURE_ERRNOS' list of "errnos that identify hard
failure of a previously-established network connection". While one could
imagine that this is sometimes recoverable, the same could be said of other
entries such as ENETDOWN. In support of this, handle ETIMEDOUT on par with
other socket errors in relevant infrastructure, such as
TranslateSocketError(). (I made a couple of cosmetic adjustments in
TranslateSocketError(), too.) The code now assumes that ETIMEDOUT is defined
everywhere, which it should be given that POSIX has required it since SUSv2.
Perhaps this should be back-patched, but I'm hesitant to do so given the lack
of previous complaints, and the hazard that there's a small ABI break on
Windows from redefining the symbol. Even if we decide to do that, it'd be
prudent to let this bake awhile in HEAD first. Jelte Fennema Discussion:
[https://postgr.es/m/AM5PR83MB01782BFF2978505F6D6C559AF7AA9@AM5PR83MB0178.EURPRD83.prod.outlook.com](https://postgr.es/m/AM5PR83MB01782BFF2978505F6D6C559AF7AA9@AM5PR83MB0178.EURPRD83.prod.outlook.com)
[https://git.postgresql.org/pg/commitdiff/b484ddf4d2eb81736512efa35ed3e5d2a72993d8](https://git.postgresql.org/pg/commitdiff/b484ddf4d2eb81736512efa35ed3e5d2a72993d8)

- Remove gratuitous environment dependency in 002_types.pl test. Computing
related timestamps by subtracting "N days" is sensitive to the prevailing
timezone, since we interpret that as "same local time on the N'th prior day".
Even though the intervals in question are only two to four days, through
remarkable bad luck they managed to cross the end of Ramadan in 2014, causing
the test's output to change if timezone is set to Africa/Casablanca. (Maybe
in other Muslim areas as well; I didn't check.) There's absolutely no reason
for this test to exercise interval subtraction, so just get rid of that and
use plain timestamptz constants representing the intended values. Per report
from Andres Freund. Back-patch to v10 where this test script came in.
Discussion:
[https://postgr.es/m/20210930183641.7lh4jhvpipvromca@alap3.anarazel.de](https://postgr.es/m/20210930183641.7lh4jhvpipvromca@alap3.anarazel.de)
[https://git.postgresql.org/pg/commitdiff/20f8671ef69b864c25ffa59471814102c1260d78](https://git.postgresql.org/pg/commitdiff/20f8671ef69b864c25ffa59471814102c1260d78)

- Fix Portal snapshot tracking to handle subtransactions properly. Commit
84f5c2908 forgot to consider the possibility that EnsurePortalSnapshotExists
could run inside a subtransaction with lifespan shorter than the Portal's. In
that case, the new active snapshot would be popped at the end of the
subtransaction, leaving a dangling pointer in the Portal, with mayhem ensuing.
To fix, make sure the ActiveSnapshot stack entry is marked with the same
subtransaction nesting level as the associated Portal. It's certainly safe to
do so since we won't be here at all unless the stack is empty; hence we can't
create an out-of-order stack. Let's also apply this logic in the case where
PortalRunUtility sets portalSnapshot, just to be sure that path can't cause
similar problems. It's slightly less clear that that path can't create an
out-of-order stack, so add an assertion guarding it. Report and patch by
Bertrand Drouvot (with kibitzing by me). Back-patch to v11, like the previous
commit. Discussion:
[https://postgr.es/m/ff82b8c5-77f4-3fe7-6028-fcf3303e82dd@amazon.com](https://postgr.es/m/ff82b8c5-77f4-3fe7-6028-fcf3303e82dd@amazon.com)
[https://git.postgresql.org/pg/commitdiff/7b5d4c29ed0262e537026cb3a85161d6cf98abcc](https://git.postgresql.org/pg/commitdiff/7b5d4c29ed0262e537026cb3a85161d6cf98abcc)

- Avoid believing incomplete MCV-only stats in get_variable_range().
get_variable_range() would incautiously believe that statistics containing
only an MCV list are sufficient to derive a range estimate. That's okay for an
enum-like column that contains only MCVs, but otherwise the estimate could be
pretty bad. Make it report that the range is indeterminate unless the MCVs
plus nullfrac account for the whole table. I don't think this needs a
dedicated test case, since a quick code coverage check verifies that the
existing regression tests traverse all the alternatives. There is room to
doubt that a future-proof test case could be built anyway, given that the
submitted example accidentally doesn't fail before v11. Per bug #17207 from
Simon Perepelitsa. Back-patch to v10. In principle this has been broken all
along, but I'm hesitant to make such changes in 9.6, since if anyone is
unhappy with 9.6.24's behavior there will be no second chance to fix it.
Discussion:
[https://postgr.es/m/17207-5265aefa79e333b4@postgresql.org](https://postgr.es/m/17207-5265aefa79e333b4@postgresql.org)
[https://git.postgresql.org/pg/commitdiff/8c1144ba73478b818d9cebe8ecd64a14b7d45bde](https://git.postgresql.org/pg/commitdiff/8c1144ba73478b818d9cebe8ecd64a14b7d45bde)

- Re-alphabetize the win32_tzmap[] array. The original intent seems to have been
to sort case-insensitively by the Windows zone name, but various changes over
the years did not get that memo. This commit just moves a few entries to
restore exact alphabetic order, to ease comparison to the outputs of
processing scripts. Back-patch to all supported branches, as is our usual
practice for time zone data updates. Discussion:
[https://postgr.es/m/3266414.1633045628@sss.pgh.pa.us](https://postgr.es/m/3266414.1633045628@sss.pgh.pa.us)
[https://git.postgresql.org/pg/commitdiff/ad740067aea5b643ca2f79da086808573d35b5f4](https://git.postgresql.org/pg/commitdiff/ad740067aea5b643ca2f79da086808573d35b5f4)

- Update our mapping of Windows time zone names using CLDR info. This corrects a
bunch of entries in win32_tzmap[], and adds a few new ones, based on the CLDR
project's windowsZones.xml file. Non-cosmetic changes fall into four main
categories: * Flat-out errors: US/Aleutan doesn't exist America/Salvador
doesn't exist Asia/Baku is wrong for Yerevan Asia/Dhaka (Bangladesh) is wrong
for Astana (Kazakhstan) Europe/Bucharest is wrong for Chisinau
America/Mexico_City is wrong for Chetumal America/Buenos_Aires is wrong for
Cayenne America/Caracas has its own zone, so poor fit for La Paz US/Eastern is
wrong for Haiti US/Eastern is wrong for Indiana (East) Asia/Karachi is wrong
for Tashkent Etc/UTC+12 doesn't exist Signs of Etc/GMT zones were backwards *
Judgment calls: (These changes follow CLDR's choices, except for the first
one) Use Europe/London for "Greenwich Standard Time", since that seems much
more likely than Africa/Casablanca to be what people will think that zone name
means. CLDR has Atlantic/Reykjavik here, but that's no better. Asia/Shanghai
seems a better fit than Hong Kong for "China Standard Time". Europe/Sarajevo
is now a link to Belgrade, ie "Central Europe Standard Time"; so use Warsaw
for "Central European Standard Time". America/Sao_Paulo seems more
representative than Araguaina for "E. South America Standard Time".
Africa/Johannesburg seems more representative than Harare for "South Africa
Standard Time". * New Windows zone names: "Israel Standard Time"
"Kaliningrad Standard Time" "Russia Time Zone N" for various N "Singapore
Standard Time" "South Sudan Standard Time" "W. Central Africa Standard Time"
"West Bank Standard Time" "Yukon Standard Time" Some of these replace older
spellings, but I kept the older spellings too in case our code runs on a
machine with the older data. * Replace aliases (tzdb Links) with underlying
city-named zones: (This tracks tzdb's longstanding practice, and reduces
inconsistency with the rest of the entries, as well as with CLDR.) US/Alaska
Asia/Kuwait Asia/Muscat Canada/Atlantic Australia/Canberra Canada/Saskatchewan
US/Central US/Eastern US/Hawaii US/Mountain Canada/Newfoundland US/Pacific
Back-patch to all supported branches, as is our usual practice for time zone
data updates. Discussion:
[https://postgr.es/m/3266414.1633045628@sss.pgh.pa.us](https://postgr.es/m/3266414.1633045628@sss.pgh.pa.us)
[https://git.postgresql.org/pg/commitdiff/9b8d68cc6589814d121344f59e927a7e4506fb8c](https://git.postgresql.org/pg/commitdiff/9b8d68cc6589814d121344f59e927a7e4506fb8c)

- Fix checking of query type in plpgsql's RETURN QUERY command. Prior to v14, we
insisted that the query in RETURN QUERY be of a type that returns tuples.
(For instance, INSERT RETURNING was allowed, but not plain INSERT.) That
happened indirectly because we opened a cursor for the query, so spi.c checked
SPI_is_cursor_plan(). As a consequence, the error message wasn't terribly
on-point, but at least it was there. Commit 2f48ede08 lost this detail.
Instead, plain RETURN QUERY insisted that the query be a SELECT (by checking
for SPI_OK_SELECT) while RETURN QUERY EXECUTE failed to check the query type
at all. Neither of these changes was intended. The only convenient place to
check this in the EXECUTE case is inside `_SPI_execute_plan`, because we haven't
done parse analysis until then. So we need to pass down a flag saying whether
to enforce that the query returns tuples. Fortunately, we can squeeze another
boolean into struct SPIExecuteOptions without an ABI break, since there's
padding space there. (It's unlikely that any extensions would already be
using this new struct, but preserving ABI in v14 seems like a smart idea
anyway.) Within spi.c, it seemed like `_SPI_execute_plan`'s parameter list was
already ridiculously long, and I didn't want to make it longer. So I thought
of passing SPIExecuteOptions down as-is, allowing that parameter list to
become much shorter. This makes the patch a bit more invasive than it might
otherwise be, but it's all internal to spi.c, so that seems fine. Per report
from Marc Bachmann. Back-patch to v14 where the faulty code came in.
Discussion:
[https://postgr.es/m/1F2F75F0-27DF-406F-848D-8B50C7EEF06A@gmail.com](https://postgr.es/m/1F2F75F0-27DF-406F-848D-8B50C7EEF06A@gmail.com)
[https://git.postgresql.org/pg/commitdiff/a0558cfa395b47adb245972f5eba7978461e7baa](https://git.postgresql.org/pg/commitdiff/a0558cfa395b47adb245972f5eba7978461e7baa)

Peter Eisentraut pushed:

- Support amcheck of sequences. Sequences were left out of the list of relation
kinds that verify_heapam knew how to check, though it is fairly trivial to
allow them. Doing that, and while at it, updating pg_amcheck to include
sequences in relations matched by table and relation patterns. Author: Mark
Dilger <mark(dot)dilger(at)enterprisedb(dot)com> Discussion:
[https://www.postgresql.org/message-id/flat/81ad4757-92c1-4aa3-7bee-f609544837e3%40enterprisedb.com](https://www.postgresql.org/message-id/flat/81ad4757-92c1-4aa3-7bee-f609544837e3%40enterprisedb.com)
[https://git.postgresql.org/pg/commitdiff/c3b011d9918100c6ec2d72297fb51635bce70e80](https://git.postgresql.org/pg/commitdiff/c3b011d9918100c6ec2d72297fb51635bce70e80)

- Fix incorrect format placeholder.
[https://git.postgresql.org/pg/commitdiff/0b947c3101d1d05c55531731d6b778f82cb21350](https://git.postgresql.org/pg/commitdiff/0b947c3101d1d05c55531731d6b778f82cb21350)

- psql: Add various tests. Add tests for psql features - AUTOCOMMIT -
ON_ERROR_ROLLBACK - ECHO errors Reviewed-by: Fabien COELHO
<coelho(at)cri(dot)ensmp(dot)fr> Discussion:
[https://www.postgresql.org/message-id/6954328d-96f2-77f7-735f-7ce493a40949%40enterprisedb.com](https://www.postgresql.org/message-id/6954328d-96f2-77f7-735f-7ce493a40949%40enterprisedb.com)
[https://git.postgresql.org/pg/commitdiff/14d755b00037ce04b9e24504f4b540d9e731c29e](https://git.postgresql.org/pg/commitdiff/14d755b00037ce04b9e24504f4b540d9e731c29e)

Magnus Hagander pushed:

- Properly schema-prefix reference to
pg_catalog.pg_get_statisticsobjdef_columns. Author: Tatsuro Yamada
Backpatch-through: 14 Discussion:
[https://www.postgresql.org/message-id/7ad8cd13-db5b-5cf6-8561-dccad1a934cb@nttcom.co.jp](https://www.postgresql.org/message-id/7ad8cd13-db5b-5cf6-8561-dccad1a934cb@nttcom.co.jp)
[https://git.postgresql.org/pg/commitdiff/07f8a9e784236a3baf707c59cf80d0f015594ffc](https://git.postgresql.org/pg/commitdiff/07f8a9e784236a3baf707c59cf80d0f015594ffc)

Fujii Masao pushed:

- pgbench: Correct log level of message output when socket wait method fails.
The failure of socket wait method like "select()" doesn't terminate pgbench.
So the log level of error message when that failure happens should be ERROR.
But previously FATAL was used in that case. Back-patch to v13 where pgbench
started using common logging API. Author: Yugo Nagata, Fabien COELHO
Reviewed-by: Kyotaro Horiguchi, Fujii Masao Discussion:
[https://postgr.es/m/20210617005934.8bd37bf72efd5f1b38e6f482@sraoss.co.jp](https://postgr.es/m/20210617005934.8bd37bf72efd5f1b38e6f482@sraoss.co.jp)
[https://git.postgresql.org/pg/commitdiff/d33674708948e10806480ee628b072a2ef8ecba1](https://git.postgresql.org/pg/commitdiff/d33674708948e10806480ee628b072a2ef8ecba1)

- pgbench: Fix handling of socket errors during benchmark. Previously socket
errors such as invalid socket or socket wait method failures during benchmark
caused pgbench to exit with status 0. Instead, errors during the run should
result in exit status 2. Back-patch to v12 where pgbench started reporting
exit status. Original complaint and patch by Hayato Kuroda. Author: Yugo
Nagata, Fabien COELHO Reviewed-by: Kyotaro Horiguchi, Fujii Masao Discussion:
[https://postgr.es/m/TYCPR01MB5870057375ACA8A73099C649F5349@TYCPR01MB5870.jpnprd01.prod.outlook.com](https://postgr.es/m/TYCPR01MB5870057375ACA8A73099C649F5349@TYCPR01MB5870.jpnprd01.prod.outlook.com)
[https://git.postgresql.org/pg/commitdiff/2acb7cc6b56c2b80029c202217e19553578456e9](https://git.postgresql.org/pg/commitdiff/2acb7cc6b56c2b80029c202217e19553578456e9)

Álvaro Herrera pushed:

- Fix WAL replay in presence of an incomplete record. Physical replication
always ships WAL segment files to replicas once they are complete. This is a
problem if one WAL record is split across a segment boundary and the primary
server crashes before writing down the segment with the next portion of the
WAL record: WAL writing after crash recovery would happily resume at the point
where the broken record started, overwriting that record ... but any standby
or backup may have already received a copy of that segment, and they are not
rewinding. This causes standbys to stop following the primary after the latter
crashes: LOG: invalid contrecord length 7262 at A8/D9FFFBC8 because the
standby is still trying to read the continuation record (contrecord) for the
original long WAL record, but it is not there and it will never be. A
workaround is to stop the replica, delete the WAL file, and restart it -- at
which point a fresh copy is brought over from the primary. But that's pretty
labor intensive, and I bet many users would just give up and re-clone the
standby instead. A fix for this problem was already attempted in commit
515e3d84a0b5, but it only addressed the case for the scenario of WAL
archiving, so streaming replication would still be a problem (as well as other
things such as taking a filesystem-level backup while the server is down after
having crashed), and it had performance scalability problems too; so it had to
be reverted. This commit fixes the problem using an approach suggested by
Andres Freund, whereby the initial portion(s) of the split-up WAL record are
kept, and a special type of WAL record is written where the contrecord was
lost, so that WAL replay in the replica knows to skip the broken parts. With
this approach, we can continue to stream/archive segment files as soon as they
are complete, and replay of the broken records will proceed across the crash
point without a hitch. Because a new type of WAL record is added, users
should be careful to upgrade standbys first, primaries later. Otherwise they
risk the standby being unable to start if the primary happens to write such a
record. A new TAP test that exercises this is added, but the portability of
it is yet to be seen. This has been wrong since the introduction of physical
replication, so backpatch all the way back. In stable branches, keep the new
XLogReaderState members at the end of the struct, to avoid an ABI break.
Author: Álvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> Reviewed-by: Kyotaro
Horiguchi <horikyota(dot)ntt(at)gmail(dot)com> Reviewed-by: Nathan Bossart
<bossartn(at)amazon(dot)com> Discussion:
[https://postgr.es/m/202108232252.dh7uxf6oxwcy@alvherre.pgsql](https://postgr.es/m/202108232252.dh7uxf6oxwcy@alvherre.pgsql)
[https://git.postgresql.org/pg/commitdiff/ff9f111bce24fd9bbca7a20315586de877d74923](https://git.postgresql.org/pg/commitdiff/ff9f111bce24fd9bbca7a20315586de877d74923)

- Repair two portability oversights of new test. First, as pointed out by Tom
Lane and Michael Paquier, I failed to realize that Windows' PostgresNode needs
an extra pg_hba.conf line (added by PostgresNode->set_replication_conf, called
internally by ->init() when 'allows_streaming=>1' is given -- but I
purposefully omitted that). I think a good fix should be to have nodes with
only 'has_archiving=>1' set up for replication too, but that's a bigger
discussion. Fix it by calling ->set_replication_conf, which is not
unprecedented, as pointed out by Andrew Dunstan. I also forgot to uncomment a
->finish() call for a pumpable IPC::Run file descriptor. Apparently this is
innocuous in almost all platforms. Backpatch to 14. The older branches were
added this file too, but not this particular part of the test. Discussion:
[https://postgr.es/m/3000074.1632947632@sss.pgh.pa.us](https://postgr.es/m/3000074.1632947632@sss.pgh.pa.us)
Discussion:
[https://postgr.es/m/YVT7qwhR8JmC2kfz@paquier.xyz](https://postgr.es/m/YVT7qwhR8JmC2kfz@paquier.xyz)
[https://git.postgresql.org/pg/commitdiff/d03bca4d70c29cca4f09e3a0e78a56cf97e237f3](https://git.postgresql.org/pg/commitdiff/d03bca4d70c29cca4f09e3a0e78a56cf97e237f3)

- Remove unstable, unnecessary test; fix typo. Commit ff9f111bce24 added some
test code that's unportable and doesn't add meaningful coverage. Remove it
rather than try and get it to work everywhere. While at it, fix a typo in a
log message added by the aforementioned commit. Backpatch to 14. Discussion:
[https://postgr.es/m/3000074.1632947632@sss.pgh.pa.us](https://postgr.es/m/3000074.1632947632@sss.pgh.pa.us)
[https://git.postgresql.org/pg/commitdiff/d186d233dfde4afb9dff346e13c8adaf4deec6b3](https://git.postgresql.org/pg/commitdiff/d186d233dfde4afb9dff346e13c8adaf4deec6b3)

- Error out if SKIP LOCKED and WITH TIES are both specified. Both bugs #16676[1]
and #17141[2] illustrate that the combination of SKIP LOCKED and FETCH FIRST
WITH TIES break expectations when it comes to rows returned to other sessions
accessing the same row. Since this situation is detectable from the syntax
and hard to fix otherwise, forbid for now, with the potential to fix in the
future. [1]
[https://postgr.es/m/16676-fd62c3c835880da6@postgresql.org](https://postgr.es/m/16676-fd62c3c835880da6@postgresql.org)
[2]
[https://postgr.es/m/17141-913d78b9675aac8e@postgresql.org](https://postgr.es/m/17141-913d78b9675aac8e@postgresql.org)
Backpatch-through: 13, where WITH TIES was introduced Author: David
Christensen <david(dot)christensen(at)crunchydata(dot)com> Discussion:
[https://postgr.es/m/CAOxo6XLPccCKru3xPMaYDpa+AXyPeWFs+SskrrL+HKwDjJnLhg@mail.gmail.com](https://postgr.es/m/CAOxo6XLPccCKru3xPMaYDpa+AXyPeWFs+SskrrL+HKwDjJnLhg@mail.gmail.com)
[https://git.postgresql.org/pg/commitdiff/c6bc655ee2ef09449da7ff688a8be19a13db5c4a](https://git.postgresql.org/pg/commitdiff/c6bc655ee2ef09449da7ff688a8be19a13db5c4a)

David Rowley pushed:

- Ensure interleaved_parts field is always initialized. This field was recently
added in db632fbca, however that commit missed one place where it should have
initialized the new field to NULL. The missed location is where the
PartitionBoundInfo is created for partition-wise join relations. Technically
there could be interleaved partitions in a partition-wise join relation, but
currently the only optimization we use this field for only does so for base
rels and other member rels. So just document that we don't populate this
field for join rels. Reported-by: Amit Langote Author: Amit Langote, David
Rowley Reviewed-by: Amit Langote, David Rowley Discussion:
[https://postgr.es/m/CA+HiwqE76Rps24kwHsd2Cr82Ua07tJC9t9reG0c7ScX9n_xrEA@mail.gmail.com](https://postgr.es/m/CA+HiwqE76Rps24kwHsd2Cr82Ua07tJC9t9reG0c7ScX9n_xrEA@mail.gmail.com)
[https://git.postgresql.org/pg/commitdiff/16239c5fdf6e457f8274c49209d1fbdeab472703](https://git.postgresql.org/pg/commitdiff/16239c5fdf6e457f8274c49209d1fbdeab472703)

Amit Kapila pushed:

- Doc: Move pg_stat_replication_slots view to "Collected Statistics Views"
section. Commit 9868167500 added pg_stat_replication_slots view to monitor
ReorderBuffer stats but mistakenly added it under "Dynamic Statistics Views"
section in the docs whereas it belongs to "Collected Statistics Views"
section. Author: Amit Kapila Reviewed-by: Masahiko Sawada Backpatch-through:
14, where it was introduced Discussion:
[https://postgr.es/m/CAA4eK1Kb5ur=OC-G4cAsqPOjoVe+S8LNw1WmUY8Owasjk8o5WQ@mail.gmail.com](https://postgr.es/m/CAA4eK1Kb5ur=OC-G4cAsqPOjoVe+S8LNw1WmUY8Owasjk8o5WQ@mail.gmail.com)
[https://git.postgresql.org/pg/commitdiff/2d44dee0281a1abf0dcb1548c910fae067f1d34d](https://git.postgresql.org/pg/commitdiff/2d44dee0281a1abf0dcb1548c910fae067f1d34d)

Daniel Gustafsson pushed:

- Fix memory leak in pg_hmac. The intermittent h buffer was not freed, causing
it to leak. Backpatch through 14 where HMAC was refactored to the current API.
Author: Sergey Shinderuk <s(dot)shinderuk(at)postgrespro(dot)ru> Discussion:
[https://postgr.es/m/af07e620-7e28-a742-4637-2bc44aa7c2be@postgrespro.ru](https://postgr.es/m/af07e620-7e28-a742-4637-2bc44aa7c2be@postgrespro.ru)
Backpatch-through: 14
[https://git.postgresql.org/pg/commitdiff/0ded7039fab314afb7cbaf36b52209f253c05539](https://git.postgresql.org/pg/commitdiff/0ded7039fab314afb7cbaf36b52209f253c05539)

Andres Freund pushed:

- Reference test binary using TESTDIR in 001_libpq_pipeline.pl. The previous
approach didn't really work on windows, due to the PATH separator being ';'
not ':'. Instead of making the PATH change more complicated, reference the
binary using the TESTDIR environment. Reported-By: Andres Freund
<andres(at)anarazel(dot)de> Suggested-By: Andrew Dunstan <andrew(at)dunslane(dot)net>
Discussion:
[https://postgr.es/m/20210930214040.odkdd42vknvzifm6@alap3.anarazel.de](https://postgr.es/m/20210930214040.odkdd42vknvzifm6@alap3.anarazel.de)
Backpatch: 14-, where the test was introduced.
[https://git.postgresql.org/pg/commitdiff/795862c280c5949bafcd8c44543d887fd32b590a](https://git.postgresql.org/pg/commitdiff/795862c280c5949bafcd8c44543d887fd32b590a)