PostgreSQL Weekly News - June 21, 2021

# PostgreSQL Weekly News - June 21, 2021

pgMustard 4, a user interface for 'explain analyze' which provides performance
tips, [released](https://www.pgmustard.com/).

psycopg2 2.9.0, a Python connector for PostgreSQL,
[released](https://www.psycopg.org/articles/2021/06/16/psycopg-29-released/).

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

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

# PostgreSQL Product News

# PostgreSQL Jobs for June

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

# PostgreSQL in the News

Planet PostgreSQL: [Planet](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

Tom Lane pushed:

- Work around portability issue with newer versions of mktime(). Recent glibc
versions have made mktime() fail if tm_isdst is inconsistent with the
prevailing timezone; in particular it fails for tm_isdst = 1 when the zone is
UTC. (This seems wildly inconsistent with the POSIX-mandated treatment of
"incorrect" values for the other fields of struct tm, so if you ask me it's a
bug, but I bet they'll say it's intentional.) This has been observed to cause
cosmetic problems when pg_restore'ing an archive created in a different
timezone. To fix, do mktime() using the field values from the archive, and if
that fails try again with tm_isdst = -1. This will give a result that's off
by the UTC-offset difference from the original zone, but that was true before,
too. It's not terribly critical since we don't do anything with the result
except possibly print it. (Someday we should flush this entire bit of logic
and record a standard-format timestamp in the archive instead. That's not
okay for a back-patched bug fix, though.) Also, guard our only other use of
mktime() by having initdb's build_time_t() set tm_isdst = -1 not 0. This case
could only have an issue in zones that are DST year-round; but I think some do
exist, or could in future. Per report from Wells Oliver. Back-patch to all
supported versions, since any of them might need to run with a newer glibc.
Discussion:
[https://postgr.es/m/CAOC+FBWDhDHO7G-i1_n_hjRzCnUeFO+H-Czi1y10mFhRWpBrew@mail.gmail.com](https://postgr.es/m/CAOC+FBWDhDHO7G-i1_n_hjRzCnUeFO+H-Czi1y10mFhRWpBrew@mail.gmail.com)
[https://git.postgresql.org/pg/commitdiff/f807e3410fdfc29ced6590c7c2afa76637e001ad](https://git.postgresql.org/pg/commitdiff/f807e3410fdfc29ced6590c7c2afa76637e001ad)

- Remove orphaned expected-result file. This should have been removed in
43e084197, which removed the corresponding spec file. Noted while fooling
about with the isolationtester.
[https://git.postgresql.org/pg/commitdiff/ffbe9dec13599fa786ea6567df1c6a3f3ee3c673](https://git.postgresql.org/pg/commitdiff/ffbe9dec13599fa786ea6567df1c6a3f3ee3c673)

- Update variant expected-result file. This should have been updated in
d2d8a229b, but it was overlooked. According to 31a877f18 which added it, this
file is meant to show the results you get under default_transaction_isolation
= serializable. We've largely lost track of that goal in other isolation
tests, but as long as we've got this one, it should be right. Noted while
fooling about with the isolationtester.
[https://git.postgresql.org/pg/commitdiff/0a1e80c5c4f094087257fc4284a87e0bc7bca591](https://git.postgresql.org/pg/commitdiff/0a1e80c5c4f094087257fc4284a87e0bc7bca591)

- Remove another orphan expected-result file. aborted-keyrevoke_2.out was
apparently needed when it was added (in commit 0ac5ad513) to handle the case
of serializable transaction mode. However, the output in serializable mode
actually matches the regular aborted-keyrevoke.out file, and AFAICT has done
so for a long time. There's no need to keep dragging this variant along.
[https://git.postgresql.org/pg/commitdiff/f6352a0d4e437ac8bc266f77df22d064592056c9](https://git.postgresql.org/pg/commitdiff/f6352a0d4e437ac8bc266f77df22d064592056c9)

- Update another variant expected-result file. This should have been updated in
533e9c6b0, but it was overlooked. Given the lack of complaints, I won't bother
back-patching.
[https://git.postgresql.org/pg/commitdiff/d3c878499c9d639ff06e0664d06b8c731e30c2fc](https://git.postgresql.org/pg/commitdiff/d3c878499c9d639ff06e0664d06b8c731e30c2fc)

- Improve SQLSTATE reporting in some replication-related code. I started out
with the goal of reporting ERRCODE_CONNECTION_FAILURE when walrcv_connect()
fails, but as I looked around I realized that whoever wrote this code was of
the opinion that errcodes are purely optional. That's not my understanding of
our project policy. Hence, make sure that an errcode is provided in each
ereport that (a) is ERROR or higher level and (b) isn't arguably an internal
logic error. Also fix some very dubious existing errcode assignments. While
this is not per policy, it's also largely cosmetic, since few of these cases
could get reported to applications. So I don't feel a need to back-patch.
Discussion:
[https://postgr.es/m/2189704.1623512522@sss.pgh.pa.us](https://postgr.es/m/2189704.1623512522@sss.pgh.pa.us)
[https://git.postgresql.org/pg/commitdiff/6b787d9e32005867ee3660d1ea20f447810a403d](https://git.postgresql.org/pg/commitdiff/6b787d9e32005867ee3660d1ea20f447810a403d)

- Fix plancache refcount leak after error in ExecuteQuery. When stuffing a plan
from the plancache into a Portal, one is not supposed to risk throwing an
error between GetCachedPlan and PortalDefineQuery; if that happens, the plan
refcount incremented by GetCachedPlan will be leaked. I managed to break this
rule while refactoring code in 9dbf2b7d7. There is no visible consequence
other than some memory leakage, and since nobody is very likely to trigger the
relevant error conditions many times in a row, it's not surprising we haven't
noticed. Nonetheless, it's a bug, so rearrange the order of operations to
remove the hazard. Noted on the way to looking for a better fix for bug
#17053. This mistake is pretty old, so back-patch to all supported branches.
[https://git.postgresql.org/pg/commitdiff/131ea3e908d3c97a2fe1ab25cce5046dd5cb905f](https://git.postgresql.org/pg/commitdiff/131ea3e908d3c97a2fe1ab25cce5046dd5cb905f)

- Centralize the logic for protective copying of utility statements. In the
"simple Query" code path, it's fine for parse analysis or execution of a
utility statement to scribble on the statement's node tree, since that'll just
be thrown away afterwards. However it's not fine if the node tree is in the
plan cache, as then it'd be corrupted for subsequent executions. Up to now
we've dealt with that by having individual utility-statement functions apply
copyObject() if they were going to modify the tree. But that's prone to
errors of omission. Bug #17053 from Charles Samborski shows that CREATE/ALTER
DOMAIN didn't get this memo, and can crash if executed repeatedly from plan
cache. In the back branches, we'll just apply a narrow band-aid for that, but
in HEAD it seems prudent to have a more principled fix that will close off the
possibility of other similar bugs in future. Hence, let's hoist the
responsibility for doing copyObject up into ProcessUtility from its children,
thus ensuring that it happens for all utility statement types. Also, modify
ProcessUtility's API so that its callers can tell it whether a copy step is
necessary. It turns out that in all cases, the immediate caller knows whether
the node tree is transient, so this doesn't involve a huge amount of code
thrashing. In this way, while we lose a little bit in the execute-from-cache
code path due to sometimes copying node trees that wouldn't be mutated anyway,
we gain something in the simple-Query code path by not copying throwaway node
trees. Statements that are complex enough to be expensive to copy are almost
certainly ones that would have to be copied anyway, so the loss in the cache
code path shouldn't be much. (Note that this whole problem applies only to
utility statements. Optimizable statements don't have the issue because we
long ago made the executor treat Plan trees as read-only. Perhaps someday we
will make utility statement execution act likewise, but I'm not holding my
breath.) Discussion:
[https://postgr.es/m/931771.1623893989@sss.pgh.pa.us](https://postgr.es/m/931771.1623893989@sss.pgh.pa.us)
Discussion:
[https://postgr.es/m/17053-3ca3f501bbc212b4@postgresql.org](https://postgr.es/m/17053-3ca3f501bbc212b4@postgresql.org)
[https://git.postgresql.org/pg/commitdiff/7c337b6b527b7052e6a751f966d5734c56f668b5](https://git.postgresql.org/pg/commitdiff/7c337b6b527b7052e6a751f966d5734c56f668b5)

- Improve version reporting in pgbench. Commit 547f04e73 caused pgbench to start
printing its version number, which seems like a fine idea, but it needs a bit
more work: * Print the server version number too, when different. * Print the
PG_VERSION string, not some reconstructed approximation. This patch copies
psql's well-tested code for the same purpose. Discussion:
[https://postgr.es/m/1226654.1624036821@sss.pgh.pa.us](https://postgr.es/m/1226654.1624036821@sss.pgh.pa.us)
[https://git.postgresql.org/pg/commitdiff/84bee9610965331d5110971d8de390a5bbe2effc](https://git.postgresql.org/pg/commitdiff/84bee9610965331d5110971d8de390a5bbe2effc)

- Fix misbehavior of DROP OWNED BY with duplicate polroles entries. Ordinarily,
a pg_policy.polroles array wouldn't list the same role more than once; but
CREATE POLICY does not prevent that. If we perform DROP OWNED BY on a role
that is listed more than once, RemoveRoleFromObjectPolicy either suffered an
assertion failure or encountered a tuple-updated-by-self error. Rewrite it to
cope correctly with duplicate entries, and add a CommandCounterIncrement call
to prevent the other problem. Per discussion, there's other cleanup that
ought to happen here, but this seems like the minimum essential fix. Per bug
#17062 from Alexander Lakhin. It's been broken all along, so back-patch to
all supported branches. Discussion:
[https://postgr.es/m/17062-11f471ae3199ca23@postgresql.org](https://postgr.es/m/17062-11f471ae3199ca23@postgresql.org)
[https://git.postgresql.org/pg/commitdiff/d21fca084356946664bfce19d66d2df2bb873cbd](https://git.postgresql.org/pg/commitdiff/d21fca084356946664bfce19d66d2df2bb873cbd)

- Provide feature-test macros for libpq features added in v14. We had a request
to provide a way to test at compile time for the availability of the new
pipeline features. More generally, it seems like a good idea to provide a way
to test via #ifdef for all new libpq API features. People have been using the
version from pg_config.h for that; but that's more likely to represent the
server version than the libpq version, in the increasingly-common scenario
where they're different. It's safer if libpq-fe.h itself is the source of
truth about what features it offers. Hence, establish a policy that starting
in v14 we'll add a suitable feature-is-present macro to libpq-fe.h when we add
new API there. (There doesn't seem to be much point in applying this policy
retroactively, but it's not too late for v14.) Tom Lane and Alvaro Herrera,
per suggestion from Boris Kolpackov. Discussion:
[https://postgr.es/m/boris.20210617102439@codesynthesis.com](https://postgr.es/m/boris.20210617102439@codesynthesis.com)
[https://git.postgresql.org/pg/commitdiff/6991e774e0304f5ef488cf1ae4fa79578b6ae3d5](https://git.postgresql.org/pg/commitdiff/6991e774e0304f5ef488cf1ae4fa79578b6ae3d5)

- Stabilize test case added by commit f61db909d. Buildfarm members ayu and tern
have sometimes shown a different plan than expected for this query. I'd been
unable to reproduce that before today, but I finally realized what is
happening. If there is a concurrent open transaction (probably an autovacuum
run in the buildfarm, but this can also be arranged manually), then the index
entries for the rows removed by the DELETE a few lines up are not killed
promptly, causing a change in the planner's estimate of the extremal value of
ft2.c1, which moves the rowcount estimate for "c1 > 1100" by enough to change
the join plan from nestloop to hash. To fix, change the query condition to
"c1 > 1000", causing the hash plan to be preferred whether or not a concurrent
open transaction exists. Since this UPDATE is tailored to be a no-op, nothing
else changes. Report:
[https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=ayu&dt=2021-06-09%2022%3A45%3A48](https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=ayu&dt=2021-06-09%2022%3A45%3A48)
Report:
[https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=ayu&dt=2021-06-13%2022%3A38%3A18](https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=ayu&dt=2021-06-13%2022%3A38%3A18)
Report:
[https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=tern&dt=2021-06-20%2004%3A55%3A36](https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=tern&dt=2021-06-20%2004%3A55%3A36)
[https://git.postgresql.org/pg/commitdiff/5843659d091bfb6f2c60e010ea1fd00e55ee6ada](https://git.postgresql.org/pg/commitdiff/5843659d091bfb6f2c60e010ea1fd00e55ee6ada)

Michaël Paquier pushed:

- Remove forced toast recompression in VACUUM FULL/CLUSTER. The extra checks
added by the recompression of toast data introduced in bbe0a81 is proving to
have a performance impact on VACUUM or CLUSTER even if no recompression is
done. This is more noticeable with more toastable columns that contain
non-NULL values. Improvements could be done to make those extra checks less
expensive, but that's not material for 14 at this stage, and we are not sure
either if the code path of VACUUM FULL/CLUSTER is adapted for this job. Per
discussion with several people, including Andres Freund, Robert Haas, Álvaro
Herrera, Tom Lane and myself. Discussion:
[https://postgr.es/m/20210527003144.xxqppojoiwurc2iz@alap3.anarazel.de](https://postgr.es/m/20210527003144.xxqppojoiwurc2iz@alap3.anarazel.de)
[https://git.postgresql.org/pg/commitdiff/dbab0c07e5ba1f19a991da2d72972a8fe9a41bda](https://git.postgresql.org/pg/commitdiff/dbab0c07e5ba1f19a991da2d72972a8fe9a41bda)

- Improve handling of dropped objects in pg_event_trigger_ddl_commands(). An
object found as dropped when digging into the list of objects returned by
pg_event_trigger_ddl_commands() could cause a cache lookup error, as the calls
grabbing for the object address and the type name would fail if the object was
missing. Those lookup errors could be seen with combinations of ALTER TABLE
sub-commands involving identity columns. The lookup logic is changed in this
code path to get a behavior similar to any other SQL-callable function by
ignoring objects that are not found, taking advantage of 2a10fdc. The
back-branches are not changed, as they require this commit that is too
invasive for stable branches. While on it, add test cases to exercise event
triggers with identity columns, and stress more cases with the event
ddl_command_end for relations. Author: Sven Klemm, Aleksander Alekseev,
Michael Paquier Discussion:
[https://postgr.es/m/CAMCrgp2R1cEXU53iYKtW6yVEp2_yKUz+z=3-CTrYpPP+xryRtg@mail.gmail.com](https://postgr.es/m/CAMCrgp2R1cEXU53iYKtW6yVEp2_yKUz+z=3-CTrYpPP+xryRtg@mail.gmail.com)
[https://git.postgresql.org/pg/commitdiff/2d689babe3cb50dcb29f6ed595a61d56e518c0d8](https://git.postgresql.org/pg/commitdiff/2d689babe3cb50dcb29f6ed595a61d56e518c0d8)

- doc: Apply markup <productname> to OpenSSL more consistently. Author: Daniel
Gustafsson Discussion:
[https://postgr.es/m/CE12DD5C-4BB3-4166-BC9A-39779568734C@yesql.se](https://postgr.es/m/CE12DD5C-4BB3-4166-BC9A-39779568734C@yesql.se)
[https://git.postgresql.org/pg/commitdiff/f80979f659d39e238e95444e6752142799428078](https://git.postgresql.org/pg/commitdiff/f80979f659d39e238e95444e6752142799428078)

Bruce Momjian pushed:

- doc: add PG 14 relnote item about array function references. User-defined
objects that reference some built-in array functions will need to be recreated
in PG 14. Reported-by: Justin Pryzby Discussion:
[https://postgr.es/m/20210608225618.GR16435@telsasoft.com](https://postgr.es/m/20210608225618.GR16435@telsasoft.com)
[https://git.postgresql.org/pg/commitdiff/25dfb5a831a1b8a83a8a68453b4bbe38a5ef737e](https://git.postgresql.org/pg/commitdiff/25dfb5a831a1b8a83a8a68453b4bbe38a5ef737e)

- doc: PG 14 relnote updates. Reported-by: Justin Pryzby Discussion:
[https://postgr.es/m/20210612034551.GU16435@telsasoft.com](https://postgr.es/m/20210612034551.GU16435@telsasoft.com)
[https://git.postgresql.org/pg/commitdiff/a2559d4093725592a3fd5da8a4c7ac7c883115a0](https://git.postgresql.org/pg/commitdiff/a2559d4093725592a3fd5da8a4c7ac7c883115a0)

- doc: PG 14 relnotes fixes. Items related to logical replication attribution
and BRIN indexes Reported-by: Tomas Vondra, John Naylor Discussion:
[https://postgr.es/m/0db66294-a668-2caa-2b5e-a8db60b30662@enterprisedb.com,](https://postgr.es/m/0db66294-a668-2caa-2b5e-a8db60b30662@enterprisedb.com,)
CAFBsxsH21KnteYdk33F1oZu2O726NSD6_XBq51Tn0jytsA1AnA(at)mail(dot)gmail(dot)com
[https://git.postgresql.org/pg/commitdiff/86b222b09071d3918c5c55b164d22b2236d3f872](https://git.postgresql.org/pg/commitdiff/86b222b09071d3918c5c55b164d22b2236d3f872)

Álvaro Herrera pushed:

- Fix logic bug in 1632ea43682f. I overlooked that one condition was logically
inverted. The fix is a little bit more involved than simply negating the
condition, to make the code easier to read. Fix some outdated comments left
by the same commit, while at it. Author: Masahiko Sawada
<sawada(dot)mshk(at)gmail(dot)com> Author: Álvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org>
Reviewed-by: Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> Discussion:
[https://postgr.es/m/YMRlmB3/lZw8YBH+(at)paquier(dot)xyz](https://postgr.es/m/YMRlmB3/lZw8YBH+(at)paquier(dot)xyz)
[https://git.postgresql.org/pg/commitdiff/33c509956704e1d918077b51ccd954f2e201a8f5](https://git.postgresql.org/pg/commitdiff/33c509956704e1d918077b51ccd954f2e201a8f5)

- Add test case for obsoleting slot with active walsender. The code to signal a
running walsender when its reserved WAL size grows too large is completely
uncovered before this commit; this adds coverage for that case. This test
involves sending SIGSTOP to walsender and walreceiver and running a checkpoint
while advancing WAL, then sending SIGCONT. There's no precedent for this
coding in Perl tests, and my reading of relevant manpages says it's likely to
fail on Windows. Because of this, this test is always skipped on that
platform. Author: Álvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> Discussion:
[https://postgr.es/m/202106102202.mjw4huiix7lo@alvherre.pgsql](https://postgr.es/m/202106102202.mjw4huiix7lo@alvherre.pgsql)
[https://git.postgresql.org/pg/commitdiff/09126984a2631db8dd6299f23954e0dede69735f](https://git.postgresql.org/pg/commitdiff/09126984a2631db8dd6299f23954e0dede69735f)

- Revert "Add test case for obsoleting slot with active walsender". This reverts
commit 09126984a263; the test case added there failed once in circumstances
that remain mysterious. It seems better to remove the test for now so that
14beta2 doesn't have random failures built in.
[https://git.postgresql.org/pg/commitdiff/96795176810b979a2bf1f4563bdcb161679d5b95](https://git.postgresql.org/pg/commitdiff/96795176810b979a2bf1f4563bdcb161679d5b95)

Noah Misch pushed:

- Copy-edit text for the pg_terminate_backend() "timeout" parameter. Revert the
pg_description entry to its v13 form, since those messages usually remain
shorter and don't discuss individual parameters. No catversion bump, since
pg_description content does not impair backend compatibility or application
compatibility. Justin Pryzby Discussion:
[https://postgr.es/m/20210612182743.GY16435@telsasoft.com](https://postgr.es/m/20210612182743.GY16435@telsasoft.com)
[https://git.postgresql.org/pg/commitdiff/0aac73e6a2602696d23aa7a9686204965f9093dc](https://git.postgresql.org/pg/commitdiff/0aac73e6a2602696d23aa7a9686204965f9093dc)

- Remove pg_wait_for_backend_termination(). It was unable to wait on a backend
that had already left the procarray. Users tolerant of that limitation can
poll pg_stat_activity. Other users can employ the "timeout" argument of
pg_terminate_backend(). Reviewed by Bharath Rupireddy. Discussion:
[https://postgr.es/m/20210605013236.GA208701@rfd.leadboat.com](https://postgr.es/m/20210605013236.GA208701@rfd.leadboat.com)
[https://git.postgresql.org/pg/commitdiff/5f1df62a459b51ab3bb625a0ee5e655429254257](https://git.postgresql.org/pg/commitdiff/5f1df62a459b51ab3bb625a0ee5e655429254257)

Amit Kapila pushed:

- Fix decoding of speculative aborts. During decoding for speculative inserts,
we were relying for cleaning toast hash on confirmation records or next change
records. But that could lead to multiple problems (a) memory leak if there is
neither a confirmation record nor any other record after toast insertion for a
speculative insert in the transaction, (b) error and assertion failures if the
next operation is not an insert/update on the same table. The fix is to start
queuing spec abort change and clean up toast hash and change record during its
processing. Currently, we are queuing the spec aborts for both toast and main
table even though we perform cleanup while processing the main table's spec
abort record. Later, if we have a way to distinguish between the spec abort
record of toast and the main table, we can avoid queuing the change for spec
aborts of toast tables. Reported-by: Ashutosh Bapat Author: Dilip Kumar
Reviewed-by: Amit Kapila Backpatch-through: 9.6, where it was introduced
Discussion:
[https://postgr.es/m/CAExHW5sPKF-Oovx_qZe4p5oM6Dvof7_P+XgsNAViug15Fm99jA@mail.gmail.com](https://postgr.es/m/CAExHW5sPKF-Oovx_qZe4p5oM6Dvof7_P+XgsNAViug15Fm99jA@mail.gmail.com)
[https://git.postgresql.org/pg/commitdiff/4daa140a2f50e9a160bc180c3997ee13c7aabf9e](https://git.postgresql.org/pg/commitdiff/4daa140a2f50e9a160bc180c3997ee13c7aabf9e)

- Document a few caveats in synchronous logical replication. In a synchronous
logical setup, locking [user] catalog tables can cause deadlock. This is
because logical decoding of transactions can lock catalog tables to access
them so exclusively locking those in transactions can lead to deadlock. To
avoid this users must refrain from having exclusive locks on catalog tables.
Author: Takamichi Osumi Reviewed-by: Vignesh C, Amit Kapila Backpatch-through:
9.6 Discussion:
[https://www.postgresql.org/message-id/20210222222847.tpnb6eg3yiykzpky%40alap3.anarazel.de](https://www.postgresql.org/message-id/20210222222847.tpnb6eg3yiykzpky%40alap3.anarazel.de)
[https://git.postgresql.org/pg/commitdiff/3cb828dbe26087e7754f49f3cfe3ed036d5af439](https://git.postgresql.org/pg/commitdiff/3cb828dbe26087e7754f49f3cfe3ed036d5af439)

- Handle no replica identity index case in RelationGetIdentityKeyBitmap. Commit
e7eea52b2d has introduced a new function RelationGetIdentityKeyBitmap which
omits to handle the case where there is no replica identity index on a
relation. Author: Mark Dilger Reviewed-by: Takamichi Osumi, Amit Kapila
Discussion:
[https://www.postgresql.org/message-id/4C99A862-69C8-431F-960A-81B1151F1B89@enterprisedb.com](https://www.postgresql.org/message-id/4C99A862-69C8-431F-960A-81B1151F1B89@enterprisedb.com)
[https://git.postgresql.org/pg/commitdiff/2731ce1bd550d08f3fdd7bcb1497af4b95170976](https://git.postgresql.org/pg/commitdiff/2731ce1bd550d08f3fdd7bcb1497af4b95170976)

Alexander Korotkov pushed:

- Support for unnest(multirange) and cast multirange as an array of ranges. 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) and cast multirange as an array of
ranges, which is quite trivial. unnest(multirange) is defined as a
polymorphic procedure. The catalog description of the cast underlying
procedure is duplicated for each multirange type because we don't have
anyrangearray polymorphic type to use here. 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
[https://git.postgresql.org/pg/commitdiff/29854ee8d1ca4a46adb7e84deb17e6fb18e531cc](https://git.postgresql.org/pg/commitdiff/29854ee8d1ca4a46adb7e84deb17e6fb18e531cc)

- Add missing type name "multirange" in docs chapter title. Discussion:
[https://postgr.es/m/CAFj8pRDioOxiJgmgw9TqQqZ3CxnJC4P5B2Oospf2eMgAjJuewA%40mail.gmail.com](https://postgr.es/m/CAFj8pRDioOxiJgmgw9TqQqZ3CxnJC4P5B2Oospf2eMgAjJuewA%40mail.gmail.com)
Author: Pavel Stehule, Alexander Korotkov Reviewed-by: Justin Pryzby, Tom Lane
[https://git.postgresql.org/pg/commitdiff/ad2da246c69dd5ec55764d4b6066f3b0c0d24ca2](https://git.postgresql.org/pg/commitdiff/ad2da246c69dd5ec55764d4b6066f3b0c0d24ca2)

- Revert 29854ee8d1 due to buildfarm failures. Reported-by: Tom Lane Discussion:
[https://postgr.es/m/CAPpHfdvcnw3x7jdV3r52p4%3D5S4WUxBCzcQKB3JukQHoicv1LSQ%40mail.gmail.com](https://postgr.es/m/CAPpHfdvcnw3x7jdV3r52p4%3D5S4WUxBCzcQKB3JukQHoicv1LSQ%40mail.gmail.com)
[https://git.postgresql.org/pg/commitdiff/817bb0a7d1e02df4643d37304aed8574cf43f629](https://git.postgresql.org/pg/commitdiff/817bb0a7d1e02df4643d37304aed8574cf43f629)

Peter Geoghegan pushed:

- Remove unneeded field from VACUUM state. Bugfix commit 5fc89376 effectively
made the lock_waiter_detected field from vacuumlazy.c's global state struct
into private state owned by lazy_truncate_heap(). Finish this off by
replacing the struct field with a local variable.
[https://git.postgresql.org/pg/commitdiff/958cfbcf2dd338e3179c2d8a35f48bde020eba60](https://git.postgresql.org/pg/commitdiff/958cfbcf2dd338e3179c2d8a35f48bde020eba60)

- Support disabling index bypassing by VACUUM. Generalize the INDEX_CLEANUP
VACUUM parameter (and the corresponding reloption): make it into a ternary
style boolean parameter. It now exposes a third option, "auto". The "auto"
option (which is now the default) enables the "bypass index vacuuming"
optimization added by commit 1e55e7d1. "VACUUM (INDEX_CLEANUP TRUE)" is
redefined to once again make VACUUM simply do any required index vacuuming,
regardless of how few dead tuples are encountered during the first scan of the
target heap relation (unless there are exactly zero). This gives users a way
of opting out of the "bypass index vacuuming" optimization, if for whatever
reason that proves necessary. It is also expected to be used by PostgreSQL
developers as a testing option from time to time. "VACUUM (INDEX_CLEANUP
FALSE)" does the same thing as it always has: it forcibly disables both index
vacuuming and index cleanup. It's not expected to be used much in PostgreSQL
14. The failsafe mechanism added by commit 1e55e7d1 addresses the same
problem in a simpler way. INDEX_CLEANUP can now be thought of as a testing and
compatibility option. Author: Peter Geoghegan <pg(at)bowt(dot)ie> Reviewed-By:
Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> Reviewed-By: Justin Pryzby
<pryzby(at)telsasoft(dot)com> Discussion:
[https://postgr.es/m/CAH2-WznrBoCST4_Gxh_G9hA8NzGUbeBGnOUC8FcXcrhqsv6OHQ@mail.gmail.com](https://postgr.es/m/CAH2-WznrBoCST4_Gxh_G9hA8NzGUbeBGnOUC8FcXcrhqsv6OHQ@mail.gmail.com)
[https://git.postgresql.org/pg/commitdiff/3499df0dee8c4ea51d264a674df5b5e31991319a](https://git.postgresql.org/pg/commitdiff/3499df0dee8c4ea51d264a674df5b5e31991319a)

Andrew Dunstan pushed:

- Further refinement of stuck_on_old_timeline recovery test. TestLib::perl2host
can take a file argument as well as a directory argument, so that code becomes
substantially simpler. Also add comments on why we're using forward slashes,
and why we're setting PERL_BADLANG=0. Discussion:
[https://postgr.es/m/e9947bcd-20ee-027c-f0fe-01f736b7e345@dunslane.net](https://postgr.es/m/e9947bcd-20ee-027c-f0fe-01f736b7e345@dunslane.net)
[https://git.postgresql.org/pg/commitdiff/54a5ed22016940d7ad5060ed62d23473924756a1](https://git.postgresql.org/pg/commitdiff/54a5ed22016940d7ad5060ed62d23473924756a1)

- Don't set a fast default for anything but a plain table. The fast default code
added in Release 11 omitted to check that the table a fast default was being
added to was a plain table. Thus one could be added to a foreign table, which
predicably blows up. Here we perform that check. In addition, on the back
branches, since some of these might have escaped into the wild, if we
encounter a missing value for an attribute of something other than a plain
table we ignore it. Fixes bug #17056 Backpatch to release 11, Reviewed by:
Andres Freund, Álvaro Herrera and Tom Lane
[https://git.postgresql.org/pg/commitdiff/0a4efdc7ebf2584257b166c87e82797eb92815b5](https://git.postgresql.org/pg/commitdiff/0a4efdc7ebf2584257b166c87e82797eb92815b5)

Heikki Linnakangas pushed:

- Fix outdated comment that talked about seek position of WAL file. Since commit
c24dcd0cfd, we have been using pg_pread() to read the WAL file, which doesn't
change the seek position (unless we fall back to the implementation in
src/port/pread.c). Update comment accordingly. Backpatch-through: 12, where
we started to use pg_pread()
[https://git.postgresql.org/pg/commitdiff/d0303bc8d2670d11c9df9220aa08a2c33529e100](https://git.postgresql.org/pg/commitdiff/d0303bc8d2670d11c9df9220aa08a2c33529e100)

- Tidy up `GetMultiXactIdMembers()'s` behavior on error. One of the error paths
left `*members` uninitialized. That's not a live bug, because most callers don't
look at `*members` when the function returns -1, but let's be tidy. One caller,
in heap_lock_tuple(), does "if (members != NULL) pfree(members)", but AFAICS
it never passes an invalid 'multi' value so it should not reach that error
case. The callers are also a bit inconsistent in their expectations.
heap_lock_tuple() pfrees the 'members' array if it's not-NULL, others pfree()
it if "nmembers >= 0", and others if "nmembers > 0". That's not a live bug
either, because the function should never return 0, but add an Assert for that
to make it more clear. I left the callers alone for now. I also moved the
line where we set `*nmembers`. It wasn't wrong before, but I like to do that
right next to the 'return' statement, to make it clear that it's always set on
return. Also remove one unreachable return statement after ereport(ERROR),
for brevity and for consistency with the similar if-block right after it.
Author: Greg Nancarrow with the additional changes by me Backpatch-through:
9.6, all supported versions
[https://git.postgresql.org/pg/commitdiff/d24c5658a80c8f5037e9e1948de311d3f3350f12](https://git.postgresql.org/pg/commitdiff/d24c5658a80c8f5037e9e1948de311d3f3350f12)

Tomáš Vondra pushed:

- Fix copying data into slots with FDW batching. Commit b676ac443b optimized
handling of tuple slots with bulk inserts into foreign tables, so that the
slots are initialized only once and reused for all batches. The data was
however copied into the slots only after the initialization, inserting
duplicate values when the slot gets reused. Fixed by moving the ExecCopySlot
outside the init branch. The existing postgres_fdw tests failed to catch this
due to inserting data into foreign tables without unique indexes, and then
checking only the number of inserted rows. This adds a new test with both a
unique index and a check of inserted values. Reported-by: Alexander Pyhalov
Discussion:
[https://postgr.es/m/7a8cf8d56b3d18e5c0bccd6cd42d04ac%40postgrespro.ru](https://postgr.es/m/7a8cf8d56b3d18e5c0bccd6cd42d04ac%40postgrespro.ru)
[https://git.postgresql.org/pg/commitdiff/99cea49d6525e30bc3768e4ffb95377e7584dea1](https://git.postgresql.org/pg/commitdiff/99cea49d6525e30bc3768e4ffb95377e7584dea1)

Fujii Masao pushed:

- Make archiver process handle barrier events. Commit d75288fb27 made WAL
archiver process an auxiliary process. An auxiliary process needs to handle
barrier events but the commit forgot to make archiver process do that.
Reported-by: Thomas Munro Author: Fujii Masao Reviewed-by: Thomas Munro
Discussion:
[https://postgr.es/m/CA+hUKGLah2w1pWKHonZP_+EQw69=q56AHYwCgEN8GDzsRG_Hgw@mail.gmail.com](https://postgr.es/m/CA+hUKGLah2w1pWKHonZP_+EQw69=q56AHYwCgEN8GDzsRG_Hgw@mail.gmail.com)
[https://git.postgresql.org/pg/commitdiff/981524d2e3aa3f28d364c472e34f5386be846811](https://git.postgresql.org/pg/commitdiff/981524d2e3aa3f28d364c472e34f5386be846811)

# Pending Patches

Amit Khandekar sent in a WIP patch to allow subtransactions in worker processes.

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

Thomas Munro sent in another revision of a patch to use tuple-level SIREAD locks
for index-only scans, and skip SIREAD locks on btree pages when possible.

Andrew Dunstan sent in another revision of a patch to intended to fix a bug that
manifested as Segmentation fault on altering the type of the foreign table
column with a default.

Tomáš Vondra sent in another revision of a patch to use extended statistics to
improve join estimates.

Bharath Rupireddy sent in another revision of a patch to remove
pg_wait_for_backend_termination().

Fabien COELHO sent in another revision of a patch to consolidate the echo system
in psql to its own function.

Yugo Nagata and Fabien COELHO traded patches for pgbench which ensure that it
computes and stores conn_duration only when requested.

Jehan-Guillaume de Rorthais sent in a PoC patch to Trace wait events to logfile
when log_statement_stats=on.

Yugo Nagata and Fabien COELHO traded patches to avoid a stuck condition in
pbgench due to skipped transactions.

Jacob Champion and Daniel Gustafsson traded patches to support NSS as a libpq
TLS backend.

Dilip Kumar and Amit Langote traded patches to intended to fix a bug that
manifested as decoding speculative insert with toast leaks memory.

Yugo Nagata and Fabien COELHO traded patches to intended to fix a bug in pgbench
that manifested as negative "initial connection time".

Takamichi Osumi and Amit Kapila traded patches to fix an infelicity among
locking [user] catalog tables, 2PC, and logical replication.

Justin Pryzby and Michaël Paquier traded patches to add more options for WAL
compression.

Ranier Vilela sent in another revision of a patch to make accesses to ProcArray
more efficient.

David Fetter sent in a patch to use the singular number where appropriate in the
regression tests.

Alexander Pyhalov sent in another revision of a patch to implement case
expression pushdown.

Ranier Vilela sent in a patch to fix a buffer in ecpg which is not null
terminated.

Fabien COELHO and Yugo Nagata traded patches to intended to fix a bug that
manifested as errors in pgbench logging.

Dmitry Dolgov sent in two more revisions of a patch to prevent jumbling of every
element in ArrayExpr.

Dilip Kumar sent in a patch to make CREATE DATABASE fully WAL logged so issuing
that command no longer forces a checkpoint.

Ajin Cherian sent in another revision of a patch to add an option to set
two-phase in CREATE_REPLICATION_SLOT command, and support two-phase decoding in
pg_recvlogical.

Peter Smith and Ajin Cherian traded patches to support logical decoding of
two-phase transactions.

Daniel Gustafsson sent in a patch to use the more correct and current TLS/SSL
instead of SSL in documentation.

Thomas Munro sent in another revision of a patch to track relation sizes in
shared memory, provide a lock-free fast path for smgrnblocks(), and update fifo
to lru to sweep a valid cache.

Kyotaro HORIGUCHI sent in another revision of a patch to show more details in
errors in pg_waldump.

Tom Lane sent in a patch to allow regular identifiers in isolationtester
scripts.

Matthias van de Meent sent in another revision of a patch to fix a bug in
GetOldestNonRemovableTransactionId, which did not return values consistent with
GlobalVisTestFor(rel). Additionally, lazy_scan_prune had an incorrect
assumption that GlobalVis*Rels would never have an OldestXmin <
vacrel->OldestXmin, which is incorrect. This assumption is now fixed, and the
changes have been documented.

Heikki Linnakangas sent in a patch to split xlog.c, which was getting unwieldy.

Thomas Munro sent in another revision of a patch to make and use a qsort
template.

Amit Langote sent in three more revisions of a patch to skip partition tuple
routing with a constant partition key.

Jeff Davis sent in two revisions of a patch to clarify the documentation for the
replication protocol.

Tomáš Vondra sent in a PoC patch to use a count-min sketch for join cardinality
estimation.

Jeff Davis sent in another revision of a patch to document what happens in START
REPLICATION.

Alexander Korotkov sent in three more revisions of a patch to implement UNNEST
for multiranges.

Vigneshwaran C sent in another revision of a patch to add schema-level support
for PUBLICATIONs.

Amul Sul sent in another revision of a patch to implement ALTER SYSTEM SET READ
{WRITE | ONLY}.

Nitin Jadhav sent in another revision of a patch to implement a startup process
progress indicator.

Matthias van de Meent sent in another revision of a patch to implement and use
index tuple attribute iteration, and implement page-level dynamic prefix
truncation for `_bt_binsrch*`.

Mark Dilger sent in two revisions of a patch to optionally disable subscriptions
on error.

Jeff Davis sent in a patch to fix start replication identify system.

Thomas Munro sent in another revision of a patch to implement snapshot_too_old
using a timer.

Takashi Menjo sent in another revision of a patch to map WAL segment files on
PMEM as WAL buffers.

Amit Kapila sent in another revision of a patch to implement row filtering for
logical replication.

Egor Rogov sent in a patch to include statistics for range types in the pg_stats
view.

Álvaro Herrera sent in two more revisions of a patch to add a test case for
obsoleting slots with active walsenders.

Greg Sabino Mullane sent in another revision of a patch to avoid computing page
checksums unnecessarily.

Noah Misch sent in a patch to remove XLogFileInit()'s ability to skip
ControlFileLock.

David Rowley sent in another revision of a patch to add a new hash table type
which has stable pointers.

Thomas Munro sent in a patch to adjust for the LLVM 13 API change.