== PostgreSQL Weekly News - May 3, 2020 ==

From: David Fetter <david(at)fetter(dot)org>
To: PostgreSQL Announce <pgsql-announce(at)postgresql(dot)org>
Subject: == PostgreSQL Weekly News - May 3, 2020 ==
Date: 2020-05-03 21:56:59
Message-ID: 20200503215658.GA21958@fetter.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-announce

== PostgreSQL Weekly News - May 3, 2020 ==

== PostgreSQL Product News ==

pgAdmin4 4.21, a web- and native GUI control center for PostgreSQL, released.

InfluxDB fdw 0.2 released

pgbouncer 1.13.0, a connection pooler and more for PostgreSQL, released.

DBD::Pg 3.11.1, a Perl driver for PostgreSQL, released.

griddb_fdw 1.2 released.

== PostgreSQL Local ==

PGCon 2020 will take place online on May 26-29, 2020.

PostgresLondon 2020 will be July 7-8, 2020 with an optional training day on
July 6.

PG Day Russia will take place in Saint Petersburg on July 10, 2020.

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. The CfP is open through May 17, 2020.

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

Michaël Paquier pushed:

- Fix some typos. Author: Justin Pryzby Discussion:

- Add more TAP coverage for archive status with crash recovery of standbys. This
part of the test was included originally in 4e87c48, but got reverted as of
f9c1b8d because of timing issues in the test, where, after more analysis, we
found that the standbys may not have recovered from the archives all the
segments needed by the test. This stabilizes the test by making sure that
standbys replay up to the position returned by pg_switch_wal(), meaning that
all segments are recovered before the manual checkpoint that tests WAL segment
recycling and its interactions with archive status files. Author:
Jehan-Guillaume de Rorthais Reviewed-by: Kyotaro Horiguchi, Michael Paquier
Discussion: https://postgr.es/m/20200424034248.GL33034@paquier.xyz

- Fix check for conflicting SSL min/max protocol settings. Commit 79dfa8a has
introduced a check to catch when the minimum protocol version was set higher
than the maximum version, however an error was getting generated when both
bounds are set even if they are able to work, causing a backend to not use a
new SSL context but keep the old one. Author: Daniel Gustafsson Discussion:

- Rename connection parameters to control min/max SSL protocol version in libpq.
The libpq parameters ssl{max|min}protocolversion are renamed to use
underscores, to become ssl_{max|min}_protocol_version. The related
environment variables still use the names introduced in commit ff8ca5f that
added the feature. Per complaint from Peter Eisentraut (this was also
mentioned by me in the original patch review but the issue got discarded).
Author: Daniel Gustafsson Reviewed-by: Peter Eisentraut, Michael Paquier

- Improve various aspects of pg_rewind documentation. The pg_rewind docs
currently assert that the state of the target's data directory after rewind is
equivalent to the source's data directory. This clarifies the documentation
to describe that the base state is further back in time and that the target's
data directory will include the current state from the source of any copied
blocks since the point of divergence. This commit also improves the section
"How It Works": - Describe the update of the pg_control file. - Reorganize the
list of files and directories ignored during the rewind. Author: James
Coleman Discussion:

Peter Eisentraut pushed:

- pg_dump: Replace can't-happen error with assertion.

- Add missing gettext triggers. Some translatable strings have been moved to
scanner_yyerror(), so we need to add that, too.

- Fix typo. from 927474ce1a2

- Update config.guess and config.sub.

- Make SQL/JSON error code names match SQL standard. see also a00c53b0cb

- Put new command-line options into alphabetical order in help output.

- Add NLS to pg_verifybackup.

- Add missing newlines in error messages.

Tom Lane pushed:

- Doc: render &pi; more nicely in PDF output. We need to select symbol font
explicitly, or it comes out misaligned. Alexander Lakhin, Tom Lane
Discussion: https://postgr.es/m/10598.1587928415@sss.pgh.pa.us

- Fix full text search to handle NOT above a phrase search correctly. Queries
such as '!(foo<->bar)' failed to find matching rows when implemented as a GiST
or GIN index search. That's because of failing to handle phrase searches as
tri-valued when considering a query without any position information for the
target tsvector. We can only say that the phrase operator might match, not
that it does match; and therefore its NOT also might match. The previous
coding incorrectly inverted the approximate phrase result to decide that there
was certainly no match. To fix, we need to make TS_phrase_execute return a
real ternary result, and then bubble that up accurately in TS_execute. As
long as we have to do that anyway, we can simplify the baroque things
TS_phrase_execute was doing internally to manage tri-valued searching with
only a bool as explicit result. For now, I left the externally-visible result
of TS_execute as a plain bool. There do not appear to be any outside callers
that need to distinguish a three-way result, given that they passed in a flag
saying what to do in the absence of position data. This might need to change
someday, but we wouldn't want to back-patch such a change. Although
tsginidx.c has its own TS_execute_ternary implementation for use at upper
index levels, that sadly managed to get this case wrong as well :-(. Fixing
it is a lot easier fortunately. Per bug #16388 from Charles Offenbacher.
Back-patch to 9.6 where phrase search was introduced. Discussion:

- Doc: re-re-revise markup for tables of functions. Make the markup a bit less
ad-hoc. A function-table cell now contains several <para> units, and we label
the ones that contain function signatures with role="func_signature". The CSS
or FO stylesheets then key off of that to decide how to set the indentation.
A very useful win from this approach is that we can have more than one
signature entry per table cell, simplifying the documentation of
closely-related operators and functions. This patch mostly just replaces the
markup in the tables I converted so far. But I did alter a couple of places
where multiple signatures were helpful. Discussion:

- Doc: update sections 9.14 - 9.16 for new function table layout. Minor
editorial changes in the first two sections; larger ones in the JSON section.

- Doc: update sections 9.17 - 9.21 for new function table layout. With the usual
quota of minor editorial changes.

- Get rid of trailing semicolons in C macro definitions. Writing a trailing
semicolon in a macro is almost never the right thing, because you almost
always want to write a semicolon after each macro call instead. (Even if
there was some reason to prefer not to, pgindent would probably make a hash of
code formatted that way; so within PG the rule should basically be "don't do
it".) Thus, if we have a semi inside the macro, the compiler sees
"something;;". Much of the time the extra empty statement is harmless, but it
could lead to mysterious syntax errors at call sites. In perhaps an
overabundance of neatnik-ism, let's run around and get rid of the excess
semicolons whereever possible. The only thing worse than a mysterious syntax
error is a mysterious syntax error that only happens in the back branches;
therefore, backpatch these changes where relevant, which is most of them
because most of these mistakes are old. (The lack of reported problems shows
that this is largely a hypothetical issue, but still, it could bite us in some
future patch.) John Naylor and Tom Lane Discussion:

Robert Haas pushed:

- Fix bogus tar-file padding logic for standby.signal. When pg_basebackup -R is
used, we inject standby.signal into the tar file for the main tablespace. The
proper thing to do is to pad each file injected into the tar file out to a
512-byte boundary by appending nulls, but here the file is of length 0 and we
add 511 zero bytes. Since 0 is already a multiple of 512, we should not add
any zero bytes. Do that instead. Patch by me, reviewed by Tom Lane.

Alexander Korotkov pushed:

- Fix definition of pg_statio_all_tables view. pg_statio_all_tables view appears
to have a wrong grouping. As the result numbers of toast index blocks read
and hit were multiplied to the number of table indexes. This commit fixes the
view definition. Backpatching this appears difficult. We don't have a
mechanism to patch a system catalog of existing instances in minor upgrade.
We can write a release notes instruction to do this manually. But per
discussion this is probably not so critical bug for doing such an intrusive
fix. Reported-by: Andrei Zubkov Discussion:

- Fix typo in comment. Reported-by: Oleg Bartunov

Peter Geoghegan pushed:

- Add LP_DEAD deletion of a posting list tuple test. Make sure that we have test
coverage of the posting list tuple path within _bt_xid_horizon(). Per
off-list complaint from Andres Freund.

- Remove redundant _bt_killitems() buffer check. _bt_getbuf() cannot return an
invalid buffer. Oversight in commit 2ed5b87f96d.

- Fix AddressSanitizer use-after-scope complaint. XLogRegisterBufData() does not
copy data pointed to by caller's pointer argument. Oversight in commit
0d861bbb702. Author: Peter Eisentraut Reported-By: Peter Eisentraut

- Add nbtree ScalarArrayOpExpr tests. Add test coverage for the nbtutils.c
routines concerned with IndexScans that have native ScalarArrayOpExpr quals.
The ScalarArrayOpExpr specialized mark and restore routines, and the "find
extreme element" routine now have some test coverage. These functions are
probably infrequently exercised by real world queries, so having some coverage
seems like a good idea. The mark and restore routines were originally added
by a bugfix that came several weeks after the first stable release of Postgres
9.2 (see commit 70bc5833195).

- Fix bug in nbtree VACUUM "skip full scan" feature. Commit 857f9c36cda (which
taught nbtree VACUUM to skip a scan of the index from btcleanup in situations
where it doesn't seem worth it) made VACUUM maintain the oldest btpo.xact
among all deleted pages for the index as a whole. It failed to handle all the
details surrounding pages that are deleted by the current VACUUM operation
correctly (though pages deleted by some previous VACUUM operation were
processed correctly). The most immediate problem was that the special area of
the page was examined without a buffer pin at one point. More fundamentally,
the handling failed to account for the full range of _bt_pagedel() behaviors.
For example, _bt_pagedel() sometimes deletes internal pages in passing, as
part of deleting an entire subtree with btvacuumpage() caller's page as the
leaf level page. The original leaf page passed to _bt_pagedel() might not be
the page that it deletes first in cases where deletion can take place. It's
unclear how disruptive this bug may have been, or what symptoms users might
want to look out for. The issue was spotted during unrelated code review. To
fix, push down the logic for maintaining the oldest btpo.xact to
_bt_pagedel(). btvacuumpage() is now responsible for pages that were fully
deleted by a previous VACUUM operation, while _bt_pagedel() is now responsible
for pages that were deleted by the current VACUUM operation (this includes
half-dead pages from a previous interrupted VACUUM operation that become fully
deleted in _bt_pagedel()). Note that _bt_pagedel() should never encounter an
existing deleted page. This commit theoretically breaks the ABI of a stable
release by changing the signature of _bt_pagedel(). However, if any third
party extension is actually affected by this, then it must already be
completely broken (since there are numerous assumptions made in _bt_pagedel()
that cannot be met outside of VACUUM). It seems highly unlikely that such an
extension actually exists, in any case. Author: Peter Geoghegan Reviewed-By:
Masahiko Sawada Discussion:
Backpatch: 11-, where the "skip full scan" feature was introduced.

- Fix undercounting in VACUUM VERBOSE output. The logic for determining how many
nbtree pages in an index are deleted pages sometimes undercounted pages.
Pages that were deleted by the current VACUUM operation (as opposed to some
previous VACUUM operation whose deleted pages have yet to be reused) were
sometimes overlooked. The final count is exposed to users through VACUUM
VERBOSE's "%u index pages have been deleted" output. btvacuumpage() avoided
double-counting when _bt_pagedel() deleted more than one page by assuming that
only one page was deleted, and that the additional deleted pages would get
picked up during a future call to btvacuumpage() by the same VACUUM operation.
_bt_pagedel() can legitimately delete pages that the btvacuumscan() scan will
not visit again, though, so that assumption was slightly faulty. Fix the
accounting by teaching _bt_pagedel() about its caller's requirements. It now
only reports on pages that it knows btvacuumscan() won't visit again
(including the current btvacuumpage() page), so everything works out in the
end. This bug has been around forever. Only backpatch to v11, though, to
keep _bt_pagedel() is sync on the branches that have today's bugfix commit
b0229f26da. Note that this commit changes the signature of _bt_pagedel(),
just like commit b0229f26da. Author: Peter Geoghegan Reviewed-By: Masahiko
Sawada Discussion:
Backpatch: 11-

- Reorder function prototypes for consistency.

- Clear up issue with FSM and oldest bpto.xact. On further reflection, code
comments added by commit b0229f26 slightly misrepresented how we determine the
oldest bpto.xact for the index. btvacuumpage() does not treat the bpto.xact of
a page that it put in the FSM as a candidate to be the oldest deleted page
(the delete-marked page that has the oldest bpto.xact XID among all pages
encountered). The definition of a deleted page for the purposes of the
bpto.xact calculation is different from the definition used by the bulk delete
statistics. The bulk delete statistics don't distinguish between pages that
were deleted by the current VACUUM, pages deleted by a previous VACUUM
operation but not yet recyclable/reusable, and pages that are reusable (though
reusable pages are counted separately). Backpatch: 11-, just like commit

- Refactor btvacuumpage(). Remove one of the arguments to btvacuumpage(), and
give up on the idea that it's a recursive function. We now use the term
"backtracking" to refer to the case where an earlier block must be visited to
make sure no tuples that need to be removed were missed. Advertising
btvacuumpage() as a recursive function was unhelpful. In reality the function
always simulates recursion with a loop (it doesn't actually call itself).
This wasn't just necessary as a precaution (per the comments mentioning tail
recursion), though. There is no reliable natural limit on the number of times
we can backtrack. There are important behavioral difference when
"recursing"/backtracking, mostly related to page deletion. We don't perform
page deletion when backtracking due to the extra complexity. And when we
recurse, we're not performing a physical order scan anymore, so we expect
fairly different conditions to hold for the page. Structuring the code like
this makes it clearer how _bt_pagedel() cooperates with btvacuumpage() and
btvacuumscan() (as established in commit b0229f26 and commit 73a076b0).
Author: Peter Geoghegan Reviewed-By: Masahiko Sawada Discussion:

Álvaro Herrera pushed:

- Check slot->restart_lsn validity in a few more places. Lack of these checks
could cause visible misbehavior, including assertion failures. This was
missed in commit c6550776394e, whereby restart_lsn becomes invalid when the
size limit is exceeded. Also reword some existing error messages, and add
errdetail(), so that the reported errors all match in spirit. Author: Kyotaro
Horiguchi <horikyota(dot)ntt(at)gmail(dot)com> Reviewed-by: Álvaro Herrera
<alvherre(at)alvh(dot)no-ip(dot)org> Discussion:

- Fix checkpoint signalling. Checkpointer uses its MyLatch to wake up when a
checkpoint request is received. But before commit c6550776394e the latch was
not used for anything else, so the code could just go to sleep after each loop
without rechecking the sleeping condition. That commit added a separate
ResetLatch in its code path[1], which can cause a checkpoint to go unnoticed
for potentially a long time. Fix by skipping sleep if any checkpoint flags
are set. Also add a test to verify this; authored by Kyotaro Horiguchi. [1]
CreateCheckPoint -> InvalidateObsoleteReplicationSlots ->
ConditionVariableTimeSleep Report and diagnosis by Kyotaro Horiguchi.
Co-authored-by: Kyotaro Horiguchi <horikyota(dot)ntt(at)gmail(dot)com> Co-authored-by:
Álvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> Discussion:

Tomáš Vondra pushed:

- Remove pg_xact entry from SLRU stats. The "pg_xact" entry was duplicate with
"clog" and was added by mistake. Reported-by: Fujii Masao Discussion:

- Simplify cost_incremental_sort a bit. Commit de0dc1a847 added code to
cost_incremental_sort to handle varno 0. Explicitly removing the RelabelType
is not really necessary, because the pull_varnos handles that just fine, which
simplifies the code a bit. Author: Richard Guo Discussion:
Discussion: https://postgr.es/m/20200411214639.GK2228%40telsasoft.com

- Remove pg_xact from pg_stat_reset_slru docs. This should have been included in
2e08d314ed. Reported-by: Fujii Masao Discussion:

- Remove superfluous memset from pgstat_recv_resetslrucounter. The extra memset
meant pg_stat_reset_slru() always reset all the entries even when reset of a
single entry was requested, but the timestamp was left uninitialized.
Reported-by: Atsushi Torikoshi Discussion:

Stephen Frost pushed:

- Fix GSS client to non-GSS server connection. If the client is compiled with
GSSAPI support and tries to start up GSS with the server, but the server is
not compiled with GSSAPI support, we would mistakenly end up falling through
to call ProcessStartupPacket with secure_done = true, but the client might
then try to perform SSL, which the backend wouldn't understand and we'd end up
failing the connection with: FATAL: unsupported frontend protocol 1234.5679:
server supports 2.0 to 3.0 Fix by arranging to track ssl_done independently
from gss_done, instead of trying to use the same boolean for both. Author:
Andrew Gierth Discussion:
https://postgr.es/m/87h82kzwqn.fsf@news-spur.riddles.org.uk Backpatch: 12-,
where GSSAPI encryption was added.

== Pending Patches ==

Robert Haas sent in a patch to improve server code to read files as part of a
base backup.

Zeng Wenjing sent in four more revisions of a patch to implement global
temporary tables.

Amit Kapila and Juan José Santamaría Flecha traded patches to fix a problem with
compilation on Microsoft Visual Studio 2015/2017/2019.

Dilip Kumar and Mahendra Singh traded patches to fix an infelicity between
logical_work_mem and logical streaming of large in-progress transactions.

Muhammad Usama sent in another revision of a patch to fix a bug that manifested
as problems with transactions involving multiple foreign PostgreSQL servers.

Jonah Harris sent in a patch to implement WITH ITERATIVE, a construct like WITH
RECURSIVE which overwrites (or discards) the previous working set rather than
keeping it as WITH RECURSIVE does.

James Coleman sent in two more revisions of a patch to make ScalarArrayOpExpr
for OR'd constant arrays more efficient.

Masahiro Ikeda and Fujii Masao traded patches to ensure that wait events about
the timeline history file are reported.

Fujii Masao sent in another revision of a patch to make it possible to add and
subtract NUMERICs from LSNs.

Antonin Houska sent in a patch to remove a misplaced check for
PVC_RECURSE_WINDOWFUNCS from make_partial_grouping_target in planner.c.

Robert Haas sent in a patch to stop exporting basebackup.c's sendTablespace(),
and merge two calls to sendDir() that are exactly the same except for the fifth
argument in perform_base_backup().

Mark Dilger sent in another revision of a patch to add verify_heapam to the
amcheck contrib module.

Melanie Plageman and David Kimura traded patches to implement adaptive

Andy Fan sent in a patch to remove the other_rels_list from
make_rels_by_clause_joins for better semantic clearly.

Kyotaro HORIGUCHI sent in another revision of a patch to implement the stats
collector based on shared memory.

Krasiyan Andreev sent in another revision of a patch to add RESPECT/IGNORE NULLS
and FROM FIRST/LAST options.

Julien Rouhaud sent in another revision of a patch to fix the WAL usage

Victor Wagner sent in a patch to ensure that a path is properly quoted in

James Coleman sent in a patch to summarize the trade-offs between simplehash and
dynahash, improve the simplehash usage notes, and show simplehash method

Victor Wagner sent in a patch to fix the way PL/Perl handles locales on Windows
using a more modern idiom.

Jonah Harris sent in a PoC patch to implement direct- and asynchronous I/O.

Peter Eisentraut sent in a patch to unify the drop-by-OID functions.

Cary Huang sent in another revision of a patch to document key management

Noah Misch sent in another revision of a patch to ensure that PGRES_COPY_OUT
ends cleanly in pg_recvlogical.

Justin Pryzby sent in another revision of a patch to ensure that the pg_ls_*
functions show directories and shared filesets.

Browse pgsql-announce by date

  From Date Subject
Next Message Christoph Berg 2020-05-04 09:35:55 arm64 on apt.postgresql.org
Previous Message Grigory Smolkin 2020-05-03 20:50:26 pg_probackup 2.3.0 released