PostgreSQL 11.21 (upcoming) commit log

Avoid naming conflict between transactions.sql and namespace.sql.

commit   : 6f1cf2efbd0199c58db9f256ade48f42f56cab49    
  
author   : Tom Lane <tgl@sss.pgh.pa.us>    
date     : Fri, 19 May 2023 10:57:46 -0400    
  
committer: Tom Lane <tgl@sss.pgh.pa.us>    
date     : Fri, 19 May 2023 10:57:46 -0400    

Click here for diff

Commits 681d9e462 et al added a test case in namespace.sql that  
implicitly relied on there not being a table "public.abc".  
However, the concurrently-run transactions.sql test creates precisely  
such a table, so with the right timing you'd get a failure.  
Creating a table named as generically as "abc" in a common schema  
seems like bad practice, so fix this by changing the name of  
transactions.sql's table.  (Compare 2cf8c7aa4.)  
  
Marina Polyakova  
  
Discussion: https://postgr.es/m/80d0201636665d82185942e7112257b4@postgrespro.ru  

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

Fix handling of empty ranges and NULLs in BRIN

commit   : fc7dc728d1a68dd67afc9795fd65b8a8d412244d    
  
author   : Tomas Vondra <tomas.vondra@postgresql.org>    
date     : Fri, 19 May 2023 00:00:22 +0200    
  
committer: Tomas Vondra <tomas.vondra@postgresql.org>    
date     : Fri, 19 May 2023 00:00:22 +0200    

Click here for diff

BRIN indexes did not properly distinguish between summaries for empty  
(no rows) and all-NULL ranges, treating them as essentially the same  
thing. Summaries were initialized with allnulls=true, and opclasses  
simply reset allnulls to false when processing the first non-NULL value.  
This however produces incorrect results if the range starts with a NULL  
value (or a sequence of NULL values), in which case we forget the range  
contains NULL values when adding the first non-NULL value.  
  
This happens because the allnulls flag is used for two separate  
purposes - to mark empty ranges (not representing any rows yet) and  
ranges containing only NULL values.  
  
Opclasses don't know which of these cases it is, and so don't know  
whether to set hasnulls=true. Setting the flag in both cases would make  
it correct, but it would also make BRIN indexes useless for queries with  
IS NULL clauses. All ranges start empty (and thus allnulls=true), so all  
ranges would end up with either allnulls=true or hasnulls=true.  
  
The severity of the issue is somewhat reduced by the fact that it only  
happens when adding values to an existing summary with allnulls=true.  
This can happen e.g. for small tables (because a summary for the first  
range exists for all BRIN indexes), or for tables with large fraction of  
NULL values in the indexed columns.  
  
Bulk summarization (e.g. during CREATE INDEX or automatic summarization)  
that processes all values at once is not affected by this issue. In this  
case the flags were updated in a slightly different way, not forgetting  
the NULL values.  
  
To identify empty ranges we use a new flag, stored in an unused bit in  
the BRIN tuple header so the on-disk format remains the same. A matching  
flag is added to BrinMemTuple, into a 3B gap after bt_placeholder.  
That means there's no risk of ABI breakage, although we don't actually  
pass the BrinMemTuple to any public API.  
  
We could also skip storing index tuples for empty summaries, but then  
we'd have to always process such ranges - even if there are no rows in  
large parts of the table (e.g. after a bulk DELETE), it would still  
require reading the pages etc. So we store them, but ignore them when  
building the bitmap.  
  
Backpatch to 11. The issue exists since BRIN indexes were introduced in  
9.5, but older releases are already EOL.  
  
Backpatch-through: 11  
Reviewed-by: Justin Pryzby, Matthias van de Meent, Alvaro Herrera  
Discussion: https://postgr.es/m/402430e4-7d9d-6cf1-09ef-464d80afff3b@enterprisedb.com  

M src/backend/access/brin/brin.c
M src/backend/access/brin/brin_tuple.c
M src/include/access/brin_tuple.h
M src/test/modules/brin/expected/summarization-and-inprogress-insertion.out
M src/test/modules/brin/specs/summarization-and-inprogress-insertion.spec

Fix handling of NULLs when merging BRIN summaries

commit   : b511d7323df605fbb97ac8f158801cf53b028e41    
  
author   : Tomas Vondra <tomas.vondra@postgresql.org>    
date     : Thu, 18 May 2023 13:00:31 +0200    
  
committer: Tomas Vondra <tomas.vondra@postgresql.org>    
date     : Thu, 18 May 2023 13:00:31 +0200    

Click here for diff

When merging BRIN summaries, union_tuples() did not correctly update the  
target hasnulls/allnulls flags. When merging all-NULL summary into a  
summary without any NULL values, the result had both flags set to false  
(instead of having hasnulls=true).  
  
This happened because the code only considered the hasnulls flags,  
ignoring the possibility the source summary has allnulls=true.  
  
Discovered while investigating issues with handling empty BRIN ranges  
and handling of NULL values, but it's a separate problem (has nothing to  
do with empty ranges).  
  
Fixed by considering both flags on the source summary, and updating the  
hasnulls flag on the target summary.  
  
Backpatch to 11. The bug exists since 9.5 (where BRIN indexes were  
introduced), but those releases are EOL already.  
  
Discussion: https://postgr.es/m/9d993d0d-e431-2196-9ccc-0554d0e60154%40enterprisedb.com  

M src/backend/access/brin/brin_inclusion.c
M src/backend/access/brin/brin_minmax.c

Ensure Soundex difference() function handles empty input sanely.

commit   : 8084bf9a493d577bb2d0d902388ed3fd48859c05    
  
author   : Tom Lane <tgl@sss.pgh.pa.us>    
date     : Tue, 16 May 2023 10:53:42 -0400    
  
committer: Tom Lane <tgl@sss.pgh.pa.us>    
date     : Tue, 16 May 2023 10:53:42 -0400    

Click here for diff

fuzzystrmatch's difference() function assumes that _soundex()  
always initializes its output buffer fully.  This was not so for  
the case of a string containing no alphabetic characters, resulting  
in unstable output and Valgrind complaints.  
  
Fix by using memset() to fill the whole buffer in the early-exit  
case.  Also make some cosmetic improvements (I didn't care for the  
random switches between "instr[0]" and "*instr" notation).  
  
Report and diagnosis by Alexander Lakhin (bug #17935).  
Back-patch to all supported branches.  
  
Discussion: https://postgr.es/m/17935-b99316aa79c18513@postgresql.org  

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