== PostgreSQL Weekly News - July 08 2018 ==

== PostgreSQL Weekly News - July 08 2018 ==

== PostgreSQL Product News ==

PostGIS 2.5.0beta1 the industry standard geographic information
system package for PostgreSQL, released.
https://git.osgeo.org/gitea/postgis/postgis/raw/tag/2.5.0beta1/NEWS

pgquarrel 0.4.0, a tool which compares PostgreSQL database schemas and outputs a
set of commands to turn a database schema into another one, released.
http://eulerto.github.io/pgquarrel

== PostgreSQL Jobs for July ==

http://archives.postgresql.org/pgsql-jobs/2018-07/

== PostgreSQL Local ==

PGConf.Brazil 2018 will take place in São Paulo, Brazil on August 3-4 2018.
http://pgconf.com.br

PostgreOpen Silicon Valley 2018 will be held in San Francisco on September 5-7, 2018.
https://2018.postgresopen.org/

The Portland PostgreSQL Users Group will be holding a PGDay on September 10,
2018 in Portland, OR. The CfP is open at https://goo.gl/forms/E0CiUQGSZGMYwh922
https://pdx.postgresql.us/pdxpgday2018

PostgresConf South Africa 2018 will take place in Johannesburg on October 9, 2018
https://postgresconf.org/conferences/SouthAfrica2018

PostgreSQL Conference Europe 2018 will be held on October 23-26, 2018 at the
Lisbon Marriott Hotel in Lisbon, Portugal. The CfP is open through August 6,
2018 midnight CET at https://2018.pgconf.eu/callforpapers
https://2017.pgconf.eu/

2Q PGConf will be on December 4-5, 2018 in Chicago, IL. The CfP is open through
August 27, 2018 at midnight Pacific Time at http://www.2qpgconf.com/#cfp
http://www.2qpgconf.com/

PGConf.ASIA 2018 will take place on December 10-12, 2018 in Akihabara, Tokyo,
Japan. The CfP is open until midnight, July 31, 2018, Japan time at
pgconf-asia-2018-submission(at)pgconf(dot)asia
http://www.pgconf.asia/EN/2018/

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

- Correct function name in comment of logical decoding code. Reported-by: Dave
Cramer Author: Euler Taveira Discussion:
https://postgr.es/m/CADK3HHKnPGJDLhjOFBY6+70Wd14iEH8c2GKw7UrOuUHp_GNFrA@mail.gmail.com
https://git.postgresql.org/pg/commitdiff/c072e80337e6b1e829141c9cd8ba0bc585f5c2ab

- Add wait event for fsync of WAL segments. This has been visibly a forgotten
spot in the first implementation of wait events for I/O added by 249cf07, and
what has been missing is a fsync call for WAL segments which is a wrapper
reacting on the value of GUC wal_sync_method. Reported-by: Konstantin
Knizhnik Author: Konstantin Knizhnik Reviewed-by: Craig Ringer, Michael
Paquier Discussion:
https://postgr.es/m/4a243897-0ad8-f471-aa40-242591f2476e@postgrespro.ru
https://git.postgresql.org/pg/commitdiff/c55de5e5123ce58ee19a47c08425949599285041

- Remove dead code for temporary relations in partition planning. Since recent
commit 1c7c317c, temporary relations cannot be mixed with permanent relations
within the same partition tree, and the same counts for temporary relations
created by other sessions, which the planner simply discarded. Instead be
paranoid and issue an error, as those should be blocked at definition time, at
least for now. At the same time, a test case is added to stress what has been
moved when expand_partitioned_rtentry gets called recursively but bumps on a
partitioned relation with no partitions which should be handled the same way
as the non-inheritance case. This code may be reworked in a close future, and
covering this code path will limit surprises. Reported-by: David Rowley
Author: David Rowley Reviewed-by: Amit Langote, Robert Haas, Michael Paquier
Discussion:
https://postgr.es/m/CAKJS1f_HyV1txn_4XSdH5EOhBMYaCwsXyAj6bHXk9gOu4JKsbw@mail.gmail.com
https://git.postgresql.org/pg/commitdiff/fc057b2b8fc3c3556d9f8cc0195c622aaad92c9e

- Prevent references to invalid relation pages after fresh promotion. If a
standby crashes after promotion before having completed its first
post-recovery checkpoint, then the minimal recovery point which marks the LSN
position where the cluster is able to reach consistency may be set to a
position older than the first end-of-recovery checkpoint while all the WAL
available should be replayed. This leads to the instance thinking that it
contains inconsistent pages, causing a PANIC and a hard instance crash even if
all the WAL available has not been replayed for certain sets of records
replayed. When in crash recovery, minRecoveryPoint is expected to always be
set to InvalidXLogRecPtr, which forces the recovery to replay all the WAL
available, so this commit makes sure that the local copy of minRecoveryPoint
from the control file is initialized properly and stays as it is while crash
recovery is performed. Once switching to archive recovery or if crash
recovery finishes, then the local copy minRecoveryPoint can be safely updated.
Pavan Deolasee has reported and diagnosed the failure in the first place, and
the base fix idea to rely on the local copy of minRecoveryPoint comes from
Kyotaro Horiguchi, which has been expanded into a full-fledged patch by me.
The test included in this commit has been written by Álvaro Herrera and Pavan
Deolasee, which I have modified to make it faster and more reliable with sleep
phases. Backpatch down to all supported versions where the bug appears, aka
9.3 which is where the end-of-recovery checkpoint is not run by the startup
process anymore. The test gets easily supported down to 10, still it has been
tested on all branches. Reported-by: Pavan Deolasee Diagnosed-by: Pavan
Deolasee Reviewed-by: Pavan Deolasee, Kyotaro Horiguchi Author: Michael
Paquier, Kyotaro Horiguchi, Pavan Deolasee, Álvaro Herrera Discussion:
https://postgr.es/m/CABOikdPOewjNL=05K5CbNMxnNtXnQjhTx2F--4p4ruorCjukbA@mail.gmail.com
https://git.postgresql.org/pg/commitdiff/3c64dcb1e35dccbfce531182fa9b9f48bec414ad

- Add note in pg_rewind documentation about read-only files. When performing
pg_rewind, the presence of a read-only file which is not accessible for writes
will cause a failure while processing. This can cause the control file of the
target data folder to be truncated, causing it to not be reusable with a
successive run. Also, when pg_rewind fails mid-flight, there is likely no way
to be able to recover the target data folder anyway, in which case a new base
backup is the best option. A note is added in the documentation as well
about. Reported-by: Christian H. Author: Michael Paquier Reviewed-by: Andrew
Dunstan Discussion:
https://postgr.es/m/20180104200633.17004.16377%40wrigleys.postgresql.org
https://git.postgresql.org/pg/commitdiff/eb270b00b2d60e751545d4b808642395c901b668

- Use access() to check file existence in GetNewRelFileNode(). Previous code
used BasicOpenFile() and close() just to check for a file collision, while
there is no need to hold open a file descriptor but that's an overkill here.
Author: Paul Guo Reviewed-by: Peter Eisentraut, Michael Paquier Discussion:
https://postgr.es/m/CABQrizcUtiHaquxK=d4etBX8GF9kbZB50Nt1gO9_aN-e9SptyQ@mail.gmail.com
https://git.postgresql.org/pg/commitdiff/677da8c15d19c11465d78f18bfd5ceb5d6fc3af1

Peter Eisentraut pushed:

- Correct comment.
https://git.postgresql.org/pg/commitdiff/683707868723d2a798ff8dcb0513d89735591b66

- Add $Test::Builder::Level to pgbench test functions. same as
c4309f4aeeae54e4c5281d68e29288af1d0d1ed2
https://git.postgresql.org/pg/commitdiff/2c059c86ba774930fa816278343ff30292db4e6c

- doc: Reorganize CREATE TABLE / LIKE option documentation. This section once
started out small but has now grown quite a bit and needs a bit of structure.
Rewrite as list, add documentation of EXCLUDING, and improve the documentation
of INCLUDING ALL instead of just listing all the options again. per report
from Yugo Nagata that EXCLUDING was not documented, that part reviewed by
Daniel Gustafsson, most of the rewrite was by me
https://git.postgresql.org/pg/commitdiff/b46727e07a9f4b8e0c8de1f10bfd4986b02c154c

- Fix typo.
https://git.postgresql.org/pg/commitdiff/f61988d160c1af8c1ed495e5c547726e88a45036

- doc: Fix typos. Author: Justin Pryzby <pryzby(at)telsasoft(dot)com>
https://git.postgresql.org/pg/commitdiff/17411e0ffa1e2a9789756203019dce368ad4bf60

- doc: Reword old inheritance partitioning documentation. Prefer to use phrases
like "child" instead of "partition" when describing the legacy
inheritance-based partitioning. The word "partition" now has a fixed meaning
for the built-in partitioning, so keeping it out of the documentation of the
old method makes things clearer. Author: Justin Pryzby <pryzby(at)telsasoft(dot)com>
https://git.postgresql.org/pg/commitdiff/0c06534bd63b0bd23d7744a53f3b490a2adeea8a

- Allow CALL with polymorphic type arguments. In order to be able to resolve
polymorphic types, we need to set fn_expr before invoking the procedure.
https://git.postgresql.org/pg/commitdiff/e34ec136201df07a05a83825ebff7fffb9043598

- Fix assert in nested SQL procedure call. When executing CALL in PL/pgSQL, we
need to set a snapshot before invoking the to-be-called procedure. Otherwise,
the to-be-called procedure might end up running without a snapshot. For
LANGUAGE SQL procedures, this would result in an assertion failure. (For most
other languages, this is usually not a problem, because those use SPI and SPI
sets snapshots in most cases.) Setting the snapshot restores the behavior of
how CALL worked when it was handled as a generic SQL statement in PL/pgSQL
(exec_stmt_execsql()). This change revealed another problem: In
SPI_commit(), we popped the active snapshot before committing the transaction,
to avoid "snapshot %p still active" errors. However, there is no particular
reason why only at most one snapshot should be on the stack. So change this
to pop all active snapshots instead of only one.
https://git.postgresql.org/pg/commitdiff/2e78c5b522a91c7893decd92d6f5b31fef0027bd

- Add separate error message for procedure does not exist. While we probably
don't want to split up all error messages into function and procedure
variants, this one is a very prominent one, so it's helpful to be more
specific here.
https://git.postgresql.org/pg/commitdiff/0903bbdad24a8f51b18a6a54a41bbb36ad2ceee4

Fujii Masao pushed:

- Improve the performance of relation deletes during recovery. When multiple
relations are deleted at the same transaction, the files of those relations
are deleted by one call to smgrdounlinkall(), which leads to scan whole
shared_buffers only one time. OTOH, previously, during recovery,
smgrdounlink() (not smgrdounlinkall()) was called for each file to delete,
which led to scan shared_buffers multiple times. Obviously this could cause to
increase the WAL replay time very much especially when shared_buffers was
huge. To alleviate this situation, this commit changes the recovery so that
it also calls smgrdounlinkall() only one time to delete multiple relation
files. This is just fix for oversight of commit 279628a0a7, not new feature.
So, per discussion on pgsql-hackers, we concluded to backpatch this to all
supported versions. Author: Fujii Masao Reviewed-by: Michael Paquier, Andres
Freund, Thomas Munro, Kyotaro Horiguchi, Takayuki Tsunakawa Discussion:
https://postgr.es/m/CAHGQGwHVQkdfDqtvGVkty+19cQakAydXn1etGND3X0PHbZ3+6w@mail.gmail.com
https://git.postgresql.org/pg/commitdiff/b41669118c00e25376a6c9ac991e0d074990484a

Andres Freund pushed:

- Check for interrupts inside the nbtree page deletion code. When deleting
pages the nbtree code has to walk through siblings of a tree node. When those
sibling links are corrupted that can lead to endless loops - which are
currently not interruptible. This is especially problematic if autovacuum is
repeatedly blocked on such indexes, as it can be hard to get out of that
situation without resorting to single user mode. Thus add interrupt checks to
appropriate places in such loops. Unfortunately in one of the cases it's it's
not easy to do so. Between 9.3 and 9.4 the page deletion (and page split)
code changed significantly. Before it was significantly less robust against
interruptions. Therefore don't backpatch to 9.3. Author: Andres Freund
Discussion:
https://postgr.es/m/20180627191629.wkunw2qbibnvlz53@alap3.anarazel.de
Backpatch: 9.4-
https://git.postgresql.org/pg/commitdiff/3a01f68e35a3584431ac5381c6ed75b1b39aaf2a

- Use context with correct lifetime in hypothetical_dense_rank_final. The query
lifetime expression context created in hypothetical_dense_rank_final() was
buggily allocated in the calling memory context. I (Andres) broke that in
bf6c614a2f2. Reported-By: Rajkumar Raghuwanshi Author: Amit Langote
Discussion:
https://postgr.es/m/CAKcux6kmzWmur5HhA_aU6gYVFu0RLQdgJJ+aC9SLdcOvBSrpfA@mail.gmail.com
Backpatch: 11-
https://git.postgresql.org/pg/commitdiff/249126e761e13c4d8e7519569d483eaeca7dac25

Álvaro Herrera pushed:

- Reduce cost of test_decoding's new oldest_xmin test. Change a whole-database
VACUUM into doing just pg_attribute, which is the portion that verifies what
we want it to do. The original formulation wastes a lot of CPU time, which
leads the test to fail when runtime exceeds isolationtester timeout when it's
super-slow, such as under CLOBBER_CACHE_ALWAYS. Per buildfarm member
friarbird. It turns out that the previous shape of the test doesn't always
detect the condition it is supposed to detect (on unpatched reorderbuffer
code): the reason is that there is a good chance of encountering a
xl_running_xacts record (logged every 15 seconds) before the checkpoint -- and
because we advance the xmin when we receive that WAL record, and we *don't*
advance the xmin twice consecutively without receiving a client message in
between, that means the xmin is not advanced enough for the tuple to be pruned
from pg_attribute by VACUUM. So the test would spuriously pass. The reason
this test deficiency wasn't detected earlier is that HOT pruning removes the
tuple anyway, even if vacuum leaves it in place, so the test correctly fails
(detecting the coding mistake), but for the wrong reason. To fix this mess,
run the s0_get_changes step twice before vacuum instead of once: this seems to
cause the xmin to be advanced reliably, wreaking havoc with more certainty.
Author: Arseny Sher Discussion:
https://postgr.es/m/87h8lkuxoa.fsf@ars-thinkpad
https://git.postgresql.org/pg/commitdiff/8d1c1ca70b012594932578a0994a5d45f29d9572

- logical decoding: beware of an unset specinsert change. Coverity complains
that there is no protection in the code (at least in non-assertion-enabled
builds) against speculative insertion failing to follow the expected protocol.
Add an elog(ERROR) for the case.
https://git.postgresql.org/pg/commitdiff/3ca966c06f91fb6ccc11d71d4094c1e297b8945d

- Allow replication slots to be dropped in single-user mode. Starting with
commit 9915de6c1cb2, replication slot drop uses a condition variable sleep to
wait until the current user of the slot goes away. This is more user friendly
than the previous behavior of erroring out if the slot is in use, but it fails
with a not-for-user-consumption error message in single-user mode; plus, if
you're using single-user mode because you don't want to start the server in
the regular mode (say, disk is full and WAL won't recycle because of the
slot), it's inconvenient. Fix by skipping the cond variable sleep in
single-user mode, since there can't be anybody to wait for anyway.
Reported-by: tushar <tushar(dot)ahuja(at)enterprisedb(dot)com> Author: Álvaro Herrera
<alvherre(at)alvh(dot)no-ip(dot)org> Discussion:
https://postgr.es/m/3b2f809f-326c-38dd-7a9e-897f957a4eb1@enterprisedb.com
https://git.postgresql.org/pg/commitdiff/0ce5cf2ef24f638ff05569d027135fa1c7683a71

Jeff Davis pushed:

- Add test for partitionwise join involving default partition. Author: Rajkumar
Raghuwanshi Reviewed-by: Ashutosh Bapat Discussion:
https://postgr.es/m/CAKcux6ky5YeZAY74qSh-ayPZZEQchz092g71iXXbC0%2BE3xoscA%40mail.gmail.com
Discussion:
https://postgr.es/m/CAKcux6kOQ85Xtzxu3tM1mR7Vk%3D7Z2e4rG7dL1iMZqPgLMpxQYg%40mail.gmail.com
https://git.postgresql.org/pg/commitdiff/4513d3a4be0bb7d0141f8b7eaf669a55c08e41b0

Andrew Dunstan pushed:

- Print DEBUG2 like that rather than as DEBUG. DEBUG is an alias for DEBUG2,
but we want DEBUG2 to show in the settings no matter how it was spelled.
Takeshi Ideriha Discussion:
https://postgr.es/m/4E72940DA2BF16479384A86D54D0988A5678EC03@G01JPEXMBKW04
https://git.postgresql.org/pg/commitdiff/8fb68aa2655762beed237ea04e2c6c490cf1ed10

Peter Geoghegan pushed:

- Correct obsolete unique index insertion comment. Commit bc292937ae6 failed to
update a comment about unique index checking. _bt_insertonpg() is no longer
responsible for finding an insertion location while preventing conflicting
insertions.
https://git.postgresql.org/pg/commitdiff/e915fed291772d8d5fcc437ac777f7beca8c24ce

== Pending Patches ==

Thomas Munro sent in another revision of a patch to add a synchronous replay
mode for avoiding stale reads on hot standbys.

Haribabu Kommi sent in a patch to document the fact that
pg_stat_statements_reset() function can be executed by super and members of the
role pg_read_all_stats.

John Naylor sent in another revision of a patch to convert info for conversion
functions into entries in pg_proc.dat and pg_conversion.dat and add a
pg_language lookup.

CCHsu sent in a patch to add tab completion to psql for SELECT.

Pierre Ducroquet sent in a patch to introduce opt1 in LLVM/JIT, and force it
with deforming.

Nikita Glukhov sent in another revision of a patch to implement jsonpath.

Ildus Kurbangaliev sent in another revision of a patch to add custom compression
methods.

Craig Ringer sent in a patch to add more statically defined tracepoints around
xlog handling.

Alexey Kryuchkov sent in a patch to make the intarray contrib extension
consistent with the built-in array code by making the '&' array intersection
operator return proper zero-dimensional empty arrays instead of one-dimensional,
zero-length "empty" arrays.

Peter Geoghegan sent in another revision of a patch to ensure nbtree leaf tuple
keys are always unique.

Daniel Gustafsson sent in two more revisions of a patch to order windows on
partition/ordering prefix to reuse Sort nodes.

Andrey V. Lepikhov sent in two more revisions of a patch to add a way to do
retail IndexTuple deletion in B-trees.

Masahiko Sawada sent in another revision of a patch to add functions for copying
physical and logical replication slots.

Andrew Gierth sent in four more revisions of a patch to make a way to include
header files in the installations of EXTENSIONs.

Haribabu Kommi sent in two more revisions of a patch to add a
pg_stat_statements_reset() function to reset specific query/user/db statistics.

Kyotaro HORIGUCHI sent in another revision of a patch to protect syscache from
bloating with negative cache entries.

Sergey Cherkashin sent in another revision of a patch to add backslash commands
to psql for access methods.

Nikita Glukhov sent in another revision of a patch to add SQL/JSON functions.

Nikita Glukhov sent in another revision of a patch to implement JSON_TABLE.

Nikhil Sontakke sent in two more revisions of a patch to support logical
decoding of two-phase transactions.

Michaël Paquier sent in another revision of a patch to fix the slot advance
comments to reflect the fact that the fast-forward does not

Robert Haas sent in another revision of a patch to fix an infelicity between
AtEOXact_ApplyLauncher() and subtransactions.

Nikita Glukhov sent in another revision of a patch to implement
k-Nearest-Neighbor for GiST indexes.

Amit Langote sent in a patch to move PartitionDispatchData definition to
execPartition.c.

Amit Langote sent in another revision of a patch to fix opening/closing of
partitioned tables in Append plans.

Peter Eisentraut sent in a patch to prohibit transaction commands in security
definer procedures.

Pavel Raiskup sent in a patch to fix btree_gist "union" for variable length
types.

Andrew Dunstan sent in a patch add a GUC to auto_explain to set the level at
which it logs.

Masahiko Sawada sent in a patch to fix a faled assertion in the GROUPS mode of
windowing functions.

Simon Muller sent in another revision of a patch to allow COPY's 'text' format
to output a header.

Kyotaro HORIGUCHI sent in another revision of a patch to add a capability to
limit the number of segments kept by replication slots by a GUC variable.

Michaël Paquier sent in a patch to disable the TRUNCATE and COPY optimizations
for two cases where it could cause data loss, and modifies ExecuteTruncateGuts
so as the TRUNCATE optimization never runs for wal_level = minimal.

Thomas Munro sent in two revisions of a patch to call
AcceptInvalidationMessages() after authenticating.

Ashutosh Bapat sent in another revision of a patch to implement TupleTableSlot
abstraction.

Andrey Borodin sent in another revision of a patch to implement covering indexes
for GiST.

David Rowley and Alexander Kuzmenkov traded patches to improve performance of
tuple conversion map generation.

Thomas Munro sent in a patch to use setproctitle_fast() to update the ps status,
if available.

Peter Eisentraut sent in a patch to cache pg_get_expr deparse context between
calls.

Amit Langote sent in a patch to remove the mention of locks taken on the catalog
by CREATE COLLATION.

Michaël Paquier sent in another revision of a patch to better show replication
status in logical replication.

Kyotaro HORIGUCHI sent in another revision of a patch to allow the generalized
expression syntax for partition bounds.

Kyotaro HORIGUCHI sent in a patch to fix the documentation of
pg_create_physical_replication_slot.

Taiki Kondo sent in a patch to fix a typo in the Japanese localization of psql.

Antonin Houska sent in another revision of a patch to allow pushing aggregates
below JOINs.

Dave Cramer sent in a patch to document which signals effect logical replication
slots and how.

Amit Kapila sent in another revision of a patch to allow ExecShutdownNode to
count stats.

Andres Freund sent in a patch to hand code string to integer conversion for
performance.

Álvaro Herrera sent in a patch to put walsegsz before its output argument in
XLogBytePosToRecPtr().

Markus Winand sent in a patch to fix some mistakes in XML/XPATH.