== PostgreSQL Weekly News - October 13, 2019 ==

== PostgreSQL Weekly News - October 13, 2019 ==

== PostgreSQL Product News ==

pgcat, a logical replication system for PostgreSQL, released.
https://github.com/kingluo/pgcat

PostGIS 3.0.0rc2, the industry standard geographic information system package
for PostgreSQL, released.
https://postgis.net/2019/10/13/postgis-3.0.0rc2/

PEV2, a graphical visualization tool to understand PostgreSQL execution
plans, released.
https://dali.bo/github_pev2

oracle_fdw 2.2.0 released.
https://laurenz.github.io/oracle_fdw

== PostgreSQL Jobs for October ==

http://archives.postgresql.org/pgsql-jobs/2019-10/

== PostgreSQL Local ==

PostgreSQL Conference Europe 2019 will be held on October 15-18, 2019 in Milan,
Italy.
https://2019.pgconf.eu/

2Q PGConf 2019 will be held December 4 & 5 in Chicago.
https://www.2qpgconf.com/

PGDay Down Under 2019 will be held on November 15, 2019 in Sydney,
Australia.
https://pgdu.org/

pgDay Paris 2020 will be held in Paris, France on March 26, 2020
at Espace Saint-Martin.
http://2020.pgday.paris/

Nordic PGDay 2020 will be held in Helsinki, Finland at the Hilton Helsinki
Strand Hotel on March 24, 2020. The CfP is open through December 31, 2019 at
https://2020.nordicpgday.org/cfp/

PGConf India 2020 will be on February 26-28, 2020 in Bengaluru, Karnataka. The
CfP is open until November 15, 2019.
http://pgconf.in/

The German-speaking PostgreSQL Conference 2020 will take place on May 10, 2019
in Stuttgart.

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

Tom Lane pushed:

- Doc: improve docs about pg_statistic_ext_data. Commit aa087ec64 was a bit
over-hasty about the doc changes needed while splitting pg_statistic_ext_data
off from pg_statistic_ext. It duplicated one para and inserted another in what
seems to me to be the wrong section. Fix that up, and in passing do some
minor copy-editing. Per report from noborusai. Discussion:
https://postgr.es/m/CAAM3qnLXLUz4mOBkqa8jxigpKhKNxzSuvwpjvCRPvO5EqWjxSg@mail.gmail.com
https://git.postgresql.org/pg/commitdiff/732457b5d2521c6ccd6b3b096d7aba73fca2a38a

- Hack pg_ctl to report postmaster's exit status. Temporarily change pg_ctl so
that the postmaster's exit status will be printed (to the postmaster's
stdout). This is to help identify the cause of intermittent "postmaster
exited during a parallel transaction" failures seen on a couple of buildfarm
members. This change degrades pg_ctl's functionality in a couple of minor
ways, so we'll revert it once we've obtained the desired info. Discussion:
https://postgr.es/m/18537.1570421268@sss.pgh.pa.us
https://git.postgresql.org/pg/commitdiff/6a5084eed49552bfc8859c438c8d74ad09fc5d3f

- Check for too many postmaster children before spawning a bgworker. The
postmaster's code path for spawning a bgworker neglected to check whether we
already have the max number of live child processes. That's a bit hard to
hit, since it would necessarily be a transient condition; but if we do,
AssignPostmasterChildSlot() fails causing a postmaster crash, as seen in a
report from Bhargav Kamineni. To fix, invoke canAcceptConnections() in the
bgworker code path, as we do in the other code paths that spawn children.
Since we don't want the same pmState tests in this case, add a
child-process-type parameter to canAcceptConnections() so that it can know
what to do. Back-patch to 9.5. In principle the same hazard exists in 9.4,
but the code is enough different that this patch wouldn't quite fix it there.
Given the tiny usage of bgworkers in that branch it doesn't seem worth
creating a variant patch for it. Discussion:
https://postgr.es/m/18733.1570382257@sss.pgh.pa.us
https://git.postgresql.org/pg/commitdiff/3887e9455f812035473eee1cba0cf9c237969998

- Put back pqsignal() as an exported libpq symbol. This reverts commit
f7ab80285. Per discussion, we can't remove an exported symbol without a
SONAME bump, which we don't want to do. In particular that breaks usage of
current libpq.so with pre-9.3 versions of psql etc, which need libpq to export
pqsignal(). As noted in that commit message, exporting the symbol from
libpgport.a won't work reliably; but actually we don't want to export
src/port's implementation anyway. Any pre-9.3 client is going to be expecting
the definition that pqsignal() had before 9.3, which was that it didn't set
SA_RESTART for SIGALRM. Hence, put back pqsignal() in a separate source file
in src/interfaces/libpq, and give it the old semantics. Back-patch to v12.
Discussion: https://postgr.es/m/E1g5vmT-0003K1-6S@gemulon.postgresql.org
https://git.postgresql.org/pg/commitdiff/06a367c382d0a3595238eff2e777222dbc91911b

Michaël Paquier pushed:

- Improve handling and coverage of --no-ensure-shutdown in pg_rewind. This
includes a couple of changes around the new behavior of pg_rewind which
enforces recovery to happen once on a cluster not shut down cleanly: - Some
comments and documentation improvements. - Shutdown the cluster to rewind with
immediate mode in all the tests, this allows to check after the forced
recovery behavior which is wanted as new default. - Use -F for the forced
recovery step, so as postgres does not use fsync. This was useless as a final
sync is done once the tool is done. Author: Michael Paquier Reviewed-by:
Alexey Kondratov Discussion:
https://postgr.es/m/20191004083721.GA1829@paquier.xyz
https://git.postgresql.org/pg/commitdiff/caa078353ecd1f3b3681c0d4fa95ad4bb8c2308a

- Improve test coverage of pg_rewind. This includes new TAP tests for a couple
of areas not covered yet and some improvements: - More coverage for
--no-ensure-shutdown, the enforced recovery step and --dry-run. - Failures
with option combinations and basic option checks. - Removal of a duplicated
comment. Author: Alexey Kondratov, Michael Paquier Discussion:
https://postgr.es/m/20191007010651.GD14532@paquier.xyz
https://git.postgresql.org/pg/commitdiff/55ba56415bae6ac1f43c12d54537bd60eaa2153b

- Clarify some comments about ntstatus.h in win32_port.h. Some comments in this
file referred to outdated links. This simplifies the outdated comment blocks
and refreshes the links. Reported-by: Vignesh C Author: Juan José Santamaría
Flecha Discussion:
https://postgr.es/m/46C03E17-16F7-4C38-B148-029AC7448E96@gmail.com
https://git.postgresql.org/pg/commitdiff/491bb81fb803b0477062bb0a51edb752fa2cb396

- Update some outdated links about XLC and UNIX specification. Author: Vignesh C
Discussion:
https://postgr.es/m/CALDaNm3Dy=dTdx8UCVw=DWbzLzmRUC1dkq45=heOZDUg3U_PtA@mail.gmail.com
https://git.postgresql.org/pg/commitdiff/a7471bd85c05f849e88d6cfe9da3c795008e8f2e

- Flush logical mapping files with fd opened for read/write at checkpoint. The
file descriptor was opened with read-only to fsync a regular file, which would
cause EBADFD errors on some platforms. This is similar to the recent fix done
by a586cc4b (which was broken by me with 82a5649), except that I noticed this
issue while monitoring the backend code for similar mistakes. Backpatch to
9.4, as this has been introduced since logical decoding exists as of b89e151.
Author: Michael Paquier Reviewed-by: Andres Freund Discussion:
https://postgr.es/m/20191006045548.GA14532@paquier.xyz Backpatch-through: 9.4
https://git.postgresql.org/pg/commitdiff/b8e19b932a99a7eb5a3bce84e74b0b7c093d0981

- Fix dependency handling of column drop with partitioned tables. When dropping
a column on a partitioned table which has one or more partitioned indexes, the
operation was failing as dependencies with partitioned indexes using the
column dropped were not getting removed in a way consistent with the columns
involved across all the relations part of an inheritance tree. This commit
refactors the code executing column drop so as all the columns from an
inheritance tree to remove are gathered first, and dropped all at the end.
This way, we let the dependency machinery sort out by itself the deletion of
all the columns with the partitioned indexes across a partition tree. This
issue has been introduced by 1d92a0c, so backpatch down to REL_12_STABLE.
Author: Amit Langote, Michael Paquier Reviewed-by: Álvaro Herrera, Ashutosh
Sharma Discussion:
https://postgr.es/m/CA+HiwqE9kuBsZ3b5pob2-cvE8ofzPWs-og+g8bKKGnu6b4-yTQ@mail.gmail.com
Backpatch-through: 12
https://git.postgresql.org/pg/commitdiff/1df5875d39383b3981b804666ee1f4b0ff65943f

Peter Eisentraut pushed:

- Fix incorrect use of term HEAD for Git. HEAD as used here was CVS terminology.
Now we mean master.
https://git.postgresql.org/pg/commitdiff/cc4ec2d29ac4f3b8335d1851627a9735b81beb50

- Simplify PGAC_STRUCT_TIMEZONE Autoconf macro. Since
63bd0db12199c5df043e1dea0f2b574f622b3a4c we don't use tzname anymore, so we
don't need to check for it. Instead, just keep the part of
PGAC_STRUCT_TIMEZONE that we need, which is the check for struct tm.tm_zone.
Discussion:
https://www.postgresql.org/message-id/flat/5eb11a37-f3ca-5fb7-308f-4485dec25a2e%402ndquadrant.com
https://git.postgresql.org/pg/commitdiff/400d5ffcafa65e0f742dd29de83b035b8ea27c4a

- Remove use of deprecated Autoconf define. Change from HAVE_TM_ZONE to
HAVE_STRUCT_TM_TM_ZONE.
https://git.postgresql.org/pg/commitdiff/4d7e5a5db01edaff749555220aa41eb35be06799

- Remove some code for old unsupported versions of MSVC. As of
d9dd406fe281d22d5238d3c26a7182543c711e74, we require MSVC 2013, which means
_MSC_VER >= 1800. This means that conditionals about older versions of
_MSC_VER can be removed or simplified. Previous code was also in some cases
handling MinGW, where _MSC_VER is not defined at all, incorrectly, such as in
pg_ctl.c and win32_port.h, leading to some compiler warnings. This should now
be handled better. Reviewed-by: Michael Paquier <michael(at)paquier(dot)xyz>
https://git.postgresql.org/pg/commitdiff/38d8dce61fff09daae0edb6bcdd42b0c7f10ebcd

- Use libc version as a collation version on glibc systems. Using glibc's
version number to detect potential collation definition changes is not 100%
reliable, but it's better than nothing. Author: Thomas Munro Reviewed-by:
Peter Eisentraut Discussion:
https://postgr.es/m/4b76c6d4-ae5e-0dc6-7d0d-b5c796a07e34%402ndquadrant.com
https://git.postgresql.org/pg/commitdiff/9f90b1d08d796a925808b24f77f624a0ff682c77

- Revert "Use libc version as a collation version on glibc systems.". This
reverts commit 9f90b1d08d796a925808b24f77f624a0ff682c77. This needs some
refinements in the pg_dump and pg_upgrade tests.
https://git.postgresql.org/pg/commitdiff/50518ec296aea3af3e00c43c2ccef74c96cb5762

- pg_upgrade: Clean up some redundant code. No need to call exit() after
pg_fatal(). Clean up a few stragglers for consistency.
https://git.postgresql.org/pg/commitdiff/3b5d3721c25ed1270832265c5475649c3baa0e26

- Fix use of term "verifier". Within the context of SCRAM, "verifier" has a
specific meaning in the protocol, per RFCs. The existing code used "verifier"
differently, to mean whatever is or would be stored in pg_auth.rolpassword.
Fix this by using the term "secret" for this, following RFC 5803.
Reviewed-by: Michael Paquier <michael(at)paquier(dot)xyz> Discussion:
https://www.postgresql.org/message-id/flat/be397b06-6e4b-ba71-c7fb-54cae84a7e18%402ndquadrant.com
https://git.postgresql.org/pg/commitdiff/b4675a8ae2d0aaafeb136c46c92bb56eaf018d32

Bruce Momjian pushed:

- doc: move mention of log_min_error_statement in a better spot. Previously it
was mentioned in the lock_timeout docs in a confusing location. Reported-by:
ivaylo(dot)zlatanov(at)gmail(dot)com Discussion:
https://postgr.es/m/157019615723.25307.15449102262106437404@wrigleys.postgresql.org
Backpatch-through: 9.4
https://git.postgresql.org/pg/commitdiff/47571ec1e46994265961b9ddfb83cccb340e4aec

- docs: clarify that today/tomorrow/yesterday is at 00:00. This should help
people clearly know that these days start at midnight. Reported-by: David
Harper Discussion:
https://postgr.es/m/156258047907.1181.11324468080514061996@wrigleys.postgresql.org
Backpatch-through: 9.4
https://git.postgresql.org/pg/commitdiff/cae078f3f98fa3614b12719d276db376df95d473

- docs: Improve A?synchronous Multimaster Replication descr. The docs for sync
and async multimaster replication were unclear about when to use it, and when
it has benefits; this change clarifies that. Reported-by:
juha-pekka(dot)eloranta(at)reaktor(dot)fi Discussion:
https://postgr.es/m/156856543824.1274.12180817186798859836@wrigleys.postgresql.org
Backpatch-through: 9.4
https://git.postgresql.org/pg/commitdiff/47eec34e4674e327ba7c2c57dda19241c889859e

- doc: improve docs so config value default units are clearer. Previously, our
docs would say "Specifies the number of milliseconds" but it wasn't clear that
"milliseconds" was merely the default unit. New text says "Specifies duration
(defaults to milliseconds)", which is clearer. Reported-by:
basil(dot)bourque(at)gmail(dot)com Discussion:
https://postgr.es/m/15912-2e35e9026f61230b@postgresql.org Backpatch-through:
12
https://git.postgresql.org/pg/commitdiff/6c9fb69f2bb589c210a114162e67c86476460453

- pg_upgrade: clarify the database names in error files. Previously, the
"Database:" label in the error file was unclear if the label was a status
report or the problem was _in_ the database. New text is "In database:".
Reported-by: Justin Pryzby Discussion:
https://postgr.es/m/20191002172337.GC9680@telsasoft.com Backpatch-through:
head
https://git.postgresql.org/pg/commitdiff/1634d361577aab30c7d90336c96b969a2f5e5811

Andres Freund pushed:

- Fix table rewrites that include a column without a default. In c2fe139c201c I
made ATRewriteTable() use tuple slots. Unfortunately I did not notice that
columns can be added in a rewrite that do not have a default, when another
column is added/altered requiring one. Initialize columns to NULL again, and
add tests. Bug: #16038 Reported-By: anonymous Author: Andres Freund
Discussion: https://postgr.es/m/16038-5c974541f2bf6749@postgresql.org
Backpatch: 12, where the bug was introduced in c2fe139c201c
https://git.postgresql.org/pg/commitdiff/93765bd956bea26206043de8cbb0ae4b67e4df15

Fujii Masao pushed:

- Make crash recovery ignore restore_command and recovery_end_command settings.
In v11 or before, those settings could not take effect in crash recovery
because they are specified in recovery.conf and crash recovery always starts
without recovery.conf. But commit 2dedf4d9a8 integrated recovery.conf into
postgresql.conf and which unexpectedly allowed those settings to take effect
even in crash recovery. This is definitely not good behavior. To fix the
issue, this commit makes crash recovery always ignore restore_command and
recovery_end_command settings. Back-patch to v12 where the issue was added.
Author: Fujii Masao Reviewed-by: Peter Eisentraut Discussion:
https://postgr.es/m/e445616d-023e-a268-8aa1-67b8b335340c@pgmasters.net
https://git.postgresql.org/pg/commitdiff/20961ceaf0426c6fba40bb422cf111f704a00058

Noah Misch pushed:

- AIX: Stop adding option -qsrcmsg. With xlc v16.1.0, it causes internal
compiler errors. With xlc versions not exhibiting that bug, removing -qsrcmsg
merely changes the compiler error reporting format. Back-patch to 9.4 (all
supported versions). Discussion:
https://postgr.es/m/20191003064105.GA3955242@rfd.leadboat.com
https://git.postgresql.org/pg/commitdiff/5f3d271d03b249f5c80e3d3ca946f62a33d7862f

== Pending Patches ==

Soumyadeep Chakraborty sent in a patch to optimize generated functions earlier
to lower memory usage.

Fujii Masao sent in a patch to allow recovery_min_apply_delay during archive
recovery.

Michaël Paquier sent in a patch to add new tests for pg_rewind.

Anders Åstrand sent in a patch to add uri percent-encoding for binary data.

Robert Haas sent in another revision of a patch to improve handling of portals
after (sub)transaction abort.

Joe Nelson sent in two more revisions of a patch to change atoi to strtol in
same place.

Tom Lane sent in a patch to report postmaster exit status from pg_ctl.

Amit Langote sent in a patch to ensure that all partitions of a table, including
future ones, are added to publications when the table is added.

Peter Smith sent in another revision of a patch to make use of C99 designated
initialisers for nulls/values arrays.

Markus Timmer sent in another revision of a patch to add an option to use ICU as
the global collation provider.

Takeshi Ideriha sent in another revision of a patch to implement a MemoryContext
for shared memory based on DSA, then use same to implement a global catalog
cache.

Jordan Deitch sent in a WIP patch to raise an error when submitting an invalid
ALTER SYSTEM command.

Ants Aasma sent in another revision of a patch to allow vacuum to use more than
1GB of memory.

David Cramer sent in a patch to add a STARTUP packet option to set GUC_REPORT on
GUC's that currently do not have that option set.

Noah Misch sent in two revisions of a patch to fix a deadlock in XLogInsert at
AIX.

Tom Lane sent in a patch to implement separate limits for different process
types.

Peter Smith sent in another revision of a patch to add more compile-time asserts
to expose inconsistencies.

Michaël Paquier sent in a patch to add a get_object_type() and
get_relkind_objtype().

Fabien COELHO sent in another revision of a patch to pgbench to extend
initialization phase control.

Pavel Stěhule sent in another revision of a patch to implement schema variables.

Kyotaro HORIGUCHI sent in a patch to fix configure's perl check for CentOS8.

Masahiko Sawada and Amit Kapila traded patches to implement block-level parallel
vacuum.

Dilip Kumar sent in another revision of a patch to gracefully handle concurrent
aborts of uncommitted transactions that are being decoded alongside.

Thomas Munro sent in a patch to use libc version as a collation version on glibc
systems.

Nikolay Shaplov sent in two more revisions of a patch to use an empty structure
for partitioned options.

Alexander Korotkov sent in another revision of a patch to refactor jsonpath's
compareDatetime().