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

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

Person of the week: https://postgresql.life/post/umair_shahid/

== PostgreSQL Product News ==

pgwatch2 1.8.0, a monitoring tool for PostgreSQL, released.
https://www.cybertec-postgresql.com/en/pgwatch2-v1-8-0-released/

pgBadger v11.3, a PostgreSQL log analyzer and graph tool written in
Perl, released.
https://github.com/darold/pgbadger/releases

pgsodium 1.2.0, an extention which adds libsodium encryption to PostgreSQL, released.
https://github.com/michelp/pgsodium/releases/tag/1.2.0

pg_chameleon 2.0.14, a tool for replicating from MySQL to PostgreSQL, released.
https://github.com/the4thdoctor/pg_chameleon/releases/tag/v2.0.14

Crunchy PostgreSQL Operator 4.4.0, a system for deploying and managing open
source PostgreSQL clusters on Kubernetes, released.
https://access.crunchydata.com/documentation/postgres-operator/latest/releases/4.4.0/

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

== PostgreSQL Jobs for July ==

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

== PostgreSQL Local ==

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

Peter Geoghegan pushed:

- Avoid harmless Valgrind no-buffer-pin errors. Valgrind builds with assertions
enabled sometimes perform a theoretically unsafe page access inside an
assertion in heapam_tuple_lock(). This happened when the eval-plan-qual
isolation test ran one of the permutations added by commit a2418f9e238. Avoid
complaints from Valgrind by moving the assertion ever so slightly. This is
minor cleanup for commit 1e0dfd16, which added Valgrind buffer access
instrumentation. No backpatch, since this only happens within an assertion,
and seems very unlikely to cause any real problems even with assert-enabled
builds.
https://git.postgresql.org/pg/commitdiff/a766d6ca22ac7c233e69c896ae0c5f19de916db4

- Assert that buffer is pinned in LockBuffer(). Strengthen the LockBuffer()
assertion that verifies BufferIsValid() by making it verify BufferIsPinned()
instead. Do the same in nearby related functions. There is probably not much
chance that anybody will try to lock a buffer that is not already pinned, but
we might as well make sure of that.
https://git.postgresql.org/pg/commitdiff/6ca7cd89a2d1998b16e8168dda62d43a9e0fdaff

- Add nbtree Valgrind buffer lock checks. Holding just a buffer pin (with no
buffer lock) on an nbtree buffer/page provides very weak guarantees,
especially compared to heapam, where it's often safe to read a page while only
holding a buffer pin. This commit has Valgrind enforce the following rule: it
is never okay to access an nbtree buffer without holding both a pin and a lock
on the buffer. A draft version of this patch detected questionable code that
was cleaned up by commits fa7ff642 and 7154aa16. The code in question used to
access an nbtree buffer page's special/opaque area with no buffer lock (only a
buffer pin). This practice (which isn't obviously unsafe) is hereby formally
disallowed in nbtree. There doesn't seem to be any reason to allow it, and
banning it keeps things simple for Valgrind. The new checks are implemented
by adding custom nbtree client requests (located in LockBuffer() wrapper
functions); these requests are "superimposed" on top of the generic bufmgr.c
Valgrind client requests added by commit 1e0dfd16. No custom resource
management cleanup code is needed to undo the effects of marking buffers as
non-accessible under this scheme. Author: Peter Geoghegan Reviewed-By:
Anastasia Lubennikova, Georgios Kokolatos Discussion:
https://postgr.es/m/CAH2-WzkLgyN3zBvRZ1pkNJThC=xi_0gpWRUb_45eexLH1+k2_Q@mail.gmail.com
https://git.postgresql.org/pg/commitdiff/4a70f829d86cb8dbd68f561720e6329f5e818c94

Amit Kapila pushed:

- Fix minor typo in nodeIncrementalSort.c. Author: Vignesh C Reviewed-by: James
Coleman Backpatch-through: 13, where it was introduced Discussion:
https://postgr.es/m/CALDaNm0WjZqRvdeL59ZfYH0o4mLbKQ23jm-bnjXcFzgpANx55g@mail.gmail.com
https://git.postgresql.org/pg/commitdiff/044dc7b964147ec6303d59320fb743693b22af30

- Immediately WAL-log subtransaction and top-level XID association. The logical
decoding infrastructure needs to know which top-level transaction the subxact
belongs to, in order to decode all the changes. Until now that might be
delayed until commit, due to the caching (GPROC_MAX_CACHED_SUBXIDS),
preventing features requiring incremental decoding. So we also write the
assignment info into WAL immediately, as part of the next WAL record (to
minimize overhead) only when wal_level=logical. We can not remove the existing
XLOG_XACT_ASSIGNMENT WAL as that is required for avoiding overflow in the hot
standby snapshot. Bump XLOG_PAGE_MAGIC, since this introduces
XLR_BLOCK_ID_TOPLEVEL_XID. Author: Tomas Vondra, Dilip Kumar, Amit Kapila
Reviewed-by: Amit Kapila Tested-by: Neha Sharma and Mahendra Singh Thalor
Discussion:
https://postgr.es/m/688b0b7f-2f6c-d827-c27b-216a8e3ea700@2ndquadrant.com
https://git.postgresql.org/pg/commitdiff/0bead9af484c1d0a67e690fda47011addaa5bc9d

- WAL Log invalidations at command end with wal_level=logical. When
wal_level=logical, write invalidations at command end into WAL so that
decoding can use this information. This patch is required to allow the
streaming of in-progress transactions in logical decoding.  The actual work to
allow streaming will be committed as a separate patch. We still add the
invalidations to the cache and write them to WAL at commit time in
RecordTransactionCommit(). This uses the existing XLOG_INVALIDATIONS xlog
record type, from the RM_STANDBY_ID resource manager (see
LogStandbyInvalidations for details). So existing code relying on those
invalidations (e.g. redo) does not need to be changed. The invalidations
written at command end uses a new xlog record type XLOG_XACT_INVALIDATIONS,
from RM_XACT_ID resource manager. See LogLogicalInvalidations for details.
These new xlog records are ignored by existing redo procedures, which still
rely on the invalidations written to commit records. The invalidations are
decoded and accumulated in top-transaction, and then executed during replay.
 This obviates the need to decode the invalidations as part of a commit
record. Bump XLOG_PAGE_MAGIC, since this introduces XLOG_XACT_INVALIDATIONS.
Author: Dilip Kumar, Tomas Vondra, Amit Kapila Reviewed-by: Amit Kapila
Tested-by: Neha Sharma and Mahendra Singh Thalor Discussion:
https://postgr.es/m/688b0b7f-2f6c-d827-c27b-216a8e3ea700@2ndquadrant.com
https://git.postgresql.org/pg/commitdiff/c55040ccd017962b7b8d3fbcdc184aa90c722a21

- Fix buffer usage stats for nodes above Gather Merge. Commit 85c9d347 addressed
a similar problem for Gather and Gather Merge nodes but forgot to account for
nodes above parallel nodes. This still works for nodes above Gather node
because we shut down the workers for Gather node as soon as there are no more
tuples. We can do a similar thing for Gather Merge as well but it seems
better to account for stats during nodes shutdown after completing the
execution. Reported-by: Stéphane Lorek, Jehan-Guillaume de Rorthais Author:
Jehan-Guillaume de Rorthais <jgdr(at)dalibo(dot)com> Reviewed-by: Amit Kapila
Backpatch-through: 10, where it was introduced Discussion:
https://postgr.es/m/20200718160206.584532a2@firost
https://git.postgresql.org/pg/commitdiff/2a2494229a709b880a6db82d8b267017fccf671f

Fujii Masao pushed:

- Add generic_plans and custom_plans fields into pg_prepared_statements. There
was no easy way to find how many times generic and custom plans have been
executed for a prepared statement. This commit exposes those numbers of times
in pg_prepared_statements view. Author: Atsushi Torikoshi, Kyotaro Horiguchi
Reviewed-by: Tatsuro Yamada, Masahiro Ikeda, Fujii Masao Discussion:
https://postgr.es/m/CACZ0uYHZ4M=NZpofH6JuPHeX=__5xcDELF8hT8_2T+R55w4RQw@mail.gmail.com
https://git.postgresql.org/pg/commitdiff/d05b172a760e0ccb3008a2144f96053720000b12

- Rename wal_keep_segments to wal_keep_size. max_slot_wal_keep_size that was
added in v13 and wal_keep_segments are the GUC parameters to specify how much
WAL files to retain for the standby servers. While max_slot_wal_keep_size
accepts the number of bytes of WAL files, wal_keep_segments accepts the number
of WAL files. This difference of setting units between those similar
parameters could be confusing to users. To alleviate this situation, this
commit renames wal_keep_segments to wal_keep_size, and make users specify the
WAL size in it instead of the number of WAL files. There was also the idea to
rename max_slot_wal_keep_size to max_slot_wal_keep_segments, in the
discussion. But we have been moving away from measuring in segments, for
example, checkpoint_segments was replaced by max_wal_size. So we concluded to
rename wal_keep_segments to wal_keep_size. Back-patch to v13 where
max_slot_wal_keep_size was added. Author: Fujii Masao Reviewed-by: Álvaro
Herrera, Kyotaro Horiguchi, David Steele Discussion:
https://postgr.es/m/574b4ea3-e0f9-b175-ead2-ebea7faea855@oss.nttdata.com
https://git.postgresql.org/pg/commitdiff/c3fe108c025e4a080315562d4c15ecbe3f00405e

Alexander Korotkov pushed:

- Update btree_gist extension for parallel query. All functions provided by this
extension are PARALLEL SAFE. Discussion:
https://postgr.es/m/AM5PR0901MB1587E47B1ACF23C6089DFCA3FD9B0%40AM5PR0901MB1587.eurprd09.prod.outlook.com
Author: Steven Winfield
https://git.postgresql.org/pg/commitdiff/d98c08cdc638a290d80fb69b20b8288c3924d875

Tom Lane pushed:

- Fix construction of updated-columns bitmap in logical replication. Commit
b9c130a1f failed to apply the publisher-to-subscriber column mapping while
checking which columns were updated. Perhaps less significantly, it didn't
exclude dropped columns either. This could result in an incorrect
updated-columns bitmap and thus wrong decisions about whether to fire
column-specific triggers on the subscriber while applying updates. In HEAD
(since commit 9de77b545), it could also result in accesses off the end of the
colstatus array, as detected by buildfarm member skink. Fix the logic, and
adjust 003_constraints.pl so that the problem is exposed in unpatched code.
In HEAD, also add some assertions to check that we don't access off the ends
of these newly variable-sized arrays. Back-patch to v10, as b9c130a1f was.
Discussion:
https://postgr.es/m/CAH2-Wz=79hKQ4++c5A060RYbjTHgiYTHz=fw6mptCtgghH2gJA@mail.gmail.com
https://git.postgresql.org/pg/commitdiff/d5daae47db5e8a61ce6ed7afaa3e3a99af108c06

- Correctly mark pg_subscription_rel.srsublsn as nullable. The code has always
set this column to NULL when it's not valid, but the catalog header's
description failed to reflect that, as did the SGML docs, as did some of the
code. To prevent future coding errors of the same ilk, let's hide the field
from C code as though it were variable-length (which, in a sense, it is). As
with commit 72eab84a5, we can only fix this cleanly in HEAD and v13; the
problem extends further back but we'll need some klugery in the released
branches. Discussion: https://postgr.es/m/367660.1595202498@sss.pgh.pa.us
https://git.postgresql.org/pg/commitdiff/0fa0b487b5d75d2b8576dec86a317212542642b8

- Make floating-point "NaN / 0" return NaN instead of raising an error. This is
more consistent with the IEEE 754 spec and our treatment of NaNs elsewhere; in
particular, the case has always acted that way in "numeric" arithmetic. Noted
by Dean Rasheed. Discussion:
https://postgr.es/m/3421746.1594927785@sss.pgh.pa.us
https://git.postgresql.org/pg/commitdiff/4fb6aeb4f6e807c8ce3e140d2d2281f50eb6fb1a

- Fix some corner cases for window ranges with infinite offsets. Many situations
where the offset is infinity were not handled sanely. We should generally
allow the val versus base +/- offset comparison to proceed according to the
normal rules of IEEE arithmetic; however, we must do something special for the
corner cases where base +/- offset would produce NaN due to subtracting two
like-signed infinities. That corresponds to asking which values infinitely
precede +inf or infinitely follow -inf, which should certainly be true of any
finite value or of the opposite-signed infinity. After some discussion it
seems that the best decision is to make it true of the same-signed infinity as
well, ie, just return constant TRUE if the calculation would produce a NaN.
(We could write this with a bit less code by subtracting anyway, and then
checking for a NaN result. However, I prefer this formulation because it'll
be easier to transpose into numeric.c.) Although this seems like clearly a
bug fix with respect to finite values, it is less obviously correct for
infinite values. Between that and the fact that the whole issue only arises
for very strange window specifications (e.g. RANGE BETWEEN 'inf' PRECEDING AND
'inf' PRECEDING), I'll desist from back-patching. Noted by Dean Rasheed.
Discussion: https://postgr.es/m/3393130.1594925893@sss.pgh.pa.us
https://git.postgresql.org/pg/commitdiff/a4faef8f8fe1493397679c014cf11a6e27c0f1e5

- Assert that we don't insert nulls into attnotnull catalog columns. The
executor checks for this error, and so does the bootstrap catalog loader, but
we never checked for it in retail catalog manipulations. The folly of that has
now been exposed, so let's add assertions checking it. Checking in
CatalogTupleInsert[WithInfo] and CatalogTupleUpdate[WithInfo] should be enough
to cover this. Back-patch to v10; the aforesaid functions didn't exist before
that, and it didn't seem worth adapting the patch to the oldest branches. But
given the risk of JIT crashes, I think we certainly need this as far back as
v11. Pre-v13, we have to explicitly exclude pg_subscription.subslotname and
pg_subscription_rel.srsublsn from the checks, since they are mismarked. (Even
if we change our mind about applying BKI_FORCE_NULL in the branch tips, it
doesn't seem wise to have assertions that would fire in existing databases.)
Discussion: https://postgr.es/m/298837.1595196283@sss.pgh.pa.us
https://git.postgresql.org/pg/commitdiff/3e66019f15549d3e1e13da665b2549684061d630

- Be more careful about marking catalog columns NOT NULL by default. The bug
fixed in commit 72eab84a5 would not have occurred if initdb had a less
surprising rule about which columns should be marked NOT NULL by default.
Let's make that rule be strictly that the column must be fixed-width and its
predecessors must be fixed-width and NOT NULL, removing the hacky and unsafe
exceptions for oidvector and int2vector. Since we do still want all existing
oidvector and int2vector columns to be marked NOT NULL, we have to put
BKI_FORCE_NOT_NULL labels on them. But making this less magic and more
documented seems like a good idea, even if it's a shade more verbose. I
didn't bump catversion since the initial catalog contents are not actually
changed by this patch. Note however that the contents of postgres.bki do
change, and feeding an old copy of that to a new backend will produce wrong
results. Discussion: https://postgr.es/m/204760.1595181800@sss.pgh.pa.us
https://git.postgresql.org/pg/commitdiff/fc032bed2fb809ee69eaf2fece349f65274c876f

- Weaken type-OID-matching checks in array_recv and record_recv. Rather than
always insisting on an exact match of the type OID in the data to the element
type or column type we expect, complain only when both OIDs fall within the
manually-assigned range. This acknowledges the reality that user-defined
types don't have stable OIDs, while still preserving some of the
mistake-detection value of the old test. (It's not entirely clear whether to
error if one OID is manually assigned and the other isn't. But perhaps that
case could arise in cross-version cases where a former extension type has been
imported into core, so I let it pass.) This change allows us to remove the
prohibition on binary transfer of user-defined arrays and composites in the
recently-landed support for binary logical replication (commit 9de77b545). We
can just unconditionally drop that check, since if the client has asked for
binary transfer it must be >= v14 and must have this change. Discussion:
https://postgr.es/m/CADK3HH+R3xMn=8t3Ct+uD+qJ1KD=Hbif5NFMJ+d5DkoCzp6Vgw@mail.gmail.com
https://git.postgresql.org/pg/commitdiff/670c0a1d474bf296dbcc1d6de912d4841f2ed643

- neqjoinsel must now pass through collation to eqjoinsel. Since commit
044c99bc5, eqjoinsel passes the passed-in collation to any operators it
invokes. However, neqjoinsel failed to pass on whatever collation it got, so
that if we invoked a collation-dependent operator via that code path, we'd get
"could not determine which collation to use for string comparison" or the
like. Per report from Justin Pryzby. Back-patch to v12, like the previous
commit. Discussion: https://postgr.es/m/20200721191606.GL5748@telsasoft.com
https://git.postgresql.org/pg/commitdiff/bd0d893aa7aa303d7f344e267a9d3e53b0219491

- Support infinity and -infinity in the numeric data type. Add infinities that
behave the same as they do in the floating-point data types. Aside from any
intrinsic usefulness these may have, this closes an important gap in our
ability to convert floating values to numeric and/or replace float-based APIs
with numeric. The new values are represented by bit patterns that were
formerly not used (although old code probably would take them for NaNs). So
there shouldn't be any pg_upgrade hazard. Patch by me, reviewed by Dean
Rasheed and Andrew Gierth Discussion:
https://postgr.es/m/606717.1591924582@sss.pgh.pa.us
https://git.postgresql.org/pg/commitdiff/a57d312a7706321d850faa048a562a0c0c01b835

- Fix ancient violation of zlib's API spec. contrib/pgcrypto mishandled the case
where deflate() does not consume all of the offered input on the first try.
It reset the next_in pointer to the start of the input instead of leaving it
alone, causing the wrong data to be fed to the next deflate() call. This has
been broken since pgcrypto was committed. The reason for the lack of
complaints seems to be that it's fairly hard to get stock zlib to not consume
all the input, so long as the output buffer is big enough (which it normally
would be in pgcrypto's usage; AFAICT the input is always going to be
packetized into packets no larger than ZIP_OUT_BUF). However, IBM's zlibNX
implementation for AIX evidently will do it in some cases. I did not add a
test case for this, because I couldn't find one that would fail with stock
zlib. When we put back the test case for bug #16476, that will cover the
zlibNX situation well enough. While here, write deflate()'s second argument
as Z_NO_FLUSH per its API spec, instead of hard-wiring the value zero. Per
buildfarm results and subsequent investigation. Discussion:
https://postgr.es/m/16476-692ef7b84e5fb893@postgresql.org
https://git.postgresql.org/pg/commitdiff/b9b610577d7f70d959968c3697557011699b3a54

- Fix assorted bugs by changing TS_execute's callback API to ternary logic. Text
search sometimes failed to find valid matches, for instance '!crew:A'::tsquery
might fail to locate 'crew:1B'::tsvector during an index search. The root of
the issue is that TS_execute's callback functions were not changed to use
ternary (yes/no/maybe) reporting when we made the search logic itself do so.
It's somewhat annoying to break that API, but on the other hand we now see
that any code using plain boolean logic is almost certainly broken since the
addition of phrase search. There seem to be very few outside callers of this
code anyway, so we'll just break them intentionally to get them to adapt.
This allows removal of tsginidx.c's private re-implementation of TS_execute,
since that's now entirely duplicative. It's also no longer necessary to avoid
use of CALC_NOT in tsgistidx.c, since the underlying callbacks can now do
something reasonable. Back-patch into v13. We can't change this in stable
branches, but it seems not quite too late to fix it in v13. Tom Lane and
Pavel Borisov Discussion:
https://postgr.es/m/CALT9ZEE-aLotzBg-pOp2GFTesGWVYzXA3=mZKzRDa_OKnLF7Mg@mail.gmail.com
https://git.postgresql.org/pg/commitdiff/2f2007fbb255be178aca586780967f43885203a7

- Replace TS_execute's TS_EXEC_CALC_NOT flag with TS_EXEC_SKIP_NOT. It's fairly
silly that ignoring NOT subexpressions is TS_execute's default behavior. It's
wrong on its face and it encourages errors of omission. Moreover, the only
two remaining callers that aren't specifying CALC_NOT are in ts_headline
calculations, and it's very arguable that those are bugs: if you've specified
"!foo" in your query, why would you want to get a headline that includes
"foo"? Hence, rip that out and change the default behavior to be to calculate
NOT accurately. As a concession to the slim chance that there is still
somebody somewhere who needs the incorrect behavior, provide a new SKIP_NOT
flag to explicitly request that. Back-patch into v13, mainly because it seems
better to change this at the same time as the previous commit's rejiggering of
TS_execute related APIs. Any outside callers affected by this change are
probably also affected by that one. Discussion:
https://postgr.es/m/CALT9ZEE-aLotzBg-pOp2GFTesGWVYzXA3=mZKzRDa_OKnLF7Mg@mail.gmail.com
https://git.postgresql.org/pg/commitdiff/79d6d1a277ee1cdda90f9a66d7970ac3885822de

- Mark built-in coercion functions as leakproof where possible. Making these
leakproof seems helpful since (for example) if you have a function f(int8)
that is leakproof, you don't want it to effectively become non-leakproof when
you apply it to an int4 or int2 column. But that's what happens today, since
the implicit up-coercion will not be leakproof. Most of the coercion
functions that visibly can't throw errors are functions that convert numeric
datatypes to other, wider ones. Notable is that float4_numeric and
float8_numeric can be marked leakproof; before commit a57d312a7 they could not
have been. I also marked the functions that coerce strings to "name" as
leakproof; that's okay today because they truncate silently, but if we ever
reconsidered that behavior then they could no longer be leakproof. I desisted
from marking rtrim1() as leakproof; it appears so right now, but the code
seems a little too complex and perhaps subject to change, since it's shared
with other SQL functions. Discussion:
https://postgr.es/m/459322.1595607431@sss.pgh.pa.us
https://git.postgresql.org/pg/commitdiff/8a37951eebffd9bf528cb06d46127fb721d0e452

- Improve performance of binary COPY FROM through better buffering. At least on
Linux and macOS, fread() turns out to have far higher per-call overhead than
one could wish. Reading 64KB of data at a time and then parceling it out with
our own memcpy logic makes binary COPY from a file significantly faster ---
around 30% in simple testing for cases with narrow text columns (on Linux ...
even more on macOS). In binary COPY from frontend, there's no per-call
fread(), and this patch introduces an extra layer of memcpy'ing, but it still
manages to eke out a small win. Apparently, the control-logic overhead in
CopyGetData() is enough to be worth avoiding for small fetches. Bharath
Rupireddy and Amit Langote, reviewed by Vignesh C, cosmetic tweaks by me
Discussion:
https://postgr.es/m/CALj2ACU5Bz06HWLwqSzNMN=Gupoj6Rcn_QVC+k070V4em9wu=A@mail.gmail.com
https://git.postgresql.org/pg/commitdiff/0a0727ccfc5f4e2926623abe877bdc0b5bfd682e

Michaël Paquier pushed:

- Rework tab completion of COPY and \copy in psql. This corrects and simplifies
$subject in a number of ways: - Remove from the completion the pre-9.0 grammar
still supported for compatibility purposes. This simplifies the code, and
allows to extend it more easily with new patterns. - Add completion for the
options of FORMAT within a WITH clause. - Complete WHERE and WITH clauses
correctly depending on if TO or FROM are used, WHERE being only available with
COPY FROM. Author: Vignesh C, Michael Paquier Reviewed-by: Ahsan Hadi
Discussion:
https://postgr.es/m/CALDaNm3zWr=OmxeNqOqfT=uZTSdam_j-gkX94CL8eTNfgUtf6A@mail.gmail.com
https://git.postgresql.org/pg/commitdiff/c273d9d8ce412a475082b1729462845d2abb5ae8

- Fix comment in sha2.h. An incorrect reference to SHA-1 was present. Author:
Daniel Gustafsson Discussion:
https://postgr.es/m/FE26C953-FA87-4BB9-9105-AA1F8705B0D0@yesql.se
https://git.postgresql.org/pg/commitdiff/e47c2602aa4d35a4e3eb6ada40454c6c0f1279bf

- Fix corner case with PGP decompression in pgcrypto. A compressed stream may
end with an empty packet, and PGP decompression finished before reading this
empty packet in the remaining stream. This caused a failure in pgcrypto,
handling this case as corrupted data. This commit makes sure to consume such
extra data, avoiding a failure when decompression the entire stream. This
corner case was reproducible with a data length of 16kB, and existed since its
introduction in e94dd6a. A cheap regression test is added to cover this case.
Thanks to Jeff Janes for the extra investigation. Reported-by: Frank
Gagnepain Author: Kyotaro Horiguchi, Michael Paquier Discussion:
https://postgr.es/m/16476-692ef7b84e5fb893@postgresql.org Backpatch-through:
9.5
https://git.postgresql.org/pg/commitdiff/9e108984fb35d8f9e2c2bffa10c0034f9161e802

- Revert "Fix corner case with PGP decompression in pgcrypto". This reverts
commit 9e10898, after finding out that buildfarm members running SLES 15 on
z390 complain on the compression and decompression logic of the new test:
pipistrelles, barbthroat and steamerduck. Those hosts are visibly using
hardware-specific changes to improve zlib performance, requiring more
investigation. Thanks to Tom Lane for the discussion. Discussion:
https://postgr.es/m/20200722093749.GA2564@paquier.xyz Backpatch-through: 9.5
https://git.postgresql.org/pg/commitdiff/38f60f174e3279069b5547b5f4015eb4a8492037

- Tweak behavior of pg_stat_activity.leader_pid. The initial implementation of
leader_pid in pg_stat_activity added by b025f32 took the approach to strictly
print what a PGPROC entry includes. In short, if a backend has been involved
in parallel query at least once, leader_pid would remain set as long as the
backend is alive. For a parallel group leader, this means that the field would
always be set after it participated at least once in parallel query, and after
more discussions this could be confusing if using for example a connection
pooler. This commit changes the data printed so as leader_pid becomes always
NULL for a parallel group leader, showing up a non-NULL value only for the
parallel workers, and actually as long as a parallel query is running as
workers are shut down once the query has completed. This does not change the
definition of any catalog, so no catalog bump is needed. Per discussion with
Justin Pryzby, Álvaro Herrera, Julien Rouhaud and me. Discussion:
https://postgr.es/m/20200721035145.GB17300@paquier.xyz Backpatch-through: 13
https://git.postgresql.org/pg/commitdiff/11a68e4b53ffccf336a2faf5fa380acda28e880b

Álvaro Herrera pushed:

- Minor glossary tweaks. Add "(process)" qualifier to two terms, remove
self-reference in one term. Author: Jürgen Purtz <juergen(at)purtz(dot)de>
Discussion: https://postgr.es/m/95f90a5d-7692-701d-2c0c-0c88eb5cea7d@purtz.de
https://git.postgresql.org/pg/commitdiff/a0b2d583db9f040e2c156570b741e46bc33c6aec

- Glossary: Add term "base backup". Author: Jürgen Purtz <juergen(at)purtz(dot)de>
Discussion: https://postgr.es/m/95f90a5d-7692-701d-2c0c-0c88eb5cea7d@purtz.de
https://git.postgresql.org/pg/commitdiff/606c3845988ddd9497cbbbf6fc559b91c76ed65d

Thomas Munro pushed:

- Fix conversion table generator scripts. convutils.pm used implicit conversion
of undefined value to integer zero. Some of conversion scripts are
susceptible to regexp greediness. Fix, avoiding whitespace changes in the
output. Also update ICU URLs that moved. No need to back-patch, because the
output of these scripts is also in the source tree so we shouldn't need to
rerun them on back-branches. Author: Kyotaro Horiguchi
<horikyoga(dot)ntt(at)gmail(dot)com> Discussion:
https://postgr.es/m/CA%2BhUKGJ7SEGLbj%3D%3DTQCcyKRA9aqj8%2B6L%3DexSq1y25TA%3DWxLziQ%40mail.gmail.com
https://git.postgresql.org/pg/commitdiff/a5073871ea655e37759f22f30c4c70359ad9759b

- Fix error message. Remove extra space. Back-patch to all releases, like
commit 7897e3bb. Author: Lu, Chenyang <lucy(dot)fnst(at)cn(dot)fujitsu(dot)com> Discussion:
https://postgr.es/m/795d03c6129844d3803e7eea48f5af0d%40G08CNEXMBPEKD04.g08.fujitsu.local
https://git.postgresql.org/pg/commitdiff/42dee8b8e362bae05de2234a4fc7a3aa9dacdf6f

Peter Eisentraut pushed:

- doc: Document that ssl_ciphers does not affect TLS 1.3. TLS 1.3 uses a
different way of specifying ciphers and a different OpenSSL API. PostgreSQL
currently does not support setting those ciphers. For now, just document
this. In the future, support for this might be added somehow. Reviewed-by:
Jonathan S. Katz <jkatz(at)postgresql(dot)org> Reviewed-by: Tom Lane
<tgl(at)sss(dot)pgh(dot)pa(dot)us>
https://git.postgresql.org/pg/commitdiff/5733fa0fe4a73efa46801aa4189f7da17dd2b4bf

- Rename configure.in to configure.ac. The new name has been preferred by
Autoconf for a long time. Future versions of Autoconf will warn about the old
name. Discussion:
https://www.postgresql.org/message-id/flat/e796c185-5ece-8569-248f-dd3799701be1%402ndquadrant.com
https://git.postgresql.org/pg/commitdiff/25244b8972a34b838c4033fe9efc1d31cba9d0e3

Noah Misch pushed:

- Remove optimization for RAND_poll() failing. The loop to generate seed data
will exit on RAND_status(), so we don't need to handle the case of RAND_poll()
failing separately. Failures here are rare, so this a code cleanup,
essentially. Daniel Gustafsson, reviewed by David Steele and Michael Paquier.
Discussion: https://postgr.es/m/9B038FA5-23E8-40D0-B932-D515E1D8F66A@yesql.se
https://git.postgresql.org/pg/commitdiff/15e441972276e95639f8c3d9f5f66c2318fe9348

- Use RAND_poll() for seeding randomness after fork(). OpenSSL deprecated
RAND_cleanup(), and OpenSSL 1.1.0 made it into a no-op. Replace it with
RAND_poll(), per an OpenSSL community recommendation. While this has no
user-visible consequences under OpenSSL defaults, it might help under
non-default settings. Daniel Gustafsson, reviewed by David Steele and Michael
Paquier. Discussion:
https://postgr.es/m/9B038FA5-23E8-40D0-B932-D515E1D8F66A@yesql.se
https://git.postgresql.org/pg/commitdiff/ce4939ff70890fa658a4095b9fe457f8432b2575

David Rowley pushed:

- Allocate consecutive blocks during parallel seqscans. Previously we would
allocate blocks to parallel workers during a parallel sequential scan 1 block
at a time. Since other workers were likely to request a block before a worker
returns for another block number to work on, this could lead to non-sequential
I/O patterns in each worker which could cause the operating system's readahead
to perform poorly or not at all. Here we change things so that we allocate
consecutive "chunks" of blocks to workers and have them work on those until
they're done, at which time we allocate another chunk for the worker. The
size of these chunks is based on the size of the relation. Initial patch here
was by Thomas Munro which showed some good improvements just having a fixed
chunk size of 64 blocks with a simple ramp-down near the end of the scan. The
revisions of the patch to make the chunk size based on the relation size and
the adjusted ramp-down in powers of two was done by me, along with quite
extensive benchmarking to determine the optimal chunk sizes. For the most
part, benchmarks have shown significant performance improvements for large
parallel sequential scans on Linux, FreeBSD and Windows using SSDs. It's less
clear how this affects the performance of cloud providers. Tests done so far
are unable to obtain stable enough performance to provide meaningful benchmark
results. It is possible that this could cause some performance regressions on
more obscure filesystems, so we may need to later provide users with some
ability to get something closer to the old behavior. For now, let's leave
that until we see that it's really required. Author: Thomas Munro, David
Rowley Reviewed-by: Ranier Vilela, Soumyadeep Chakraborty, Robert Haas
Reviewed-by: Amit Kapila, Kirk Jamison Discussion:
https://postgr.es/m/CA+hUKGJ_EErDv41YycXcbMbCBkztA34+z1ts9VQH+ACRuvpxig@mail.gmail.com
https://git.postgresql.org/pg/commitdiff/56788d2156fc32bd5737e7ac716d70e6a269b7bc

== Pending Patches ==

Greg Nancarrow sent in another revision of a patch to enhance the connection
parameter "target_session_attrs" to support values
primary/standby/prefer-standby (using the existing "read-write" as a synonym for
"primary"). To provide closer alignment with similar functionality in the PGJDBC
driver, add a new connection parameter "target_server_type".

Movead Li sent in another revision of a patch to fix a bug that manifested as
pg_resetwal --next-transaction-id may cause database failed to restart.

Daniel Gustafsson sent in another revision of a patch to support libnss for as
TLS backend.

Dmitry Dolgov sent in another revision of a patch to implement generic type
subscripting.

Mark Dilger sent in two more revisions of a patch to implement a new heapcheck
contrib extension.

Dilip Kumar, Amit Kapila, and Ajin Cherian traded patches to fix an infelicity
between logical_work_mem and logical streaming of large in-progress
transactions.

Kato Sho sent in another revision of a patch to add a feature that enables
row-wise comparison partition pruning.

Amit Khandekar sent in another revision of a patch to auto-vectorize loop to
speed up large-precision numeric product.

Dilip Kumar sent in two more revisions of a patch to add a new GUC to control
whether the vacuum will error out immediately on detection of a corrupted tuple
or it will just emit a WARNING for each such instance and complete the rest of
the vacuuming.

Surafel Temesgen sent in another revision of a patch to implement
system-versioned temporal tables.

Mark Dilger sent in another revision of a patch to remove postfix operator
support, allow most keywords to be used as implicit column labels, and add to
the return value of pg_get_keywords() a fourth column showing whether the
keyword can be used as a bare column label.

Amit Langote sent in another revision of a patch to fix a bug with RETURNING
when UPDATE "moves" a tuple.

Atsushi Torikoshi sent in another revision of a patch to add custom and generic
plan information to pg_stat_statements.

Floris Van Nee sent in another revision of a patch to implement generic index
skip scan.

Vigneshwaran C sent in another revision of a patch to make it possible for COPY
to run in parallel in some circumstances.

Masahiko Sawada sent in another revision of a patch to support atomic commit
among multiple foreign servers.

Masahiko Sawada sent in another revision of a patch to reset spilled txn
statistics in pg_stat_replication.

Andrey V. Lepikhov sent in another revision of a patch to speed up COPY FROM
into a tables with foreign partitions.

Masahiko Sawada sent in another revision of a patch to fix a bug in pg_dump that
would fail in the case of inherited generated columns.

Anastasia Lubennikova sent in a WIP patch to fix a root_offsets out of bound
error in brin_summarize_new_values().

Vigneshwaran C and Bharath Rupireddy traded patches to fix a bug that manifested
as parallel worker hanging while handling errors by moving where error handling
occurs.

Tom Lane sent in three revisions of a patch to make CASE error handling less
surprising.

Bharath Rupireddy sent in a patch to fix an issue with cancel_before_shmem_exit
while searching to remove a particular registered exit callbacks.

Wang, Shenhao sent in a patch to handle an ECPG_bytea typo.

Mahendra Singh sent in two revisions of a patch to display offset along with
block number in vacuum errors.

Andres Freund sent in another revision of a patch to improve connection
scalability.

Ashutosh Sharma sent in a patch to add a new contrib extension, pg_surgery.

Peter Geoghegan sent in another revision of a patch to remove the
hashagg_avoid_disk_plan GUC, and add one called hash_mem_multiplier.

Tom Lane and Pavel Stěhule traded patches to improve row estimates for empty
tables.

Peter Geoghegan sent in another revision of a patch to fix a
LookupTupleHashEntryHash() pipeline-stall issue, fixing some bit rot from a
previous patch by Andres Freund.

Amit Kapila sent in another revision of a patch to fix comments in heapam.c to
reflect new places where parallel insert is now possible.

Dilip Kumar sent in two revisions of a patch to implement parallel bitmap index
scans.

Jeff Davis sent in a patch to to use HyperLogLog to estimate the number of
groups contained in a spill file.

Justin Pryzby sent in another revision of a patch to include the leader PID in
logfile.