== PostgreSQL Weekly News - April 26, 2020 ==

== PostgreSQL Weekly News - April 26, 2020 ==

== PostgreSQL Product News ==

pgBackRest 2.26, a backup and restore system for PostgreSQL, released.
https://pgbackrest.org/release.html#2.26

pglogical 2.3.1, a logical-WAL-based replication system for PostgreSQL, released.
https://www.2ndquadrant.com/en/resources/pglogical/release-notes/

Patroni 1.6.5, a template for PostgreSQL High Availability with ZooKeeper,
etcd, or Consul, released.
https://github.com/zalando/patroni/blob/master/docs/releases.rst

== PostgreSQL Jobs for April ==

http://archives.postgresql.org/pgsql-jobs/2020-04/

== PostgreSQL Local ==

PGCon 2020 will take place online on May 26-29, 2020.
https://www.pgcon.org/2020/

PostgresLondon 2020 will be July 7-8, 2020 with an optional training day on
July 6.
http://postgreslondon.org

PG Day Russia will take place in Saint Petersburg on July 10, 2020.
https://pgday.ru/en/2020/

FOSS4G 2020, will take place in Calgary, Alberta, Canada August 24-29 2020.
the Call for Papers is currently open at https://2020.foss4g.org/speakers/
https://2020.foss4g.org/

PGDay Ukraine will take place September 5th, 2020 in Lviv at the Bank Hotel.
https://pgday.org.ua/

pgDay Israel 2020 will take place on September 10, 2020 in Tel Aviv.
http://pgday.org.il/

PGDay Austria will take place September 18, 2020 at Schloss Schoenbrunn
(Apothekertrakt) in Vienna.
https://pgday.at/en/

== PostgreSQL in the News ==

Planet PostgreSQL: http://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 ==

Jeff Davis pushed:

- Fix missing pfree() in logtape.c, missed by 24d85952.
https://git.postgresql.org/pg/commitdiff/0cacb2b79d8fa1aeec34cd956544f0c96e7915ed

Tom Lane pushed:

- Doc: update the rest of section 9.4 for new function table layout. Notably,
this replaces the previous handwaving about these functions' behavior with
"character"-type inputs with some actual facts.
https://git.postgresql.org/pg/commitdiff/9aece5cd05f1b21b67eac0dc4f105853eec3e4eb

- Doc: update sections 9.5 and 9.6 for new function table layout. Along the way,
update the older examples for bytea to use "hex" output format. That lets us
get rid of the lame disclaimer about how the examples assume bytea_output =
escape, which was only half true anyway because none of the
more-recently-added examples had paid any attention to that.
https://git.postgresql.org/pg/commitdiff/4157f73b4ba7fa0c6fb117cb9b5a771875850c83

- Doc: update sections 9.7 and 9.8 for new function table layout. Also some
mop-up in section 9.9.
https://git.postgresql.org/pg/commitdiff/1ec42696bed5709e4ff885c109d1e48d92b2b331

- Fix duplicate typedef from commit 0d8c9c121. Older gcc versions don't like
duplicate typedefs, so get rid of that in favor of doing it like we do it
elsewhere, ie just use a "struct" declaration when trying to avoid importing a
whole header file. Also, there seems no reason to include stringinfo.h here
at all, so get rid of that addition too. Discussion:
https://postgr.es/m/27239.1587415696@sss.pgh.pa.us
https://git.postgresql.org/pg/commitdiff/2117c3cb3d51e73290f464ad725fe829c96b9213

- Clean up cpluspluscheck violation. "operator" is a reserved word in C++, so
per project conventions, don't use it as an identifier in header files. My
oversight in commit a80818605.
https://git.postgresql.org/pg/commitdiff/9d25e1aa311c0f0c77155382e6608545b7bf579c

- Allow matchingsel() to be used with operators that might return NULL. Although
selfuncs.c will never call a target operator with null inputs, some functions
might return null anyway. The existing coding will fail if that happens
(since FunctionCall2Coll will punt), which seems undesirable given that
matchingsel() has such a broad range of potential applicability --- in fact,
we already have a problem because we apply it to jsonb_path_exists_opr, which
can return null. Hence, rejigger the underlying functions mcv_selectivity and
histogram_selectivity to cope, treating a null result as false. While we are
at it, we can move the InitFunctionCallInfoData overhead out of the inner
loops, which isn't a huge number of cycles but might save something
considering we are likely calling functions as cheap as int4eq(). Plus, the
number of loop cycles to be expected is much more than it was when this code
was written, since typical settings of default_statistics_target are higher.
In view of that consideration, let's apply the same change to var_eq_const,
eqjoinsel_inner, and eqjoinsel_semi. We do not expect equality functions to
ever return null for non-null inputs (and certainly that code has been that
way a long time without complaints), but the cycle savings seem attractive,
especially in the eqjoinsel loops where there's potentially an O(N^2) savings.
Similar code exists in ineq_histogram_selectivity and get_variable_range, but
I forebore from changing those for now. The performance argument for changing
ineq_histogram_selectivity is really weak anyway, since that will only iterate
log2(N) times. Nikita Glukhov and Tom Lane Discussion:
https://postgr.es/m/9d3b0959-95d6-c37e-2c0b-287bcfe5c705@postgrespro.ru
https://git.postgresql.org/pg/commitdiff/1c455078b0950cb6bad83198d818a55f02649fd4

- Fix minor violations of FunctionCallInvoke usage protocol. Working on commit
1c455078b led me to check through FunctionCallInvoke call sites to see if
every one was being honest about (a) making sure that fcinfo.isnull is
initially false, and (b) checking its state after the call. Sure enough, I
found some violations. The main one is that finalize_partialaggregate re-used
serialfn_fcinfo without resetting isnull, even though it clearly intends to
cater for serialfns that return NULL. There would only be an issue with a
non-strict serialfn, since it's unlikely that a serialfn would return NULL for
non-null input. We have no non-strict serialfns in core, and there may be
none in the wild either, which would account for the lack of complaints.
Still, it's clearly wrong, so back-patch that fix to 9.6 where
finalize_partialaggregate was introduced. Also, arrayfuncs.c and rowtypes.c
contained various callers that were not bothering to check for result nulls.
While what's being called is a comparison or hash function that probably
*shouldn't* return null, that's a lousy excuse for not having any check at
all. There are existing places that just Assert(!fcinfo->isnull) in
comparable situations, so I added that to the places that were calling btree
comparison or hash support functions. In the places calling boolean-returning
equality functions, it's quite cheap to have them treat isnull as FALSE, so
make those places do that. Also remove some "locfcinfo->isnull = false"
assignments that are unnecessary given the assumption that no previous call
returned null. These changes seem like mostly neatnik-ism or debugging
support, so I didn't back-patch.
https://git.postgresql.org/pg/commitdiff/5836d326552cd0bada1b63281892a8515f07af0f

- Fix possible crash during FATAL exit from reindexing. index.c supposed that it
could just use a PG_TRY block to clean up the state associated with an active
REINDEX operation. However, that code doesn't run if we do a FATAL exit ---
for example, due to a SIGTERM shutdown signal --- while the REINDEX is
happening. And that state does get consulted during catalog accesses, which
makes it problematic if we do any catalog accesses during shutdown --- for
example, to clean up any temp tables created in the session. If this
combination of circumstances occurred, we could find ourselves trying to
access already-freed memory. In debug builds that'd fairly reliably cause an
assertion failure. In production we might often get away with it, but with
some bad luck it could cause a core dump. Another possible bad outcome is an
erroneous conclusion that an index-to-be-accessed is being reindexed; but it
looks like that would be unlikely to have any consequences worse than failing
to drop temp tables right away. (They'd still get dropped by the next session
that uses that temp schema.) To fix, get rid of the use of PG_TRY here, and
instead hook into the transaction abort mechanisms to clean up reindex state.
Per bug #16378 from Alexander Lakhin. This has been wrong for a very long
time, so back-patch to all supported branches. Discussion:
https://postgr.es/m/16378-7a70ca41b3ec2009@postgresql.org
https://git.postgresql.org/pg/commitdiff/d12bdba77b0fce9df818bc84ad8b1d8e7a96614b

- Doc: update section 9.11 for new function table layout. This also makes an
attempt to flesh out the docs for some of the more severely underdocumented
geometric operators and functions. This effort exposed that the point <^
point (point_below) and point >^ point (point_above) operators are misnamed;
they should be <<| and |>>, because they act like the other operators named
that way and not like the other operators named <^ and >^. But I just
documented them that way; fixing it is matter for another patch. The
haphazard datatype coverage of many of the operators is also now depressingly
obvious. Discussion:
https://postgr.es/m/158110996889.1089.4224139874633222837@wrigleys.postgresql.org
https://git.postgresql.org/pg/commitdiff/791090bd775b6a2b488ae2078c8479fcd3324a2c

- Sync up some inconsistent comments in config/c-compiler.m4. Make
header/trailer comments agree with the actual names of some macros. These seem
like legit names in earlier iterations of respective patches (commit b779168ff
"Detect PG_PRINTF_ATTRIBUTE automatically." and commit 6869b4f25 "Add C++
support to configure.") but the macro had been renamed out of sync with the
header / trailer comment in the final committed patch. Even more nitpickily,
make the dashed underlines agree with the lengths of the macro names
everyplace. There doesn't seem to have been any meeting of the minds
previously on whether those should match or not, but at least some people have
been trying to make 'em match. Jesse Zhang, Tom Lane Discussion:
https://postgr.es/m/CAGf+fX7DDyq6WfCy6X_KtD28MkbNBE6NkRi26fSf25dfUwX0zw@mail.gmail.com
https://git.postgresql.org/pg/commitdiff/748507c780a39c8e31276bf29dd18d7b32a91b34

- Doc: improve description of geometric multiplication/division. David Johnston
reminded me that the per-point calculations being done by these operators are
equivalent to complex multiplication/division. (Once I would've recognized
that immediately, but it's been too long since I did any of that sort of
math.) Also put in a footnote mentioning that "rotation" of a box doesn't do
what you might expect, as I'd griped about in the referenced thread.
Discussion:
https://postgr.es/m/158110996889.1089.4224139874633222837@wrigleys.postgresql.org
https://git.postgresql.org/pg/commitdiff/1cc34640cabcb32b4f062673cce1d6b1819d492d

- Doc: update section 9.12 for new function table layout. Also rearrange that
page a bit for more consistency and less duplication. In passing, fix
erroneous examples of the results of abbrev(cidr) in datatype.sgml, and do a
bit of copy-editing there.
https://git.postgresql.org/pg/commitdiff/5b0aa112a8f74e93d28c2dc002cfdaea5c40eeda

- Remove useless (and broken) logging logic in memory context functions. Nobody
really uses this stuff, especially not since we created valgrind-based
infrastructure that does the same thing better. It is thus unsurprising that
the generation.c and slab.c versions were actually broken. Rather than fix
'em, let's just remove 'em. Alexander Lakhin Discussion:
https://postgr.es/m/8936216c-3492-3f6e-634b-d638fddc5f91@gmail.com
https://git.postgresql.org/pg/commitdiff/ee88ef55dbacfca15ad425e849280669e3d6ee4d

- Remove ACLDEBUG #define and associated code. In the footsteps of aaf069aa3,
remove ACLDEBUG, which was the only other remaining undocumented symbol in
pg_config_manual.h. The fact that nobody had bothered to document it in
seventeen years is a good clue to its usefulness. In practice, none of the
tracing logic it enabled would be of any value without additional effort.
Discussion: https://postgr.es/m/6631.1587565046@sss.pgh.pa.us
https://git.postgresql.org/pg/commitdiff/3436c5e28374d4e0587634fda09faf4a38a9d848

- Update time zone data files to tzdata release 2020a. DST law changes in
Morocco and the Canadian Yukon. Historical corrections for Shanghai. The
America/Godthab zone is renamed to America/Nuuk to reflect current English
usage; however, the old name remains available as a compatibility link.
https://git.postgresql.org/pg/commitdiff/4cac3a49e691040ddb3f7776ea1f0d63383cbe15

- Repair performance regression in information_schema.triggers view. Commit
32ff26911 introduced use of rank() into the triggers view to calculate the
spec-mandated action_order column. As written, this prevents query
constraints on the table-name column from being pushed below the window
aggregate step. That's bad for performance of this typical usage pattern,
since the view now has to be evaluated for all tables not just the one(s) the
user wants to see. It's also the cause of some recent buildfarm failures, in
which trying to evaluate the view rows for triggers in process of being
dropped resulted in "cache lookup failed for function NNN" errors. Those rows
aren't of interest to the test script doing the query, but the filter that
would eliminate them is being applied too late. None of this happened before
the rank() call was there, so it's a regression compared to v10 and before.
We can improve matters by changing the rank() call so that instead of
partitioning by OIDs, it partitions by nspname and relname, casting those to
sql_identifier so that they match the respective view output columns exactly.
The planner has enough intelligence to know that constraints on partitioning
columns are safe to push down, so this eliminates the performance problem and
the regression test failure risk. We could make the other partitioning
columns match view outputs as well, but it'd be more complicated and the
performance benefits are questionable. Side note: as this stands, the planner
will push down constraints on event_object_table and trigger_schema, but not
on event_object_schema, because it checks for ressortgroupref matches not
expression equivalence. That might be worth improving someday, but it's not
necessary to fix the immediate concern. Back-patch to v11 where the rank()
call was added. Ordinarily we'd not change information_schema in released
branches, but the test failure has been seen in v12 and presumably could
happen in v11 as well, so we need to do this to keep the buildfarm happy. The
change is harmless so far as users are concerned. Some might wish to apply it
to existing installations if performance of this type of query is of concern,
but those who don't are no worse off. I bumped catversion in HEAD as a pro
forma matter (there's no catalog incompatibility that would really require a
re-initdb). Obviously that can't be done in the back branches. Discussion:
https://postgr.es/m/5891.1587594470@sss.pgh.pa.us
https://git.postgresql.org/pg/commitdiff/baf17ad9dff4552b7e494d3f574972c21d9f90bc

- Doc: update section 9.13 for new function table layout. This includes the
usual amount of editorial cleanup, such as correcting wrong or
less-helpful-than-they-could-be examples. I moved the two tsvector-updating
triggers into "9.28 Trigger Functions", which seems like a better home for
them. (I believe that section didn't exist when this text was originally
written.)
https://git.postgresql.org/pg/commitdiff/f8d3e2ab27d22c1f032b0541fd7650e02e8907f7

- Improve placement of "display name" comment in win32_tzmap[] entries. Sticking
this comment at the end of the last line was a bad idea: it's not particularly
readable, and it tempts pgindent to mess with line breaks within the comment,
which in turn reveals that win32tzlist.pl's clean_displayname() does the wrong
thing to clean up such line breaks. While that's not hard to fix, there's
basically no excuse for this arrangement to begin with, especially since it
makes the table layout needlessly vary across back branches with different
pgindent rules. Let's just put the comment inside the braces, instead. This
commit just moves and reformats the comments, and updates win32tzlist.pl to
match; there's no actual data change. Per odd-looking results from Juan José
Santamaría Flecha. Back-patch, since the point is to make win32_tzmap[] look
the same in all supported branches again. Discussion:
https://postgr.es/m/5752.1587740484@sss.pgh.pa.us
https://git.postgresql.org/pg/commitdiff/bd8c5cee961af86e65b873e9debba13cfcb3cb89

- Update Windows timezone name list to include currently-known zones. Thanks to
Juan José Santamaría Flecha. Discussion:
https://postgr.es/m/5752.1587740484@sss.pgh.pa.us
https://git.postgresql.org/pg/commitdiff/6c5f9161682697418156b6391038318d130fe6e4

- Doc: improve documentation of websearch_to_tqsuery(). It wasn't totally clear
about punctuation other than what's specified being ignored. Pavel Borisov
and Tom Lane Discussion:
https://postgr.es/m/CALT9ZEFsBdsogVjG40Z4KfM1Um=wj1FE9hJ00GK3oVfzz0sFNg@mail.gmail.com
https://git.postgresql.org/pg/commitdiff/459f4076c87ac953aa0efa7ddf84df08f8fafe7c

Magnus Hagander pushed:

- Allow pg_read_all_stats to access all stats views again. The views
pg_stat_progress_* had not gotten the memo that pg_read_all_stats is supposed
to be able to read all statistics. Also make a pass over all text-returning
pg_stat_xyz functions that could return "insufficient privilege" and make sure
they also respect pg_read_all_status. Reported-by: Andrey M. Borodin
Reviewed-by: Andrey M. Borodin, Kyotaro Horiguchi Discussion:
https://postgr.es/m/13145F2F-8458-4977-9D2D-7B2E862E5722@yandex-team.ru
https://git.postgresql.org/pg/commitdiff/7e4e574744c13aac613909a59bf38ef5aae5bd8c

Álvaro Herrera pushed:

- Add ALTER .. NO DEPENDS ON. Commit f2fcad27d59c (9.6 era) added the ability to
mark objects as dependent an extension, but forgot to add a way for such
dependencies to be removed. This commit fixes that oversight. Strictly
speaking this should be backpatched to 9.6, but due to lack of demand we're
not doing so at this time. Discussion:
https://postgr.es/m/20200217225333.GA30974@alvherre.pgsql Reviewed-by: ahsan
hadi <ahsan(dot)hadi(at)gmail(dot)com> Reviewed-by: Ibrar Ahmed <ibrar(dot)ahmad(at)gmail(dot)com>
Reviewed-by: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
https://git.postgresql.org/pg/commitdiff/5fc703946bf3b18642ce83b937671d254a8ac5b5

- Add tab-completion for ALTER INDEX .. [NO] DEPENDS ON. ... as added in the
prior commit. (We'd like to have tab-completion for the other object types
too, but they don't have sub-command completion yet.) Author: Ibrar Ahmed
<ibrar(dot)ahmad(at)gmail(dot)com> Reviewed-by: Álvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org>
Discussion:
https://postgr.es/m/CALtqXTcogrFEVP9uou5vFtnGsn+vHZUu9+9a0inarfYVOHScYQ@mail.gmail.com
https://git.postgresql.org/pg/commitdiff/1e324cb0e7613dc035a403c4201c7dc004e7dedb

- Fix detaching partitions with cloned row triggers. When a partition is
detached, any triggers that had been cloned from its parent were not properly
disentangled from its parent triggers. This resulted in triggers that could
not be dropped because they depended on the trigger in the trigger in the
no-longer-parent table: ALTER TABLE t DETACH PARTITION t1; DROP TRIGGER
trig ON t1; ERROR: cannot drop trigger trig on table t1 because trigger
trig on table t requires it HINT: You can drop trigger trig on table t
instead. Moreover the table can no longer be re-attached to its parent,
because the trigger name is already taken: ALTER TABLE t ATTACH PARTITION t1
FOR VALUES FROM (1)TO(2); ERROR: trigger "trig" for relation "t1" already
exists The former is a bug introduced in commit 86f575948c77. (The latter is
not necessarily a bug, but it makes the bug more uncomfortable.) To avoid the
complexity that would be needed to tell whether the trigger has a local
definition that has to be merged with the one coming from the parent table,
establish the behavior that the trigger is removed when the table is detached.
Backpatch to pg11. Author: Justin Pryzby <pryzby(at)telsasoft(dot)com> Reviewed-by:
Amit Langote <amitlangote09(at)gmail(dot)com> Reviewed-by: Álvaro Herrera
<alvherre(at)alvh(dot)no-ip(dot)org> Discussion:
https://www.postgresql.org/message-id/flat/20200408152412(dot)GZ2228(at)telsasoft(dot)com
https://git.postgresql.org/pg/commitdiff/afccd76f1ccef73a341e9b0c6efb29a429f35aa4

- Document partitiong tables ancillary object handling some more. Add a couple
of lines to make it explicit that indexes, constraints, triggers are added,
removed, or left alone. Backpatch to pg11. Author: Álvaro Herrera
<alvherre(at)alvh(dot)no-ip(dot)org> Reviewed-by: Justin Pryzby <pryzby(at)telsasoft(dot)com>
Discussion: https://postgr.es/m/20200421162038.GA18628@alvherre.pgsql
https://git.postgresql.org/pg/commitdiff/8803506c411e457adc2531c6ecb69e002e8a83c6

- psql \d: Display table where trigger is defined, if inherited. It's important
to know that a trigger is cloned from a parent table, because of the behavior
that the trigger is dropped on detach. Make psql's \d display it. We'd like
to backpatch, but lack of the pg_trigger.tgparentid column makes it more
difficult. Punt for now. If somebody wants to volunteer an implementation
that reads pg_depend on older versions, that can probably be backpatched.
Authors: Justin Pryzby, Amit Langote, Álvaro Herrera Discussion:
https://postgr.es/m/20200419002206.GM26953@telsasoft.com
https://git.postgresql.org/pg/commitdiff/c33869cc3bfc42bce822251f2fa1a2a346f86cc5

Robert Haas pushed:

- Move the server's backup manifest code to a separate file. basebackup.c is
already a pretty big and complicated file, so it makes more sense to keep the
backup manifest support routines in a separate file, for clarity and ease of
maintenance. Discussion:
http://postgr.es/m/CA+TgmoavRak5OdP76P8eJExDYhPEKWjMb0sxW7dF01dWFgE=uA@mail.gmail.com
https://git.postgresql.org/pg/commitdiff/079ac29d4dafe581748ceca523aa90c8ce8b035b

- Rename exposed identifiers to say "backup manifest". Function names declared
"extern" now use BackupManifest in the name rather than just Manifest, and
data types use backup_manifest rather than just manifest. Per note from
Michael Paquier. Discussion:
http://postgr.es/m/20200418125713.GG350229@paquier.xyz
https://git.postgresql.org/pg/commitdiff/3989dbdf1293ecc16991065a3d84857a945ea853

- Also rename 'struct manifest_info'. The previous commit renamed the struct's
typedef, but not the struct name itself.
https://git.postgresql.org/pg/commitdiff/ab7646ff92c799303b9ee70ce88b89e07dae717c

- Try to avoid compiler warnings in optimized builds. Per report from Andres
Freund, who also says that this fix works for him. Discussion:
http://postgr.es/m/20200405193118.alprgmozhxcfabkw@alap3.anarazel.de
https://git.postgresql.org/pg/commitdiff/05021a2c0cd212dbe9d7883e2d1677ba739653d5

Bruce Momjian pushed:

- doc: change SGML markup "figure" to "example". Reported-by: Jürgen Purtz
Discussion: https://postgr.es/m/709d7809-d7f4-8175-47f3-4d131341bba8@purtz.de
Author: Jürgen Purtz Backpatch-through: 9.5
https://git.postgresql.org/pg/commitdiff/f192312dc07601c5abb2e04126b434c70f7c8e50

- docs: land height is "elevation", not "altitude". See
https://mapscaping.com/blogs/geo-candy/what-is-the-difference-between-elevation-relief-and-altitude
No patching of regression tests. Reported-by: taf1(at)cornell(dot)edu Discussion:
https://postgr.es/m/158506544539.679.2278386310645558048@wrigleys.postgresql.org
Backpatch-through: 9.5
https://git.postgresql.org/pg/commitdiff/92c12e46d5f1e25fc85608a6d6a19b8f5ea02600

- git_changelog: use modern format for rel branch names in example. e.g.,
REL_12_STABLE
https://git.postgresql.org/pg/commitdiff/395a9a124877d3c41328fcfebcf0c68df86d9bfd

Fujii Masao pushed:

- Mention pg_promote() as a method to trigger promotion in documentation.
Previously in the "Standby Server Operation" section, pg_ctl promote and
protmote_trigger_file were documented as a method to trigger standby
promotion, but pg_promote() function not. This commit also adds parentheses
into <function>pg_promote</function> in some docs to make it clearer that a
function is being referred to. Author: Masahiro Ikeda Reviewed-by: Michael
Paquier, Laurenz Albe, Tom Lane, Fujii Masao Discussion:
https://postgr.es/m/de0068417a9f4046bac693cbcc00bdc9@oss.nttdata.com
https://git.postgresql.org/pg/commitdiff/67f82e966b524fc0eb44024976c5178612a77fc8

- Fix option related issues in pg_verifybackup. This commit does: - get rid of
the garbage code for unused --print-parse-wal option. - add help message for
--quiet option into usage(). - fix typo of option name in help message.
Author: Fujii Masao Reviewed-by: Robert Haas Discussion:
https://postgr.es/m/ff4710f7-2331-4f6b-012e-d76da3275e91@oss.nttdata.com
https://git.postgresql.org/pg/commitdiff/0a89e93bfaa6f2b0a37c19c92943207e3f600098

Peter Geoghegan pushed:

- Consider outliers in split interval calculation. Commit 0d861bbb, which
introduced deduplication to nbtree, added some logic to take large posting
list tuples into account when choosing a split point. We subtract firstright
posting list overhead from the projected new high key size when calculating
leftfree/rightfree values for an affected candidate split point. Posting list
tuples aren't special to nbtsplitloc.c, but taking them into account like this
makes a huge difference in practice. Posting list tuples are frequently tuple
size outliers. However, commit 0d861bbb missed a closely related issue: split
interval itself is calculated based on the assumption that tuples on the page
being split are roughly equisized. That assumption was acceptable back when
commit fab25024 taught the logic for choosing a split point about suffix
truncation, but it's pretty questionable now that very large tuple sizes are
common. This oversight led to unbalanced page splits in low cardinality
multi-column indexes when deduplication was used: page splits that don't give
sufficient weight to how unbalanced the split is when the interval happens to
include some large posting list tuples (and when most other tuples on the page
are not so large). Nail this down by calculating an initial split interval in
a way that's attuned to the actual cost that we want to keep under control
(not a fuzzy proxy for the cost): apply a leftfree + rightfree evenness test
to each candidate split point that actually gets included in the split
interval (for the default strategy). This replaces logic that used a
percentage of all legal split points for the page as the basis of the initial
split interval. Discussion:
https://postgr.es/m/CAH2-WznJt5aT2uUB2Bs+JBLdwe0XTX67+xeLFcaNvCKxO=QBVQ@mail.gmail.com
https://git.postgresql.org/pg/commitdiff/1542e16f2ca257656717ab8ea263bc310f1b6d51

- nbtree: Rename BT_RESERVED_OFFSET_MASK. The mask was added by commit 8224de4f,
which introduced INCLUDE nbtree indexes. The status bits really were reserved
initially. We now use 2 out of 4 of the bits for additional tuple metadata,
though. Rename the mask to BT_STATUS_OFFSET_MASK. Also consolidate related
nbtree.h code comments about the format of pivot tuples and posting list
tuples.
https://git.postgresql.org/pg/commitdiff/48107e396f75ea65192153707a8c476f66b59061

- Fix minor nbtree page deletion buffer lock issue. Avoid accessing the deletion
target page's special area during nbtree page deletion at a point where there
is no buffer lock held. This issue was detected by a patch that extends
Valgrind's memcheck tool to mark nbtree pages that are unsafe to access (due
to not having a buffer lock or buffer pin) as NOACCESS. We do hold a buffer
pin at this point, and only access the special area, so the old approach was
safe. Even still, it seems like a good idea to tighten up the rules in this
area. There is no reason to not simply insist on always holding a buffer lock
(not just a pin) when accessing nbtree pages. Update some related comments in
passing. Discussion:
https://postgr.es/m/CAH2-WzkLgyN3zBvRZ1pkNJThC=xi_0gpWRUb_45eexLH1+k2_Q@mail.gmail.com
https://git.postgresql.org/pg/commitdiff/fa7ff642c22ceccad869af5add00c2661d4d091e

- Fix another minor page deletion buffer lock issue. Avoid accessing the leaf
page's top parent tuple without a buffer lock held during the second phase of
nbtree page deletion. The old approach was safe, though only because VACUUM
never drops its buffer pin (and because only VACUUM itself can modify a
half-dead page). Even still, it seems like a good idea to be strict here.
Tighten things up by copying the top parent page's block number to a local
variable before releasing the buffer lock on the leaf page -- not after. This
is a follow-up to commit fa7ff642, which fixed a similar issue in the first
phase of nbtree page deletion. Update some related comments in passing.
Discussion:
https://postgr.es/m/CAH2-WzkLgyN3zBvRZ1pkNJThC=xi_0gpWRUb_45eexLH1+k2_Q@mail.gmail.com
https://git.postgresql.org/pg/commitdiff/7154aa16a64dd4afc2cbf02e7ce86dc6711a1087

Michaël Paquier pushed:

- Fix memory leak in libpq when using sslmode=verify-full. Checking if Subject
Alternative Names (SANs) from a certificate match with the hostname connected
to leaked memory after each lookup done. This is broken since acd08d7 that
added support for SANs in SSL certificates, so backpatch down to 9.5. Author:
Roman Peshkurov Reviewed-by: Hamid Akhtar, Michael Paquier, David Steele
Discussion:
https://postgr.es/m/CALLDf-pZ-E3mjxd5=bnHsDu9zHEOnpgPgdnO84E2RuwMCjjyPw@mail.gmail.com
Backpatch-through: 9.5
https://git.postgresql.org/pg/commitdiff/27dbe1a18423f2f80c10d191844a0ba4dea650ff

- Fix single-record reads to use restore_command if available in pg_rewind.
readOneRecord() is used now when looking for a checkpoint record to check if
the target server is an ancestor of the source across multiple timelines, and
using a restore_command if available improves the stability of the operation.
This part was missed in a7e8ece. Reported-by: Kyotaro Horiguchi Discussion:
https://postgr.es/m/20200421.150830.1410714948345179794.horikyota.ntt@gmail.com
https://git.postgresql.org/pg/commitdiff/cd123234404ef9a45415060633d3be31329820b2

- Fix handling of WAL segments ready to be archived during crash recovery.
78ea8b5 has fixed an issue related to the recycling of WAL segments on
standbys depending on archive_mode. However, it has introduced a regression
with the handling of WAL segments ready to be archived during crash recovery,
causing those files to be recycled without getting archived. This commit
fixes the regression by tracking in shared memory if a live cluster is either
in crash recovery or archive recovery as the handling of WAL segments ready to
be archived is different in both cases (those WAL segments should not be
removed during crash recovery), and by using this new shared memory state to
decide if a segment can be recycled or not. Previously, it was not possible
to know if a cluster was in crash recovery or archive recovery as the shared
state was able to track only if recovery was happening or not, leading to the
problem. A set of TAP tests is added to close the gap here, making sure that
WAL segments ready to be archived are correctly handled when a cluster is in
archive or crash recovery with archive_mode set to "on" or "always", for both
standby and primary. Reported-by: Benoît Lobréau Author: Jehan-Guillaume de
Rorthais Reviewed-by: Kyotaro Horiguchi, Fujii Masao, Michael Paquier
Discussion: https://postgr.es/m/20200331172229.40ee00dc@firost
Backpatch-through: 9.5
https://git.postgresql.org/pg/commitdiff/4e87c4836ab9059cdec17b0a288db3622a42ac18

- Remove some unstable parts from new TAP test for archive status check. The
test is proving to have timing issues when looking at archive status files on
standbys after crash recovery, while other parts of the test rely on
pg_stat_archiver as a wait point to make sure that a given state of the
archiving is reached. The coverage is not heavily impacted by the removal
those extra tests. Per reports from several buildfarm animals, like crake,
piculet, culicidae and francolin. Discussion:
https://postgr.es/m/20200424005929.GK33034@paquier.xyz Backpatch-through: 9.5
https://git.postgresql.org/pg/commitdiff/f9c1b8dba4da4c17bc6b7c76dec476de6725660b

Peter Eisentraut pushed:

- Remove HEAPDEBUGALL. This has been broken since PostgreSQL 12 and was probably
never really used. PostgreSQL 12 added an analogous HEAPAMSLOTDEBUGALL, which
still works right now, but it's also not very useful, so remove that as well.
Discussion:
https://www.postgresql.org/message-id/flat/645c0646-4218-d4c3-409a-a7003a0c108d%402ndquadrant.com
https://git.postgresql.org/pg/commitdiff/aaf069aa345231823464f65b33c479a0958fe147

- Update Unicode data to Unicode 13.0.0 and CLDR 37.
https://git.postgresql.org/pg/commitdiff/3a8961577677dd4e910ed239047ad6c02cb2591b

- Fix typo. from 303640199d0
https://git.postgresql.org/pg/commitdiff/f057980149ddccd4b862d2c6b3920ed498b0d7ec

David Rowley pushed:

- Remove bogus Assert in foreign key cloning code. This Assert was trying to
ensure that the number of columns in the foreign key being cloned was the same
number of attributes in the parentRel.  Of course, it's perfectly valid to
have columns in the table which are not part of the foreign key constraint. It
appears that this Assert was misunderstanding that. Reported-by: Rajkumar
Raghuwanshi Reviewed-by: amul sul Discussion:
https://postgr.es/m/CAKcux6=z1dtiWw5BOpqDx-U6KTiq+zD0Y2m810zUtWL+giVXWA@mail.gmail.com
https://git.postgresql.org/pg/commitdiff/9f2c4edec2e2182a2fef8495efdaf90a65d64e6c

Tomáš Vondra pushed:

- Fix cost_incremental_sort for expressions with varno 0. When estimating the
number of pre-sorted groups in cost_incremental_sort we must not pass Vars
with varno 0 to estimate_num_groups, which would cause failues in
find_base_rel. This may happen when sorting output of set operations, thanks
to generate_append_tlist. Unlike recurse_set_operations we can't easily
access the original target list, so if we find any Vars with varno 0, we fall
back to the default estimate DEFAULT_NUM_DISTINCT. Reported-by: Justin Pryzby
Discussion: https://postgr.es/m/20200411214639.GK2228%40telsasoft.com
https://git.postgresql.org/pg/commitdiff/de0dc1a84710f127fdd40f87e783797cc2d69a77

Andres Freund pushed:

- Fix transient memory leak for SRFs in FROM. In a9c35cf85ca I changed
ExecMakeTableFunctionResult() to dynamically allocate the FunctionCallInfo
used to call the SRF. Unfortunately I did not account for the fact that the
surrounding memory context has query lifetime, leading to a leak till the end
of the query. In most cases the leak is fairly inconsequential, but if the
FunctionScan is done many times in the query, the leak can add up. This
happens e.g. if the function scan is on the inner side of a nested loop, due
to a lateral join. EXPLAIN SELECT sum(f) FROM generate_series(1, 100000000)
g(i), generate_series(i, i+1) f; quickly shows the leak. Instead of
explicitly freeing the FunctionCallInfo it seems better to make sure all the
per-set temporary state in ExecMakeTableFunctionResult() is cleaned up
wholesale. Currently that's probably just the FunctionCallInfo allocation, but
since there's some initialization work, and since there's already an
appropriate context, this seems like a more robust approach. Bug: #16112
Reported-By: Ben Cornett Author: Andres Freund Reviewed-By: Tom Lane
Discussion: https://postgr.es/m/16112-4448bbf55a404189%40postgresql.org
Backpatch: 12, a9c35cf85ca
https://git.postgresql.org/pg/commitdiff/299298bc873374ed49fa2f39944c09ac62bd75e3

Andrew Gierth pushed:

- Fix error case for CREATE ROLE ... IN ROLE. CreateRole() was passing a Value
node, not a RoleSpec node, for the newly-created role name when adding the
role as a member of existing roles for the IN ROLE syntax. This mistake went
unnoticed because the node in question is used only for error messages and is
not accessed on non-error paths. In older pg versions (such as 9.5 where this
was found), this results in an "unexpected node type" error in place of the
real error. That node type check was removed at some point, after which the
code would accidentally fail to fail on 64-bit platforms (on which accessing
the Value node as if it were a RoleSpec would be mostly harmless) or give an
"unexpected role type" error on 32-bit platforms. Fix the code to pass the
correct node type, and add an lfirst_node assertion just in case. Per report
on irc from user m1chelangelo. Backpatch all the way, because this error has
been around for a long time.
https://git.postgresql.org/pg/commitdiff/d9a4cce29d563e4e6f6eec8b807736d98b1ad553

Noah Misch pushed:

- Revert "When WalSndCaughtUp, sleep only in WalSndWaitForWal().". This reverts
commit 421685812290406daea58b78dfab0346eb683bbb. It caused idle physical
walsenders to busy-wait, as reported by Fujii Masao. Discussion:
https://postgr.es/m/20200417054146.GA1061007@rfd.leadboat.com
https://git.postgresql.org/pg/commitdiff/72a3dc321d76c93842d502793f93b9dc2d2305b2

- In caught-up logical walsender, sleep only in WalSndWaitForWal(). Before
sleeping, WalSndWaitForWal() sends a keepalive if MyWalSnd->write < sentPtr.
When the latest physical LSN yields no logical replication messages (a common
case), that keepalive elicits a reply. Processing the reply updates
pg_stat_replication.replay_lsn. WalSndLoop() lacks that; when WalSndLoop()
slept, replay_lsn advancement could stall until wal_receiver_status_interval
elapsed. This sometimes stalled src/test/subscription/t/001_rep_changes.pl
for up to 10s. Reviewed by Fujii Masao and Michael Paquier. Discussion:
https://postgr.es/m/20200418070142.GA1075445@rfd.leadboat.com
https://git.postgresql.org/pg/commitdiff/f246ea3b2a5e0b75e44f0f18157c4b5e10b5547f

- Raise a timeout to 180s, in test 003_recovery_targets.pl. Buildfarm member
chipmunk has failed twice due to taking >30s, and twenty-four runs of other
members have used >5s. The test is new in v13, so no back-patch.
https://git.postgresql.org/pg/commitdiff/896135512ef67cc084f5e058cfa9b4954ca26861

== Pending Patches ==

Masahiko Sawada sent in another revision of a patch to fix an infelicity between
pg_dump and generated columns.

Robert Haas sent in a patch to rename exposed identifiers to say "backup
manifest."

Thomas Munro and Dilip Kumar traded patches to fix old_snapshot_threshold's
time->xid mapping.

Fujii Masao sent in two revisions of a patch to remove non-fast promotion.

Mark Dilger sent in two more revisions of a patch to add verify_heapam() to the
amcheck contrib module.

Zeng Wenjing sent in four more revisions of a patch to implement global
temporary tables.

Takamichi Osumi sent in another revision of a patch to implement CREATE OR
REPLACE TRIGGER.

Kyotaro HORIGUCHI sent in two more revisions of a patch to remove the page-read
callback from XLogReaderState.

Alexander Korotkov sent in a patch to fix the definition of the
pg_statio_all_tables view, which was mistakenly multiplying statistics for toast
index by the number of relation indexes.

Juan José Santamaría Flecha, Amit Kapila, and Ranier Vilela traded patches to
fix a compilation error with Microsoft Visual Studio 2015/2017/2019.

James Coleman and Tomáš Vondra traded patches to speed up ScalarArrayOpExpr for
OR'd constant arrays using binary search and Bloom filters, individually and
together.

Andy Fan sent in another revision of a patch to make some changes for
init_grouping_targets, which helps with aggregate push-down.

Asif Rehman sent in another revision of a patch to implement parallel
base_backup.

Dilip Kumar sent in another revision of a patch to fix an infelicity between
logical_work_mem and logical streaming of large in-progress transactions.

Robert Haas sent in a patch to add a pg_attribute_noreturn() to the typedef of
json_manifest_error_callback.

Fujii Masao sent in a patch to fix some typos in pg_verifybackup.

David Rowley sent in two revisions of a patch to always pull up child appends to
the top level append.

Ranier Vilela sent in a patch to fix an explicit null dereference pointer in
nbtree.c.

Craig Ringer sent in a patch to fix the install-tests target for vpath builds.

Masahiko Sawada sent in a patch to fix an issue that manifested as a failure to
dump and restore on inherited generated columns.

Amit Kapila sent in a patch to Change the display of WAL usage statistics in
EXPLAIN to make them consistent with Buffer usage statistics.

Ranier Vilela sent in a patch to fix Null pointer dereferences in pgoutput.c.

Ranier Vilela sent in a patch to fix some possible uninitialized variables in
parse_manifest.c.

Ranier Vilela sent in two revisions of a patch to fix some resource leaks in
pg_resetwal.c.

Ranier Vilela sent in a patch to fix a division by zero in explain.c.

Peter Geoghegan sent in a PoC patch to add Valgrind instrumentation.

Fujii Masao sent in a patch to make it possible to add and subtract int8s from
pg_lsn.

Gareth Palmer sent in another revision of a patch to implement INSERT ... SET.

Peter Geoghegan sent in a patch to mark lockless nbtree leaf page memory
undefined.

Masahiro Ikeda sent in a patch to fix an issue wherein some wait events for
timeline history file were not reported.

Andy Fan sent in a PoC patch to add a Materialized Path for subplan and reuse
the previous result if the input parameters are not changed.

Pavel Stěhule sent in a PoC patch to make all PL/pgSQL auto variables constant.

Julien Rouhaud sent in another revision of a patch to implement collation
versioning.

Álvaro Herrera sent in a patch to Rework XLogReader callback system.
segment_open and segment_close are now passed in at XLogReaderAllocate time,
together with page_read.

Robert Haas sent in a patch to fix the bogus tar-file padding logic for
standby.signal, and do some assorted cleanup of the tar-related code.

James Coleman sent in another revision of a patch for incremental sort to
disable mark/restore, fix up EXEC_FLAG_REWIND, and remove an erroneous reset of
node->bounded.

Juan José Santamaría Flecha sent in a patch to update time zones for Windows.

Daniel Gustafsson sent in a patch to rename the client-side TLS protocol
settings for legibility and consistency with other environment variables.

Hamid Akhtar sent in a patch to improve psql's \d commands for external
(foreign) objects.

Jesse Zhang sent in a patch to remove an unused include that was causing a
compilation failure against LLVM 11.

Justin Pryzby sent in another revision of a patch to review the documentation
for the upcoming major version release.

Justin Pryzby sent in another revision of a patch to allow CLUSTER, VACUUM FULL
and REINDEX to change tablespace on the fly.

Tom Lane sent in a patch to clean up some of the function/operator table
redesign.