== PostgreSQL Weekly News - June 7, 2020 ==

From: David Fetter <david(at)fetter(dot)org>
To: PostgreSQL Announce <pgsql-announce(at)postgresql(dot)org>
Subject: == PostgreSQL Weekly News - June 7, 2020 ==
Date: 2020-06-07 22:41:07
Message-ID: 20200607224107.GA25833@fetter.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-announce

== PostgreSQL Weekly News - June 7, 2020 ==

== PostgreSQL Product News ==

wal2mongo v1.0.6, a tool for replicating from PostgreSQL to MongoDB, released.

pgsodium 1.0.0, an extention which adds libsodium encryption to PostgreSQL, released.

PGEE, a fork of PostgreSQL by Cybertec, released.

pitrery 3.1, a set of Bash scripts to manage PITR backups for PostgreSQL, released.

== PostgreSQL Jobs for June ==


== PostgreSQL Local ==

FOSS4G 2020, will take place in Calgary, Alberta, Canada August 24-29 2020.
the Call for Papers is currently open at https://2020.foss4g.org/speakers/

PGDay Ukraine will take place September 5th, 2020 in Lviv at the Bank Hotel.

pgDay Israel 2020 will take place on September 10, 2020 in Tel Aviv.

PGDay Austria will take place September 18, 2020 at Schloss Schoenbrunn
(Apothekertrakt) in Vienna.

PostgreSQL Conference Europe 2020 will be held on October 20-23, 2020 in Berlin,
Germany. The CfP is open through July 31, 2020 at https://2020.pgconf.eu/callforpapers

PG Day Russia will take place in Saint Petersburg on July 9, 2021.

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

Andrew Dunstan pushed:

- Make install-tests target work with vpath builds. Also add a top-level
install-tests target. Backpatch to all live branches. Craig Ringer, tweaked
by me.

- Make ssl certificate for ssl_passphrase_callback test via Makefile. The recipe
was previously given in comments in the module's test script, but now we have
an explicit recipe in the Makefile. The now redundant comments in the script
are removed. This recipe shouldn't be needed in normal use, as the
certificate and key are in git and don't need to be regenerated. Discussion:

Michaël Paquier pushed:

- Fix crashes with currtid() and currtid2(). A relation that has no storage
initializes rd_tableam to NULL, which caused those two functions to crash
because of a pointer dereference. Note that in 11 and older versions, this has
always failed with a confusing error "could not open file". These two
functions are used by the Postgres ODBC driver, which requires them only when
connecting to a backend strictly older than 8.1. When connected to 8.2 or a
newer version, the driver uses a RETURNING clause instead whose support has
been added in 8.2, so it should be possible to just remove both functions in
the future. This is left as an issue to address later. While on it, add more
regression tests for those functions as we never really had coverage for them,
and for aggregates of TIDs. Reported-by: Jaime Casanova, via sqlsmith Author:
Michael Paquier Reviewed-by: Álvaro Herrera Discussion:
Backpatch-through: 12

- Fix use-after-release mistake in currtid() and currtid2() for views. This
issue has been present since the introduction of this code as of a3519a2 from
2002, and has been found by buildfarm member prion that uses
RELCACHE_FORCE_RELEASE via the tests introduced recently in e786be5.
Discussion: https://postgr.es/m/20200601022055.GB4121@paquier.xyz
Backpatch-through: 9.5

- Fix instance of elog() called while holding a spinlock. This broke the project
rule to not call any complex code while a spinlock is held. Issue introduced
by b89e151. Discussion:
Backpatch-through: 9.5

- Fix comment in be-secure-openssl.c. Since 573bd08, hardcoded DH parameters
have been moved to a different file, making the comment on top of
load_dh_buffer() incorrect. Author: Daniel Gustafsson Discussion:

- Preserve pg_index.indisreplident across REINDEX CONCURRENTLY. If the flag
value is lost, logical decoding would work the same way as REPLICA IDENTITY
NOTHING, meaning that no old tuple values would be included in the changes
anymore produced by logical decoding. Author: Michael Paquier Reviewed-by:
Euler Taveira Discussion:
https://postgr.es/m/20200603065340.GK89559@paquier.xyz Backpatch-through: 12

Peter Eisentraut pushed:

- Use correct and consistent unit abbreviation.

- psql: Clean up terminology in \dAp command. The preferred terminology has been
support "function", not procedure, for some time, so change that over. The
command stays \dAp, since \dAf is already something else.

- OpenSSL 3.0.0 compatibility in tests. DES has been deprecated in OpenSSL 3.0.0
which makes loading keys encrypted with DES fail with "fetch failed". Solve
by changing the cipher used to aes256 which has been supported since 1.0.1
(and is more realistic to use anyways). Note that the minimum supported
OpenSSL version is 1.0.1 as of 7b283d0e1d1d79bf1c962d790c94d2a53f3bb38a, so
this does not introduce any new version requirements. Author: Daniel
Gustafsson <daniel(at)yesql(dot)se> Discussion:

- Add missing source files to nls.mk.

- doc: Fix incorrect link target.

- doc: Remove line breaks after <title>. This creates unnecessary rendering
problem risks, and it's inconsistent and gets copied around.

- doc: Clean up title case use.

- doc: Trim trailing whitespace.

- doc: Language review.

- doc: Fix up spacing around verbatim DocBook elements.

- doc: Move options on man pages into more alphabetical order.

- Formatting and punctuation improvements in postgresql.conf.sample.

- doc: Fix man page whitespace issues. Whitespace between tags is significant,
and in some cases it creates extra vertical space in man pages. The fix is
either to remove some newlines or in some cases to reword slightly to avoid
the awkward markup layout.

- Spelling adjustments.

- Fix message translatability. Two parts of the same message shouldn't be split
across two function calls.

- psql: Format \? output a little better.

Amit Kapila pushed:

- Doc: Update the documentation for spilled transaction statistics. Reported-by:
Sawada Masahiko Author: Sawada Masahiko, Amit Kapila Reviewed-by: Amit Kapila

Fujii Masao pushed:

- Don't call elog() while holding spinlock. Previously UpdateSpillStats() called
elog(DEBUG2) while holding the spinlock even though the local variables that
the elog() accesses don't need to be protected by the lock. Since spinlocks
are intended for very short-term locks, they should not be used when calling
elog(DEBUG2). So this commit moves that elog() out of spinlock period.
Author: Kyotaro Horiguchi Reviewed-by: Amit Kapila and Fujii Masao Discussion:

- doc: Move wal_init_zero and wal_recycle descriptions to proper section. The
group of wal_init_zero and wal_recycle is WAL_SETTINGS in guc.c, but
previously their documents were located in "Replication"/"Sending Servers"
section. This commit moves them to the proper section "Write Ahead
Log"/"Settings". Back-patch to v12 where wal_init_zero and wal_recycle
parameters were introduced. Author: Fujii Masao Discussion:

Bruce Momjian pushed:

- doc: PG 13 relnotes: fix link for grouping sets hash overflow. Use
"guc-enable-groupingsets-hash-disk". Reported-by: TAKATSUKA Haruka
Discussion: https://postgr.es/m/16468-7939d39f1786516c@postgresql.org
Backpatch-through: master

Tom Lane pushed:

- Don't call palloc() while holding a spinlock, either. Fix some more violations
of the "only straight-line code inside a spinlock" rule. These are hazardous
not only because they risk holding the lock for an excessively long time, but
because it's possible for palloc to throw elog(ERROR), leaving a stuck
spinlock behind. copy_replication_slot() had two separate places that did
pallocs while holding a spinlock. We can make the code simpler and safer by
copying the whole ReplicationSlot struct into a local variable while holding
the spinlock, and then referencing that copy. (While that's arguably more
cycles than we really need to spend holding the lock, the struct isn't all
that big, and this way seems far more maintainable than copying fields
piecemeal. Anyway this is surely much cheaper than a palloc.) That bug goes
back to v12. InvalidateObsoleteReplicationSlots() not only did a palloc while
holding a spinlock, but for extra sloppiness then leaked the memory ---
probably for the lifetime of the checkpointer process, though I didn't try to
verify that. Fortunately that silliness is new in HEAD.
pg_get_replication_slots() had a cosmetic violation of the rule, in that it
only assumed it's safe to call namecpy() while holding a spinlock. Still,
that's a hazard waiting to bite somebody, and there were some other cosmetic
coding-rule violations in the same function, so clean it up. I back-patched
this as far as v10; the code exists before that but it looks different, and
this didn't seem important enough to adapt the patch further back.

- Reject "23:59:60.nnn" in datetime input. It's intentional that we don't allow
values greater than 24 hours, while we do allow "24:00:00" as well as
"23:59:60" as inputs. However, the range check was miscoded in such a way that
it would accept "23:59:60.nnn" with a nonzero fraction. For time or timetz,
the stored result would then be greater than "24:00:00" which would fail
dump/reload, not to mention possibly confusing other operations. Fix by
explicitly calculating the result and making sure it does not exceed 24 hours.
(This calculation is redundant with what will happen later in tm2time or
tm2timetz. Maybe someday somebody will find that annoying enough to justify
refactoring to avoid the duplication; but that seems too invasive for a
back-patched bug fix, and the cost is probably unmeasurable anyway.) Note
that this change also rejects such input as the time portion of a
timestamp(tz) value. Back-patch to v10. The bug is far older, but to change
this pre-v10 we'd need to ensure that the logic behaves sanely with float
timestamps, which is possibly nontrivial due to roundoff considerations.
Doesn't really seem worth troubling with. Per report from Christoph Berg.
Discussion: https://postgr.es/m/20200520125807.GB296739@msg.df7cb.de

- Use query collation, not column's collation, while examining statistics.
Commit 5e0928005 changed the planner so that, instead of blindly using
DEFAULT_COLLATION_OID when invoking operators for selectivity estimation, it
would use the collation of the column whose statistics we're considering.
This was recognized as still being not quite the right thing, but it seemed
like a good incremental improvement. However, shortly thereafter we
introduced nondeterministic collations, and that creates cases where operators
can fail if they're passed the wrong collation. We don't want planning to
fail in cases where the query itself would work, so this means that we *must*
use the query's collation when invoking operators for estimation purposes.
The only real problem this creates is in ineq_histogram_selectivity, where the
binary search might produce a garbage answer if we perform comparisons using a
different collation than the column's histogram is ordered with. However, when
the query's collation is significantly different from the column's default
collation, the estimate we previously generated would be pretty irrelevant
anyway; so it's not clear that this will result in noticeably worse estimates
in practice. (A follow-on patch will improve this situation in HEAD, but it
seems too invasive for back-patch.) The patch requires changing the
signatures of mcv_selectivity and allied functions, which are exported and
very possibly are used by extensions. In HEAD, I just did that, but an API/ABI
break of this sort isn't acceptable in stable branches. Therefore, in v12 the
patch introduces "mcv_selectivity_ext" and so on, with signatures matching
HEAD, and makes the old functions into wrappers that assume
DEFAULT_COLLATION_OID should be used. That does not match the prior behavior,
but it should avoid risk of failure in most cases. (In practice, I think most
extension datatypes aren't collation-aware, so the change probably doesn't
matter to them.) Per report from James Lucas. Back-patch to v12 where the
problem was introduced. Discussion:

- Improve ineq_histogram_selectivity's behavior for non-default orderings.
ineq_histogram_selectivity() can be invoked in situations where the ordering
we care about is not that of the column's histogram. We could be considering
some other collation, or even more drastically, the query operator might not
agree at all with what was used to construct the histogram. (We'll get here
for anything using scalarineqsel-based estimators, so that's quite likely to
happen for extension operators.) Up to now we just ignored this issue and
assumed we were dealing with an operator/collation whose sort order exactly
matches the histogram, possibly resulting in junk estimates if the binary
search gets confused. It's past time to improve that, since the use of
nondefault collations is increasing. What we can do is verify that the given
operator and collation match what's recorded in pg_statistic, and use the
existing code only if so. When they don't match, instead execute the operator
against each histogram entry, and take the fraction of successes as our
selectivity estimate. This gives an estimate that is probably good to about
1/histogram_size, with no assumptions about ordering. (The quality of the
estimate is likely to degrade near the ends of the value range, since the two
orderings probably don't agree on what is an extremal value; but this is
surely going to be more reliable than what we did before.) At some point we
might further improve matters by storing more than one histogram calculated
according to different orderings. But this code would still be good fallback
logic when no matches exist, so that is not an argument for not doing this.
While here, also improve get_variable_range() to deal more honestly with
non-default collations. This isn't back-patchable, because it requires adding
another argument to ineq_histogram_selectivity, and because it might have
significant impact on the estimation results for extension operators relying
on scalarineqsel --- mostly for the better, one hopes, but in any case
destabilizing plan choices in back branches is best avoided. Per
investigation of a report from James Lucas. Discussion:

- Doc: remove annotations about multi-row output of set-returning functions. I
thought this added clarity, or at least was consistent with the way these
entries looked before v13 ... but apparently I'm in the minority. Discussion:

- Rethink definition of cancel.c's CancelRequested flag. As it stands, this flag
is only set when we've successfully sent a cancel request, not if we get
SIGINT and then fail to send a cancel. However, for almost all callers, that's
the Wrong Thing: we'd prefer to abort processing after control-C even if no
cancel could be sent. As an example, since commit 1d468b9ad "pgbench -i"
fails to give up sending COPY data even after control-C, if the postmaster has
been stopped, which is clearly not what the code intends and not what anyone
would want. (The fact that it keeps going at all is the fault of a separate
bug in libpq, but not letting CancelRequested become set is clearly not what
we want here.) The sole exception, as far as I can find, is that
scripts_parallel.c's ParallelSlotsGetIdle tries to consume a query result
after issuing a cancel, which of course might not terminate quickly if no
cancel happened. But that behavior was poorly thought out too. No user of
ParallelSlotsGetIdle tries to continue processing after a cancel, so there is
really no point in trying to clear the connection's state. Moreover this has
the same defect as for other users of cancel.c, that if the cancel request
fails for some reason then we end up with control-C being completely ignored.
(On top of that, select_loop failed to distinguish clearly between SIGINT and
other reasons for select(2) failing, which means that it's possible that the
existing code would think that a cancel has been sent when it hasn't.) Hence,
redefine CancelRequested as simply meaning that SIGINT was received. We could
add a second flag with the other meaning, but in the absence of any compelling
argument why such a flag is needed, I think it would just offer an opportunity
for future callers to get it wrong. Also remove the consumeQueryResult call
in ParallelSlotsGetIdle's failure exit. In passing, simplify the API of
select_loop. It would now be possible to re-unify psql's cancel_pressed with
CancelRequested, partly undoing 5d43c3c54. But I'm not really convinced that
that's worth the trouble, so I left psql alone, other than fixing a misleading
comment. This code is new in v13 (cf a4fd3aa71), so no need for back-patch.
Per investigation of a complaint from Andres Freund. Discussion:

- Try to read data from the socket in pqSendSome's write_failed paths. Even when
we've concluded that we have a hard write failure on the socket, we should
continue to try to read data. This gives us an opportunity to collect any
final error message that the backend might have sent before closing the
connection; moreover it is the job of pqReadData not pqSendSome to close the
socket once EOF is detected. Due to an oversight in 1f39a1c06, pqSendSome
failed to try to collect data in the case where we'd already set write_failed.
The problem was masked for ordinary query operations (which really only make
one write attempt anyway), but COPY to the server would continue to send data
indefinitely after a mid-COPY connection loss. Hence, add pqReadData calls
into the paths where pqSendSome drops data because of write_failed. If we've
lost the connection, this will eventually result in closing the socket and
setting CONNECTION_BAD, which will cause PQputline and siblings to report
failure, allowing the application to terminate the COPY sooner. (Basically
this restores what happened before 1f39a1c06.) There are related issues that
this does not solve; for example, if the backend sends an error but doesn't
drop the connection, we did and still will keep pumping COPY data as long as
the application sends it. Fixing that will require application-visible
behavior changes though, and anyway it's an ancient behavior that we've had
few complaints about. For now I'm just trying to fix the regression from
1f39a1c06. Per a complaint from Andres Freund. Back-patch into v12 where
1f39a1c06 came in. Discussion:

Joe Conway pushed:

- Add unlikely() to CHECK_FOR_INTERRUPTS(). Add the unlikely() branch hint macro
to CHECK_FOR_INTERRUPTS(). Backpatch to REL_10_STABLE where we first started
using unlikely(). Discussion:

Noah Misch pushed:

- Refresh function name in CRC-associated Valgrind suppressions. Back-patch to
9.5, where commit 4f700bcd20c087f60346cb8aefd0e269be8e2157 first appeared.
Reviewed by Tom Lane. Reported by Andrew Dunstan. Discussion:

Magnus Hagander pushed:

- Fix reference to wrong view in release notes. The estimate of total backup
size effects the view pg_stat_progress_basebackup, not

Thomas Munro pushed:

- Doc: Clean up references to obsolete OS versions. Remove obsolete instructions
for old operating system versions, and update the text to reflect the defaults
on modern systems. Reviewed-by: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> Reviewed-by:
Peter Eisentraut <peter(dot)eisentraut(at)2ndquadrant(dot)com> Reviewed-by: Magnus
Hagander <magnus(at)hagander(dot)net> Discussion:

Jeff Davis pushed:

- Fix platform-specific performance regression in logtape.c. Commit 24d85952
made a change that indirectly caused a performance regression by triggering a
change in the way GCC optimizes memcpy() on some platforms. The behavior
seemed to contradict a GCC document, so I filed a report:
https://gcc.gnu.org/bugzilla/show_bug.cgi?id=95556 This patch implements a
narrow workaround which eliminates the regression I observed. The workaround
is benign enough that it seems unlikely to cause a different regression on
another platform. Discussion:

== Pending Patches ==

Nathan Bossart sent in another revision of a patch to add MAIN_RELATION_CLEANUP

Kyotaro HORIGUCHI sent in another revision of a patch to make the stats
collector use shared memory instead of files for temporary storage.

Mark Dilger sent in a patch to remove a duplicate check for

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

Martín Marqués and Kyotaro HORIGUCHI traded patches to add read access for
pg_monitor to the pg_replication_origin_status view.

Amit Langote sent in another revision of a patch to make updates and deletes to
inheritance trees scale better.

Mark Dilger sent in two more revisions of a patch to implement cmdstats, a
subsystem for tracking the number of times a type of command has been run in a
database cluster, since startup or since the last time the counts were reset,
whichever is newer.

Michaël Paquier sent in another revision of a patch to fix a bug that manifested
as SIGSEGV from START_REPLICATION 0/XXXXXXX in XLogSendPhysical () at

Bertrand Drouvot sent in two revisions of a patch to add a
pg_lwlock_blocking_pid() function.

Fujii Masao sent in another revision of a patch to drop non-fast promotion.

Andres Freund sent in another revision of a patch to make pq_begintypsend and
friends more efficient.

Mark Dilger sent in a patch to rename relkind as objtype where appropriate. The
previous overloading was confusing.

Mikhail Titov sent in a patch to add a leading minus for negative time intervals
in ISO 8601 format.

Vigneshwaran C sent in another revision of a patch to enable the COPY FROM
command to process data from files or STDIN to a table in parallel.

Michaël Paquier sent in a patch to remove currtid() and currtid2(), and move
heap_get_latest_tid() within the heap AM handler.

Kyotaro HORIGUCHI sent in another revision of a patch to allow wait event sets
to be registered to resource owners, add infrastructure for asynchronous
execution, and use same to add asynchronous operations to the PostgreSQL FDW.

Atsushi Torikoshi sent in another revision of a patch to expose the counters of
plancache to pg_prepared_statements.

Lee Dong Wook sent in two revisions of a patch to add an example and link for
--encoding option of pg_dump.

Thomas Munro sent in a patch to redesign the error handling in buffile.c.

Masahiko Sawada sent in another revision of a patch to make 2PC work on foreign

Pavel Stěhule sent in another revision of a patch to add a string_to_table()

Chen Hujaun sent in a PoC patch to enable page compression for OLTP workloads.

Antonin Houska sent in another revision of a patch to make referential integrity
checks more efficient.

Peter Eisentraut sent in a patch to make more use of RELKIND_HAS_STORAGE().

Alexey Bashtanov sent in a patch to improve planner cost estimations for
alternative subplans.

Amit Kapila sent in another revision of a patch to immediately WAL-log
the subtransaction and top-level XID association.

Jeff Davis sent in a patch to fix a performance regression related to

Fabien COELHO sent in another revision of a patch to psql to make it show all
results of all queries sent.

Justin Pryzby sent in a patch to make it possible to use CREATE INDEX
CONCURRENTLY on a partitioned table.

Browse pgsql-announce by date

  From Date Subject
Next Message Michel Pelletier 2020-06-10 00:26:47 pgsodium 1.1.1 is released!
Previous Message Thibaut Madelaine 2020-06-03 14:00:38 pitrery 3.1 released