== PostgreSQL Weekly News - April 30 2017 ==

== PostgreSQL Weekly News - April 30 2017 ==

Ubuntu Zesty (17.04) and Debian stretch (9) are now supported on
apt.postgresql.org.
https://wiki.postgresql.org/wiki/Apt

== PostgreSQL Product News ==

Pgpool-II 3.6.3, 3.5.7, 3.4.11, 3.3.15, 3.2.20, and pgpoolAdmin 3.6.1 released.
http://pgpool.net/mediawiki/index.php/Downloads

pg_repack 1.4, an extension for removing bloat, released.
http://reorg.github.io/pg_repack/1.4/

PGroonga 1.2.0, a full text search platform for all languages, released.
http://groonga.org/en/blog/2017/04/29/pgroonga-1.2.0.html

== PostgreSQL Jobs for April ==

http://archives.postgresql.org/pgsql-jobs/2017-04/

== PostgreSQL Local ==

PGCon 2017 will take place in Ottawa on 23-26 May.
http://www.pgcon.org/2017/

PGDay Argentina 2017 will be held in Santa Fe on June 9, 2017.
The CfP is open.
http://www.pgday.com.ar

Postgres Vision will take place in Boston, June 26 - 28, 2017.
http://postgresvision.com/

Swiss PGDay in Rapperswil will take place June 30, 2017.
http://www.pgday.ch/2017/

PostgresOpen will occur September 6-8, 2017 in San Francisco.
The CfP is open until May 30, 2017.
https://2017.postgresopen.org/

PGDay UK 2017 will take place July 4, 2017 in London, England, UK.
http://www.pgconf.uk

== 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 Pacific time.
Please send English language ones to david(at)fetter(dot)org, German language
to pwn(at)pgug(dot)de, Italian language to pwn(at)itpug(dot)org(dot)

== Applied Patches ==

Tom Lane pushed:

- Code review for commands/statscmds.c. Fix machine-dependent sorting of column
numbers. (Odd behavior would only materialize for column numbers above 255,
but that's certainly legal.) Fix poor choice of SQLSTATE for some errors, and
improve error message wording. (Notably, "is not a scalar type" is a totally
misleading way to explain "does not have a default btree opclass".) Avoid
taking AccessExclusiveLock on the associated relation during DROP STATISTICS.
That's neither necessary nor desirable, and it could easily have put us into
situations where DROP fails (compare commit 68ea2b7f9). Adjust/improve
comments. David Rowley and Tom Lane Discussion:
https://postgr.es/m/CAKJS1f-GmCfPvBbAEaM5xoVOaYdVgVN1gicALSoYQ77z-+vLbw@mail.gmail.com
https://git.postgresql.org/pg/commitdiff/4b34624daadd9837cd65f20419f832b295c67ecb

- Fix postmaster's handling of fork failure for a bgworker process. This corner
case didn't behave nicely at all: the postmaster would (partially) update its
state as though the process had started successfully, and be quite confused
thereafter. Fix it to act like the worker had crashed, instead. In passing,
refactor so that do_start_bgworker contains all the state-change logic for
bgworker launch, rather than just some of it. Back-patch as far as 9.4. 9.3
contains similar logic, but it's just enough different that I don't feel
comfortable applying the patch without more study; and the use of bgworkers in
9.3 was so small that it doesn't seem worth the extra work.
transam/parallel.c is still entirely unprepared for the possibility of
bgworker startup failure, but that seems like material for a separate patch.
Discussion: https://postgr.es/m/4905.1492813727@sss.pgh.pa.us
https://git.postgresql.org/pg/commitdiff/4fe04244b584749351657e99f3e6e1436e9b65a8

- Run the postmaster's signal handlers without SA_RESTART. The postmaster keeps
signals blocked everywhere except while waiting for something to happen in
ServerLoop(). The code expects that the select(2) will be cancelled with
EINTR if an interrupt occurs; without that, followup actions that should be
performed by ServerLoop() itself will be delayed. However, some platforms
interpret the SA_RESTART signal flag as meaning that they should restart
rather than cancel the select(2). Worse yet, some of them restart it with the
original timeout delay, meaning that a steady stream of signal interrupts can
prevent ServerLoop() from iterating at all if there are no incoming connection
requests. Observable symptoms of this, on an affected platform such as HPUX
10, include extremely slow parallel query startup (possibly as much as 30
seconds) and failure to update timestamps on the postmaster's sockets and
lockfiles when no new connections arrive for a long time. We can fix this by
running the postmaster's signal handlers without SA_RESTART. That would be
quite a scary change if the range of code where signals are accepted weren't
so tiny, but as it is, it seems safe enough. (Note that postmaster children
do, and must, reset all the handlers before unblocking signals; so this change
should not affect any child process.) There is talk of rewriting the
postmaster to use a WaitEventSet and not do signal response work in signal
handlers, at which point it might be appropriate to revert this patch. But
that's not happening before v11 at the earliest. Back-patch to 9.6. The
problem exists much further back, but the worst symptom arises only in
connection with parallel query, so it does not seem worth taking any
portability risks in older branches. Discussion:
https://postgr.es/m/9205.1492833041@sss.pgh.pa.us
https://git.postgresql.org/pg/commitdiff/8939020853e63da6b4f5a63f023b02776a441b5d

- Use pselect(2) not select(2), if available, to wait in postmaster's loop.
Traditionally we've unblocked signals, called select(2), and then blocked
signals again. The code expects that the select() will be cancelled with
EINTR if an interrupt occurs; but there's a race condition, which is that an
already-pending signal will be delivered as soon as we unblock, and then when
we reach select() there will be nothing preventing it from waiting. This can
result in a long delay before we perform any action that ServerLoop was
supposed to have taken in response to the signal. As with the
somewhat-similar symptoms fixed by commit 893902085, the main practical
problem is slow launching of parallel workers. The window for trouble is
usually pretty short, corresponding to one iteration of ServerLoop; but it's
not negligible. To fix, use pselect(2) in place of select(2) where available,
as that's designed to solve exactly this problem. Where not available, we
continue to use the old way, and are no worse off than before. pselect(2) has
been required by POSIX since about 2001, so most modern platforms should have
it. A bigger portability issue is that some implementations are said to be
non-atomic, ie pselect() isn't really any different from
unblock/select/reblock. Still, we're no worse off than before on such a
platform. There is talk of rewriting the postmaster to use a WaitEventSet and
not do signal response work in signal handlers, at which point this could be
reverted, since we'd be using a self-pipe to solve the race condition. But
that's not happening before v11 at the earliest. Back-patch to 9.6. The
problem exists much further back, but the worst symptom arises only in
connection with parallel query, so it does not seem worth taking any
portability risks in older branches. Discussion:
https://postgr.es/m/9205.1492833041@sss.pgh.pa.us
https://git.postgresql.org/pg/commitdiff/81069a9efc5a374dd39874a161f456f0fb3afba4

- Revert "Use pselect(2) not select(2), if available, to wait in postmaster's
loop.". This reverts commit 81069a9efc5a374dd39874a161f456f0fb3afba4.
Buildfarm results suggest that some platforms have versions of pselect(2) that
are not merely non-atomic, but flat out non-functional. Revert the
use-pselect patch to confirm this diagnosis (and exclude the no-SA_RESTART
patch as the source of trouble). If it's so, we should probably look into
blacklisting specific platforms that have broken pselect. Discussion:
https://postgr.es/m/9696.1493072081@sss.pgh.pa.us
https://git.postgresql.org/pg/commitdiff/64925603c972aa3a9f1d4c42686dba67f1a7e9d0

- Silence compiler warning induced by commit de4389712. Smarter compilers can
see that "slot" can't be used uninitialized, but some popular ones cannot.
Noted by Jeff Janes.
https://git.postgresql.org/pg/commitdiff/49da00677dc25d83be44372918e21a405863ace2

- Allow multiple bgworkers to be launched per postmaster iteration. Previously,
maybe_start_bgworker() would launch at most one bgworker process per call, on
the grounds that the postmaster might otherwise neglect its other duties for
too long. However, that seems overly conservative, especially since bad
effects only become obvious when many hundreds of bgworkers need to be
launched at once. On the other side of the coin is that the existing logic
could result in substantial delay of bgworker launches, because ServerLoop
isn't guaranteed to iterate immediately after a signal arrives. (My attempt
to fix that by using pselect(2) encountered too many portability question
marks, and in any case could not help on platforms without pselect().) One
could also question the wisdom of using an O(N^2) processing method if the
system is intended to support so many bgworkers. As a compromise, allow that
function to launch up to 100 bgworkers per call (and in consequence, rename it
to maybe_start_bgworkers). This will allow any normal parallel-query request
for workers to be satisfied immediately during sigusr1_handler, avoiding the
question of whether ServerLoop will be able to launch more promptly. There is
talk of rewriting the postmaster to use a WaitEventSet to avoid the
signal-response-delay problem, but I'd argue that this change should be kept
even after that happens (if it ever does). Backpatch to 9.6 where parallel
query was added. The issue exists before that, but previous uses of bgworkers
typically aren't as sensitive to how quickly they get launched. Discussion:
https://postgr.es/m/4707.1493221358@sss.pgh.pa.us
https://git.postgresql.org/pg/commitdiff/aa1351f1eec4adae39be59ce9a21410f9dd42118

- Make latch.c more paranoid about child-process cases. Although the postmaster
doesn't currently create a self-pipe or any latches, there's discussion of it
doing so in future. It's also conceivable that a shared_preload_libraries
extension would try to create such a thing in the postmaster process today.
In that case the self-pipe FDs would be inherited by forked child processes.
latch.c was entirely unprepared for such a case and could suffer an assertion
failure, or worse try to use the inherited pipe if somebody called WaitLatch
without having called InitializeLatchSupport in that process. Make it keep
track of whether InitializeLatchSupport has been called in the *current*
process, and do the right thing if state has been inherited from a parent.
Apply FD_CLOEXEC to file descriptors created in latch.c (the self-pipe, as
well as epoll event sets). This ensures that child processes spawned in
backends, the archiver, etc cannot accidentally or intentionally mess with
these FDs. It also ensures that we end up with the right state for the
self-pipe in EXEC_BACKEND processes, which otherwise wouldn't know to close
the postmaster's self-pipe FDs. Back-patch to 9.6, mainly to keep latch.c
looking similar in all branches it exists in. Discussion:
https://postgr.es/m/8322.1493240739@sss.pgh.pa.us
https://git.postgresql.org/pg/commitdiff/fa31b6f4e9696f3c9777bf4ec2faea822826ce9f

- Cope with glibc too old to have epoll_create1(). Commit fa31b6f4e supposed
that we didn't have to worry about that anymore, but it seems that RHEL5 is
like that, and that's still a supported platform. Put back the prior coding
under an #ifdef, adding an explicit fcntl() to retain the desired CLOEXEC
property. Discussion: https://postgr.es/m/12307.1493325329@sss.pgh.pa.us
https://git.postgresql.org/pg/commitdiff/82ebbeb0abfe40fe5f19a6fcdffc7484fd3a35b0

- Avoid slow shutdown of pg_basebackup. pg_basebackup's child process did not
pay any attention to the pipe from its parent while waiting for input from the
source server. If no server data was arriving, it would only wake up and
check the pipe every standby_message_timeout or so. This creates a problem
since the parent process might determine and send the desired stop position
only after the server has reached end-of-WAL and stopped sending data. In the
src/test/recovery regression tests, the timing is repeatably such that it
takes nearly 10 seconds for the child process to realize that it should shut
down. It's not clear how often that would happen in real-world cases, but it
sure seems like a bug --- and if the user turns off standby_message_timeout or
sets it very large, the delay could be a lot worse. To fix, expand the
StreamCtl API to allow the pipe input FD to be passed down to the low-level
wait routine, and watch both sockets when sleeping. (Note: AFAICS this issue
doesn't affect the Windows port, since it doesn't rely on a pipe to transfer
the stop position to the child thread.) Discussion:
https://postgr.es/m/6456.1493263884@sss.pgh.pa.us
https://git.postgresql.org/pg/commitdiff/7834d20b57a4320308c3f8262fabf898f89e6a71

- Micro-optimize some slower queries in the opr_sanity regression test. Convert
the binary_coercible() and physically_coercible() functions from SQL to
plpgsql. It's not that plpgsql is inherently better at doing queries; if you
simply convert the previous single SQL query into one RETURN expression, it's
no faster. The problem with the existing code is that it fools the plancache
into deciding that it's worth re-planning the query every time, since
constant-folding with a concrete value for $2 allows elimination of at least
one sub-SELECT. In reality that's using the planner to do the equivalent of a
few runtime boolean tests, causing the function to run much slower than it
should. Splitting the AND/OR logic into separate plpgsql statements allows
each if-expression to acquire a static plan. Also, get rid of some uses of
obj_description() in favor of explicitly joining to pg_description, allowing
the joins to be optimized better. (Someday we might improve the
SQL-function-inlining logic enough that this happens automatically, but today
is not that day.) Together, these changes reduce the runtime of the opr_sanity
regression test by about a factor of two on one of my slower machines. They
don't seem to help as much on a fast machine, but this should at least benefit
the buildfarm.
https://git.postgresql.org/pg/commitdiff/c23844212d768b0423859437ca8189b89fd85250

- Fix possible null pointer dereference or invalid warning message. Thinko in
commit de4389712: this warning message references the wrong "LogicalRepWorker
*" variable. This would often result in a core dump, but if it didn't, the
message would show the wrong subscription OID. In passing, adjust the message
text to format a subscription OID similarly to how that's done elsewhere in
the function; and fix grammatical issues in some nearby messages. Per
Coverity testing.
https://git.postgresql.org/pg/commitdiff/12d11432b4db8a2ae665287e05f0f6868d35545e

- Sync our copy of the timezone library with IANA release tzcode2017b. zic no
longer mishandles some transitions in January 2038 when it attempts to work
around Qt bug 53071. This fixes a bug affecting Pacific/Tongatapu that was
introduced in zic 2016e. localtime.c now contains a workaround, useful when
loading a file generated by a buggy zic. There are assorted cosmetic changes
as well, notably relocation of a bunch of #defines.
https://git.postgresql.org/pg/commitdiff/e18b2c480da478f62781e06488cda56fe1b4e919

Fujii Masao pushed:

- Get rid of extern declarations of non-existent functions. Those extern
declartions were mistakenly added by commit 7c4f52409. Author: Petr Jelinek
https://git.postgresql.org/pg/commitdiff/cbc2270e3fcfae0831e4aa22f797f1dcac483d46

- Update copyright in recently added files. This commit also fixes copyright
line missed by the automated script. Author: Masahiko Sawada
https://git.postgresql.org/pg/commitdiff/7cc14ae9d8a46a6fac002cb5d7ef385fc6ccdeb4

- Set the priorities of all quorum synchronous standbys to 1. In quorum-based
synchronous replication, all the standbys listed in synchronous_standby_names
equally have chances to be chosen as synchronous standbys. So they should have
the same priority. However, previously, quorum standbys whose names appear
earlier in the list were given higher priority values though the difference of
those priority values didn't affect the selection of synchronous standbys.
Users could see those "meaningless" priority values in pg_stat_replication and
this was confusing. This commit gives all the quorum synchronous standbys the
same highest priority, i.e., 1, in order to remove such confusion. Author:
Fujii Masao Reviewed-by: Masahiko Sawada, Kyotaro Horiguchi Discussion:
http://postgr.es/m/CAHGQGwEKOw=SmPLxJzkBsH6wwDBgOnVz46QjHbtsiZ-d-2RGUg@mail.gmail.com
https://git.postgresql.org/pg/commitdiff/346199dcab4cfb2c023373fb3d859583b59810d7

- Fix typo in comment. Author: Masahiko Sawada
https://git.postgresql.org/pg/commitdiff/1f8b060121d654fbb08702752f63d251f5ce8088

- Fix bug so logical rep launcher saves correctly time of last startup of
worker. Previously the logical replication launcher stored the last timestamp
when it started the worker, in the local variable "last_start_time", in order
to check whether wal_retrive_retry_interval elapsed since the last startup of
worker. If it has elapsed, the launcher sees pg_subscription and starts new
worker if necessary. This is for limitting the startup of worker to once a
wal_retrieve_retry_interval. The bug was that the variable "last_start_time"
was defined and always initialized with 0 at the beginning of the launcher's
main loop. So even if it's set to the last timestamp in later phase of the
loop, it's always reset to 0. Therefore the launcher could not check correctly
whether wal_retrieve_retry_interval elapsed since the last startup. This
patch moves the variable "last_start_time" outside the main loop so that it
will not be reset. Reviewed-by: Petr Jelinek Discussion:
http://postgr.es/m/CAHGQGwGJrPO++XM4mFENAwpy1eGXKsGdguYv43GUgLgU-x8nTQ@mail.gmail.com
https://git.postgresql.org/pg/commitdiff/9f11fcec6624511ca85c1a6b049201be1fed6ef4

Bruce Momjian pushed:

- doc: update release doc markup instructions.
https://git.postgresql.org/pg/commitdiff/66fade8a0447093e6bd5a39ee6fec38790479664

- doc: first draft of Postgres 10 release notes.
https://git.postgresql.org/pg/commitdiff/1d8573ed5569c014b9fd32e42248e58c7c28386d

- doc: fix attribution of sequence item, order incompatibilities. Reported-by:
Andreas Karlsson
https://git.postgresql.org/pg/commitdiff/a0d932b39da7d7445dfc8da1e9979dd5cf4ea0a1

- doc: several minor PG 10 doc adjustments.
https://git.postgresql.org/pg/commitdiff/bba375eb9df037c73af3743521f2b44e6fdf8c3b

- doc: PG 10 fixes. Reported-by: Takayuki Tsunakawa
https://git.postgresql.org/pg/commitdiff/6e033c6a9639078501ac1830676ae062ee8a4aca

- doc: merge PG 10 release SysV item. Reported-by: Takayuki Tsunakawa
https://git.postgresql.org/pg/commitdiff/419a055454dd234b78925e605582caf0def51ecb

- doc: fix PG 10 release note doc markup.
https://git.postgresql.org/pg/commitdiff/d103e6718bfe4a1f50eb25b94db2cf9f84c69766

- doc: add Rafia Sabih to PG 10 release note item. Reported-by: Amit Kapila
https://git.postgresql.org/pg/commitdiff/b007b1af20c3b276d5804b11dc3e471c8045480b

- doc: move hash performance item into index section. The requirement to
rebuild pg_upgrade-ed hash indexes was kept in the incompatibilities section.
Reported-by: Amit Kapila
https://git.postgresql.org/pg/commitdiff/cef5dbbf2b8d8a5e7b536bcbf6d9066588c6173b

- doc: move hash info to new section and split out growth item. Reported-by:
Amit Kapila
https://git.postgresql.org/pg/commitdiff/45e3d8ae2b1d16f6e6453089424e9c3346fa35f8

- doc: PG 10 release note updates. Reported-by: David Rowley, Amit Langote,
Ashutosh Bapat
https://git.postgresql.org/pg/commitdiff/64f0f7cfc75d92910cadb07dbcce3844eec79c36

- doc: PG 10 release notes updates. Reported-by: Michael Paquier, Felix
Gerzaguet
https://git.postgresql.org/pg/commitdiff/cdd5bcad5ea6a2409e38e5c6e11e9901dae2d946

- doc: properly indent SGML tags in PG 10 release notes.
https://git.postgresql.org/pg/commitdiff/bf368fbee2a9ff1139b9331f025986ba68b8922a

- doc PG10: add commit 090010f2e and adjust EXPLAIN SUMMARY item. Reported-by:
Tels, Andres Freund
https://git.postgresql.org/pg/commitdiff/3640cf5e1c8a0ae6ac55bf6384de0521dc77facf

- doc: add PG 10 doc item about VACUUM truncation, 7e26e02ee. Reported-by:
Andres Freund
https://git.postgresql.org/pg/commitdiff/3d7741194a4688adea8404ab0a7bb57bac20232b

- doc: update PG 10 item about referencing many relations. Reported-by: Tom
Lane
https://git.postgresql.org/pg/commitdiff/ef0ba572b6e163328a2292bf9d6de7c919bd7cdd

- doc PG10: update EXPLAIN SUMMARY item. Reported-by: Tels
https://git.postgresql.org/pg/commitdiff/5f2b48d1dd17156c2021f9fa7c85d5c550bc2c6a

- doc PG10: add Rafia Sabih to parallel index scan item. Reported-by: Amit
Kapila
https://git.postgresql.org/pg/commitdiff/309191f66a947c5b63dd348a13aafa52b5847f94

- doc PG10: add commit for transition table item.
https://git.postgresql.org/pg/commitdiff/fda4fec578505a624e4c0f0244816ae5bc10a3d1

- doc PG10rel: adjust hash index commits and add parallel subquery.
Reported-by: Amit Kapila
https://git.postgresql.org/pg/commitdiff/f8ab08ad0e411139060bf943fa9931f7fbe97cde

- doc: PG10 release note typo fix. Reported-by: daniel.westermann
https://git.postgresql.org/pg/commitdiff/a311d2a04f849f76d33b59995097999a311a4c62

Peter Eisentraut pushed:

- postgres_fdw: Fix join push down with extensions. Objects in an extension are
shippable to a foreign server if the extension is part of the foreign server
definition's shippable extensions list. But this was not properly considered
in some cases when checking whether a join condition can be pushed to a
foreign server and the join condition uses an object from a shippable
extension. So the join would never be pushed down in those cases. So, the
list of extensions needs to be made available in fpinfo of the relation being
considered to be pushed down before any expressions are assessed for being
shippable. Fix foreign_join_ok() to do that for a join relation. The code to
save FDW options in fpinfo is scattered at multiple places. Bring all of that
together into functions apply_server_options(), apply_table_options(), and
merge_fdw_options(). David Rowley and Ashutosh Bapat, per report from David
Rowley
https://git.postgresql.org/pg/commitdiff/332bec1e6096679b04ec9717beb44a858495260f

- Wake up launcher when enabling a subscription. Otherwise one would have to
wait up to DEFAULT_NAPTIME_PER_CYCLE until the subscription worker is
considered for starting. There is a small race condition: If one enables a
subscription right after disabling it, the launcher might not have registered
the stopping when receiving the wakeup signal for the re-enabling. The start
will then not happen right away but after the full cycle time. Author:
Kyotaro HORIGUCHI <horiguchi(dot)kyotaro(at)lab(dot)ntt(dot)co(dot)jp>
https://git.postgresql.org/pg/commitdiff/a3f17b9c315512e9c116dbc243056aad8b372e18

- Fix various concurrency issues in logical replication worker launching. The
code was originally written with assumption that launcher is the only process
starting the worker. However that hasn't been true since commit 7c4f52409
which failed to modify the worker management code adequately. This patch adds
an in_use field to the LogicalRepWorker struct to indicate whether the worker
slot is being used and uses proper locking everywhere this flag is set or
read. However if the parent process dies while the new worker is starting and
the new worker fails to attach to shared memory, this flag would never get
cleared. We solve this rare corner case by adding a sort of garbage collector
for in_use slots. This uses another field in the LogicalRepWorker struct
named launch_time that contains the time when the worker was started. If any
request to start a new worker does not find free slot, we'll check for workers
that were supposed to start but took too long to actually do so, and reuse
their slot. In passing also fix possible race conditions when stopping a
worker that hasn't finished starting yet. Author: Petr Jelinek
<petr(dot)jelinek(at)2ndquadrant(dot)com> Reported-by: Fujii Masao
<masao(dot)fujii(at)gmail(dot)com>
https://git.postgresql.org/pg/commitdiff/de4389712206d2686e09ad8d6dd112dc4b6c6d42

- doc: ALTER SUBSCRIPTION documentation fixes. WITH is optional for REFRESH
PUBLICATION. Also, remove a spurious bracket and fix a punctuation. Author:
Euler Taveira <euler(at)timbira(dot)com(dot)br>
https://git.postgresql.org/pg/commitdiff/e315346d839ef27f9d5f2584f44de09f08573df2

- Fix query that gets remote relation info. Publisher relation can be
incorrectly chosen, if there are more than one relation in different schemas
with the same name. Author: Euler Taveira <euler(at)timbira(dot)com(dot)br>
https://git.postgresql.org/pg/commitdiff/61ecc90be624e699164a8d3efb291e267b711142

- Spelling fixes in code comments. Author: Euler Taveira <euler(at)timbira(dot)com(dot)br>
https://git.postgresql.org/pg/commitdiff/e495c1683f2c243f6769b34a009cf9c28526b555

- Fix typo in comment. Author: Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com>
https://git.postgresql.org/pg/commitdiff/6c9bd27aece5efd0ccef668828a44f59bd2c7a44

- Wait between tablesync worker restarts. Before restarting a tablesync worker
for the same relation, wait wal_retrieve_retry_interval (currently 5s by
default). This avoids restarting failing workers in a tight loop. We keep
the last start times in a hash table last_start_times that is separate from
the table_states list, because that list is cleared out on syscache
invalidation, which happens whenever a table finishes syncing. The hash table
is kept until all tables have finished syncing. A future project might be to
unify these two and keep everything in one data structure, but for now this is
a less invasive change to accomplish the original purpose. For the test
suite, set wal_retrieve_retry_interval to its minimum value, to not increase
the test suite run time. Reviewed-by: Petr Jelinek
<petr(dot)jelinek(at)2ndquadrant(dot)com> Reported-by: Masahiko Sawada
<sawada(dot)mshk(at)gmail(dot)com>
https://git.postgresql.org/pg/commitdiff/e3cf708016ca6045dc1cd5a0768cfecf17caf3d1

- psql: Support identity columns in sequence display. Where the footer for an
owned serial sequence would say "Owned by", put something analogous for a
sequence belonging to an identity column. Reported-by: Vitaly Burovoy
<vitaly(dot)burovoy(at)gmail(dot)com>
https://git.postgresql.org/pg/commitdiff/e4fddfd49241dc8dfda354993bad8d5518df1873

- doc: Fix typo in 9.6 release notes. Author: Huong Dangminh
<huo-dangminh(at)ys(dot)jp(dot)nec(dot)com>
https://git.postgresql.org/pg/commitdiff/bc920bee296ec4c1e8cd1598c71f21d80a59d351

Robert Haas pushed:

- Adjust outdated comment. Commit 5dfc198146b49ce7ecc8a1fc9d5e171fb75f6ba5
removed the only existing caller of hash_freeze, but left behind a comment
indicating that hash_freeze was still used. Adjust. Kyotaro Horiguchi
Discussion:
http://postgr.es/m/20170424.165541.230634914.horiguchi.kyotaro@lab.ntt.co.jp
https://git.postgresql.org/pg/commitdiff/914ae8d3cbbe480f8002a38deef4a5319970d2f4

- In load_relcache_init_file, initialize rd_pdcxt. Oversight noted by Gao Zeng
Qi. Discussion:
http://postgr.es/m/CAFmBtr1N3-SbepJbnGpaYp=jw-FvWMnYY7-bTtRgvjvbyB8YJA@mail.gmail.com
https://git.postgresql.org/pg/commitdiff/5e1ccd48442dbdbe812c95cd70cc1d3c1c960c4c

- Speed up dropping tables with many partitions. We need to lock the parent,
but we don't need a relcache entry for it. Gao Zeng Qi, reviewed by Amit
Langote Discussion:
http://postgr.es/m/CAFmBtr0ukqJjRJEhPWL5wt4rNMrJUUxggVAGXPR3SyYh3E+HDQ@mail.gmail.com
https://git.postgresql.org/pg/commitdiff/c1e0e7e1d790bf18c913e6a452dea811e858b554

- Fix crash when partitioned column specified twice. Amit Langote, reviewed by
Beena Emerson Discussion:
http://postgr.es/m/6ed23d3d-c09d-4cbc-3628-0a8a32f750f4@lab.ntt.co.jp
https://git.postgresql.org/pg/commitdiff/504c2205abc7de67386f9c95630f38ee15626f07

- Fix VALIDATE CONSTRAINT to consider NO INHERIT attribute. Currently, trying
to validate a NO INHERIT constraint on the parent will search for the
constraint in child tables (where it is not supposed to exist), wrongly
causing a "constraint does not exist" error. Amit Langote, per a report from
Hans Buschmann. Discussion:
http://postgr.es/m/20170421184012.24362.19@wrigleys.postgresql.org
https://git.postgresql.org/pg/commitdiff/6a4dda44e02bfd5148c13b00d8f81045a2e17f2c

Stephen Frost pushed:

- Allow ALTER TABLE ONLY on partitioned tables. There is no need to forbid
ALTER TABLE ONLY on partitioned tables, when no partitions exist yet. This
can be handy for users who are building up their partitioned table
independently and will create actual partitions later. In addition, this is
how pg_dump likes to operate in certain instances. Author: Amit Langote, with
some error message word-smithing by me
https://git.postgresql.org/pg/commitdiff/9139aa19423b736470f669e566f8ef6a7f19b801

- pg_get_partkeydef: return NULL for non-partitions. Our general rule for
pg_get_X(oid) functions is to simply return NULL when passed an invalid or
inappropriate OID. Teach pg_get_partkeydef to do this also, making it easier
for users to use this function when querying against tables with both
partitions and non-partitions (such as pg_class). As a concrete example, this
makes pg_dump's life a little easier. Author: Amit Langote
https://git.postgresql.org/pg/commitdiff/0c76c2463e8ab4cfd633ad8de259050e3f28b78f

- Remove unnecessairly duplicated gram.y productions. Declarative partitioning
duplicated the TypedTableElement productions, evidently to remove the need to
specify WITH OPTIONS when creating partitions. Instead, simply make WITH
OPTIONS optional in the TypedTableElement production and remove all of the
duplicate PartitionElement-related productions. This change simplifies the
syntax and makes WITH OPTIONS optional when adding defaults, constraints or
storage parameters to columns when creating either typed tables or partitions.
Also update pg_dump to no longer include WITH OPTIONS, since it's not
necessary, and update the documentation to reflect that WITH OPTIONS is now
optional.
https://git.postgresql.org/pg/commitdiff/b9a3ef55b253d885081c2d0e9dc45802cab71c7b

Simon Riggs pushed:

- Additional tests for subtransactions in recovery. Tests for normal and
prepared transactions Author: Nikhil Sontakke, placed in new test file by me
https://git.postgresql.org/pg/commitdiff/0352c15e5ab4f70c3ab2ac1d48d9f38a5dd11786

- Rework handling of subtransactions in 2PC recovery. The bug fixed by
0874d4f3e183757ba15a4b3f3bf563e0393dd9c2 caused us to question and rework the
handling of subtransactions in 2PC during and at end of recovery. Patch adds
checks and tests to ensure no further bugs. This effectively removes the
temporary measure put in place by 546c13e11b29a5408b9d6a6e3cca301380b47f7f.
Author: Simon Riggs Reviewed-by: Tom Lane, Michael Paquier Discussion:
http://postgr.es/m/CANP8+j+vvXmruL_i2buvdhMeVv5TQu0Hm2+C5N+kdVwHJuor8w@mail.gmail.com
https://git.postgresql.org/pg/commitdiff/49e928154978da2a5976628588fc545b726ad84a

Andres Freund pushed:

- Preserve required !catalog tuples while computing initial decoding snapshot.
The logical decoding machinery already preserved all the required catalog
tuples, which is sufficient in the course of normal logical decoding, but did
not guarantee that non-catalog tuples were preserved during computation of the
initial snapshot when creating a slot over the replication protocol. This
could cause a corrupted initial snapshot being exported. The time window for
issues is usually not terribly large, but on a busy server it's perfectly
possible to it hit it. Ongoing decoding is not affected by this bug. To
avoid increased overhead for the SQL API, only retain additional tuples when a
logical slot is being created over the replication protocol. To do so this
commit changes the signature of CreateInitDecodingContext(), but it seems
unlikely that it's being used in an extension, so that's probably ok. In a
drive-by fix, fix handling of ReplicationSlotsComputeRequiredXmin's
already_locked argument, which should only apply to ProcArrayLock, not
ReplicationSlotControlLock. Reported-By: Erik Rijkers Analyzed-By: Petr
Jelinek Author: Petr Jelinek, heavily editorialized by Andres Freund
Reviewed-By: Andres Freund Discussion:
https://postgr.es/m/9a897b86-46e1-9915-ee4c-da02e4ff6a95@2ndquadrant.com
Backport: 9.4, where logical decoding was introduced.
https://git.postgresql.org/pg/commitdiff/2bef06d51646058c6bb480fcdbffb1f0cc914fed

- Don't use on-disk snapshots for exported logical decoding snapshot. Logical
decoding stores historical snapshots on disk, so that logical decoding can
restart without having to reconstruct a snapshot from scratch (for which the
resources are not guaranteed to be present anymore). These serialized
snapshots were also used when creating a new slot via the walsender interface,
which can export a "full" snapshot (i.e. one that can read all tables, not
just catalog ones). The problem is that the serialized snapshots are only
useful for catalogs and not for normal user tables. Thus the use of such a
serialized snapshot could result in an inconsistent snapshot being exported,
which could lead to queries returning wrong data. This would only happen if
logical slots are created while another logical slot already exists. Author:
Petr Jelinek Reviewed-By: Andres Freund Discussion:
https://postgr.es/m/f37e975c-908f-858e-707f-058d3b1eb214@2ndquadrant.com
Backport: 9.4, where logical decoding was introduced.
https://git.postgresql.org/pg/commitdiff/56e19d938dd1457ae078304df1b9903509a0a2bf

- Don't build full initial logical decoding snapshot if NOEXPORT_SNAPSHOT.
Earlier commits (56e19d938dd14 and 2bef06d5164) make it cheaper to create a
logical slot if not exporting the initial snapshot. If NOEXPORT_SNAPSHOT is
specified, we can skip the overhead, not just when creating a slot via sql
(which can't export snapshots). As NOEXPORT_SNAPSHOT has only recently been
introduced, this shouldn't be backpatched.
https://git.postgresql.org/pg/commitdiff/ab9c43381ef7a7333086107847413e0b593854d0

Heikki Linnakangas pushed:

- Misc SCRAM code cleanups. * Move computation of SaltedPassword to a separate
function from scram_ClientOrServerKey(). This saves a lot of cycles in libpq,
by computing SaltedPassword only once per authentication. (Computing
SaltedPassword is expensive by design.) * Split scram_ClientOrServerKey() into
two functions. Improves readability, by making the calling code less verbose.
* Rename "server proof" to "server signature", to better match the
nomenclature used in RFC 5802. * Rename SCRAM_SALT_LEN to
SCRAM_DEFAULT_SALT_LEN, to make it more clear that the salt can be of any
length, and the constant only specifies how long a salt we use when we
generate a new verifier. Also rename SCRAM_ITERATIONS_DEFAULT to
SCRAM_DEFAULT_ITERATIONS, for consistency. These things caught my eye while
working on other upcoming changes.
https://git.postgresql.org/pg/commitdiff/d981074c24d2f1e4f44bc6d80e967e523ce64f50

== Pending Patches ==

Andrew Dunstan sent in a patch to fix TAP tests on the MSYS build system.

Jan Michálek sent in another revision of a patch to add mediawiki and rst
formats for tables in psql.

Haribabu Kommi sent in a patch to add build support for Visual Studio 2017.

Andrew Dunstan sent in two revisions of a patch to add vcregress support for
single TAP test.

Michaël Paquier sent in two revisions of a patch to fix an issue with logical
replication and PANIC during shutdown checkpoint in publisher.

Surafel Temesgen sent in a patch to add DELETE and UPDATE with LIMIT and ORDER
BY.

Daniel Gustafsson sent in two more revisions of a patch to help debug TAP test
failures.

Andres Freund sent in a POC patch to unify the SQL and replication grammars.

Fabien COELHO sent in two more revisions of a patch to fix pgbench TAP tests.

Rahila Syed sent in another revision of a patch to enable creating a default
partition for declaratively partitioned tables.

Ashutosh Bapat sent in another revision of a patch to speed up partition-wise
JOIN between declaratively partitioned tables.

Thomas Munro sent in a patch to allow creating transition tables for triggers on
foreign tables and views.

Thomas Munro sent in another revision of a patch to implement [[parallel]
shared] hash.

Amit Langote sent in a patch to fix VALIDATE CONSTRAINT to consider NO INHERIT
attribute.

Tom Lane sent in a patch to fix the inefficient shutdown of pg_basebackup.

Alexander Korotkov sent in another revision of a patch to implement incremental
sort.

Antonin Houska sent in two revisions of a patch to enable partition-wise
aggregation/grouping.

Konstantin Knizhnik sent in two more revisions of a patch to auto-prepare
queries similar to previous queries.

Amit Langote sent in two revisions of a patch to fire per-statement triggers of
partitioned tables.

Dagfinn Ilmari Mannsåker sent in a patch to fix some release note links.

Kyotaro HORIGUCHI sent in a patch to fix a bug where PQhost may return socket
dir for network connection.

Antonin Houska sent in another revision of a patch to do aggregate push-down.

Tom Lane sent in two revisions of a patch to fix a bug in conversion from EXISTS
to an inner join.

Kyotaro HORIGUCHI sent in a patch to fix a behavior change in .pgpass.

Petr Jelínek sent in another revision of a patch to add support for time based
lag tracking to logical replication.