== PostgreSQL Weekly News - August 30, 2020 ==

== PostgreSQL Weekly News - August 30, 2020 ==

== PostgreSQL Product News ==

sqlite_fdw 1.2.1 released.
https://github.com/pgspider/sqlite_fdw

== PostgreSQL Jobs for August ==

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

== PostgreSQL Local ==

pgDay Israel 2020 will take place on September 10, 2020 in Tel Aviv.
http://pgday.org.il/

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

Amit Kapila pushed:

- Improve the vacuum error context phase information. We were displaying the
wrong phase information for 'info' message in the index clean up phase because
we were switching to the previous phase a bit early. We were also not
displaying context information for heap phase unless the block number is valid
which is fine for error cases but for messages at 'info' or lower error level
it appears to be inconsistent with index phase information. Reported-by:
Sawada Masahiko Author: Sawada Masahiko Reviewed-by: Amit Kapila
Backpatch-through: 13, where it was introduced Discussion:
https://postgr.es/m/CA+fd4k4HcbhPnCs7paRTw1K-AHin8y4xKomB9Ru0ATw0UeTy2w@mail.gmail.com
https://git.postgresql.org/pg/commitdiff/a3c66de6c5e1ee9dd41ce1454496568622fb7712

- Extend the BufFile interface. Allow BufFile to support temporary files that
can be used by the single backend when the corresponding files need to be
survived across the transaction and need to be opened and closed multiple
times. Such files need to be created as a member of a SharedFileSet.
Additionally, this commit implements the interface for BufFileTruncate to
allow files to be truncated up to a particular offset and extends the
BufFileSeek API to support the SEEK_END case. This also adds an option to
provide a mode while opening the shared BufFiles instead of always opening in
read-only mode. These enhancements in BufFile interface are required for the
upcoming patch to allow the replication apply worker, to handle streamed
in-progress transactions. Author: Dilip Kumar, Amit Kapila Reviewed-by: Amit
Kapila Tested-by: Neha Sharma Discussion:
https://postgr.es/m/688b0b7f-2f6c-d827-c27b-216a8e3ea700@2ndquadrant.com
https://git.postgresql.org/pg/commitdiff/808e13b282efa7e7ac7b78e886aca5684f4bccd3

- Add additional information in the vacuum error context. The additional
information added will be an offset number for heap operations. This
information will help us in finding the exact tuple due to which the error has
occurred. Author: Mahendra Singh Thalor and Amit Kapila Reviewed-by: Sawada
Masahiko, Justin Pryzby and Amit Kapila Discussion:
https://postgr.es/m/CAKYtNApK488TDF4bMbw+1QH8HJf9cxdNDXquhU50TK5iv_FtCQ@mail.gmail.com
https://git.postgresql.org/pg/commitdiff/7e453634bb62f06a048f5562ba59d52aa1f28d12

Michaël Paquier pushed:

- doc: Fix some markups for support functions of index AMs. All the
documentation of index AMs has been using <replaceable> for local_relopts.
This is a structure, so <structname> is a much better choice. Alexander has
found the inconsistency for btree, while I have spotted the rest when applying
the concept of consistency to the docs. Author: Alexander Lakhin, Michael
Paquier Reviewed-by: Tom Lane Discussion:
https://postgr.es/m/20200822133022.GC24782@paquier.xyz
https://git.postgresql.org/pg/commitdiff/77c1537f512e6ac2513f8695c795dc94cbf207ee

- Add regression tests for REPLICA IDENTITY with dropped indexes. REPLICA
IDENTITY USING INDEX behaves the same way as NOTHING if the associated index
is dropped, even if there is a primary key that could be used as a fallback
for the changes generated. There have never been any tests to cover such
scenarios, so this commit closes the gap. Author: Michael Paquier
Reviewed-by: Masahiko Sawada, Rahila Syed, Euler Taveira Discussion:
https://postgr.es/m/20200522035028.GO2355@paquier.xyz
https://git.postgresql.org/pg/commitdiff/fe7fd4e9613f58262d30782a34b01cc0c4cbbeb5

- Fix comment in procarray.c. The description of GlobalVisDataRels was missing,
GlobalVisCatalogRels being mentioned instead. Author: Jim Nasby Discussion:
https://postgr.es/m/8e06c883-2858-1fd4-07c5-560c28b08dcd@amazon.com
https://git.postgresql.org/pg/commitdiff/77c7267c37f7fa8e5e48abda4798afdbecb2b95a

- doc: Rework tables for built-in operator classes of index AMs. The tables
listing all the operator classes available for BRIN, GIN, GiST and SP-GiST had
a confusing format where the same operator could be listed multiple times, for
different data types. This improves the shape of these tables by adding the
types associated to each operator, for their associated operator class. Each
table included previously the data type that could be used for an operator
class in an extra column. This is removed to reduce the width of the tables
as this is now described within each operator. This also makes the tables fit
better in the PDF documentation. Reported-by: osdba Author: Michael Paquier
Reviewed-by: Álvaro Herrera, Tom Lane, Bruce Momjian Discussion:
https://postgr.es/m/38d55061.9604.173b32c60ec.Coremail.mailtch@163.com
https://git.postgresql.org/pg/commitdiff/7a1cd5260aa20bc13aec8960a57904b5623d1830

- Reset indisreplident for an invalid index in DROP INDEX CONCURRENTLY. A
failure when dropping concurrently an index used in a replica identity could
leave in pg_index an index marked as !indisvalid and indisreplident.
Reindexing this index would switch back indisvalid to true, and if the replica
identity of the parent relation was switched to use a different index, it
would be possible to finish with more than one index marked as indisreplident.
If that were to happen, this could mess up with the relation cache as an
incorrect index could be used for the replica identity. Indexes marked as
invalid are discarded as candidates for the replica identity, as of
RelationGetIndexList(), so similarly to what is done with indisclustered,
resetting indisreplident when the index is marked as invalid keeps things
consistent. REINDEX CONCURRENTLY's swapping already resets the flag for the
old index, while the new index inherits the value of the old index
to-be-dropped, so only DROP INDEX was an issue. Even if this is a bug, the
sequence able to reproduce a problem requires a failure while running DROP
INDEX CONCURRENTLY, something unlikely going to happen in the field, so no
backpatch is done. Author: Michael Paquier Reviewed-by: Dmitry Dolgov
Discussion: https://postgr.es/m/20200827025721.GN2017@paquier.xyz
https://git.postgresql.org/pg/commitdiff/9511fb37ac78c77736e5483118265f7e83cd9f3c

Peter Eisentraut pushed:

- doc: Fix up title case. This fixes some instances that were missed in earlier
processings and that now look a bit strange because they are inconsistent with
nearby titles.
https://git.postgresql.org/pg/commitdiff/7f055fba3fa99d807837a229967fd6c5dd720530

- passwordcheck: Log cracklib diagnostics. When calling cracklib to check the
password, the diagnostic from cracklib was thrown away. This would hide
essential information such as no dictionary being installed. Change this to
show the cracklib error message using errdetail_log(). Reviewed-by: Daniel
Gustafsson <daniel(at)yesql(dot)se> Reviewed-by: Laurenz Albe
<laurenz(dot)albe(at)cybertec(dot)at> Discussion:
https://www.postgresql.org/message-id/flat/f7266133-618a-0adc-52ef-f43c78806b0e%402ndquadrant.com
https://git.postgresql.org/pg/commitdiff/924123a87f40c12063a2bb2500805447cddc02a3

- doc: Update cracklib URL. Author: Daniel Gustafsson <daniel(at)yesql(dot)se>
Reviewed-by: Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> Discussion:
https://www.postgresql.org/message-id/flat/f7266133-618a-0adc-52ef-f43c78806b0e%402ndquadrant.com
https://git.postgresql.org/pg/commitdiff/42aaed60c83ff51aa736f50ad96e43653fc539da

Bruce Momjian pushed:

- docs: client certificates are always sent to the server. They are not
"requested" by the server. Reported-by: Kyotaro Horiguchi Discussion:
https://postgr.es/m/20200825.155320.986648039251743210.horikyota.ntt@gmail.com
Backpatch-through: 9.5
https://git.postgresql.org/pg/commitdiff/ff60394a8c9a7af8b32de420ccb54a20a0f019c1

David Rowley pushed:

- Fixup some misusages of bms_num_members(). It's a bit inefficient to test if a
Bitmapset is empty by counting all the members and seeing if that number is
zero. It's much better just to use bms_is_empty(). Likewise for checking if
there are at least two members, just use bms_membership(), which does not need
to do anything more after finding two members. Discussion:
https://postgr.es/m/CAApHDvpvwm_QjbDOb5xga%2BKmX9XkN9xQavNGm3SvDbVnCYOerQ%40mail.gmail.com
Reviewed-by: Tomas Vondra
https://git.postgresql.org/pg/commitdiff/c34605daed563fcade07a9f45bcf440459599c00

Fujii Masao pushed:

- Prevent non-superusers from reading pg_backend_memory_contexts, by default.
pg_backend_memory_contexts view contains some internal information of memory
contexts. Since exposing them to any users by default may cause security
issue, this commit allows only superusers to read this view, by default, like
we do for pg_shmem_allocations view. Bump catalog version. Author: Atsushi
Torikoshi Reviewed-by: Michael Paquier, Fujii Masao Discussion:
https://postgr.es/m/1414992.1597849297@sss.pgh.pa.us
https://git.postgresql.org/pg/commitdiff/29dd6d8bc631eebc3e50493c115f7a215f03bd0a

- Move codes for pg_backend_memory_contexts from mmgr/mcxt.c to adt/mcxtfuncs.c.
Previously the codes for pg_backend_memory_contexts were in
src/backend/utils/mmgr/mcxt.c. This commit moves them to
src/backend/utils/adt/mcxtfuncs.c so that mcxt.c basically includes only the
low-level interface for memory contexts. Author: Atsushi Torikoshi
Reviewed-by: Michael Paquier, Fujii Masao Discussion:
https://postgr.es/m/20200819135545.GC19121@paquier.xyz
https://git.postgresql.org/pg/commitdiff/50db5964ee333bc148e0c8844ffafaf585c719c6

- Add regression test for pg_backend_memory_contexts. Author: Atsushi Torikoshi
Reviewed-by: Michael Paquier, Fujii Masao Discussion:
https://postgr.es/m/20200819135545.GC19121@paquier.xyz
https://git.postgresql.org/pg/commitdiff/adc8fc6167aa3f68b951ddd60ea32a62b13f18d6

Tom Lane pushed:

- Suppress compiler warning in non-cassert builds. Oversight in 808e13b28,
reported by Bruce Momjian. Discussion:
https://postgr.es/m/20200826160251.GB21909@momjian.us
https://git.postgresql.org/pg/commitdiff/e942af7b8261cd8070d0eeaf518dbc1a664859fd

- Fix code for re-finding scan position in a multicolumn GIN index.
collectMatchBitmap() needs to re-find the index tuple it was previously
looking at, after transiently dropping lock on the index page it's on. The
tuple should still exist and be at its prior position or somewhere to the
right of that, since ginvacuum never removes tuples but concurrent insertions
could add one. However, there was a thinko in that logic, to the effect of
expecting any inserted tuples to have the same index "attnum" as what we'd
been scanning. Since there's no physical separation of tuples with different
attnums, it's not terribly hard to devise scenarios where this fails, leading
to transient "lost saved point in index" errors. (While I've duplicated this
with manual testing, it seems impossible to make a reproducible test case with
our available testing technology.) Fix by just continuing the scan when the
attnum doesn't match. While here, improve the error message used if we do
fail, so that it matches the wording used in btree for a similar case.
collectMatchBitmap()'s posting-tree code path was previously not exercised at
all by our regression tests. While I can't make a regression test that
exhibits the bug, I can at least improve the code coverage here, so do that.
The test case I made for this is an extension of one added by 4b754d6c1, so it
only works in HEAD and v13; didn't seem worth trying hard to back-patch it.
Per bug #16595 from Jesse Kinkead. This has been broken since multicolumn
capability was added to GIN (commit 27cb66fdf), so back-patch to all supported
branches. Discussion:
https://postgr.es/m/16595-633118be8eef9ce2@postgresql.org
https://git.postgresql.org/pg/commitdiff/10564ee02ca380f8d614eabc4e80c5d39ea4edad

- Redefine pg_class.reltuples to be -1 before the first VACUUM or ANALYZE.
Historically, we've considered the state with relpages and reltuples both zero
as indicating that we do not know the table's tuple density. This is
problematic because it's impossible to distinguish "never yet vacuumed" from
"vacuumed and seen to be empty". In particular, a user cannot use VACUUM or
ANALYZE to override the planner's normal heuristic that an empty table should
not be believed to be empty because it is probably about to get populated.
That heuristic is a good safety measure, so I don't care to abandon it, but
there should be a way to override it if the table is indeed intended to stay
empty. Hence, represent the initial state of ignorance by setting reltuples
to -1 (relpages is still set to zero), and apply the minimum-ten-pages
heuristic only when reltuples is still -1. If the table is empty, VACUUM or
ANALYZE (but not CREATE INDEX) will override that to reltuples = relpages = 0,
and then we'll plan on that basis. This requires a bunch of fiddly little
changes, but we can get rid of some ugly kluges that were formerly needed to
maintain the old definition. One notable point is that FDWs'
GetForeignRelSize methods will see baserel->tuples = -1 when no ANALYZE has
been done on the foreign table. That seems like a net improvement, since those
methods were formerly also in the dark about what baserel->tuples = 0 really
meant. Still, it is an API change. I bumped catversion because code
predating this change would get confused by seeing reltuples = -1.
Discussion:
https://postgr.es/m/F02298E0-6EF4-49A1-BCB6-C484794D9ACC@thebuild.com
https://git.postgresql.org/pg/commitdiff/3d351d916b20534f973eda760cde17d96545d4c4

== Pending Patches ==

Amit Langote sent in another revision of a patch to move the multi-insert
decision logic into executor and use the fact that it's done there to speed up
COPY FROM into tables with foreign partitions.

Masahiro Ikeda sent in two more revisions of a patch to add a pg_stat_walwrites
view and supporting infrastructure.

Bharath Rupireddy sent in a patch to avoid displaying unnecessary Recheck Cond
when there are no lossy pages in the bitmap.

Pavel Stěhule sent in two more revisions of a patch to implement
string_to_table().

Greg Nancarrow sent in a patch to fix GUC parse_int() to allow fractional input
only when a unit is accepted.

Takamichi Osumi sent in two more revisions of a patch to implement CREATE OR
REPLACE TRIGGER.

Pavel Stěhule sent in four revisions of a patch to make it possible to write
window functions in PL/pgsql.

Dilip Kumar and Amit Kapila traded patches to add support for streaming to
built-in logical replication.

Pavel Borisov sent in two more revisions of a patch to implement covering
indexes for the SP-GiST index AM.

Tsutomu Yamada sent in four revisions of a patch to show extended statistics via
psql commands.

Masahiko Sawada sent in a patch to avoid unnecessary ReplicationSlotControl
lwlock acquistion.

Bruce Momjian sent in a patch to correct the misapprehension that the file FDW
could use only absolute paths. It can also use relative paths.

Anastasia Lubennikova sent in another revision of a patch to automate the
creation of HASH and LIST partitions.

Heeikki Linnakangas sent in another revision of a patch to refactor the
pg_rewind code and make it work against a standby.

Ranier Vilela sent in a patch to fix a potential NULL dereference in
src/backend/commands/statscmds.c.

Ranier Vilela sent in a patch to fix an uninitialized scalar variable in
src/backend/access/heap/heapam_handler.c.

Daniel Gustafsson sent in a patch to move OpenSSL random under
USE_OPENSSL_RANDOM.

Peter Eisentraut sent in a patch to remove some unused parameters.

Bruce Momjian sent in a patch to clarify the fact that client_cert
authentication implies cert=verify-full.

Mark Dilger sent in another revision of a patch to implement a pg_amcheck
contrib module.

Peter Eisentraut sent in another revision of a patch to allow CURRENT_ROLE where
CURRENT_USER is accepted.

Anastasia Lubennikova sent in another revision of a patch to improve COPY
FREEZE.

Fujii Masao sent in two more revisions of a patch to exit quickly if the user
has not requested sync replication or there are no sync replication standby
names defined.

Ashutosh Sharma and Robert Haas traded patches to implement a new contrib
extension, pg_surgery, so named because it helps perform surgery on a damaged
relation.

Mark Dilger, John Naylor, and Robert Haas traded patches to remove postfix
operators, of which factorial was the only known example, clearing the way for
future improvements.

Masahiko Sawada sent in another revision of a patch to fix a bug in pg_dump by
skipping a column if it is both inherited and generated.

Grigory Smolkin sent in a patch to fix an issue where when running switchover
from replica to origin and back to replica, the new history file is streamed to
replica, but not archived, which breaks PITR. Ensure that it's archived in this
case.

Georgios Kokolatos sent in a patch to make dbsize more consistent.

Jeff Janes sent in a patch to ensure that autovacuum is checking whether another
process is also autovacuum when deciding whether to kick in.

Surafel Temesgen sent in a patch to evaluate expression at planning time for two
more cases.

Peter Eisentraut sent in a patch to support OUT parameters for procedures.

Mark Dilger sent in three revisions of a patch to put out a deprecation notice
for postfix operators.

Peter Eisentraut sent in another revision of a patch to support writing CREATE
FUNCTION and CREATE PROCEDURE statements for language SQL with a function body
that conforms to the SQL standard and is portable to other implementations.

Ranier Vilela sent in two revisions of a patch to fix an explicit null
dereferenced in src/backend/access/heap/heaptoast.c.

David Rowley sent in another revision of a patch to fix an infelicity between
hybrid hash/nested loop joins and caching results from subplans.

Stephen Frost sent in a patch to implement a new role: pg_read_all_data.

Anastasia Lubennikova sent in another revision of a patch to use shared lock in
GetMultiXactIdMembers for offsets and members, make the MultiXact local cache
size configurable, and add a condition variable to wait for next MultXact offset
in edge case.

Dilip Kumar sent in another revision of a patch to provide a GUC to allow vacuum
to continue on corrupted tuple.

Andrey V. Lepikhov sent in another revision of a patch to make it possible to
pull statistics for a foreign table from remote server.

Thomas Munro sent in a patch to run checkpointer and bgworker in crash recovery.

Noah Misch sent in another revision of a patch to fix a bug that manifested as a
spurious "apparent wraparound" via SimpleLruTruncate() rounding.

Vigneshwaran C sent in another revision of a patch to make COPY FROM more
efficient by passing the actual space available in the buffer in the case of
COPY ... FROM STDIN, which reduces the frequency of calls to CopyGetData, and
removing some operations on the header line, which is already getting thrown
away.

Masahiko Sawada sent in a patch to make the boundary value check in
lazy_tid_reaped() more efficient.

Andrey Borodin sent in another revision of a patch to add sort support for point
gist_point_sortsupport, and use same to implement GiST build using sort support.

David Carlier sent in a patch to ensure that on NetBSD, the compiler knows that
it provides the same guarantee as explicit_bzero to avoid a compiler
optimisation.