Correctifs appliqués

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:
  • 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:
  • 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:
  • 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:
  • 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:
  • 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.
  • 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:
  • 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:
  • 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:
  • 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:
  • 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.
  • 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.
  • 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.

Fujii Masao pushed:

Bruce Momjian pushed:

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
  • 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 <>
  • 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 <> Reported-by: Fujii Masao <>
  • doc: ALTER SUBSCRIPTION documentation fixes. WITH is optional for REFRESH PUBLICATION. Also, remove a spurious bracket and fix a punctuation. Author: Euler Taveira <>
  • 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 <>
  • Spelling fixes in code comments. Author: Euler Taveira <>
  • Fix typo in comment. Author: Masahiko Sawada <>
  • 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 <> Reported-by: Masahiko Sawada <>
  • 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 <>
  • doc: Fix typo in 9.6 release notes. Author: Huong Dangminh <>

Robert Haas pushed:

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
  • 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
  • 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.

Simon Riggs pushed:

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: Backport: 9.4, where logical decoding was introduced.
  • 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: Backport: 9.4, where logical decoding was introduced.
  • 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.

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.

Correctifs en attente

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.