# PostgreSQL Weekly News - June 13, 2021 [Person of the week](https://postgresql.life/post/tom_kincaid/)
# PostgreSQL Product News pg_statement_rollback v1.2, an extension that adds server side transaction with rollback at statement level, [released](https://github.com/lzlabs/pg_statement_rollback/releases/tag/v1.2). # PostgreSQL Jobs for June [https://archives.postgresql.org/pgsql-jobs/2021-06/](https://archives.postgresql.org/pgsql-jobs/2021-06/) # PostgreSQL in the News Planet PostgreSQL: [https://planet.postgresql.org/](https://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 [email protected]. # Applied Patches Tomáš Vondra pushed: - Add transformed flag to `nodes/*funcs.c` for CREATE STATISTICS. Commit a4d75c86bf added a new flag, tracking if the statement was processed by transformStatsStmt(), but failed to add this flag to `nodes/*funcs.c`. Catversion bump, due to adding a flag to copy/equal/out functions. Reported-by: Noah Misch Discussion: [https://postgr.es/m/ad7891d2-e90c-b446-9fe2-7419143847d7%40enterprisedb.com](https://postgr.es/m/ad7891d2-e90c-b446-9fe2-7419143847d7%40enterprisedb.com) [https://git.postgresql.org/pg/commitdiff/d57ecebd128cdf2f4844a2ea4d35ff28d7d69be8](https://git.postgresql.org/pg/commitdiff/d57ecebd128cdf2f4844a2ea4d35ff28d7d69be8) - Fix pg_visibility regression failure with CLOBBER_CACHE_ALWAYS. Commit 8e03eb92e9 reverted a bit too much code, reintroducing one of the issues fixed by 39b66a91bd - a page might have been left partially empty after relcache invalidation. Reported-By: Tom Lane Author: Masahiko Sawada Discussion: [https://postgr.es/m/[email protected]](https://postgr.es/m/[email protected]) Discussion: [https://postgr.es/m/CAD21AoA%3D%3Df2VSw3c-Cp_y%3DWLKHMKc1D6s7g3YWsCOvgaYPpJcg%40mail.gmail.com](https://postgr.es/m/CAD21AoA%3D%3Df2VSw3c-Cp_y%3DWLKHMKc1D6s7g3YWsCOvgaYPpJcg%40mail.gmail.com) [https://git.postgresql.org/pg/commitdiff/d1f0aa7696917213485c03b076b573497a535076](https://git.postgresql.org/pg/commitdiff/d1f0aa7696917213485c03b076b573497a535076) - Adjust batch size in postgres_fdw to not use too many parameters. The FE/BE protocol identifies parameters with an Int16 index, which limits the maximum number of parameters per query to 65535. With batching added to postges_fdw this limit is much easier to hit, as the whole batch is essentially a single query, making this error much easier to hit. The failures are a bit unpredictable, because it also depends on the number of columns in the query. So instead of just failing, this patch tweaks the batch_size to not exceed the maximum number of parameters. Reported-by: Hou Zhijie <[email protected]> Reviewed-by: Bharath Rupireddy <[email protected]> Discussion: [https://postgr.es/m/OS0PR01MB571603973C0AC2874AD6BF2594299%40OS0PR01MB5716.jpnprd01.prod.outlook.com](https://postgr.es/m/OS0PR01MB571603973C0AC2874AD6BF2594299%40OS0PR01MB5716.jpnprd01.prod.outlook.com) [https://git.postgresql.org/pg/commitdiff/cb92703384e2bb3fa0a690e5dbb95ad333c2b44c](https://git.postgresql.org/pg/commitdiff/cb92703384e2bb3fa0a690e5dbb95ad333c2b44c) - Optimize creation of slots for FDW bulk inserts. Commit b663a41363 introduced bulk inserts for FDW, but the handling of tuple slots turned out to be problematic for two reasons. Firstly, the slots were re-created for each individual batch. Secondly, all slots referenced the same tuple descriptor - with reasonably small batches this is not an issue, but with large batches this triggers O(N^2) behavior in the resource owner code. These two issues work against each other - to reduce the number of times a slot has to be created/dropped, larger batches are needed. However, the larger the batch, the more expensive the resource owner gets. For practical batch sizes (100 - 1000) this would not be a big problem, as the benefits (latency savings) greatly exceed the resource owner costs. But for extremely large batches it might be much worse, possibly even losing with non-batching mode. Fixed by initializing tuple slots only once (and reusing them across batches) and by using a new tuple descriptor copy for each slot. Discussion: [https://postgr.es/m/ebbbcc7d-4286-8c28-0272-61b4753af761%40enterprisedb.com](https://postgr.es/m/ebbbcc7d-4286-8c28-0272-61b4753af761%40enterprisedb.com) [https://git.postgresql.org/pg/commitdiff/b676ac443b6a83558d4701b2dd9491c0b37e17c4](https://git.postgresql.org/pg/commitdiff/b676ac443b6a83558d4701b2dd9491c0b37e17c4) Tom Lane pushed: - Fix inconsistent equalfuncs.c behavior for FuncCall.funcformat. Other equalfuncs.c checks on CoercionForm fields use `COMPARE_COERCIONFORM_FIELD` (which makes them no-ops), but commit 40c24bfef neglected to make `_equalFuncCall` do likewise. Fix that. This is only strictly correct if FuncCall.funcformat has no semantic effect, instead just determining ruleutils.c display formatting. 40c24bfef added a couple of checks in parse analysis that could break that rule; but on closer inspection, they're redundant, so just take them out again. Per report from Noah Misch. Discussion: [https://postgr.es/m/[email protected]](https://postgr.es/m/[email protected]) [https://git.postgresql.org/pg/commitdiff/a65e9f3f1405b786673feec131879843432bf9a6](https://git.postgresql.org/pg/commitdiff/a65e9f3f1405b786673feec131879843432bf9a6) - Fix incautious handling of possibly-miscoded strings in client code. An incorrectly-encoded multibyte character near the end of a string could cause various processing loops to run past the string's terminating NUL, with results ranging from no detectable issue to a program crash, depending on what happens to be in the following memory. This isn't an issue in the server, because we take care to verify the encoding of strings before doing any interesting processing on them. However, that lack of care leaked into client-side code which shouldn't assume that anyone has validated the encoding of its input. Although this is certainly a bug worth fixing, the PG security team elected not to regard it as a security issue, primarily because any untrusted text should be sanitized by PQescapeLiteral or the like before being incorporated into a SQL or psql command. (If an app fails to do so, the same technique can be used to cause SQL injection, with probably much more dire consequences than a mere client-program crash.) Those functions were already made proof against this class of problem, cf CVE-2006-2313. To fix, invent PQmblenBounded() which is like PQmblen() except it won't return more than the number of bytes remaining in the string. In HEAD we can make this a new libpq function, as PQmblen() is. It seems imprudent to change libpq's API in stable branches though, so in the back branches define PQmblenBounded as a macro in the files that need it. (Note that just changing PQmblen's behavior would not be a good idea; notably, it would completely break the escaping functions' defense against this exact problem. So we just want a version for those callers that don't have any better way of handling this issue.) Per private report from houjingyi. Back-patch to all supported branches. [https://git.postgresql.org/pg/commitdiff/42f94f56bf9559f0a3cf5f3ffde50655834694ee](https://git.postgresql.org/pg/commitdiff/42f94f56bf9559f0a3cf5f3ffde50655834694ee) - Stabilize contrib/seg regression test. If autovacuum comes along just after we fill table test_seg with some data, it will update the stats to the point where we prefer a plain indexscan over a bitmap scan, breaking the expected output (as well as the point of the test case). To fix, just force a bitmap scan to be chosen here. This has evidently been wrong since commit de1d042f5. It's not clear why we just recently saw any buildfarm failures due to it; but prairiedog has failed twice on this test in the past week. Hence, backpatch to v11 where this test case came in. [https://git.postgresql.org/pg/commitdiff/d16ebfbff74b30c83a4669a1df318cacfa7e52ca](https://git.postgresql.org/pg/commitdiff/d16ebfbff74b30c83a4669a1df318cacfa7e52ca) - Don't crash on empty statements in SQL-standard function bodies. gram.y should discard NULL pointers (empty statements) when assembling a routine_body_stmt_list, as it does for other sorts of statement lists. Julien Rouhaud and Tom Lane, per report from Noah Misch. Discussion: [https://postgr.es/m/[email protected]](https://postgr.es/m/[email protected]) [https://git.postgresql.org/pg/commitdiff/bfeede9fa464ab707eb5ac5704742bf78bd7ac9e](https://git.postgresql.org/pg/commitdiff/bfeede9fa464ab707eb5ac5704742bf78bd7ac9e) - Avoid misbehavior when persisting a non-stable cursor. PersistHoldablePortal has long assumed that it should store the entire output of the query-to-be-persisted, which requires rewinding and re-reading the output. This is problematic if the query is not stable: we might get different row contents, or even a different number of rows, which'd confuse the cursor state mightily. In the case where the cursor is NO SCROLL, this is very easy to solve: just store the remaining query output, without any rewinding, and tweak the portal's cursor state to match. Aside from removing the semantic problem, this could be significantly more efficient than storing the whole output. If the cursor is scrollable, there's not much we can do, but it was already the case that scrolling a volatile query's result was pretty unsafe. We can just document more clearly that getting correct results from that is not guaranteed. There are already prohibitions in place on using SCROLL with FOR UPDATE/SHARE, which is one way for a SELECT query to have non-stable results. We could imagine prohibiting SCROLL when the query contains volatile functions, but that would be expensive to enforce. Moreover, it could break applications that work just fine, if they have functions that are in fact stable but the user neglected to mark them so. So settle for documenting the hazard. While this problem has existed in some guise for a long time, it got a lot worse in v11, which introduced the possibility of persisting plpgsql cursors (perhaps implicit ones) even when they violate the rules for what can be marked WITH HOLD. Hence, I've chosen to back-patch to v11 but not further. Per bug #17050 from Алексей Булгаков. Discussion: [https://postgr.es/m/[email protected]](https://postgr.es/m/[email protected]) [https://git.postgresql.org/pg/commitdiff/ba2c6d6cec000f0aeaeda4d56a23a335f6164860](https://git.postgresql.org/pg/commitdiff/ba2c6d6cec000f0aeaeda4d56a23a335f6164860) - Force NO SCROLL for plpgsql's implicit cursors. Further thought about bug #17050 suggests that it's a good idea to use CURSOR_OPT_NO_SCROLL for the implicit cursor opened by a plpgsql FOR-over-query loop. This ensures that, if somebody commits inside the loop, PersistHoldablePortal won't try to rewind and re-read the cursor. While we'd have selected NO_SCROLL anyway if FOR UPDATE/SHARE appears in the query, there are other hazards with volatile functions; and in any case, it's silly to expend effort storing rows that we know for certain won't be needed. (While here, improve the comment in exec_run_select, which was a bit confused about the rationale for when we can use parallel mode. Cursor operations aren't a hazard for nameless portals.) This wasn't an issue until v11, which introduced the possibility of persisting such cursors. Hence, back-patch to v11. Per bug #17050 from Алексей Булгаков. Discussion: [https://postgr.es/m/[email protected]](https://postgr.es/m/[email protected]) [https://git.postgresql.org/pg/commitdiff/be90098907475f3cfff7dc6d590641b9c2dae081](https://git.postgresql.org/pg/commitdiff/be90098907475f3cfff7dc6d590641b9c2dae081) - Avoid ECPG test failures in some GSS-capable environments. Buildfarm member hamerkop has been reporting that two cases in connect/test5.pgc show different error messages than the test expects, because since commit ffa2e4670 libpq's connection failure messages are exposing the fact that a GSS-encrypted connection was attempted and failed. That's pretty interesting information in itself, and I certainly don't wish to shoot the messenger, but we need to do something to stabilize the ECPG results. For the second of these two failure cases, we can add the gssencmode=disable option to prevent the discrepancy. However, that solution is problematic for the first failure, because the only unique thing about that case is that it's testing a completely-omitted connection target; there's noplace to add the option without defeating the point of the test case. After some thrashing around with alternative fixes that turned out to have undesirable side-effects, the most workable answer is just to give up and remove that test case. Perhaps we can revert this later, if we figure out why the GSS code is misbehaving in hamerkop's environment. Thanks to Michael Paquier for exploration of alternatives. Discussion: [https://postgr.es/m/[email protected]](https://postgr.es/m/[email protected]) [https://git.postgresql.org/pg/commitdiff/9bb5eecce645dd72853e3ed262bef7bf11cae566](https://git.postgresql.org/pg/commitdiff/9bb5eecce645dd72853e3ed262bef7bf11cae566) - Shut down EvalPlanQual machinery when LockRows node reaches the end. Previously, we left the EPQ sub-executor alone until ExecEndLockRows. This caused any buffer pins or other resources that it might hold to remain held until ExecutorEnd, which in some code paths means that they are held till the Portal is closed. That can cause user-visible problems, such as blocking VACUUM; and it's unlike the behavior of ordinary table-scanning nodes, which will have released all buffer pins by the time they return an EOF indication. We can make LockRows work more like other plan nodes by calling EvalPlanQualEnd just before returning NULL. We still need to call it in ExecEndLockRows in case the node was not run to completion, but in the normal case the second call does nothing and costs little. Per report from Yura Sokolov. In principle this is a longstanding bug, but in view of the lack of other complaints and the low severity of the consequences, I chose not to back-patch. Discussion: [https://postgr.es/m/[email protected]](https://postgr.es/m/[email protected]) [https://git.postgresql.org/pg/commitdiff/bb4aed46a5aeb00d2f1d8b8160feed339f4eaf12](https://git.postgresql.org/pg/commitdiff/bb4aed46a5aeb00d2f1d8b8160feed339f4eaf12) - Rearrange logrep worker's snapshot handling some more. It turns out that worker.c's code path for TRUNCATE was also careless about establishing a snapshot while executing user-defined code, allowing the checks added by commit 84f5c2908 to fail when a trigger is fired in that context. We could just wrap Push/PopActiveSnapshot around the truncate call, but it seems better to establish a policy of holding a snapshot throughout execution of a replication step. To help with that and possible future requirements, replace the previous ensure_transaction calls with pairs of begin/end_replication_step calls. Per report from Mark Dilger. Back-patch to v11, like the previous changes. Discussion: [https://postgr.es/m/[email protected]](https://postgr.es/m/[email protected]) [https://git.postgresql.org/pg/commitdiff/3a09d75b4f6cabc8331e228b6988dbfcd9afdfbe](https://git.postgresql.org/pg/commitdiff/3a09d75b4f6cabc8331e228b6988dbfcd9afdfbe) - Reconsider the handling of procedure OUT parameters. Commit 2453ea142 redefined pg_proc.proargtypes to include the types of OUT parameters, for procedures only. While that had some advantages for implementing the SQL-spec behavior of DROP PROCEDURE, it was pretty disastrous from a number of other perspectives. Notably, since the primary key of pg_proc is name + proargtypes, this made it possible to have multiple procedures with identical names + input arguments and differing output argument types. That would make it impossible to call any one of the procedures by writing just NULL (or "?", or any other data-type-free notation) for the output argument(s). The change also seems likely to cause grave confusion for client applications that examine pg_proc and expect the traditional definition of proargtypes. Hence, revert the definition of proargtypes to what it was, and undo a number of complications that had been added to support that. To support the SQL-spec behavior of DROP PROCEDURE, when there are no argmode markers in the command's parameter list, we perform the lookup both ways (that is, matching against both proargtypes and proallargtypes), succeeding if we get just one unique match. In principle this could result in ambiguous-function failures that would not happen when using only one of the two rules. However, overloading of procedure names is thought to be a pretty rare usage, so this shouldn't cause many problems in practice. Postgres-specific code such as pg_dump can defend against any possibility of such failures by being careful to specify argmodes for all procedure arguments. This also fixes a few other bugs in the area of CALL statements with named parameters, and improves the documentation a little. catversion bump forced because the representation of procedures with OUT arguments changes. Discussion: [https://postgr.es/m/[email protected]](https://postgr.es/m/[email protected]) [https://git.postgresql.org/pg/commitdiff/e56bce5d43789cce95d099554ae9593ada92b3b7](https://git.postgresql.org/pg/commitdiff/e56bce5d43789cce95d099554ae9593ada92b3b7) - Fix multiple crasher bugs in partitioned-table replication logic. apply_handle_tuple_routing(), having detected and reported that the tuple it needed to update didn't exist, tried to update that tuple anyway, leading to a null-pointer dereference. logicalrep_partition_open() failed to ensure that the LogicalRepPartMapEntry it built for a partition was fully independent of that for the partition root, leading to trouble if the root entry was later freed or rebuilt. Meanwhile, on the publisher's side, pgoutput_change() sometimes attempted to apply execute_attr_map_tuple() to a NULL tuple. The first of these was reported by Sergey Bernikov in bug #17055; I found the other two while developing some test cases for this sadly under-tested code. Diagnosis and patch for the first issue by Amit Langote; patches for the others by me; new test cases by me. Back-patch to v13 where this logic came in. Discussion: [https://postgr.es/m/[email protected]](https://postgr.es/m/[email protected]) [https://git.postgresql.org/pg/commitdiff/ab55d742eb7162c22ee60f1e15e07d2a60063c4e](https://git.postgresql.org/pg/commitdiff/ab55d742eb7162c22ee60f1e15e07d2a60063c4e) - Don't use Asserts to check for violations of replication protocol. Using an Assert to check the validity of incoming messages is an extremely poor decision. In a debug build, it should not be that easy for a broken or malicious remote client to crash the logrep worker. The consequences could be even worse in non-debug builds, which will fail to make such checks at all, leading to who-knows-what misbehavior. Hence, promote every Assert that could possibly be triggered by wrong or out-of-order replication messages to a full test-and-ereport. To avoid bloating the set of messages the translation team has to cope with, establish a policy that replication protocol violation error reports don't need to be translated. Hence, all the new messages here use errmsg_internal(). A couple of old messages are changed likewise for consistency. Along the way, fix some non-idiomatic or outright wrong uses of hash_search(). Most of these mistakes are new with the "streaming replication" patch (commit 464824323), but a couple go back a long way. Back-patch as appropriate. Discussion: [https://postgr.es/m/[email protected]](https://postgr.es/m/[email protected]) [https://git.postgresql.org/pg/commitdiff/fe6a20ce54cbbb6fcfe9f6675d563af836ae799a](https://git.postgresql.org/pg/commitdiff/fe6a20ce54cbbb6fcfe9f6675d563af836ae799a) - Ensure pg_filenode_relation(0, 0) returns NULL. Previously, a zero value for the relfilenode resulted in a confusing error message about "unexpected duplicate". This function returns NULL for other invalid relfilenode values, so zero should be treated likewise. It's been like this all along, so back-patch to all supported branches. Justin Pryzby Discussion: [https://postgr.es/m/[email protected]](https://postgr.es/m/[email protected]) [https://git.postgresql.org/pg/commitdiff/1250aad42520fd5a3db68d6381997b7e1f9bb4aa](https://git.postgresql.org/pg/commitdiff/1250aad42520fd5a3db68d6381997b7e1f9bb4aa) - Restore robustness of TAP tests that wait for postmaster restart. Several TAP tests use poll_query_until() to wait for the postmaster to restart. They were checking to see if a trivial query (e.g. "SELECT 1") succeeds. However, that's problematic in the wake of commit 11e9caff8, because now that we feed said query to psql via stdin, we risk IPC::Run whining about a SIGPIPE failure if psql quits before reading the query. Hence, we can't use a nonempty query in cases where we need to wait for connection failures to stop happening. Per the precedent of commits c757a3da0 and 6d41dd045, we can pass "undef" as the query in such cases to ensure that IPC::Run has nothing to write. However, then we have to say that the expected output is empty, and this exposes a deficiency in poll_query_until: if psql fails altogether and returns empty stdout, poll_query_until will treat that as a success! That's because, contrary to its documentation, it makes no actual check for psql failure, looking neither at the exit status nor at stderr. To fix that, adjust poll_query_until to insist on empty stderr as well as a stdout match. (I experimented with checking exit status instead, but it seems that psql often does exit(1) in cases that we need to consider successes. That might be something to fix someday, but it would be a non-back-patchable behavior change.) Back-patch to v10. The test cases needing this exist only as far back as v11, but it seems wise to keep poll_query_until's behavior the same in v10, in case we back-patch another such test case in future. (9.6 does not currently need this change, because in that branch poll_query_until can't be told to accept empty stdout as a success case.) Per assorted buildfarm failures, mostly on hoverfly. Discussion: [https://postgr.es/m/caa4ek1+zm6l4qsa1xmvxy_qqwwdumqkos1+hwvl8qcyebga...@mail.gmail.com](https://postgr.es/m/caa4ek1+zm6l4qsa1xmvxy_qqwwdumqkos1+hwvl8qcyebga...@mail.gmail.com) [https://git.postgresql.org/pg/commitdiff/f452aaf7d4a96cfcecd6c60ccd294ffe7b8ea088](https://git.postgresql.org/pg/commitdiff/f452aaf7d4a96cfcecd6c60ccd294ffe7b8ea088) Etsuro Fujita pushed: - Fix rescanning of async-aware Append nodes. In cases where run-time pruning isn't required, the synchronous and asynchronous subplans for an async-aware Append node determined using classify_matching_subplans() should be re-used when rescanning the node, but the previous code re-determined them using that function repeatedly each time when rescanning the node, leading to incorrect results in a normal build and an Assert failure in an Assert-enabled build as that function doesn't assume that it's called repeatedly in such cases. Fix the code as mentioned above. My oversight in commit 27e1f1456. While at it, initialize async-related pointers/variables to NULL/zero explicitly in ExecInitAppend() and ExecReScanAppend(), just to be sure. (The variables would have been set to zero before we get to the latter function, but let's do so.) Reviewed-by: Kyotaro Horiguchi Discussion: [https://postgr.es/m/CAPmGK16Q4B2_KY%2BJH7rb7wQbw54AUprp7TMekGTd2T1B62yysQ%40mail.gmail.com](https://postgr.es/m/CAPmGK16Q4B2_KY%2BJH7rb7wQbw54AUprp7TMekGTd2T1B62yysQ%40mail.gmail.com) [https://git.postgresql.org/pg/commitdiff/f3baaf28a6da588987b94a05a725894805c3eae9](https://git.postgresql.org/pg/commitdiff/f3baaf28a6da588987b94a05a725894805c3eae9) - Doc: Further update documentation for asynchronous execution. Add a note about asynchronous execution by postgres_fdw when applied to Append nodes that contain synchronous subplan(s) as well. Follow-up for commit 27e1f1456. Andrey Lepikhov and Etsuro Fujita Discussion: [https://postgr.es/m/58fa2aa5-07f5-80b5-59a1-fec8a349fee7%40postgrespro.ru](https://postgr.es/m/58fa2aa5-07f5-80b5-59a1-fec8a349fee7%40postgrespro.ru) [https://git.postgresql.org/pg/commitdiff/eab81953682d5087295afb911c93f36cb1533bd9](https://git.postgresql.org/pg/commitdiff/eab81953682d5087295afb911c93f36cb1533bd9) Amit Kapila pushed: - Remove two_phase variable from CreateReplicationSlotCmd struct. Commit 19890a064e added the option to enable two_phase commits via pg_create_logical_replication_slot but didn't extend the support of same in replication protocol. However, by mistake, it added the two_phase variable in CreateReplicationSlotCmd which is required only when we extend the replication protocol. Reported-by: Jeff Davis Author: Ajin Cherian Reviewed-by: Amit Kapila Discussion: [https://postgr.es/m/[email protected]](https://postgr.es/m/[email protected]) [https://git.postgresql.org/pg/commitdiff/be57f21650d36449ec34a67b2d9af71126a663b3](https://git.postgresql.org/pg/commitdiff/be57f21650d36449ec34a67b2d9af71126a663b3) Michaël Paquier pushed: - Fix portability issue in test indirect_toast. When run on a server using default_toast_compression set to LZ4, this test would fail because of a consistency issue with the order of the tuples treated. LZ4 causes one tuple to be stored inline instead of getting externalized. As the goal of this test is to check after data stored externally, stick to pglz as the compression algorithm used, so as all data of this test is stored the way it should. Analyzed-by: Dilip Kumar Discussion: [https://postgr.es/m/[email protected]](https://postgr.es/m/[email protected]) [https://git.postgresql.org/pg/commitdiff/68a6d8a87006ba727d9662ec84c7a3d9de402df0](https://git.postgresql.org/pg/commitdiff/68a6d8a87006ba727d9662ec84c7a3d9de402df0) - Reorder superuser check in pg_log_backend_memory_contexts(). The use of this function is limited to superusers and the code includes a hardcoded check for that. However, the code would look for the PGPROC entry to signal for the memory dump before checking if the user is a superuser or not, which does not make sense if we know that an error will be returned. Note that the code would let one know if a process was a PostgreSQL process or not even for non-authorized users, which is not the case now, but this avoids taking ProcArrayLock that will most likely finish by being unnecessary. Thanks to Julien Rouhaud and Tom Lane for the discussion. Discussion: [https://postgr.es/m/[email protected]](https://postgr.es/m/[email protected]) [https://git.postgresql.org/pg/commitdiff/4e47b02834827fa700627290fae02f89a450368c](https://git.postgresql.org/pg/commitdiff/4e47b02834827fa700627290fae02f89a450368c) - Fix inconsistencies in psql --help=commands. The set of subcommands supported by \dAp, \do and \dy was described incorrectly in psql's --help. The documentation was already consistent with the code. Reported-by: inoas, from IRC Author: Matthijs van der Vleuten Reviewed-by: Neil Chen Discussion: [https://postgr.es/m/[email protected]](https://postgr.es/m/[email protected]) Backpatch-through: 9.6 [https://git.postgresql.org/pg/commitdiff/845cad4d51cb12a34ea012dfe58af5ef490384fc](https://git.postgresql.org/pg/commitdiff/845cad4d51cb12a34ea012dfe58af5ef490384fc) - Improve psql tab completion for options of subcriptions and publications. The list of options provided by the tab completion was outdated for the following commands: - ALTER SUBSCRIPTION - CREATE SUBSCRIPTION - ALTER PUBLICATION - CREATE PUBLICATION Author: Vignesh C Reviewed-by: Bharath Rupireddy Discussion: [https://postgr.es/m/CALDaNm18oHDFu6SFCHE=zbio153fx7e-l1mg0yyscbadv--...@mail.gmail.com](https://postgr.es/m/CALDaNm18oHDFu6SFCHE=zbio153fx7e-l1mg0yyscbadv--...@mail.gmail.com) [https://git.postgresql.org/pg/commitdiff/d08237b5b494f96e72220bcef36a14a642969f16](https://git.postgresql.org/pg/commitdiff/d08237b5b494f96e72220bcef36a14a642969f16) - Improve log pattern detection in recently-added TAP tests. ab55d74 has introduced some tests with rows found as missing in logical replication subscriptions for partitioned tables, relying on a logic with a lookup of the logs generated, scanning the whole file. This commit makes the logic more precise, by scanning the logs only from the position before the key queries are run to the position where we check for the logs. This will reduce the risk of issues with log patterns overlapping with each other if those tests get more complicated in the future. Per discussion with Tom Lane. Discussion: [https://postgr.es/m/[email protected]](https://postgr.es/m/[email protected]) Backpatch-through: 13 [https://git.postgresql.org/pg/commitdiff/bfd96b7a3dc26a8384d4185d274573fb6a46b033](https://git.postgresql.org/pg/commitdiff/bfd96b7a3dc26a8384d4185d274573fb6a46b033) - Simplify some code in getObjectTypeDescription(). This routine is designed to never return an empty description or NULL, providing description fallbacks even if missing objects are accepted, but it included a code path where this was considered possible. All the callers of this routine already consider NULL as not possible, so change a bit the code to map with the assumptions of the callers, and add more comments close to the callers of this routine to outline the behavior expected. This code is new as of 2a10fdc, so no backpatch is needed. Discussion: [https://postgr.es/m/[email protected]](https://postgr.es/m/[email protected]) [https://git.postgresql.org/pg/commitdiff/b56b83aa0d6e044cf38d553f7a87f4b84708cac6](https://git.postgresql.org/pg/commitdiff/b56b83aa0d6e044cf38d553f7a87f4b84708cac6) - Ignore more environment variables in pg_regress.c. This is similar to the work done in 8279f68 for TestLib.pm, where environment variables set may cause unwanted failures if using a temporary installation with pg_regress. The list of variables reset is adjusted in each stable branch depending on what is supported. Comments are added to remember that the lists in TestLib.pm and pg_regress.c had better be kept in sync. Reviewed-by: Álvaro Herrera Discussion: [https://postgr.es/m/[email protected]](https://postgr.es/m/[email protected]) Backpatch-through: 9.6 [https://git.postgresql.org/pg/commitdiff/a9e0b3b08fe38d5e31f03ea96859ff5e413d4a38](https://git.postgresql.org/pg/commitdiff/a9e0b3b08fe38d5e31f03ea96859ff5e413d4a38) Peter Eisentraut pushed: - Add `_outTidRangePath()`. We have outNode() coverage for all path nodes, but this one was missed when it was added. [https://git.postgresql.org/pg/commitdiff/3bb309be7533e153d86390642e8a2d054bbe82c8](https://git.postgresql.org/pg/commitdiff/3bb309be7533e153d86390642e8a2d054bbe82c8) - libpq: Fix SNI host handling. Fix handling of NULL host name (possibly by using hostaddr). It previously crashed. Also, we should look at connhost, not pghost, to handle multi-host specifications. Also remove an unnecessary SSL_CTX_free(). Reported-by: Jacob Champion <[email protected]> Reviewed-by: Michael Paquier <[email protected]> Discussion: [https://www.postgresql.org/message-id/[email protected]](https://www.postgresql.org/message-id/[email protected]) [https://git.postgresql.org/pg/commitdiff/37e1cce4ddf0be362e3093cee55493aee41bc423](https://git.postgresql.org/pg/commitdiff/37e1cce4ddf0be362e3093cee55493aee41bc423) - Add some const decorations. One of these functions is new in PostgreSQL 14; might as well start it out right. [https://git.postgresql.org/pg/commitdiff/b29fa951ec519bdde153465e2caa6c0b7b3bcfc3](https://git.postgresql.org/pg/commitdiff/b29fa951ec519bdde153465e2caa6c0b7b3bcfc3) Bruce Momjian pushed: - doc: update release note item about the v2 wire protocol. Protocol v2 was last used in PG 7.3, not 7.2. Reported-by: Tatsuo Ishii Discussion: [https://postgr.es/m/[email protected]](https://postgr.es/m/[email protected]) [https://git.postgresql.org/pg/commitdiff/444302ed56273e4c4006a9be319e60fa12a90346](https://git.postgresql.org/pg/commitdiff/444302ed56273e4c4006a9be319e60fa12a90346) - docs: fix incorrect indenting in PG 14 relnotes. [https://git.postgresql.org/pg/commitdiff/0725913982e5e06895a32a9eeea2c59a13978927](https://git.postgresql.org/pg/commitdiff/0725913982e5e06895a32a9eeea2c59a13978927) - doc: remove extra right angle bracket in PG 14 relnotes. Reported-by: Justin Pryzby [https://git.postgresql.org/pg/commitdiff/d120e66fac87f768ea2289d2d923d0ee4262ec0f](https://git.postgresql.org/pg/commitdiff/d120e66fac87f768ea2289d2d923d0ee4262ec0f) Robert Haas pushed: - Fix corner case failure of new standby to follow new primary. This only happens if (1) the new standby has no WAL available locally, (2) the new standby is starting from the old timeline, (3) the promotion happened in the WAL segment from which the new standby is starting, (4) the timeline history file for the new timeline is available from the archive but the WAL files for are not (i.e. this is a race), (5) the WAL files for the new timeline are available via streaming, and (6) recovery_target_timeline='latest'. Commit ee994272ca50f70b53074f0febaec97e28f83c4e introduced this logic and was an improvement over the previous code, but it mishandled this case. If recovery_target_timeline='latest' and restore_command is set, validateRecoveryParameters() can change recoveryTargetTLI to be different from receiveTLI. If streaming is then tried afterward, expectedTLEs gets initialized with the history of the wrong timeline. It's supposed to be a list of entries explaining how to get to the target timeline, but in this case it ends up with a list of entries explaining how to get to the new standby's original timeline, which isn't right. Dilip Kumar and Robert Haas, reviewed by Kyotaro Horiguchi. Discussion: [http://postgr.es/m/CAFiTN-sE-jr=lb8jquxeqikd-ux+jhixyh4ydizmpedgqku...@mail.gmail.com](http://postgr.es/m/CAFiTN-sE-jr=lb8jquxeqikd-ux+jhixyh4ydizmpedgqku...@mail.gmail.com) [https://git.postgresql.org/pg/commitdiff/caba8f0d43fb679c6f9643456080408a6bc370e8](https://git.postgresql.org/pg/commitdiff/caba8f0d43fb679c6f9643456080408a6bc370e8) - Adjust new test case to set wal_keep_size. Per buildfarm member conchuela and Kyotaro Horiguchi, it's possible for the WAL segment that the cascading standby needs to be removed too quickly. Hopefully this will prevent that. Kyotaro Horiguchi Discussion: [http://postgr.es/m/[email protected]](http://postgr.es/m/[email protected]) [https://git.postgresql.org/pg/commitdiff/4dcb1d087aebc6fc2477ce4458ea82f548e2c1ee](https://git.postgresql.org/pg/commitdiff/4dcb1d087aebc6fc2477ce4458ea82f548e2c1ee) David Rowley pushed: - Fix an asssortment of typos in brin_minmax_multi.c and mcv.c. Discussion: [https://postgr.es/m/caaphdvrbyjnopbws4ruhxghz7+tbjtdo-rzntsqzecuowno...@mail.gmail.com](https://postgr.es/m/caaphdvrbyjnopbws4ruhxghz7+tbjtdo-rzntsqzecuowno...@mail.gmail.com) [https://git.postgresql.org/pg/commitdiff/55ba5973d9144a552661cf1fa4cbd228a3799212](https://git.postgresql.org/pg/commitdiff/55ba5973d9144a552661cf1fa4cbd228a3799212) - Use the correct article for abbreviations. We've accumulated quite a mix of instances of "an SQL" and "a SQL" in the documents. It would be good to be a bit more consistent with these. The most recent version of the SQL standard I looked at seems to prefer "an SQL". That seems like a good lead to follow, so here we change all instances of "a SQL" to become "an SQL". Most instances correctly use "an SQL" already, so it also makes sense to use the dominant variation in order to minimise churn. Additionally, there were some other abbreviations that needed to be adjusted. FSM, SSPI, SRF and a few others. Also fix some pronounceable, abbreviations to use "a" instead of "an". For example, "a SASL" instead of "an SASL". Here I've only adjusted the documents and error messages. Many others still exist in source code comments. Translator hint comments seem to be the biggest culprit. It currently does not seem worth the churn to change these. Discussion: [https://postgr.es/m/CAApHDvpML27UqFXnrYO1MJddsKVMQoiZisPvsAGhKE_tsKXquw%40mail.gmail.com](https://postgr.es/m/CAApHDvpML27UqFXnrYO1MJddsKVMQoiZisPvsAGhKE_tsKXquw%40mail.gmail.com) [https://git.postgresql.org/pg/commitdiff/04539e73faaaaa1c06c1407671910dceaffdfcd4](https://git.postgresql.org/pg/commitdiff/04539e73faaaaa1c06c1407671910dceaffdfcd4) Noah Misch pushed: - Rename PQtraceSetFlags() to PQsetTraceFlags(). We have a dozen PQset*() functions. PQresultSetInstanceData() and this were the libpq setter functions having a different word order. Adopt the majority word order. Reviewed by Alvaro Herrera and Robert Haas, though this choice of name was not unanimous. Discussion: [https://postgr.es/m/[email protected]](https://postgr.es/m/[email protected]) [https://git.postgresql.org/pg/commitdiff/d0e750c0acaf31f60667b1635311bcef5ab38bbe](https://git.postgresql.org/pg/commitdiff/d0e750c0acaf31f60667b1635311bcef5ab38bbe) - Change position of field "transformed" in struct CreateStatsStmt. Resolve the disagreement with nodes/*funcs.c field order in favor of the latter, which is better-aligned with the IndexStmt field order. This field is new in v14. Discussion: [https://postgr.es/m/[email protected]](https://postgr.es/m/[email protected]) [https://git.postgresql.org/pg/commitdiff/13a1ca160dcfc316c9f4005891a312f5a84c5ca2](https://git.postgresql.org/pg/commitdiff/13a1ca160dcfc316c9f4005891a312f5a84c5ca2) Álvaro Herrera pushed: - Fix race condition in invalidating obsolete replication slots. The code added to mark replication slots invalid in commit c6550776394e had the race condition that a slot can be dropped or advanced concurrently with checkpointer trying to invalidate it. Rewrite the code to close those races. The changes to ReplicationSlotAcquire's API added with c6550776394e are not necessary anymore. To avoid an ABI break in released branches, this commit leaves that unchanged; it'll be changed in a master-only commit separately. Backpatch to 13, where this code first appeared. Reported-by: Andres Freund <[email protected]> Author: Andres Freund <[email protected]> Author: Álvaro Herrera <[email protected]> Discussion: [https://postgr.es/m/[email protected]](https://postgr.es/m/[email protected]) [https://git.postgresql.org/pg/commitdiff/96540f80f8334a3f0f4a13f0d42e4565d8fa9eb7](https://git.postgresql.org/pg/commitdiff/96540f80f8334a3f0f4a13f0d42e4565d8fa9eb7) - Return ReplicationSlotAcquire API to its original form. Per 96540f80f833; the awkward API introduced by c6550776394e is no longer needed. Author: Andres Freund <[email protected]> Reviewed-by: Álvaro Herrera <[email protected]> Discussion: [https://postgr.es/m/[email protected]](https://postgr.es/m/[email protected]) [https://git.postgresql.org/pg/commitdiff/1632ea43682fcea8836ea245771ae85b9e1bcd38](https://git.postgresql.org/pg/commitdiff/1632ea43682fcea8836ea245771ae85b9e1bcd38) - Add 'Portal Close' message to pipelined PQsendQuery(). Commit acb7e4eb6b1c added a new implementation for PQsendQuery so that it works in pipeline mode (by using extended query protocol), but it behaves differently from the 'Q' message (in simple query protocol) used by regular implementation: the new one doesn't close the unnamed portal. Change the new code to have identical behavior to the old. Reported-by: Yura Sokolov <[email protected]> Author: Álvaro Herrera <[email protected]> Discussion: [https://postgr.es/m/[email protected]](https://postgr.es/m/[email protected]) [https://git.postgresql.org/pg/commitdiff/4efcf47053eaf8dd88de2b1a89478df43d37d5c0](https://git.postgresql.org/pg/commitdiff/4efcf47053eaf8dd88de2b1a89478df43d37d5c0) - Report sort phase progress in parallel btree build. We were already reporting it, but only after the parallel workers were finished, which is visibly much later than what happens in a serial build. With this change we report it when the leader starts its own sort phase when participating in the build (the normal case). Now this might happen a little later than when the workers start their sorting phases, but a) communicating the actual phase start from workers is likely to be a hassle, and b) the sort phase start is pretty fuzzy anyway, since sorting per se is actually initiated by tuplesort.c internally earlier than tuplesort_performsort() is called. Backpatch to pg12, where the progress reporting code for CREATE INDEX went in. Reported-by: Tomas Vondra <[email protected]> Author: Matthias van de Meent <[email protected]> Reviewed-by: Greg Nancarrow <[email protected]> Reviewed-by: Álvaro Herrera <[email protected]> Discussion: [https://postgr.es/m/1128176d-1eee-55d4-37ca-e63644422adb](https://postgr.es/m/1128176d-1eee-55d4-37ca-e63644422adb) [https://git.postgresql.org/pg/commitdiff/5cc1cd502879d642da799e1fd12619d83d987369](https://git.postgresql.org/pg/commitdiff/5cc1cd502879d642da799e1fd12619d83d987369) Andres Freund pushed: - Improve and cleanup ProcArrayAdd(), ProcArrayRemove(). 941697c3c1a changed ProcArrayAdd()/Remove() substantially. As reported by zhanyi, I missed that due to the introduction of the PGPROC->pgxactoff ProcArrayRemove() does not need to search for the position in procArray->pgprocnos anymore - that's pgxactoff. Remove the search loop. The removal of the search loop reduces assertion coverage a bit - but we can easily do better than before by adding assertions to other loops over pgprocnos elements. Also do a bit of cleanup, mostly by reducing line lengths by introducing local helper variables and adding newlines. Author: zhanyi <[email protected]> Author: Andres Freund <[email protected]> Discussion: [https://postgr.es/m/[email protected]](https://postgr.es/m/[email protected]) [https://git.postgresql.org/pg/commitdiff/d8e950d3ae7b33a2064a4fb39b7829334b0b47bc](https://git.postgresql.org/pg/commitdiff/d8e950d3ae7b33a2064a4fb39b7829334b0b47bc) Andrew Dunstan pushed: - Fix new recovery test for use under msys. Commit caba8f0d43 wasn't quite right for msys, as demonstrated by several buildfarm animals, including jacana and fairywren. We need to use the msys perl in the archive command, but call it in such a way that Windows will understand the path. Furthermore, inside the copy script we need to convert a Windows path to an msys path. [https://git.postgresql.org/pg/commitdiff/c3652f976b7696a96a9c5606cc2d613af77e2e63](https://git.postgresql.org/pg/commitdiff/c3652f976b7696a96a9c5606cc2d613af77e2e63) - Further tweaks to stuck_on_old_timeline recovery test. Translate path slashes on target directory path. This was confusing old branches, but is applied to all branches for the sake of uniformity. Perl is perfectly able to understand paths with forward slashes. Along the way, restore the previous archive_wait query, for the sake of uniformity with other tests, per gripe from Tom Lane. [https://git.postgresql.org/pg/commitdiff/9d97c3408319b43718e4b85bc694697db1af32c6](https://git.postgresql.org/pg/commitdiff/9d97c3408319b43718e4b85bc694697db1af32c6) # Pending Patches Ranier Vilela sent in another revision of a patch to reduce the overhead on detoasted values. Takamichi Osumi sent in three more revisions of a patch to document a deadlock that can arise in synchronous logical decoding. Andrey V. Lepikhov sent in another revision of a patch to implement bulk COPY FROM into foreign tables. Hou Zhijie sent in another revision of a patch to cache partition bound offsets adaptively. Nathan Bossart sent in a patch to pre-allocate WAL segments. Tomáš Vondra sent in two revisions of a patch to make it possible for logical decoding to replicate sequences. Justin Pryzby sent in a patch to show "syncing data directories" in the ps display. Anastasia Lubennikova sent in two more revisions of a patch to test the replay of regression tests. Aleksander Alekseev and Michaël Paquier raded patches to fix dropped object handling in pg_event_trigger_ddl_commands. Dilip Kumar and Amit Langote traded patches to fix decoding of speculative aborts. Peter Eisentraut sent in a patch to generate node support functions automagically. Kyotaro HORIGUCHI and Amit Kapila traded patches intended to fix a bug that manifested as a keepalive flood in logical replication. Quan Zongliang sent in a patch to remove unused code from the KnownAssignedTransactionIdes submodule. Emre Hasegeli sent in a patch to support bool in btree_gist. Jacob Champion sent in a PoC patch to implement federated authn/z with OAUTHBEARER. Jeff Davis sent in another revision of a patch to track last recovery LSN, time, and total count, which would make it possible to see unlogged table resets from other nodes. Hou Zhijie sent in a patch to remove a now-unused function parameter in get_qual_from_partbound. Hou Zhijie sent in two more revisions of a patch to make it possible for INSERT ... SELECT to execute in parallel. Peter Geoghegan and Matthias van de Meent traded patches to fix a bug in GetOldestNonRemovableTransactionId. GetOldestNonRemovableTransactionId(rel) did not return values consistent with GlobalVisTestFor(rel). This is now updated, and some assertions are added to ensure this problem case does not return. Peter Smith and Ajin Cherian traded patches to add support for prepared transactions to built-in logical replication, add a prepare API support for streaming transactions, and skip empty transactions for logical replication. Ajin Cherian sent in three more revisions of a patch to add an option to set two-phase in CREATE_REPLICATION_SLOT command, and add support for two-phase decoding in pg_recvlogical. Julien Rouhaud sent in two more revisions of a patch to add a hook for extensible parsing. Kyotaro HORIGUCHI and Fabien COELHO traded patches to intended to fix a bug that manifested as pgbench logging 0-interval log entries. Alexander Pyhalov sent in a patch to allow pushing CASE expressions to foreign servers. Jeff Davis sent in two more revisions of a patch to implement ALTER TABLE ... SET ACCESS METHOD. Nitin Jadhav sent in another revision of a patch to track startup progress. Atsushi Torikoshi sent in another revision of a patch to add a function to log the complete query string and its plan for the query currently running on the backend with the specified process ID. Thomas Mannhart sent in two more revisions of a patch to implement range merge joins. Thomas Munro sent in a patch to adjust pg_regress output for new long test names. Bharath Rupireddy sent in two more revisions of a patch to refactor parse_subscription_options, and remove similar ereport calls in parse_subscription_options. Tomáš Vondra sent in two more revisions of a patch to create copy of a descriptor for batching, and initialize slots only once for batching. Nathan Bossart sent in a patch to add a pg_ctl option for retrieving shmem size. John Naylor sent in another revision of a patch to rewrite pg_utf8_verifystr for speed. David Rowley sent in a patch to use "an SQL" in places where "a SQL" appeared. Alexander Korotkov sent in four revisions of a patch to support UNNEST(multirange) and cast multirange as an array of ranges. Hayato Kuroda sent in a patch to ignore failed threads in pgbench. Amit Langote sent in another revision of a patch to implement multi-column list partitioning. Jacob Champion sent in another revision of a patch to add an column projection list to the table AM API. Bruce Momjian sent in three revisions of a patch to document the fact that some aggregate functions need to be re-created on upgrade. Ranier Vilela sent in a patch to change some signed types to unsigned. Pavel Stěhule sent in another revision of a patch to implement schema variables. Thomas Munro sent in another revision of a patch to add WL_SOCKET_CLOSED for socket shutdown events, and use WL_SOCKET_CLOSED for client_connection_check_interval. Fabien COELHO sent in another revision of a patch to add a SHOW_ALL_RESULTS to psql. David Rowley sent in a patch to clean up some aggregate code in the executor. David Rowley sent in a patch to improve various places that double the size of a buffer by replacing a tight loop doubling at each iteration with pg_nextpower2_32 or pg_nextpower2_64, as appropriate. David Rowley sent in a patch to add proper planner support for ORDER BY / DISTINCT aggregates. Noah Misch sent in a patch to remove pg_wait_for_backend_termination(). Yugo Nagata and Fabien COELHO traded patches to keep pgbench from getting stuck due to skipped transactions. Ranier Vilela sent in another revision of a patch to reduce unmatched types in procarray. Thomas Munro sent in three revisions of a patch to use tuple-level SIREAD locks for index-only scans, and skip SIREAD locks on btree pages when possible. Alexander Korotkov sent in a patch to change the title of a new documentation chapter to Range/Multirange Functions and Operators, which is clearer. Bharath Rupireddy sent in a patch to enhance the batch insert test case for postgres_fdw. Tomáš Vondra sent in a patch to shorten the runtime of the postgres_fdw test. Tomáš Vondra sent in a patch to handle Expr op Expr clauses in extended stats.
