PostgreSQL 11.6 (upcoming) commit log

Disallow changing an inherited column's type if not all parents changed.

commit   : 909efc4498a071df1198677bc73366f0e29492ce    
  
author   : Tom Lane <tgl@sss.pgh.pa.us>    
date     : Sun, 18 Aug 2019 17:11:58 -0400    
  
committer: Tom Lane <tgl@sss.pgh.pa.us>    
date     : Sun, 18 Aug 2019 17:11:58 -0400    

Click here for diff

If a table inherits from multiple unrelated parents, we must disallow  
changing the type of a column inherited from multiple such parents, else  
it would be out of step with the other parents.  However, it's possible  
for the column to ultimately be inherited from just one common ancestor,  
in which case a change starting from that ancestor should still be  
allowed.  (I would not be excited about preserving that option, were  
it not that we have regression test cases exercising it already ...)  
  
It's slightly annoying that this patch looks different from the logic  
with the same end goal in renameatt(), and more annoying that it  
requires an extra syscache lookup to make the test.  However, the  
recursion logic is quite different in the two functions, and a  
back-patched bug fix is no place to be trying to unify them.  
  
Per report from Manuel Rigger.  Back-patch to 9.5.  The bug exists in  
9.4 too (and doubtless much further back); but the way the recursion  
is done in 9.4 is a good bit different, so that substantial refactoring  
would be needed to fix it in 9.4.  I'm disinclined to do that, or risk  
introducing new bugs, for a bug that has escaped notice for this long.  
  
Discussion: https://postgr.es/m/CA+u7OA4qogDv9rz1HAb-ADxttXYPqQdUdPY_yd4kCzywNxRQXA@mail.gmail.com  

M src/backend/commands/tablecmds.c
M src/test/regress/expected/inherit.out
M src/test/regress/sql/inherit.sql

Prevent possible double-free when update trigger returns old tuple.

commit   : aed967d697de19a78a653926c72604f9b04c3b1e    
  
author   : Tom Lane <tgl@sss.pgh.pa.us>    
date     : Thu, 15 Aug 2019 20:04:19 -0400    
  
committer: Tom Lane <tgl@sss.pgh.pa.us>    
date     : Thu, 15 Aug 2019 20:04:19 -0400    

Click here for diff

This is a variant of the problem fixed in commit 25b692568, which  
unfortunately we failed to detect at the time.  If an update trigger  
returns the "old" tuple, as it's entitled to do, then a subsequent  
iteration of the loop in ExecBRUpdateTriggers would have "oldtuple"  
equal to "trigtuple" and would fail to notice that it shouldn't  
free that.  
  
In addition to fixing the code, extend the test case added by  
25b692568 so that it covers multiple-trigger-iterations cases.  
  
This problem does not manifest in v12/HEAD, as a result of the  
relevant code having been largely rewritten for slotification.  
However, include the test case into v12/HEAD anyway, since this  
is clearly an area that someone could break again in future.  
  
Per report from Piotr Gabriel Kosinski.  Back-patch into all  
supported branches, since the bug seems quite old.  
  
Diagnosis and code fix by Thomas Munro, test case by me.  
  
Discussion: https://postgr.es/m/CAFMLSdP0rd7LqC3j-H6Fh51FYSt5A10DDh-3=W4PPc4LLUQ8YQ@mail.gmail.com  

M src/backend/commands/trigger.c
M src/test/regress/expected/triggers.out
M src/test/regress/sql/triggers.sql

Fix plpgsql to re-look-up composite type names at need.

commit   : 6070ccdd179f34efecc92d6679a141093df0f879    
  
author   : Tom Lane <tgl@sss.pgh.pa.us>    
date     : Thu, 15 Aug 2019 15:21:48 -0400    
  
committer: Tom Lane <tgl@sss.pgh.pa.us>    
date     : Thu, 15 Aug 2019 15:21:48 -0400    

Click here for diff

Commit 4b93f5799 rearranged things in plpgsql to make it cope better with  
composite types changing underneath it intra-session.  However, I failed to  
consider the case of a composite type being dropped and recreated entirely.  
In my defense, the previous coding didn't consider that possibility at all  
either --- but it would accidentally work so long as you didn't change the  
type's field list, because the built-at-compile-time list of component  
variables would then still match the type's new definition.  The new  
coding, however, occasionally tries to re-look-up the type by OID, and  
then fails to find the dropped type.  
  
To fix this, we need to save the TypeName struct, and then redo the type  
OID lookup from that.  Of course that's expensive, so we don't want to do  
it every time we need the type OID.  This can be fixed in the same way that  
4b93f5799 dealt with changes to composite types' definitions: keep an eye  
on the type's typcache entry to see if its tupledesc has been invalidated.  
(Perhaps, at some point, this mechanism should be generalized so it can  
work for non-composite types too; but for now, plpgsql only tries to  
cope with intra-session redefinitions of composites.)  
  
I'm slightly hesitant to back-patch this into v11, because it changes  
the contents of struct PLpgSQL_type as well as the signature of  
plpgsql_build_datatype(), so in principle it could break code that is  
poking into the innards of plpgsql.  However, the only popular extension  
of that ilk is pldebugger, and it doesn't seem to be affected.  Since  
this is a regression for people who were relying on the old behavior,  
it seems worth taking the small risk of causing compatibility issues.  
  
Per bug #15913 from Daniel Fiori.  Back-patch to v11 where 4b93f5799  
came in.  
  
Discussion: https://postgr.es/m/15913-a7e112e16dedcffc@postgresql.org  

M src/backend/utils/cache/typcache.c
M src/pl/plpgsql/src/expected/plpgsql_record.out
M src/pl/plpgsql/src/pl_comp.c
M src/pl/plpgsql/src/pl_exec.c
M src/pl/plpgsql/src/pl_gram.y
M src/pl/plpgsql/src/plpgsql.h
M src/pl/plpgsql/src/sql/plpgsql_record.sql

Doc: improve documentation about postgresql.auto.conf.

commit   : 7f77f2aec3d06e580915acf16c4d7c7c5d998f23    
  
author   : Tom Lane <tgl@sss.pgh.pa.us>    
date     : Thu, 15 Aug 2019 11:14:26 -0400    
  
committer: Tom Lane <tgl@sss.pgh.pa.us>    
date     : Thu, 15 Aug 2019 11:14:26 -0400    

Click here for diff

Clarify what external tools can do to this file, and add a bit  
of detail about what ALTER SYSTEM itself does.  
  
Discussion: https://postgr.es/m/aed6cc9f-98f3-2693-ac81-52bb0052307e@2ndquadrant.com  

M doc/src/sgml/config.sgml

Fix ALTER SYSTEM to cope with duplicate entries in postgresql.auto.conf.

commit   : 32d38f54a369290cd5c5efb939c9f037a7c13e80    
  
author   : Tom Lane <tgl@sss.pgh.pa.us>    
date     : Wed, 14 Aug 2019 15:09:20 -0400    
  
committer: Tom Lane <tgl@sss.pgh.pa.us>    
date     : Wed, 14 Aug 2019 15:09:20 -0400    

Click here for diff

ALTER SYSTEM itself normally won't make duplicate entries (although  
up till this patch, it was possible to confuse it by writing case  
variants of a GUC's name).  However, if some external tool has appended  
entries to the file, that could result in duplicate entries for a single  
GUC name.  In such a situation, ALTER SYSTEM did exactly the wrong thing,  
because it replaced or removed only the first matching entry, leaving  
the later one(s) still there and hence still determining the active value.  
  
This patch fixes that by making ALTER SYSTEM sweep through the file and  
remove all matching entries, then (if not ALTER SYSTEM RESET) append the  
new setting to the end.  This means entries will be in order of last  
setting rather than first setting, but that shouldn't hurt anything.  
  
Also, make the comparisons case-insensitive so that the right things  
happen if you do, say, ALTER SYSTEM SET "TimeZone" = 'whatever'.  
  
This has been broken since ALTER SYSTEM was invented, so back-patch  
to all supported branches.  
  
Ian Barwick, with minor mods by me  
  
Discussion: https://postgr.es/m/aed6cc9f-98f3-2693-ac81-52bb0052307e@2ndquadrant.com  

M src/backend/utils/misc/guc.c

Un-break pg_dump for pre-8.3 source servers.

commit   : 4dea8ad566d3f41102a0ab79c7faefae0af71774    
  
author   : Tom Lane <tgl@sss.pgh.pa.us>    
date     : Tue, 13 Aug 2019 16:57:58 -0400    
  
committer: Tom Lane <tgl@sss.pgh.pa.us>    
date     : Tue, 13 Aug 2019 16:57:58 -0400    

Click here for diff

Commit 07b39083c inserted an unconditional reference to pg_opfamily,  
which of course fails on servers predating that catalog.  Fortunately,  
the case it's trying to solve can't occur on such old servers (AFAIK).  
Hence, just skip the additional code when the source predates 8.3.  
  
Per bug #15955 from sly.  Back-patch to all supported branches,  
like the previous patch.  
  
Discussion: https://postgr.es/m/15955-1daa2e676e903d87@postgresql.org  

M src/bin/pg_dump/pg_dump.c

Fix random regression failure in test case "temp"

commit   : 72f92549b64176e00dad433f8486e302eab8e9bd    
  
author   : Michael Paquier <michael@paquier.xyz>    
date     : Tue, 13 Aug 2019 10:56:02 +0900    
  
committer: Michael Paquier <michael@paquier.xyz>    
date     : Tue, 13 Aug 2019 10:56:02 +0900    

Click here for diff

This test case could fail because of an incorrect result ordering when  
looking up at pg_class entries.  This commit adds an ORDER BY to the  
culprit query.  The cause of the failure was likely caused by a plan  
switch.  By default, the planner would likely choose an index-only scan  
or an index scan, but even a small change in the startup cost could have  
caused a bitmap heap scan to be chosen, causing the failure.  
  
While on it, switch some filtering quals to a regular expression as per  
an idea of Tom Lane.  As previously shaped, the quals would have  
selected any relations whose name begins with "temp".  And that could  
cause failures if another test running in parallel began to use similar  
relation names.  
  
Per report from buildfarm member anole, though the failure was very  
rare.  This test has been introduced by 319a810, so backpatch down to  
v10.  
  
Discussion: https://postgr.es/m/20190807132422.GC15695@paquier.xyz  
Backpatch-through: 10  

M src/test/regress/expected/temp.out
M src/test/regress/sql/temp.sql

amcheck: Skip unlogged relations during recovery.

commit   : 4f393793f7d637e4ab8e6460e7bb4cd747f01f85    
  
author   : Peter Geoghegan <pg@bowt.ie>    
date     : Mon, 12 Aug 2019 15:21:28 -0700    
  
committer: Peter Geoghegan <pg@bowt.ie>    
date     : Mon, 12 Aug 2019 15:21:28 -0700    

Click here for diff

contrib/amcheck failed to consider the possibility that unlogged  
relations will not have any main relation fork files when running in hot  
standby mode.  This led to low-level "can't happen" errors that complain  
about the absence of a relfilenode file.  
  
To fix, simply skip verification of unlogged index relations during  
recovery.  In passing, add a direct check for the presence of a main  
fork just before verification proper begins, so that we cleanly verify  
the presence of the main relation fork file.  
  
Author: Andrey Borodin, Peter Geoghegan  
Reported-By: Andrey Borodin  
Diagnosed-By: Andrey Borodin  
Discussion: https://postgr.es/m/DA9B33AC-53CB-4643-96D4-7A0BBC037FA1@yandex-team.ru  
Backpatch: 10-, where amcheck was introduced.  

M contrib/amcheck/verify_nbtree.c

Fix planner's test for case-foldable characters in ILIKE with ICU.

commit   : c914e74d2dee0ecf372c1d40f87499d94d591935    
  
author   : Tom Lane <tgl@sss.pgh.pa.us>    
date     : Mon, 12 Aug 2019 13:15:48 -0400    
  
committer: Tom Lane <tgl@sss.pgh.pa.us>    
date     : Mon, 12 Aug 2019 13:15:48 -0400    

Click here for diff

As coded, the ICU-collation path in pattern_char_isalpha() failed  
to consider regular ASCII letters to be case-varying.  This led to  
like_fixed_prefix treating too much of an ILIKE pattern as being a  
fixed prefix, so that indexscans derived from an ILIKE clause might  
miss entries that they should find.  
  
Per bug #15892 from James Inform.  This is an oversight in the original  
ICU patch (commit eccfef81e), so back-patch to v10 where that came in.  
  
Discussion: https://postgr.es/m/15892-e5d2bea3e8a04a1b@postgresql.org  

M src/backend/utils/adt/selfuncs.c
M src/test/regress/expected/collate.icu.utf8.out
M src/test/regress/sql/collate.icu.utf8.sql

Fix "ANALYZE t, t" inside a transaction block.

commit   : ceb850d4a30c06f2f127487acd898847b80dcfc5    
  
author   : Tom Lane <tgl@sss.pgh.pa.us>    
date     : Sat, 10 Aug 2019 11:30:12 -0400    
  
committer: Tom Lane <tgl@sss.pgh.pa.us>    
date     : Sat, 10 Aug 2019 11:30:12 -0400    

Click here for diff

This failed with either "tuple already updated by self" or "duplicate  
key value violates unique constraint", depending on whether the table  
had previously been analyzed or not.  The reason is that ANALYZE tried  
to insert or update the same pg_statistic rows twice, and there was no  
CommandCounterIncrement between.  So add one.  The same case works fine  
outside a transaction block, because then there's a whole transaction  
boundary between, as a consequence of the way VACUUM works.  
  
This issue has been latent all along, but the problem was unreachable  
before commit 11d8d72c2 added the ability to specify multiple tables  
in ANALYZE.  We could, perhaps, alternatively fix it by adding code to  
de-duplicate the list of VacuumRelations --- but that would add a  
lot of overhead to work around dumb commands, so it's not attractive.  
  
Per bug #15946 from Yaroslav Schekin.  Back-patch to v11.  
  
(Note: in v11 I also back-patched the test added by commit 23224563d;  
otherwise the problem doesn't manifest in the test I added, because  
"vactst" is empty when the tests for multiple ANALYZE targets are  
reached.  That seems like not a very good thing anyway, so I did this  
rather than rethinking the choice of test case.)  
  
Discussion: https://postgr.es/m/15946-5c7570a2884a26cf@postgresql.org  

M src/backend/commands/vacuum.c
M src/test/regress/expected/vacuum.out
M src/test/regress/sql/vacuum.sql

Fix SIGSEGV in pruning for ScalarArrayOp with constant-null array.

commit   : 2f729d83226705d1149419a2aef7c1678fe641ec    
  
author   : Tom Lane <tgl@sss.pgh.pa.us>    
date     : Fri, 9 Aug 2019 13:20:28 -0400    
  
committer: Tom Lane <tgl@sss.pgh.pa.us>    
date     : Fri, 9 Aug 2019 13:20:28 -0400    

Click here for diff

Not much to be said here: commit 9fdb675fc should have checked  
constisnull, didn't.  
  
Per report from Piotr Włodarczyk.  Back-patch to v11 where  
bug was introduced.  
  
Discussion: https://postgr.es/m/CAP-dhMr+vRpwizEYjUjsiZ1vwqpohTm+3Pbdt6Pr7FEgPq9R0Q@mail.gmail.com  

M src/backend/partitioning/partprune.c
M src/test/regress/expected/partition_prune.out
M src/test/regress/sql/partition_prune.sql

Clarify the default partition's role

commit   : bf6455d4c557ff9ef42985bb4806b251497bdc60    
  
author   : Alvaro Herrera <alvherre@alvh.no-ip.org>    
date     : Thu, 8 Aug 2019 16:03:14 -0400    
  
committer: Alvaro Herrera <alvherre@alvh.no-ip.org>    
date     : Thu, 8 Aug 2019 16:03:14 -0400    

Click here for diff

Reviewed by Tom Lane and Amit Langote  
Discussion: https://postgr.es/m/20190806222735.GA9535@alvherre.pgsql  

M doc/src/sgml/ref/create_table.sgml

Fix certificate subjects in ldap test

commit   : 273e78715a0f92708f6b7b0c99393666e3281f72    
  
author   : Andrew Dunstan <andrew@dunslane.net>    
date     : Thu, 8 Aug 2019 14:57:48 -0400    
  
committer: Andrew Dunstan <andrew@dunslane.net>    
date     : Thu, 8 Aug 2019 14:57:48 -0400    

Click here for diff

openssl doesn't like lower case subject attribute names. Error observed  
in buildfarm results.  
  
Backpatch to release 11.  

M src/test/ldap/t/001_auth.pl

Doc: document permissions required for ANALYZE.

commit   : ed6df2e0e39ceffef5d3099466bb077371f14443    
  
author   : Tom Lane <tgl@sss.pgh.pa.us>    
date     : Wed, 7 Aug 2019 18:09:28 -0400    
  
committer: Tom Lane <tgl@sss.pgh.pa.us>    
date     : Wed, 7 Aug 2019 18:09:28 -0400    

Click here for diff

VACUUM's reference page had this text, but ANALYZE's didn't.  That's  
a clear oversight given that section 5.7 explicitly delegates the  
responsibility to define permissions requirements to the individual  
commands' man pages.  
  
Per gripe from Isaac Morland.  Back-patch to all supported branches.  
  
Discussion: https://postgr.es/m/CAMsGm5fp3oBUs-2iRfii0iEO=fZuJALVyM2zJLhNTjG34gpAVQ@mail.gmail.com  

M doc/src/sgml/ref/analyze.sgml

Fix typo in comment.

commit   : 26555cbd6e1443ea783717fd49b2ca1d55e4a015    
  
author   : Etsuro Fujita <efujita@postgresql.org>    
date     : Wed, 7 Aug 2019 19:05:19 +0900    
  
committer: Etsuro Fujita <efujita@postgresql.org>    
date     : Wed, 7 Aug 2019 19:05:19 +0900    

Click here for diff

M src/backend/utils/cache/partcache.c

Fix predicate-locking of HOT updated rows.

commit   : c5b796125299b7a1b37b8b5d6e5f0b316521c33a    
  
author   : Heikki Linnakangas <heikki.linnakangas@iki.fi>    
date     : Wed, 7 Aug 2019 12:40:49 +0300    
  
committer: Heikki Linnakangas <heikki.linnakangas@iki.fi>    
date     : Wed, 7 Aug 2019 12:40:49 +0300    

Click here for diff

In serializable mode, heap_hot_search_buffer() incorrectly acquired a  
predicate lock on the root tuple, not the returned tuple that satisfied  
the visibility checks. As explained in README-SSI, the predicate lock does  
not need to be copied or extended to other tuple versions, but for that to  
work, the correct, visible, tuple version must be locked in the first  
place.  
  
The original SSI commit had this bug in it, but it was fixed back in 2013,  
in commit 81fbbfe335. But unfortunately, it was reintroduced a few months  
later in commit b89e151054. Wising up from that, add a regression test  
to cover this, so that it doesn't get reintroduced again. Also, move the  
code that sets 't_self', so that it happens at the same time that the  
other HeapTuple fields are set, to make it more clear that all the code in  
the loop operate on the "current" tuple in the chain, not the root tuple.  
  
Bug spotted by Andres Freund, analysis and original fix by Thomas Munro,  
test case and some additional changes to the fix by Heikki Linnakangas.  
Backpatch to all supported versions (9.4).  
  
Discussion: https://www.postgresql.org/message-id/20190731210630.nqhszuktygwftjty%40alap3.anarazel.de  

M src/backend/access/heap/heapam.c
A src/test/isolation/expected/predicate-lock-hot-tuple.out
M src/test/isolation/isolation_schedule
A src/test/isolation/specs/predicate-lock-hot-tuple.spec

Fix some incorrect parsing of time with time zone strings

commit   : d16d241a55557352e09c9ffd6ab20548b066d193    
  
author   : Michael Paquier <michael@paquier.xyz>    
date     : Wed, 7 Aug 2019 18:17:39 +0900    
  
committer: Michael Paquier <michael@paquier.xyz>    
date     : Wed, 7 Aug 2019 18:17:39 +0900    

Click here for diff

When parsing a timetz string with a dynamic timezone abbreviation or a  
timezone not specified, it was possible to generate incorrect timestamps  
based on a date which uses some non-initialized variables if the input  
string did not specify fully a date to parse.  This is already checked  
when a full timezone spec is included in the input string, but the two  
other cases mentioned above missed the same checks.  
  
This gets fixed by generating an error as this input is invalid, or in  
short when a date is not fully specified.  
  
Valgrind was complaining about this problem.  
  
Bug: #15910  
Author: Alexander Lakhin  
Discussion: https://postgr.es/m/15910-2eba5106b9aa0c61@postgresql.org  
Backpatch-through: 9.4  

M src/backend/utils/adt/datetime.c
M src/test/regress/expected/timetz.out
M src/test/regress/sql/timetz.sql

Fix intarray's GiST opclasses to not fail for empty arrays with <@.

commit   : 113b3d903b348a3c8ac92cdb0389d5ca779c197f    
  
author   : Tom Lane <tgl@sss.pgh.pa.us>    
date     : Tue, 6 Aug 2019 18:04:51 -0400    
  
committer: Tom Lane <tgl@sss.pgh.pa.us>    
date     : Tue, 6 Aug 2019 18:04:51 -0400    

Click here for diff

contrib/intarray considers "arraycol <@ constant-array" to be indexable,  
but its GiST opclass code fails to reliably find index entries for empty  
array values (which of course should trivially match such queries).  
This is because the test condition to see whether we should descend  
through a non-leaf node is wrong.  
  
Unfortunately, empty array entries could be anywhere in the index,  
as these index opclasses are currently designed.  So there's no way  
to fix this except by lobotomizing <@ indexscans to scan the whole  
index ... which is what this patch does.  That's pretty unfortunate:  
the performance is now actually worse than a seqscan, in most cases.  
We'd be better off to remove <@ from the GiST opclasses entirely,  
and perhaps a future non-back-patchable patch will do so.  
  
In the meantime, applications whose performance is adversely impacted  
have a couple of options.  They could switch to a GIN index, which  
doesn't have this bug, or they could replace "arraycol <@ constant-array"  
with "arraycol <@ constant-array AND arraycol && constant-array".  
That will provide about the same performance as before, and it will find  
all non-empty subsets of the given constant-array, which is all that  
could reliably be expected of the query before.  
  
While at it, add some more regression test cases to improve code  
coverage of contrib/intarray.  
  
In passing, adjust resize_intArrayType so that when it's returning an  
empty array, it uses construct_empty_array for that rather than  
cowboy hacking on the input array.  While the hack produces an array  
that looks valid for most purposes, it isn't bitwise equal to empty  
arrays produced by other code paths, which could have subtle odd  
effects.  I don't think this code path is performance-critical  
enough to justify such shortcuts.  (Back-patch this part only as far  
as v11; before commit 01783ac36 we were not careful about this in  
other intarray code paths either.)  
  
Back-patch the <@ fixes to all supported versions, since this was  
broken from day one.  
  
Patch by me; thanks to Alexander Korotkov for review.  
  
Discussion: https://postgr.es/m/458.1565114141@sss.pgh.pa.us  

M contrib/intarray/_int_gist.c
M contrib/intarray/_int_tool.c
M contrib/intarray/_intbig_gist.c
M contrib/intarray/expected/_int.out
M contrib/intarray/sql/_int.sql

Save Kerberos and LDAP daemon logs where the buildfarm can find them.

commit   : f1c23b00b9b7cee1e86a6740e5be94de163b44d4    
  
author   : Tom Lane <tgl@sss.pgh.pa.us>    
date     : Tue, 6 Aug 2019 17:08:07 -0400    
  
committer: Tom Lane <tgl@sss.pgh.pa.us>    
date     : Tue, 6 Aug 2019 17:08:07 -0400    

Click here for diff

src/test/kerberos and src/test/ldap try to run private authentication  
servers, which of course might fail.  The logs from these servers  
were being dropped into the tmp_check/ subdirectory, but they should  
be put in tmp_check/log/, because the buildfarm will only capture  
log files in that subdirectory.  Without the log output there's  
little hope of diagnosing buildfarm failures related to these servers.  
  
Backpatch to v11 where these test suites were added.  
  
Discussion: https://postgr.es/m/16017.1565047605@sss.pgh.pa.us  

M src/test/kerberos/t/001_auth.pl
M src/test/ldap/t/001_auth.pl