PostgreSQL 9.6.17 (upcoming) commit log

Ensure maxlen is at leat 1 in dict_int

commit   : fabdad822287d6aac6a80fc57a97d38bd7456958    
author   : Tomas Vondra <>    
date     : Tue, 3 Dec 2019 16:55:51 +0100    
committer: Tomas Vondra <>    
date     : Tue, 3 Dec 2019 16:55:51 +0100    

Click here for diff

The dict_int text search dictionary template accepts maxlen parameter,  
which is then used to cap the length of input strings. The value was  
not properly checked, and the code simply does  
    txt[d->maxlen] = '\0';  
to insert a terminator, leading to segfaults with negative values.  
This commit simply rejects values less than 1. The issue was there since  
dct_int was introduced in 9.3, so backpatch all the way back to 9.4  
which is the oldest supported version.  
Reported-by: cili  
Backpatch-through: 9.4  

M contrib/dict_int/dict_int.c
M contrib/dict_int/expected/dict_int.out
M contrib/dict_int/sql/dict_int.sql

Fix misbehavior with expression indexes on ON COMMIT DELETE ROWS tables.

commit   : 283f095d0bff349682985bfa84c36e86c282a055    
author   : Tom Lane <>    
date     : Sun, 1 Dec 2019 13:09:27 -0500    
committer: Tom Lane <>    
date     : Sun, 1 Dec 2019 13:09:27 -0500    

Click here for diff

We implement ON COMMIT DELETE ROWS by truncating tables marked that  
way, which requires also truncating/rebuilding their indexes.  But  
RelationTruncateIndexes asks the relcache for up-to-date copies of any  
index expressions, which may cause execution of eval_const_expressions  
on them, which can result in actual execution of subexpressions.  
This is a bad thing to have happening during ON COMMIT.  Manuel Rigger  
reported that use of a SQL function resulted in crashes due to  
expectations that ActiveSnapshot would be set, which it isn't.  
The most obvious fix perhaps would be to push a snapshot during  
PreCommit_on_commit_actions, but I think that would just open the door  
to more problems: CommitTransaction explicitly expects that no  
user-defined code can be running at this point.  
Fortunately, since we know that no tuples exist to be indexed, there  
seems no need to use the real index expressions or predicates during  
RelationTruncateIndexes.  We can set up dummy index expressions  
instead (we do need something that will expose the right data type,  
as there are places that build index tupdescs based on this), and  
just ignore predicates and exclusion constraints.  
In a green field it'd likely be better to reimplement ON COMMIT DELETE  
ROWS using the same "init fork" infrastructure used for unlogged  
relations.  That seems impractical without catalog changes though,  
and even without that it'd be too big a change to back-patch.  
So for now do it like this.  
Per private report from Manuel Rigger.  This has been broken forever,  
so back-patch to all supported branches.  

M src/backend/catalog/heap.c
M src/backend/catalog/index.c
M src/backend/utils/cache/relcache.c
M src/include/catalog/index.h
M src/include/utils/relcache.h
M src/test/regress/expected/temp.out
M src/test/regress/sql/temp.sql

Fix off-by-one error in PGTYPEStimestamp_fmt_asc

commit   : c59414da7bd3be9f80e7585fc173d21942468d3b    
author   : Tomas Vondra <>    
date     : Sat, 30 Nov 2019 14:51:27 +0100    
committer: Tomas Vondra <>    
date     : Sat, 30 Nov 2019 14:51:27 +0100    

Click here for diff

When using %b or %B patterns to format a date, the code was simply using  
tm_mon as an index into array of month names. But that is wrong, because  
tm_mon is 1-based, while array indexes are 0-based. The result is we  
either use name of the next month, or a segfault (for December).  
Fix by subtracting 1 from tm_mon for both patterns, and add a regression  
test triggering the issue. Backpatch to all supported versions (the bug  
is there far longer, since at least 2003).  
Reported-by: Paul Spencer  
Backpatch-through: 9.4  

M src/interfaces/ecpg/pgtypeslib/timestamp.c
M src/interfaces/ecpg/test/expected/pgtypeslib-dt_test.c
M src/interfaces/ecpg/test/expected/pgtypeslib-dt_test.stderr
M src/interfaces/ecpg/test/expected/pgtypeslib-dt_test.stdout
M src/interfaces/ecpg/test/pgtypeslib/dt_test.pgc

Fix typo in comment.

commit   : 474cd0931b758f4ee353fbc8cfc38a762b997be1    
author   : Etsuro Fujita <>    
date     : Wed, 27 Nov 2019 16:00:51 +0900    
committer: Etsuro Fujita <>    
date     : Wed, 27 Nov 2019 16:00:51 +0900    

Click here for diff

M src/backend/optimizer/util/relnode.c

Don't shut down Gather[Merge] early under Limit.

commit   : 1ad0df67c7904ff64166d7a453c53943f069ee52    
author   : Amit Kapila <>    
date     : Tue, 26 Nov 2019 09:41:41 +0530    
committer: Amit Kapila <>    
date     : Tue, 26 Nov 2019 09:41:41 +0530    

Click here for diff

Revert part of commit 19df1702f5.  
Early shutdown was added by that commit so that we could collect  
statistics from workers, but unfortunately, it interacted badly with  
rescans.  The problem is that we ended up destroying the parallel context  
which is required for rescans.  This leads to rescans of a Limit node over  
a Gather node to produce unpredictable results as it tries to access  
destroyed parallel context.  By reverting the early shutdown code, we  
might lose statistics in some cases of Limit over Gather [Merge], but that  
will require further study to fix.  
Reported-by: Jerry Sievers  
Diagnosed-by: Thomas Munro  
Author: Amit Kapila  
Backpatch-through: 9.6  

M src/backend/executor/nodeLimit.c

Avoid assertion failure with LISTEN in a serializable transaction.

commit   : cdba85eb01ed378d8c2d713f3df62a96c3daabd4    
author   : Tom Lane <>    
date     : Sun, 24 Nov 2019 15:57:31 -0500    
committer: Tom Lane <>    
date     : Sun, 24 Nov 2019 15:57:31 -0500    

Click here for diff

If LISTEN is the only action in a serializable-mode transaction,  
and the session was not previously listening, and the notify queue  
is not empty, predicate.c reported an assertion failure.  That  
happened because we'd acquire the transaction's initial snapshot  
during PreCommit_Notify, which was called *after* predicate.c  
expects any such snapshot to have been established.  
To fix, just swap the order of the PreCommit_Notify and  
PreCommit_CheckForSerializationFailure calls during CommitTransaction.  
This will imply holding the notify-insertion lock slightly longer,  
but the difference could only be meaningful in serializable mode,  
which is an expensive option anyway.  
It appears that this is just an assertion failure, with no  
consequences in non-assert builds.  A snapshot used only to scan  
the notify queue could not have been involved in any serialization  
conflicts, so there would be nothing for  
PreCommit_CheckForSerializationFailure to do except assign it a  
prepareSeqNo and set the SXACT_FLAG_PREPARED flag.  And given no  
conflicts, neither of those omissions affect the behavior of  
ReleasePredicateLocks.  This admittedly once-over-lightly analysis  
is backed up by the lack of field reports of trouble.  
Per report from Mark Dilger.  The bug is old, so back-patch to all  
supported branches; but the new test case only goes back to 9.6,  
for lack of adequate isolationtester infrastructure before that.  

M src/backend/access/transam/xact.c
M src/test/isolation/expected/async-notify.out
M src/test/isolation/specs/async-notify.spec

Stabilize NOTIFY behavior by transmitting notifies before ReadyForQuery.

commit   : 111298aa65339a91c513c42ac2ea3eb6d343d0ea    
author   : Tom Lane <>    
date     : Sun, 24 Nov 2019 14:42:59 -0500    
committer: Tom Lane <>    
date     : Sun, 24 Nov 2019 14:42:59 -0500    

Click here for diff

This patch ensures that, if any notify messages were received during  
a just-finished transaction, they get sent to the frontend just before  
not just after the ReadyForQuery message.  With libpq and other client  
libraries that act similarly, this guarantees that the client will see  
the notify messages as available as soon as it thinks the transaction  
is done.  
This probably makes no difference in practice, since in realistic  
use-cases the application would have to cope with asynchronous  
arrival of notify events anyhow.  However, it makes it a lot easier  
to build cross-session-notify test cases with stable behavior.  
I'm a bit surprised now that we've not seen any buildfarm instability  
with the test cases added by commit b10f40bf0.  Tests that I intend  
to add in an upcoming bug fix are definitely unstable without this.  
Back-patch to 9.6, which is as far back as we can do NOTIFY testing  
with the isolationtester infrastructure.  

M src/backend/commands/async.c
M src/backend/tcop/postgres.c

Improve test coverage for LISTEN/NOTIFY.

commit   : 8173fa5abb16947c6e96d774e34e50f141cbe255    
author   : Tom Lane <>    
date     : Sat, 23 Nov 2019 17:30:01 -0500    
committer: Tom Lane <>    
date     : Sat, 23 Nov 2019 17:30:01 -0500    

Click here for diff

Back-patch commit b10f40bf0 into older branches.  This adds reporting  
of NOTIFY messages to isolationtester.c, and extends the async-notify  
test to include direct tests of basic NOTIFY functionality.  
This provides useful infrastructure for testing a bug fix I'm about  
to back-patch, and there seems no good reason not to have better tests  
of LISTEN/NOTIFY in the back branches.  The commit's survived long  
enough in HEAD to make it unlikely that it will cause problems.  
Back-patch as far as 9.6.  isolationtester.c changed too much in 9.6  
to make it sane to try to fix older branches this way, and I don't  
really want to back-patch those changes too.  

M src/test/isolation/expected/async-notify.out
M src/test/isolation/isolationtester.c
M src/test/isolation/specs/async-notify.spec

Defend against self-referential views in relation_is_updatable().

commit   : 52434ba73e3cab79f21b5deb921f51ea84a32e53    
author   : Tom Lane <>    
date     : Thu, 21 Nov 2019 16:21:44 -0500    
committer: Tom Lane <>    
date     : Thu, 21 Nov 2019 16:21:44 -0500    

Click here for diff

While a self-referential view doesn't actually work, it's possible  
to create one, and it turns out that this breaks some of the  
information_schema views.  Those views call relation_is_updatable(),  
which neglected to consider the hazards of being recursive.  In  
older PG versions you get a "stack depth limit exceeded" error,  
but since v10 it'd recurse to the point of stack overrun and crash,  
because commit a4c35ea1c took out the expression_returns_set() call  
that was incidentally checking the stack depth.  
Since this function is only used by information_schema views, it  
seems like it'd be better to return "not updatable" than suffer  
an error.  Hence, add tracking of what views we're examining,  
in just the same way that the nearby fireRIRrules() code detects  
self-referential views.  I added a check_stack_depth() call too,  
just to be defensive.  
Per private report from Manuel Rigger.  Back-patch to all  
supported versions.  

M src/backend/rewrite/rewriteHandler.c
M src/backend/utils/adt/misc.c
M src/include/rewrite/rewriteHandler.h


commit   : 84dcf5235984f45458d13a9e0e486caf97f152ea    
author   : Alexander Korotkov <>    
date     : Tue, 19 Nov 2019 23:11:24 +0300    
committer: Alexander Korotkov <>    
date     : Tue, 19 Nov 2019 23:11:24 +0300    

Click here for diff

We find GIN concurrency bugs from time to time.  One of the problems here is  
that concurrency of GIN isn't well-documented in README.  So, it might be even  
hard to distinguish design bugs from implementation bugs.  
This commit revised concurrency section in GIN README providing more details.  
Some examples are illustrated in ASCII art.  
Also, this commit add the explanation of how is tuple layout in internal GIN  
B-tree page different in comparison with nbtree.  
Author: Alexander Korotkov  
Reviewed-by: Peter Geoghegan  
Backpatch-through: 9.4  

M src/backend/access/gin/README

Fix traversing to the deleted GIN page via downlink

commit   : 99f5888d358a5db375ce0299b18fb47ccfa1646c    
author   : Alexander Korotkov <>    
date     : Tue, 19 Nov 2019 23:08:14 +0300    
committer: Alexander Korotkov <>    
date     : Tue, 19 Nov 2019 23:08:14 +0300    

Click here for diff

Current GIN code appears to don't handle traversing to the deleted page via  
downlink.  This commit fixes that by stepping right from the delete page like  
we do in nbtree.  
This commit also fixes setting 'deleted' flag to the GIN pages.  Now other page  
flags are not erased once page is deleted.  That helps to keep our assertions  
true if we arrive deleted page via downlink.  
Author: Alexander Korotkov  
Reviewed-by: Peter Geoghegan  
Backpatch-through: 9.4  

M src/backend/access/gin/ginbtree.c
M src/backend/access/gin/gindatapage.c
M src/backend/access/gin/ginvacuum.c
M src/backend/access/gin/ginxlog.c

Doc: clarify use of RECURSIVE in WITH.

commit   : 5bb9954c1cda315add1ceeeec601eadf6ee48c0c    
author   : Tom Lane <>    
date     : Tue, 19 Nov 2019 14:43:37 -0500    
committer: Tom Lane <>    
date     : Tue, 19 Nov 2019 14:43:37 -0500    

Click here for diff

Apparently some people misinterpreted the syntax as being that  
RECURSIVE is a prefix of individual WITH queries.  It's a modifier  
for the WITH clause as a whole, so state that more clearly.  

M doc/src/sgml/ref/select.sgml

Doc: clarify behavior of ALTER DEFAULT PRIVILEGES ... IN SCHEMA.

commit   : 611a4aba15f0e9ef8ef710454bd0ecb4f671eb39    
author   : Tom Lane <>    
date     : Tue, 19 Nov 2019 14:21:42 -0500    
committer: Tom Lane <>    
date     : Tue, 19 Nov 2019 14:21:42 -0500    

Click here for diff

The existing text stated that "Default privileges that are specified  
per-schema are added to whatever the global default privileges are for  
the particular object type".  However, that bare-bones observation is  
not quite clear enough, as demonstrated by the complaint in bug #16124.  
Flesh it out by stating explicitly that you can't revoke built-in  
default privileges this way, and by providing an example to drive  
the point home.  
Back-patch to all supported branches, since it's been like this  
from the beginning.  

M doc/src/sgml/ref/alter_default_privileges.sgml

Further fix dumping of views that contain just VALUES(...).

commit   : e4865bbdc72a702d09c349116ad64d0b3d8c9add    
author   : Tom Lane <>    
date     : Sat, 16 Nov 2019 20:00:19 -0500    
committer: Tom Lane <>    
date     : Sat, 16 Nov 2019 20:00:19 -0500    

Click here for diff

It turns out that commit e9f1c01b7 missed a case: we must print a  
VALUES clause in long format if get_query_def is given a resultDesc  
that would require the query's output column name(s) to be different  
from what the bare VALUES clause would produce.  
This applies in case an ALTER ... RENAME COLUMN has been done to  
a view that formerly could be printed in simple format, as shown  
in the added regression test case.  It also explains bug #16119  
from Dmitry Telpt, because it turns out that (unlike CREATE VIEW)  
CREATE MATERIALIZED VIEW fails to apply any column aliases it's  
given to the stored ON SELECT rule.  So to get them to be printed,  
we have to account for the resultDesc renaming.  It might be worth  
changing the matview code so that it creates the ON SELECT rule  
with the correct aliases; but we'd still need these messy checks in  
get_simple_values_rte to handle the case of a subsequent column  
rename, so any such change would be just neatnik-ism not a bug fix.  
Like the previous patch, back-patch to all supported branches.  

M src/backend/utils/adt/ruleutils.c
M src/test/regress/expected/rules.out
M src/test/regress/sql/rules.sql

Handle arrays and ranges in pg_upgrade's test for non-upgradable types.

commit   : f378d4dac4ce80d6772ae4956cd71b10985c481c    
author   : Tom Lane <>    
date     : Wed, 13 Nov 2019 11:35:37 -0500    
committer: Tom Lane <>    
date     : Wed, 13 Nov 2019 11:35:37 -0500    

Click here for diff

pg_upgrade needs to check whether certain non-upgradable data types  
appear anywhere on-disk in the source cluster.  It knew that it has  
to check for these types being contained inside domains and composite  
types; but it somehow overlooked that they could be contained in  
arrays and ranges, too.  Extend the existing recursive-containment  
query to handle those cases.  
We probably should have noticed this oversight while working on  
commit 0ccfc2822 and follow-ups, but we failed to :-(.  The whole  
thing's possibly a bit overdesigned, since we don't really expect  
that any of these types will appear on disk; but if we're going to  
the effort of doing a recursive search then it's silly not to cover  
all the possibilities.  
While at it, refactor so that we have only one copy of the search  
logic, not three-and-counting.  Also, to keep the branches looking  
more alike, back-patch the output wording change of commit 1634d3615.  
Back-patch to all supported branches.  

M src/bin/pg_upgrade/version.c