== PostgreSQL Weekly News - July 28, 2019 ==

== PostgreSQL Weekly News - July 28, 2019 ==

== PostgreSQL Product News ==

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

pgAdmin4 4.11, a web- and native GUI control center for PostgreSQL, released.
https://www.pgadmin.org/docs/pgadmin4/dev/release_notes_4_11.html

Pyrseas 0.9.0, a set of utilities for comparing and synchronizing Postgres
database schemas, released.
https://github.com/pyrseas/Pyrseas

== PostgreSQL Jobs for July ==

http://archives.postgresql.org/pgsql-jobs/2019-07/

== PostgreSQL Local ==

PGConf.Brazil 2019 will take place August 1-3, 2019 in São Paulo.
http://pgconf.com.br

The first Austrian pgDay, will take place September 6, 2019 at the Hilton Garden
Inn in Wiener Neustadt.
https://pgday.at/en/

PostgresOpen will be September 11th - 13th, 2019 in Orlando, Florida at the
Rosen Centre Hotel. The CfP is open at https://2019.postgresopen.org/callforpapers/
https://2019.postgresopen.org/

PostgresConf South Africa 2019 will take place in Johannesburg on October 8-9, 2019
https://postgresconf.org/conferences/SouthAfrica2019

2Q PGConf 2019 will be held December 4 & 5 in Chicago.
The CFP is open through August 30, 2019.
https://www.2qpgconf.com/

pgDay Paris 2020 will be held in Paris, France on March 26, 2020
at Espace Saint-Martin.
http://2020.pgday.paris/

Nordic PGDay 2020 will be held in Helsinki, Finland at the Hilton Helsinki
Strand Hotel on March 24, 2020. The CfP is open through December 31, 2019 at
https://2020.nordicpgday.org/cfp/

== 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 ==

David Rowley pushed:

- Adjust overly strict Assert. 3373c7155 changed how we determine
EquivalenceClasses for relations and added an Assert to ensure all relations
mentioned in each EC's ec_relids was a RELOPT_BASEREL. However, the join
removal code may remove a LEFT JOIN and since it does not clean up EC members
belonging to the removed relations it can leave RELOPT_DEADREL rels in
ec_relids. Fix this by adjusting the Assert to allow RELOPT_DEADREL rels too.
Reported-by: sqlsmith via Andreas Seltenreich Discussion:
https://postgr.es/m/87y30r8sls.fsf@ansel.ydns.eu
https://git.postgresql.org/pg/commitdiff/e1a0f6a983068675813074847e1d0d61bd37ac0e

- Make better use of the new List implementation in a couple of places. In
nodeAppend.c and nodeMergeAppend.c there were some foreach loops which looped
over the list of subplans and only performed any work if the subplan index was
found in a Bitmapset. With the old linked list implementation of List, this
form made sense as accessing the Nth list element was O(N). However, thanks
to 1cff1b95a we now have array-based lists, so accessing the Nth element has
become O(1). Here we make the most of the O(1) lookups and just loop over the
set members of the Bitmapset with bms_next_member(). This performs slightly
better when a small number of the list items are in the Bitmapset. Micro
benchmarks show that when the Bitmapset contains all or most of the list items
then the new code is ever so slightly slower. In practice, the cost is so
small that it's drowned out by various other things such as locking the
relations belonging to each subplan, etc. The primary goal here is to leave
better code examples around which benefit better from the new list
implementation. Reviewed-by: Tom Lane Discussion:
https://postgr.es/m/CAKJS1f8ZcsLVgkF4wOfRyMYTcPgLFiUAOedFC+U2vK_aFZk-BA@mail.gmail.com
https://git.postgresql.org/pg/commitdiff/efdcca55a3df27a12efb84a18bce6ea739927b80

- Use appendBinaryStringInfo in more places where the length is known. When we
already know the length that we're going to append, then it makes sense to use
appendBinaryStringInfo instead of appendStringInfoString so that the append
can be performed with a simple memcpy() using a known length rather than
having to first perform a strlen() call to obtain the length. Discussion:
https://postgr.es/m/CAKJS1f8+FRAM1s5+mAa3isajeEoAaicJ=4e0WzrH3tAusbbiMQ@mail.gmail.com
https://git.postgresql.org/pg/commitdiff/1e6a759838f7c104f3cd1fe6981a98780da4131b

Michaël Paquier pushed:

- Fix inconsistencies and typos in the tree. This is numbered take 7, and
addresses a set of issues with code comments, variable names and unreferenced
variables. Author: Alexander Lakhin Discussion:
https://postgr.es/m/dff75442-2468-f74f-568c-6006e141062f@gmail.com
https://git.postgresql.org/pg/commitdiff/23bccc823d434d9dcf3c12622fe260d9235baae2

- Remove more progname references in vacuumdb.c. Oversight in 5f384037. Author:
Álvaro Herrera Discussion:
https://postgr.es/m/20190722151806.GA22634@alvherre.pgsql
https://git.postgresql.org/pg/commitdiff/3cae75f4209bcbb06285544de0f1c59f717a3159

- Improve stability of TAP test for synchronous replication. Slow buildfarm
machines have run into issues with this TAP test caused by a race condition
related to the startup of a set of standbys, where it is possible to finish
with an unexpected order in the WAL sender array of the primary. This closes
the race condition by making sure that any standby started is registered into
the WAL sender array of the primary before starting the next one based on
lookups of pg_stat_replication. Backpatch down to 9.6 where the test has been
introduced. Author: Michael Paquier Reviewed-by: Álvaro Herrera, Noah Misch
Discussion: https://postgr.es/m/20190617055145.GB18917@paquier.xyz
Backpatch-through: 9.6
https://git.postgresql.org/pg/commitdiff/7d81bdc8c0ce838efa248928065e9b2da829f981

- Doc: Clarify interactions of pg_receivewal with remote_apply. Using
pg_receivewal with synchronous_commit = remote_apply set in the backend is
incompatible if pg_receivewal is a synchronous standby as it never applies
WAL, so document this problem and solutions to it. Backpatch to 9.6, where
remote_apply has been added. Author: Robert Haas, Jesper Pedersen
Reviewed-by: Laurenz Albe, Álvaro Herrera, Michael Paquier Discussion:
https://postgr.es/m/1427a2d3-1e51-9335-1931-4f8853d90d5e@redhat.com
Backpatch-through: 9.6
https://git.postgresql.org/pg/commitdiff/fd7d387e0548fd371c06a91d75bc4632541ccfdd

- Fix failure with pgperlcritic from the TAP test of synchronous replication.
Oversight in 7d81bdc, which introduced a new routine in perl lacking a return
clause. Per buildfarm member crake. Backpatch down to 9.6 like its parent.
Reported-by: Andrew Dunstan Discussion:
https://postgr.es/m/16da29fa-d504-1380-7095-40de586dc038@2ndQuadrant.com
Backpatch-through: 9.6
https://git.postgresql.org/pg/commitdiff/c8e177f0bba6bcd9db7180580d58968974d8f6a9

- Add support for --jobs in reindexdb. When doing a schema-level or a
database-level operation, a list of relations to build is created which gets
processed in parallel using multiple connections, based on the recent
refactoring for parallel slots in src/bin/scripts/. System catalogs are
processed first in a serialized fashion to prevent deadlocks, followed by the
rest done in parallel. This new option is not compatible with --system as
reindexing system catalogs in parallel can lead to deadlocks, and with --index
as there is no conflict handling for indexes rebuilt in parallel depending in
the same relation. Author: Julien Rouhaud Reviewed-by: Sergei Kornilov,
Michael Paquier Discussion:
https://postgr.es/m/CAOBaU_YrnH_Jqo46NhaJ7uRBiWWEcS40VNRQxgFbqYo9kApUsg@mail.gmail.com
https://git.postgresql.org/pg/commitdiff/5ab892c391c6bc54a00e7a8de5cab077cabace6a

- Fix typo in fd.c. The frontend version of walkdir() is defined in
file_utils.c, and not initdb.c. Author: Sehrope Sarkuni Discussion:
https://postgr.es/m/CAH7T-artawnBt4=KODNCD8Mt2ZX4CCjJT8c=_=950xjutcRZ4Q@mail.gmail.com
https://git.postgresql.org/pg/commitdiff/b7a82317b66362880c16f1bd49573ab34f0dad1f

- Doc: Fix event trigger firing table. The table has not been updated for some
commands introduced in recent releases, so refresh it. While on it, reorder
entries alphabetically. Backpatch all the way down for all the commands which
have gone missing. Reported-by: Jeremy Smith Discussion:
https://postgr.es/m/15883-afff0ea3cc2dbbb6@postgresql.org Backpatch-through:
9.4
https://git.postgresql.org/pg/commitdiff/44460d7017cde005d7a2e246db0b32375bfec15d

Peter Eisentraut pushed:

- Make identity sequence management more robust. Some code could get confused
when certain catalog state involving both identity and serial sequences was
present, perhaps during an attempt to upgrade the latter to the former.
Specifically, dropping the default of a serial column maintains the ownership
of the sequence by the column, and so it would then be possible to afterwards
make the column an identity column that would now own two sequences. This
causes the code that looks up the identity sequence to error out, making the
new identity column inoperable until the ownership of the previous sequence is
released. To fix this, make the identity sequence lookup only consider
sequences with the appropriate dependency type for an identity sequence, so it
only ever finds one (unless something else is broken). In the above example,
the old serial sequence would then be ignored. Reorganize the various
owned-sequence-lookup functions a bit to make this clearer. Reported-by:
Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> Discussion:
https://www.postgresql.org/message-id/flat/470c54fc8590be4de0f41b0d295fd6390d5e8a6c(dot)camel(at)cybertec(dot)at
https://git.postgresql.org/pg/commitdiff/19781729f789f3c6b2540e02b96f8aa500460322

- initdb: Change authentication defaults. Change the defaults for the
pg_hba.conf generated by initdb to "peer" for local (if supported, else "md5")
and "md5" for host. (Changing from "md5" to SCRAM is left as a separate
exercise.) "peer" is currently not supported on AIX, HP-UX, and Windows.
Users on those operating systems will now either have to provide a password to
initdb or choose a different authentication method when running initdb.
Reviewed-by: Julien Rouhaud <rjuju123(at)gmail(dot)com> Discussion:
https://www.postgresql.org/message-id/flat/bec17f0a-ddb1-8b95-5e69-368d9d0a3390%40postgresql.org
https://git.postgresql.org/pg/commitdiff/09f08930f0f6fd4a7350ac02f29124b919727198

- Revert "initdb: Change authentication defaults". This reverts commit
09f08930f0f6fd4a7350ac02f29124b919727198. The buildfarm client needs some
adjustments first.
https://git.postgresql.org/pg/commitdiff/7961886580a594e519ca7ed1811b464206738be5

- Add CREATE DATABASE LOCALE option. This sets both LC_COLLATE and LC_CTYPE with
one option. Similar behavior is already supported in initdb, CREATE
COLLATION, and createdb. Reviewed-by: Fabien COELHO <coelho(at)cri(dot)ensmp(dot)fr>
Discussion:
https://www.postgresql.org/message-id/flat/d9d5043a-dc70-da8a-0166-1e218e6e34d4%402ndquadrant.com
https://git.postgresql.org/pg/commitdiff/06140c201b982436974d71e756d7331767a41e57

- doc: Fix typo.
https://git.postgresql.org/pg/commitdiff/35a34e62ed4974b9178a2dc924d645d6a12e7e9a

- doc: Change libpq function ids to mixed case. The ids for linking to libpq
functions were previously all lower-case. Change to mixed-case, matching the
actual function name, for easier readability in the source. The output isn't
changed in a significant way, since the ids are converted to lower or upper
case for file names and anchors.
https://git.postgresql.org/pg/commitdiff/d0f5d25b393747157d35c775e0942a0c68957823

- doc: Add support for xref to command and function elements. Discussion:
https://www.postgresql.org/message-id/517abe28-8a93-5b7a-ff40-b1fd61d33b26%402ndquadrant.com
https://git.postgresql.org/pg/commitdiff/2e32a7711a8a6e3020c9fb431705dadaed305120

- doc: Make libpq documentation navigable between functions. Turn most mentions
of libpq functions into links. At id attributes to most libpq functions,
where not existing yet, so that they can be linked to. (In a handful of cases
there were problems with the PDF processing toolchain, so those instances were
not changed.) Author: Fabien COELHO <coelho(at)cri(dot)ensmp(dot)fr> Reviewed-by: Peter
Eisentraut <peter(dot)eisentraut(at)2ndquadrant(dot)com> Discussion:
https://www.postgresql.org/message-id/flat/alpine(dot)DEB(dot)2(dot)21(dot)1905121032330(dot)27203(at)lancre
https://git.postgresql.org/pg/commitdiff/e829337d42d0e3c73ba9f380e81e51aab4865f8f

- doc: Fix some markup whitespace issues. When making an xref to a varlistentry,
the stylesheets use the first <term> as the link text. In the cases fixed
here, the <term> element contained extra whitespace that ended up being part
of the link text, which looked strange in the output in some cases. This
whitespace is significant, so remove it since we don't want it.
https://git.postgresql.org/pg/commitdiff/f4100839a00a9fffb19e70ed2e3c6a73ce2a5259

- Fix typo in pg_upgrade file header. Author: Daniel Gustafsson
<daniel(at)yesql(dot)se>
https://git.postgresql.org/pg/commitdiff/28cb0555c1153a0dcdf1c908d7265acafa413b57

- pg_upgrade: Check all used executables. Expand the validate_exec() calls to
cover all the used binaries. Author: Daniel Gustafsson <daniel(at)yesql(dot)se>
Reviewed-by: Peter Eisentraut <peter(dot)eisentraut(at)2ndquadrant(dot)com> Discussion:
https://www.postgresql.org/message-id/flat/9328(dot)1552952117(at)sss(dot)pgh(dot)pa(dot)us
https://git.postgresql.org/pg/commitdiff/0befb4f31386efb622e4df9f3a313aa1f2e17899

- pg_upgrade: Default new bindir to pg_upgrade location. Make the directory
where the pg_upgrade binary resides the default for new bindir, as running the
pg_upgrade binary from where the new cluster is installed is a very common
scenario. Setting this as the defauly bindir for the new cluster will remove
the need to provide it explicitly via -B in many cases. To support
directories being missing from option parsing, extend the directory check with
a missingOk mode where the path must be filled at a later point before being
used. Also move the exec_path check to earlier in setup to make sure we know
the new cluster bindir when we scan for required executables. This removes
the exec_path from the OSInfo struct as it is not used anywhere. Author:
Daniel Gustafsson <daniel(at)yesql(dot)se> Reviewed-by: Peter Eisentraut
<peter(dot)eisentraut(at)2ndquadrant(dot)com> Discussion:
https://www.postgresql.org/message-id/flat/9328(dot)1552952117(at)sss(dot)pgh(dot)pa(dot)us
https://git.postgresql.org/pg/commitdiff/959f6d6a1821b7d9068244f500dd80953e768d16

- pg_upgrade: Update obsolescent documentation note. Recently released xfsprogs
5.1.0 has reflink support enabled by default, so the note that it's not
enabled by default can be removed.
https://git.postgresql.org/pg/commitdiff/4552c0f566667160ab3eeaf1620ebb969c1e7eb0

Tom Lane pushed:

- Install dependencies to prevent dropping partition key columns. The logic in
ATExecDropColumn that rejects dropping partition key columns is quite an
inadequate defense, because it doesn't execute in cases where a column needs
to be dropped due to cascade from something that only the column, not the
whole partitioned table, depends on. That leaves us with a badly broken
partitioned table; even an attempt to load its relcache entry will fail. We
really need to have explicit pg_depend entries that show that the column can't
be dropped without dropping the whole table. Hence, add those entries. In
v12 and HEAD, bump catversion to ensure that partitioned tables will have such
entries. We can't do that in released branches of course, so in v10 and v11
this patch affords protection only to partitioned tables created after the
patch is installed. Given the lack of field complaints (this bug was found by
fuzz-testing not by end users), that's probably good enough. In passing, fix
ATExecDropColumn and ATPrepAlterColumnType messages to be more specific about
which partition key column they're complaining about. Per report from Manuel
Rigger. Back-patch to v10 where partitioned tables were added. Discussion:
https://postgr.es/m/CA+u7OA4JKCPFrdrAbOs7XBiCyD61XJxeNav4LefkSmBLQ-Vobg@mail.gmail.com
Discussion: https://postgr.es/m/31920.1562526703@sss.pgh.pa.us
https://git.postgresql.org/pg/commitdiff/a0555ddab9b672a04681ce0d9f6c94104c01b15f

- Improve psql's \d output for TOAST tables. Add the name of the owning table to
the footers for a TOAST table. Also, show all the same footers as for a
regular table (in practice, this adds the index and perhaps the tablespace and
access method). Justin Pryzby, reviewed by Fabien Coelho Discussion:
https://postgr.es/m/20190422154902.GH14223@telsasoft.com
https://git.postgresql.org/pg/commitdiff/eb5472da9f83c2e432ac27a053929947e354d20c

- Improve psql's \d output for partitioned indexes. Include partitioning
information much as we do for partitioned tables. (However, \d+ doesn't show
the partition bounds, because those are not stored for indexes.) In passing,
fix a couple of queries to look less messy in -E output. Also, add some tests
for \d on tables with nondefault tablespaces. (Somebody previously added a
rather silly number of tests for \d on partitioned indexes, yet completely
neglected other cases.) Justin Pryzby, reviewed by Fabien Coelho Discussion:
https://postgr.es/m/20190422154902.GH14223@telsasoft.com
https://git.postgresql.org/pg/commitdiff/24f62e93f314c107b4fa679869e5ba9adb2d545f

- Fix infelicities in describeOneTableDetails' partitioned-table handling.
describeOneTableDetails issued a partition-constraint-fetching query for every
table, even ones it knows perfectly well are not partitions. To add insult to
injury, it then proceeded to leak the empty PGresult if the table wasn't a
partition. Doing that a lot of times might amount to a meaningful leak, so
this seems like a back-patchable bug. Fix that, and also fix a related
PGresult leak in the partition-parent case (though that leak would occur only
if we got no row, which is unexpected). Minor code beautification too, to
make this code look more like the pre-existing code around it. Back-patch the
whole change into v12. However, the fact that we already know whether the
table is a partition dates only to commit 1af25ca0c; back-patching the
relevant changes from that is probably more churn than is justified in
released branches. Hence, in v11 and v10, just do the minimum to fix the
PGresult leaks. Noted while messing around with adjacent code for yesterday's
\d improvements.
https://git.postgresql.org/pg/commitdiff/4e784f35145bc6e01d54282afe10d9bb5200ebfe

- Fix contrib/sepgsql test policy to work with latest SELinux releases. As of
Fedora 30, it seems that the system-provided macros for setting up user
privileges in SELinux policies don't grant the ability to read /etc/passwd, as
they formerly did. This restriction breaks psql (which tries to use
getpwuid() to obtain the user name it's running under) and thereby the
contrib/sepgsql regression test. Add explicit specifications that we need the
right to read /etc/passwd. Mike Palmiotto, per a report from me. Back-patch
to all supported branches. Discussion:
https://postgr.es/m/23856.1563381159@sss.pgh.pa.us
https://git.postgresql.org/pg/commitdiff/f5a4ab23e42ac35862e3f7dc021a41f41a34386c

- Fix failures to ignore \r when reading Windows-style newlines. libpq failed to
ignore Windows-style newlines in connection service files. This normally
wasn't a problem on Windows itself, because fgets() would convert \r\n to just
\n. But if libpq were running inside a program that changes the default fopen
mode to binary, it would see the \r's and think they were data. In any case,
it's project policy to ignore \r in text files unconditionally, because people
sometimes try to use files with DOS-style newlines on Unix machines, where the
C library won't hide that from us. Hence, adjust parseServiceFile() to ignore
\r as well as \n at the end of the line. In HEAD, go a little further and
make it ignore all trailing whitespace, to match what it's always done with
leading whitespace. In HEAD, also run around and fix up everyplace where we
have newline-chomping code to make all those places look consistent and
uniformly drop \r. It is not clear whether any of those changes are fixing
live bugs. Most of the non-cosmetic changes are in places that are reading
popen output, and the jury is still out as to whether popen on Windows can
return \r\n. (The Windows-specific code in pipe_read_line seems to think so,
but our lack of support for this elsewhere suggests maybe it's not a problem
in practice.) Hence, I desisted from applying those changes to back branches,
except in run_ssl_passphrase_command() which is new enough and little-tested
enough that we'd probably not have heard about any problems there. Tom Lane
and Michael Paquier, per bug #15827 from Jorge Gustavo Rocha. Back-patch the
parseServiceFile() change to all supported branches, and the
run_ssl_passphrase_command() change to v11 where that was added. Discussion:
https://postgr.es/m/15827-e6ba53a3a7ed543c@postgresql.org
https://git.postgresql.org/pg/commitdiff/b654714f9bcfb4443bcc531c32f059fd85f798ec

- Fix syntax error in commit 20e99cddd. Per buildfarm.
https://git.postgresql.org/pg/commitdiff/cb9bb15783f2d6b2e66f7c18bc35e849df623dfa

- Fix loss of fractional digits for large values in cash_numeric(). Money values
exceeding about 18 digits (depending on lc_monetary) could be inaccurately
converted to numeric, due to select_div_scale() deciding it didn't need to
compute any fractional digits. Force its hand by setting the dscale of one
division input to equal the number of fractional digits we need. In passing,
rearrange the logic to not do useless work in locales where money values are
considered integral. Per bug #15925 from Slawomir Chodnicki. Back-patch to
all supported branches. Discussion:
https://postgr.es/m/15925-da9953e2674bb5c8@postgresql.org
https://git.postgresql.org/pg/commitdiff/b9d2c5c7ac800bf20ea6cd4c556b6b3305863a8e

- Avoid choosing "localtime" or "posixrules" as TimeZone during initdb. Some
platforms create a file named "localtime" in the system timezone directory,
making it a copy or link to the active time zone file. If Postgres is built
with --with-system-tzdata, initdb will see that file as an exact match to
localtime(3)'s behavior, and it may decide that "localtime" is the most
preferred spelling of the active zone. That's a very bad choice though,
because it's neither informative, nor portable, nor stable if someone changes
the system timezone setting. Extend the preference logic added by commit
e3846a00c so that we will prefer any other zone file that matches localtime's
behavior over "localtime". On the same logic, also discriminate against
"posixrules", which is another not-really-a-zone file that is often present in
the timezone directory. (Since we install "posixrules" but not "localtime",
this change can affect the behavior of Postgres with or without
--with-system-tzdata.) Note that this change doesn't prevent anyone from
choosing these pseudo-zones if they really want to (i.e., by setting TZ for
initdb, or modifying the timezone GUC later on). It just prevents initdb from
preferring these zone names when there are multiple matches to localtime's
behavior. Since we generally prefer to keep timezone-related behavior the
same in all branches, and since this is arguably a bug fix, back-patch to all
supported branches. Discussion:
https://postgr.es/m/CADT4RqCCnj6FKLisvT8tTPfTP4azPhhDFJqDF1JfBbOH5w4oyQ@mail.gmail.com
Discussion: https://postgr.es/m/27991.1560984458@sss.pgh.pa.us
https://git.postgresql.org/pg/commitdiff/3754113f33651965d92aaad6f393757ac0cf8333

- Tweak our special-case logic for the IANA "Factory" timezone.
pg_timezone_names() tries to avoid showing the "Factory" zone in the view,
mainly because that has traditionally had a very long "abbreviation" such as
"Local time zone must be set--see zic manual page", so that showing it messes
up psql's formatting of the whole view. Since tzdb version 2016g, IANA instead
uses the abbreviation "-00", which is sane enough that there's no reason to
discriminate against it. On the other hand, it emerges that FreeBSD and
possibly other packagers are so wedded to backwards compatibility that they
hack the IANA data to keep the old spelling --- and not just that old
spelling, but even older spellings that IANA used back in the stone age. This
caused the filter logic to fail to suppress "Factory" at all on such
platforms, though the formatting problem is definitely real in that case. To
solve both problems, get rid of the hard-wired assumption about exactly what
Factory's abbreviation is, and instead reject abbreviations exceeding 31
characters. This will allow Factory to appear in the view if and only if it's
using the modern abbreviation. In passing, simplify the code we add to zic.c
to support "zic -P" to remove its now-obsolete hacks to not print the Factory
zone's abbreviation. Unlike pg_timezone_names(), there's no reason for that
code to support old/nonstandard timezone data. Since we generally prefer to
keep timezone-related behavior the same in all branches, and since this is
arguably a bug fix, back-patch to all supported branches. Discussion:
https://postgr.es/m/3961.1564086915@sss.pgh.pa.us
https://git.postgresql.org/pg/commitdiff/8ab66081ca496fd74c406e435e20f4264881a02d

- Don't drop NOTICE messages in isolation tests. For its entire existence,
isolationtester.c has forced client_min_messages to WARNING, but that seems
like a very poor choice of test design. It should be up to individual test
scripts to manage whether they emit notices and to ensure that the results are
stable. (There were no NOTICE messages in the original set of isolation
tests, so this was certainly dead code when committed, but perhaps it was
needed at some earlier point.) It's possible that the original motivation was
due to platform-dependent variations in the timing of stdout vs. stderr
output. That should be moot since commits 73bcb76b7/6eda3e9c2, but just in
case, adjust isotesterNoticeProcessor to print to stdout not stderr. (stderr
seems like the wrong thing anyway: it should be for error printouts not
expected test output.) Testing shows that the notices in
insert-conflict-specconflict are indeed a bit timing-unstable on very slow
machines, so hide them; maybe we can improve that later. Also, make the
notices in plpgsql-toast a bit less verbose than the original code would've
had them. Discussion: https://postgr.es/m/14616.1564251339@sss.pgh.pa.us
https://git.postgresql.org/pg/commitdiff/ebd49928215e3854d91167e798949a75b34958d0

- Fix isolationtester race condition for notices sent before blocking. If a test
sends a notice just before blocking, it's possible on slow machines for
isolationtester to detect the blocked state before it's consumed the notice.
(For this to happen, the notice would have to arrive after isolationtester has
waited for data for 10ms, so on fast/lightly-loaded machines it's hard to
reproduce the failure.) But, if we have seen the backend as blocked, it's
certainly already sent any notices it's going to send. Therefore, one more
round of PQconsumeInput and PQisBusy should be enough to collect and process
any such notices. This appears to explain the instability noted in commit
ebd499282, so undo the hack therein to not print notices from
insert-conflict-specconflict. Patch by me, diagnosis by Andres Freund.
Discussion: https://postgr.es/m/14616.1564251339@sss.pgh.pa.us
https://git.postgresql.org/pg/commitdiff/30717637c1c58fcd02980a6752c7e13c9de12b69

Álvaro Herrera pushed:

- Check that partitions are not in use when dropping constraints. If the user
creates a deferred constraint in a partition, and in a transaction they cause
the constraint's trigger execution to be deferred until commit time *and* drop
the constraint, then when commit time comes the queued trigger will fail to
run because the trigger object will have been dropped. This is explained
because when a constraint gets dropped in a partitioned table, the recursion
to drop the ones in partitions is done by the dependency mechanism, not by
ALTER TABLE traversing the recursion tree as in all other cases. In the
non-partitioned case, this problem is avoided by checking that the table is
not "in use" by alter-table; other alter-table subcommands that recurse to
partitions do that check for each partition. But the dependency mechanism
doesn't have a way to do that. Fix the problem by applying the same check to
all partitions during ALTER TABLE's "prep" phase, which correctly raises the
necessary error. Reported-by: Rajkumar Raghuwanshi
<rajkumar(dot)raghuwanshi(at)enterprisedb(dot)com> Discussion:
https://postgr.es/m/CAKcux6nZiO9-eEpr1ZD84bT1mBoVmeZkfont8iSpcmYrjhGWgA@mail.gmail.com
https://git.postgresql.org/pg/commitdiff/5562272a4229cfa57354aa203cffd36b4e7f70cb

- Don't uselessly escape a string that doesn't need escaping. Per gripe from Ian
Barwick Co-authored-by: Ian Barwick <ian(at)2ndquadrant(dot)com> Discussion:
https://postgr.es/m/CABvVfJWNnNKb8cHsTLhkTsvL1+G6BVcV+57+w1JZ61p8YGPdWQ@mail.gmail.com
https://git.postgresql.org/pg/commitdiff/0994cfc0ac853de4245f003698160fe1b8c577bd

Andrew Dunstan pushed:

- Don't assume expr is available in pgbench tests. Windows hosts do not normally
come with expr, so instead of using that to test the \setshell command, use
echo instead, which is fairly universally available. Backpatch to release 11,
where this came in. Problem found by me, patch by Fabien Coelho.
https://git.postgresql.org/pg/commitdiff/1a721248f3899ccf8c0c7512b91d8458f2394aeb

- Honor MSVC WindowsSDKVersion if set. Add a line to the project file setting
the target SDK. Otherwise, in for example VS2017, if the default but optional
8.1 SDK is not installed the build will fail. Patch from Peifeng Qiu,
slightly edited by me. Discussion:
https://postgr.es/m/CABmtVJhw1boP_bd4=b3Qv5YnqEdL696NtHFi2ruiyQ6mFHkeQQ@mail.gmail.com
Backpatch to all live branches.
https://git.postgresql.org/pg/commitdiff/20e99cdddbd3b55257827d621c2f9c592521cd4b

Heikki Linnakangas pushed:

- Refactor checks for deleted GiST pages. The explicit check in gistScanPage()
isn't currently really necessary, as a deleted page is always empty, so the
loop would fall through without doing anything, anyway. But it's a marginal
optimization, and it gives a nice place to attach a comment to explain how it
works. Backpatch to v12, where GiST page deletion was introduced.
Reviewed-by: Andrey Borodin Discussion:
https://www.postgresql.org/message-id/835A15A5-F1B4-4446-A711-BF48357EB602%40yandex-team.ru
https://git.postgresql.org/pg/commitdiff/9eb5607e69933f0a88b6774d1ba728f27afdbd3d

- Use full 64-bit XID for checking if a deleted GiST page is old enough.
Otherwise, after a deleted page gets even older, it becomes unrecyclable
again. B-tree has the same problem, and has had since time immemorial, but
let's at least fix this in GiST, where this is new. Backpatch to v12, where
GiST page deletion was introduced. Reviewed-by: Andrey Borodin Discussion:
https://www.postgresql.org/message-id/835A15A5-F1B4-4446-A711-BF48357EB602%40yandex-team.ru
https://git.postgresql.org/pg/commitdiff/6655a7299d835dea9e8e0ba69cc5284611b96f29

Andres Freund pushed:

- Fix system column accesses in ON CONFLICT ... RETURNING. After 277cb789836 ON
CONFLICT ... SET ... RETURNING failed with ERROR: virtual tuple table slot
does not have system attributes when taking the update path, as the slot used
to insert into the table (and then process RETURNING) was defined to be a
virtual slot in that commit. Virtual slots don't support system columns except
for tableoid and ctid, as the other system columns are AM dependent. Fix that
by using a slot of the table's type. Add tests for system column accesses in
ON CONFLICT ... RETURNING. Reported-By: Roby, bisected to the relevant
commit by Jeff Janes Author: Andres Freund Discussion:
https://postgr.es/m/73436355-6432-49B1-92ED-1FE4F7E7E100@finefun.com.au
Backpatch: 12-, where the bug was introduced in 277cb789836
https://git.postgresql.org/pg/commitdiff/ecbdd009344d3a00733e4382f50137b5e0248ce8

- Fix slot type handling for Agg nodes performing internal sorts. Since
15d8f8312 we assert that - and since 7ef04e4d2cb2, 4da597edf1 rely on - the
slot type for an expression's ecxt_{outer,inner,scan}tuple not changing,
unless explicitly flagged as such. That allows to either skip deforming (for a
virtual tuple slot) or optimize the code for JIT accelerated deforming
appropriately (for other known slot types). This assumption was sometimes
violated for grouping sets, when nodeAgg.c internally uses tuplesorts, and the
child node doesn't return a TTSOpsMinimalTuple type slot. Detect that case,
and flag that the outer slot might not be "fixed". It's probably worthwhile
to optimize this further in the future, and more granularly determine whether
the slot is fixed. As we already instantiate per-phase transition and equal
expressions, we could cheaply set the slot type appropriately for each phase.
But that's a separate change from this bugfix. This commit does include a
very minor optimization by avoiding to create a slot for handling tuplesorts,
if no such sorts are performed. Previously we created that slot unnecessarily
in the common case of computing all grouping sets via hashing. The code looked
too confusing without that, as the conditions for needing a sort slot and
flagging that the slot type isn't fixed, are the same. Reported-By: Ashutosh
Sharma Author: Andres Freund Discussion:
https://postgr.es/m/CAE9k0PmNaMD2oHTEAhRyxnxpaDaYkuBYkLa1dpOpn=RS0iS2AQ@mail.gmail.com
Backpatch: 12-, where the bug was introduced in 15d8f8312
https://git.postgresql.org/pg/commitdiff/af3deff3f2ac79585481181cb198b04c67486c09

- Add missing (COSTS OFF) to EXPLAIN added in previous commit. Backpatch: 12-,
like the previous commit
https://git.postgresql.org/pg/commitdiff/f63d9e68d4132a4608e9f50782aaacbe5ed6f57a

Thomas Munro pushed:

- Fix LDAP test instability. After starting slapd, wait until it can accept a
connection before beginning the real test work. This avoids occasional test
failures. Back-patch to 11, where the LDAP tests arrived. Author: Thomas
Munro Reviewed-by: Michael Paquier Discussion:
https://postgr.es/m/20190719033013.GI1859%40paquier.xyz
https://git.postgresql.org/pg/commitdiff/27cd521e6e7084516fbc5e5a8492316b3ba8c25c

== Pending Patches ==

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

Dagfinn Ilmari Mannsåker sent in another revision of a patch to add an
appendStringInfoStringInfo() function.

Peter Eisentraut sent in another revision of a patch to add support for
backtraces.

Tsutomu Yamada sent in two more revisions of a patch to add a progress report
for ANALYZE.

Nikita Glukhov sent in another revision of a patch to add psql AM info commands.

Jehan-Guillaume de Rorthais sent in a patch to document pg_receivewal's
remote_apply in more detail.

Ryo Matsumura sent in two revisions of a patch to add
CONNECTION_CHECK_WRITABLE_NEEDED to libpq.

Ning Yu sent in another revision of a patch to fix a race condition in
pg_mkdir_p(), test concurrent calls to pg_mkdir_p(), and fix some callers of
pg_mkdir_p() and MakePGDirectory().

Fabien COELHO sent in another revision of a patch to add a pseudo-random
permutation function to pgbench.

David Rowley sent in another revision of a patch to improve runtime pruning
EXPLAIN output.

David Rowley sent in three more revisions of a patch to shrink bloated
locallocktables.

Jehan-Guillaume de Rorthais sent in four revisions of a patch to enable fetching
timeline during recovery.

Anastasia Lubennikova sent in two more revisions of a patch to Simplify
immutable RTE_FUNCTION to RTE_RESULT.

Fabien COELHO sent in four revisions of a patch to enable pgbench to create
partitioned tables.

Daniel Gustafsson sent in two more revisions of a patch to pg_upgrade to ensure
that it only allows upgrades by the same exact version new bindir, checks all
used executables, and makes the directory where the pg_upgrade binary resides
the default for new bindir.

Jesper Krogh sent in another revision of a patch to speed up truncates of
relation forks.

Joe Nelson sent in a patch to change some atoi calls to strtol.

Jeff Janes sent in a patch to ensure that pg_basebackup closes stdout.

Fabien COELHO sent in a patch to fix a bug in pgbench tests on windows.

Yuli Khodorkovskiy sent in a patch to add a MAC check for TRUNCATE.

John Naylor sent in another revision of a patch to handle uescapes in the
parser.

Peter Geoghegan sent in another revision of a patch to use compression/deduplication
in nbtree, add pageinspect instrumentation for same, and account for posting
list overhead during splits.

Suraj Kharage sent in a patch to fix an odd behavior in to_timestamp and
to_date.

Dmitry Dolgov sent in another revision of a patch to implement index skip scans.

Fabien COELHO sent in three more revisions of a patch to ensure that psql
returns multiple result sets in multi-query string rather that dropping all
after the first.

Craig Ringer sent in a patch to fix a race condition in logical walsender which
causes long PostgreSQL shutdown delay.

Binguo Bao sent in another revision of a patch to de-TOAST using an iterator.

James Coleman sent in a patch to document effects of autovacuum interruption
caused by frequent DDL commands.

Vigneshwaran C sent in a patch to fix a pg_upgrade failure by ensuring that the
old redo is passed.

Rafia Sabih sent in a patch to ensure that $PGDATAs that are too long fail early
in the initdb process.

Kyotaro HORIGUCHI sent in another revision of a patch to add a TAP test for the
copy-truncation optimization, fix the WAL-skipping feature, and rename
smgrDoPendingDeletes to smgrDoPendingOperations to reflect the new reality.

Konstantin Knizhnik sent in another revision of a patch to add a built-in
connection pooler.

Kyotaro HORIGUCHI sent in a patch to ensure that messages after COMPLETE message
in libpq are not parsed.

Kyotaro HORIGUCHI sent in a patch to fix the offset of pg_walfile_name_offset.

Andrew Dunstan sent in a patch to better document TestLib.

Fabien COELHO sent in a patch to document the fact that FETCH_COUNT is incorrect
for combined queries.

Liudmila Mantrova sent in another revision of a patch to add pg_rewind options
to use restore_command.

Joe Nelson sent in another revision of a patch to make some jsonb functions more
efficient.

Tomáš Vondra sent in another revision of a patch to add a SET STATISTICS option
to ALTER STATISTICS.

Tom Lane sent in a patch to detect duplicate NOTIFYs by hashing.

Chengchao Yu sent in a patch to fix a deadlock issue in single user mode when an
I/O failure occurs.

Álvaro Herrera sent in another revision of a patch to decode XACT_ASSIGNMENT
while building snapshot.

Andres Freund sent in a patch to suppress a misleading warning when specifying a
config a PGC_POSTMASTER variable on the command line.

Peter Geoghegan sent in another revision of a patch to add sort support for the
inet/cidr opfamily.

Thomas Munro sent in a patch to avoid a macro clash with LLVM 9.

Tom Lane sent in four revisions of a patch to test LISTEN/NOTIFY more
effectively.

Andres Freund sent in a patch to make some minor fixes after the pgindent
prototype fixes.

Alexander Lakhin sent in another revision of a patch to fix typos and
inconsistencies.

Daniel Migowski sent in a patch to add a "mem_usage" column to the
pg_prepared_statements view.

Tomáš Vondra sent in a patch to ensure that stats are not built for inheritance
trees.