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

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

PostgreSQL 13 Beta 2 released. Test!
https://www.postgresql.org/about/news/2047/

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

== PostgreSQL Jobs for June ==

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

== 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/
https://2020.foss4g.org/

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 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
https://2020.pgconf.eu/

PG Day Russia will take place in Saint Petersburg on July 9, 2021.
https://pgday.ru/en/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:

- Add --no-index-cleanup and --no-truncate to vacuumdb. Both INDEX_CLEANUP and
TRUNCATE have been available since v12, and are enabled by default except if
respectively vacuum_index_cleanup and vacuum_truncate are disabled for a given
relation. This change adds support for disabling these options from vacuumdb.
Author: Nathan Bossart Reviewed-by: Michael Paquier, Masahiko Sawada
Discussion:
https://postgr.es/m/6F7F17EF-B1F2-4681-8D03-BA96365717C0@amazon.com
https://git.postgresql.org/pg/commitdiff/9550ea3027aa4f290c998afd8836a927df40b09d

- Fix inconsistent markups in catalogs.sgml. Some fields related to pg_opclass
and pg_opfamily were using incorrect markups, listing them as structname
instead of structfield. Author: Fabien Coelho Discussion:
https://postgr.es/m/alpine.DEB.2.22.394.2006210903560.859381@pseudo
https://git.postgresql.org/pg/commitdiff/fe186b4c200b76a5c0f03379fe8645ed1c70a844

- Fix comment in heap.c. The description of InsertPgAttributeTuple() does not
match its handling of pg_attribute contents with NULL values for a long time,
with 911e702 making things more inconsistent. This adjusts the description to
match the reality. Author: Daniel Gustafsson Discussion:
https://postgr.es/m/4E4E4B33-9FDF-4D21-B77A-642D027AEAD9@yesql.se
https://git.postgresql.org/pg/commitdiff/a3554b2d718520cbd16c13ff5c9f2e8257846170

Tom Lane pushed:

- Undo double-quoting of index names in non-text EXPLAIN output formats.
explain_get_index_name() applied quote_identifier() to the index name. This is
fine for text output, but the non-text output formats all have their own
quoting conventions and would much rather start from the actual index name.
For example in JSON you'd get something like "Index Name": "\"My
Index\"", which is surely not desirable, especially when the same does not
happen for table names. Hence, move the responsibility for applying quoting
out to the callers, where it can go into already-existing special code paths
for text format. This changes the API spec for users of
explain_get_index_name_hook: before, they were supposed to apply
quote_identifier() if necessary, now they should not. Research suggests that
the only publicly available user of the hook is hypopg, and it actually forgot
to apply quoting anyway, so it's fine. (In any case, there's no behavioral
change for the output of a hook as seen in non-text EXPLAIN formats, so this
won't break any case that programs should be relying on.) Digging in the
commit logs, it appears that quoting was included in explain_get_index_name's
duties when commit 604ffd280 invented it; and that was fine at the time
because we only had text output format. This should have been rethought when
non-text formats were invented, but it wasn't. This is a fairly clear bug for
users of non-text EXPLAIN formats, so back-patch to all supported branches.
Per bug #16502 from Maciek Sakrejda. Patch by me (based on investigation by
Euler Taveira); thanks to Julien Rouhaud for review. Discussion:
https://postgr.es/m/16502-57bd1c9f913ed1d1@postgresql.org
https://git.postgresql.org/pg/commitdiff/63d2ac23b018c2b173f42d274ae46b7b0c3263df

- Fix compiler warning induced by commit d8b15eeb8. I forgot that INT64_FORMAT
can't be used with sscanf on Windows. Use the same trick of sscanf'ing into a
temp variable as we do in some other places in zic.c. The upstream IANA code
avoids the portability problem by relying on <inttypes.h>'s SCNdFAST64 macro.
Once we're requiring C99 in all branches, we should do likewise and drop this
set of diffs from upstream. For now, though, a hack seems fine, since we do
not actually care about leapseconds anyway. Discussion:
https://postgr.es/m/4e5d1a5b-143e-e70e-a99d-a3b01c1ae7c3@2ndquadrant.com
https://git.postgresql.org/pg/commitdiff/235c0f6eed2d9f5650f9b6ee0c51601792eff8e4

- Doc: correct nitpicky mistakes in array_position/array_positions examples.
Daniel Gustafsson and Erik Rijkers, per report from nick(at)cleaton Discussion:
https://postgr.es/m/159275646273.679.16940709892308114570@wrigleys.postgresql.org
https://git.postgresql.org/pg/commitdiff/463b808e757928f053490dd397af77a80b4e7baa

- Doc: explain that "timestamp - timestamp" applies justify_hours(). Back-patch
to v13; before that, there's not really space for this kind of detail.
Discussion: https://postgr.es/m/c1696f68-fa8d-7759-6a9c-eb293ab1bbc9@gmx.net
https://git.postgresql.org/pg/commitdiff/eca08f58d05f45c4cae02bca5e1556ba58732fc4

- Change libpq's default ssl_min_protocol_version to TLSv1.2. When we initially
created this parameter, in commit ff8ca5fad, we left the default as "allow any
protocol version" on grounds of backwards compatibility. However, that's
inconsistent with the backend's default since b1abfec82; protocol versions
prior to 1.2 are not considered very secure; and OpenSSL has had TLSv1.2
support since 2012, so the number of PG servers that need a lesser minimum is
probably quite small. On top of those things, it emerges that some popular
distros (including Debian and RHEL) set MinProtocol=TLSv1.2 in openssl.cnf.
Thus, far from having "allow any protocol version" behavior in practice, what
we actually have as things stand is a platform-dependent lower limit. So,
change our minds and set the min version to TLSv1.2. Anybody wanting to
connect with a new libpq to a pre-2012 server can either set
ssl_min_protocol_version=TLSv1 or accept the fallback to non-SSL. Back-patch
to v13 where the aforementioned patches appeared. Patch by me, reviewed by
Daniel Gustafsson Discussion:
https://postgr.es/m/a9408304-4381-a5af-d259-e55d349ae4ce@2ndquadrant.com
https://git.postgresql.org/pg/commitdiff/6e682f61a5bdb08164a805419144318db6b7229f

- Add hints about protocol-version-related SSL connection failures. OpenSSL's
native reports about problems related to protocol version restrictions are
pretty opaque and inconsistent. When we get an SSL error that is plausibly
due to this, emit a hint message that includes the range of SSL protocol
versions we (think we) are allowing. This should at least get the user
thinking in the right direction to resolve the problem, even if the hint isn't
totally accurate, which it might not be for assorted reasons. Back-patch to
v13 where we increased the default minimum protocol version, thereby
increasing the risk of this class of failure. Patch by me, reviewed by Daniel
Gustafsson Discussion:
https://postgr.es/m/a9408304-4381-a5af-d259-e55d349ae4ce@2ndquadrant.com
https://git.postgresql.org/pg/commitdiff/b63dd3d88f479947ef7fb7cbf5db27de66ae0654

- Fix list of SSL error codes for older OpenSSL versions. Apparently 1.0.1 lacks
SSL_R_VERSION_TOO_HIGH and SSL_R_VERSION_TOO_LOW. Per buildfarm.
https://git.postgresql.org/pg/commitdiff/e1cc25f59a8a90d821aaf894e1691575ed94454e

Jeff Davis pushed:

- Doc fixup for hashagg_avoid_disk_plan GUC. Reported-by: Justin Pryzby
Discussion: https://postgr.es/m/20200620220402.GZ17995@telsasoft.com
Backport-through: 13
https://git.postgresql.org/pg/commitdiff/7ce461560159948ba0c802c767e42c5f5ae08b4a

Álvaro Herrera pushed:

- Add parens to ConvertToXSegs macro. The current definition is dangerous. No
bugs exist in our code at present, but backpatch to 11 nonetheless where it
was introduced. Author: Álvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org>
https://git.postgresql.org/pg/commitdiff/368d7f3297e7e1304da03904d2e1310d79fa82a9

- Save slot's restart_lsn when invalidated due to size. We put it aside as
invalidated_at, which let us show "lost" in pg_replication slot. Prior to
this change, the state value was reported as NULL. Backpatch to 13. Author:
Kyotaro Horiguchi <horikyota(dot)ntt(at)gmail(dot)com> Reviewed-by: Álvaro Herrera
<alvherre(at)alvh(dot)no-ip(dot)org> Discussion:
https://postgr.es/m/20200617.101707.1735599255100002667.horikyota.ntt@gmail.com
Discussion:
https://postgr.es/m/20200407.120905.1507671100168805403.horikyota.ntt@gmail.com
https://git.postgresql.org/pg/commitdiff/0188bb82531f1b0ae3648fb81a4bd4a4f6242127

- Adjust max_slot_wal_keep_size behavior per review. In pg_replication_slot,
change output from normal/reserved/lost to reserved/extended/unreserved/ lost,
which better expresses the possible states particularly near the time where
segments are no longer safe but checkpoint has not run yet. Under the new
definition, reserved means the slot is consuming WAL that's still under the
normal WAL size constraints; extended means it's consuming WAL that's being
protected by wal_keep_segments or the slot itself, whose size is below
max_slot_wal_keep_size; unreserved means the WAL is no longer safe, but
checkpoint has not yet removed those files. Such as slot is in imminent
danger, but can still continue for a little while and may catch up to the
reserved WAL space. Also, there were some bugs in the calculations used to
report the status; fixed those. Backpatch to 13. Reported-by: Fujii Masao
<masao(dot)fujii(at)oss(dot)nttdata(dot)com> Author: Kyotaro Horiguchi
<horikyota(dot)ntt(at)gmail(dot)com> Reviewed-by: Fujii Masao
<masao(dot)fujii(at)oss(dot)nttdata(dot)com> Reviewed-by: Álvaro Herrera
<alvherre(at)alvh(dot)no-ip(dot)org> Discussion:
https://postgr.es/m/20200616.120236.1809496990963386593.horikyota.ntt@gmail.com
https://git.postgresql.org/pg/commitdiff/b8fd4e02c6d01183bf6def5897ad6cf7766bfff4

- Persist slot invalidation correctly. We failed to save slot to disk after
invalidating it, so the state was lost in case of server restart or crash.
Fix by marking it dirty and flushing. Also, if the slot is known invalidated
we don't need to reason about the LSN at all -- it's known invalidated. Only
test the LSN if the slot is known not invalidated. Author: Fujii Masao
<masao(dot)fujii(at)oss(dot)nttdata(dot)com> Author: Kyotaro Horiguchi
<horikyota(dot)ntt(at)gmail(dot)com> Reviewed-by: Álvaro Herrera
<alvherre(at)alvh(dot)no-ip(dot)org> Discussion:
https://postgr.es/m/17a69cfe-f1c1-a416-ee25-ae15427c69eb@oss.nttdata.com
https://git.postgresql.org/pg/commitdiff/4ae08cd5fd19d566538005c15e7bf992ebae4c72

Fujii Masao pushed:

- Remove erroneous assertion from pg_copy_logical_replication_slot(). If
restart_lsn of logical replication slot gets behind more than
max_slot_wal_keep_size from the current LSN, the logical replication slot
would be invalidated and its restart_lsn is reset to an invalid LSN. If this
logical replication slot with an invalid restart_lsn was specified as the
source slot in pg_copy_logical_replication_slot(), the function caused the
assertion failure unexpectedly. This assertion was added because restart_lsn
should not be invalid before. But in v13, it can be invalid thanks to
max_slot_wal_keep_size. So since this assertion is no longer useful, this
commit removes it. This commit also changes the errcode in the error message
that pg_copy_logical_replication_slot() emits when the slot with an invalid
restart_lsn is specified, to more appropriate one. Back-patch to v13 where
max_slot_wal_keep_size was added and the assertion was no longer valid.
Author: Fujii Masao Reviewed-by: Alvaro Herrera, Kyotaro Horiguchi Discussion:
https://postgr.es/m/f91de4fb-a7ab-b90e-8132-74796e049d51@oss.nttdata.com
https://git.postgresql.org/pg/commitdiff/a82ba066ea217e7fe4da3c20ced01e7ca976a351

Peter Geoghegan pushed:

- Fix misuse of table_index_fetch_tuple_check(). Commit 0d861bbb, which added
deduplication to nbtree, had _bt_check_unique() pass a TID to
table_index_fetch_tuple_check() that isn't safe to mutate.
table_index_fetch_tuple_check()'s tid argument is modified when the TID in
question is not the latest visible tuple in a hot chain, though this wasn't
documented. To fix, go back to using a local copy of the TID in
_bt_check_unique(), and update comments above table_index_fetch_tuple_check().
Backpatch: 13-, where B-Tree deduplication was introduced.
https://git.postgresql.org/pg/commitdiff/10f1ab2cb8bea3c6741a78f6dc19a5c91c0a34e1

Bruce Momjian pushed:

- docs: clarify that CREATE DATABASE does not copy db permissions. That is,
those database permissions set by GRANT. Diagnosed-by: Joseph Nahmias
Discussion: https://postgr.es/m/20200614072613.GA21852@nahmias.net
Backpatch-through: 9.5
https://git.postgresql.org/pg/commitdiff/d352de8d8eb7102e51e6adf0a965a9eae09e3f39

- doc: mention trigger helper functions in CREATE TRIGGER docs. Reported-by:
petermpallesen(at)gmail(dot)com Discussion:
https://postgr.es/m/159195294959.673.5752624528747900508@wrigleys.postgresql.org
Backpatch-through: 9.5
https://git.postgresql.org/pg/commitdiff/81d46ea12cef2391a4cae63c1f3951401e3dd883

Amit Kapila pushed:

- Remove duplicate check added by commit b2a5545bd6. As this doesn't cause any
harm so we decided to this clean up in HEAD only. Author: Ádám Balogh
Discussion:
https://postgr.es/m/VI1PR0702MB36631BD67559461AFDE1FEEE81920@VI1PR0702MB3663.eurprd07.prod.outlook.com
https://git.postgresql.org/pg/commitdiff/e7b476c657ebe4c0a47fa14b8f1c7ec767067585

Noah Misch pushed:

- Fix documentation of "must be vacuumed within" warning. Warnings start 10M
transactions before xidStopLimit, which is 11M transactions before wraparound.
The sample WARNING output showed a value greater than 11M, and its HINT
message predated commit 25ec228ef760eb91c094cc3b6dea7257cc22ffb5. Hence, the
sample was impossible. Back-patch to 9.5 (all supported versions).
https://git.postgresql.org/pg/commitdiff/96879a0efb65b9cde0a688201516633aa79fd5b0

== Pending Patches ==

Odin Ugedal sent in another revision of a patch to add support for choosing huge
page size.

Tom Lane sent in a patch to make better AlternativeSubplan cost estimates.

Justin Pryzby sent in another revision of a patch to make pg_ls_*() show
directories and shared filesets.

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

Justin Pryzby sent in a patch to make Testlib.pm write to stdout instead of
--disable-seeking.

Daniel Gustafsson sent in a patch to update the InsertPgAttributeTuple comment
to match its new signature.

Movead Li sent in a patch to fix an issue that manifested as pg_resetwal
--next-transaction-id may cause database failed to restart.

Fujii Masao sent in two revisions of a patch to fix an assertion failure in
pg_copy_logical_replication_slot().

Michaël Paquier sent in another revision of a patch to add a
pg_wal_oldest_lsn() function and remove min_safe_lsn from the
pg_replication_slots view.

Daniel Gustafsson sent in two more revisions of a patch to make it possible to
turn checksums on online.

Amul Sul sent in another revision of a patch to implement ALTER SYSTEM READ
{WRITE | ONLY}.

Bharath Rupireddy sent in a patch to fix an issue where postgres_fdw connection
caching would cause remote sessions linger till the local session exit.

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

Mark Dilger sent in another revision of a patch to add verify_heapam in a new
contrib extension, pg_amcheck.

Pavel Stěhule sent in another revision of a patch to implement a
unicode_unescape() function.

Ranier Vilela sent in a patch to fix the table parallel scan estimate size.

Masahiko Sawada sent in another revision of a patch to implement an internal key
management system.

Thomas Munro sent in a patch to move syncscan.c to src/backend/access/table.

Peter Eisentraut sent in a patch to pg_dump which reorganizes dumpFunc() and
dumpAgg() to have less duplication in the queries they run.

Masahiko Sawada sent in another revision of a patch to enable transactions
involving multiple foreign servers.

Vigneshwaran C and Bharath Rupireddy traded patches to parallelize COPY.

Michaël Paquier sent in another revision of a patch to skip symlink TAP tests on
Win32.

Álvaro Herrera and Kyotaro HORIGUCHI traded patches to review
GetWALAvailability().

Masahiko Sawada sent in another revision of a patch to fix a possible xid
wraparound caused by setting INDEX_CLEANUP to false.

Takashi Menjo sent in another revision of a patch to implement non-volatile WAL
buffers.

Peter Eisentraut sent in another revision of a patch to pause recovery for
insufficient parameter settings.

Robert Haas sent in a patch to add flexible options for BASE_BACKUP and
CREATE_REPLICATION_SLOT.

Peter Eisentraut sent in a patch to allow CURRENT_ROLE in GRANTED BY.

Bharath Rupireddy sent in a patch to make COPY's format commands
case-insensitive.

Kyotaro HORIGUCHI sent in a patch to avoid archiving or sending immature
records.

Melanie Plageman sent in a patch to move extracting columns for hashagg to the
planner.

Daniel Gustafsson sent in another revision of a patch to support libnss as TLS
backend, and make pg_stat_ssl reporting backend agnostic to support this and
similar work.

David Rowley sent in a patch to get EXPLAIN to drop on-disk sorts from non-text
output when it doesn't do one.

Michaël Paquier sent in a patch to fill in some missing ifndef FRONTEND at the
top of logging.c and file_utils.c.

David Rowley sent in a patch to keep elog(ERROR) and ereport(ERROR) calls in the
cold path.

Jehan-Guillaume de Rorthais and Kyotaro HORIGUCHI traded patches to implement
DEMOTE.

Daniel Gustafsson and Michaël Paquier traded patches to use heap_multi_insert()
for pg_attribute/depend insertions.

Ádám Balogh sent in a patch to remove a redundant condition check.

Melanie Plageman sent in another revision of a patch to implement adaptive
hashjoin.

Amit Langote sent in a patch to revise how some FDW executor APIs obtain
ResultRelInfo, avoid setting rootResultRelIndex unnecessarily, and delay
initializing UPDATE/DELETE ResultRelInfos.

Fabrízio de Royes Mello sent in a patch to fix a bug in pg_dump where extension
objects were not schema-qualified, causing a crash.

Ranier Vilela sent in a patch to fix a possible null dereference in
src/backend/tcop/pquery.c.

Bharath Rupireddy and Rushabh Lathia traded patches to remove an extra palloc of
raw_buf for binary format in COPY FROM.

Quan Zongliang and Tom Lane traded patches to fix an issue where the "%c" format
was being used on data that might not be ASCII.

Vigneshwaran C sent in a patch to add tab completion for the missing options in
copy statement to psql.

Peter Eisentraut sent in another revision of a patch to add the current
substring regular expression syntax.

Felix Lechner sent in a PoC patch to support WolfSSL for TLS.

Joe Conway sent in two revisions of a patch to fix an issue that manifested as
pg_read_file() with virtual files returns empty string.

Noah Misch sent in a patch to raise xidWrapLimit-xidStopLimit to 3M and
xidWrapLimit-xidWarnLimit to 40M. Likewise for mxact counterparts.

Daniel Gustafsson sent in a patch to generalize TLS checking in pgstat beyond
the OpenSSL-specific implementation details.

Tomáš Vondra sent in a PoC patch to batch writes to FDWs as a way to speed up
from the current situation, which does a synchronous write at each row.