Happy New Year from the PostgreSQL Weekly News!
Database Lab 2.1, a tool for fast cloning of large PostgreSQL databases to build non-production environments, released: https://postgres.ai/blog/dle-2.1-release/
Planet PostgreSQL: 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 firstname.lastname@example.org.
Jeff Davis pushed:
Bruce Momjian pushed:
Revert "Add key management system" (978f869b99) & later commits. The patch needs test cases, reorganization, and cfbot testing. Technically reverts commits 5c31afc49d..e35b2bad1a (exclusive/inclusive) and 08db7c63f3..ccbe34139b. Reported-by: Tom Lane, Michael Paquier Discussion: https://postgr.es/m/E1ktAAG-0002V2-VB@gemulon.postgresql.org https://git.postgresql.org/pg/commitdiff/3187ef7c46c5b884267a88f2d6119c9a05f1bbba
Update copyright for 2021. Backpatch-through: 9.5 https://git.postgresql.org/pg/commitdiff/ca3b37487be333a1d241dab1bbdd17a211a88f43
Fujii Masao pushed:
Michaël Paquier pushed:
Fix inconsistent code with shared invalidations of snapshots. The code in charge of processing a single invalidation message has been using since 568d413 the structure for relation mapping messages. This had fortunately no consequence as both locate the database ID at the same location, but it could become a problem in the future if this area of the code changes. Author: Konstantin Knizhnik Discussion: https://email@example.com Backpatch-through: 9.5 https://git.postgresql.org/pg/commitdiff/643428c54b95d472c6d949c3c3c11f347e371a2b
doc: Improve description of min_dynamic_shared_memory. While on it, fix one oversight in 90fbf7c, that introduced a reference to an incorrect value for the compression level of pg_dump. Author: Justin Pryzby Reviewed-by: Thomas Munro, Michael Paquier Discussion: https://postgr.es/m/CA+hUKGJRTLWWPcQfjm_xaOk98M8aROK903X92O0x-4vLJPWrrA@mail.gmail.com https://git.postgresql.org/pg/commitdiff/1b3433e25f81d6fc4f231887ab965e1ea1bcb47e
Remove references to libpq_srcdir in adminpack and old_snapshot. Those two modules included references to libpq's source path, without using anything from libpq. Some copy-pastos done when each module was created are likely at the origin of those useless references (aecf5ee for old_snapshot, fe59e56 for adminpack). Reviewed-by: Tom Lane, David Rowley Discussion: https://postgr.es/m/X+LQpfLyk7jgzUki@paquier.xyz https://git.postgresql.org/pg/commitdiff/107a2d4204ff4bf4ce05e3525f0d94fc0bd497ff
Sanitize IF NOT EXISTS in EXPLAIN for CTAS and matviews. IF NOT EXISTS was ignored when specified in an EXPLAIN query for CREATE MATERIALIZED VIEW or CREATE TABLE AS. Hence, if this clause was specified, the caller would get a failure if the relation already exists instead of a success with a NOTICE message. This commit makes the behavior of IF NOT EXISTS in EXPLAIN consistent with the non-EXPLAIN'd DDL queries, preventing a failure with IF NOT EXISTS if the relation to-be-created already exists. The skip is done before the SELECT query used for the relation is planned or executed, and a "dummy" plan is generated instead depending on the format used by EXPLAIN. Author: Bharath Rupireddy Reviewed-by: Zhijie Hou, Michael Paquier Discussion: https://postgr.es/m/CALj2ACVa3oJ9O_wcGd+FtHWZds04dEKcakxphGz5POVgD4wC7Q@mail.gmail.com https://git.postgresql.org/pg/commitdiff/e665769e6d1e84b6650f53ed297058fc11152f7f
Tom Lane pushed:
Fix thinko in plpgsql memory leak fix. Commit a6b1f5365 intended to place the transient "target" list of a CALL statement in the function's statement-lifespan context, but I fat-fingered that and used get_eval_mcontext() instead of get_stmt_mcontext(). The eval_mcontext belongs to the "simple expression" infrastructure, which is destroyed at transaction end. The net effect is that a CALL in a procedure to another procedure that has OUT or INOUT parameters would fail if the called procedure did a COMMIT. Per report from Peter Eisentraut. Back-patch to v11, like the prior patch. Discussion: https://firstname.lastname@example.org https://git.postgresql.org/pg/commitdiff/ea80d8d9437e80de6506dbfe3765d834653312bf
Further fix thinko in plpgsql memory leak fix. There's a second call of get_eval_mcontext() that should also be get_stmt_mcontext(). This is actually dead code, since no interesting allocations happen before switching back to the original context, but we should keep it in sync with the other call to forestall possible future bugs. Discussion: https://email@example.com https://git.postgresql.org/pg/commitdiff/5f2e09bcccd771629fb7a2885f8c468ae0f7fb33
Expose the default for channel_binding in PQconndefaults(). If there's a static default value for a connection option, it should be shown in the PQconninfoOptions array. Daniele Varrazzo Discussion: https://postgr.es/m/CA+mi_8Zo8Rgn7p+6ZRY7QdDu+23ukT9AvoHNyPbgKACxwgGhZA@mail.gmail.com https://git.postgresql.org/pg/commitdiff/cf61b0734c61d93c62827fe4e44fa2162a533b8e
Fix bugs in libpq's GSSAPI encryption support. The critical issue fixed here
is that if a GSSAPI-encrypted connection is successfully made,
pqsecure_open_gss() cleared conn->allow_ssl_try, as an admittedly-hacky way of
preventing us from then trying to tunnel SSL encryption over the
already-encrypted connection. The problem with that is that if we abandon the
GSSAPI connection because of a failure during authentication, we would not
attempt SSL encryption in the next try with the same server. This can lead to
unexpected connection failure, or silently getting a non-encrypted connection
where an encrypted one is expected. Fortunately, we'd only manage to make a
GSSAPI-encrypted connection if both client and server hold valid tickets in
the same Kerberos infrastructure, which is a relatively uncommon environment.
Nonetheless this is a very nasty bug with potential security consequences. To
fix, don't reset the flag, instead adding a check for conn->gssenc being
already true when deciding whether to try to initiate SSL. While here, fix
some lesser issues in libpq's GSSAPI code:
* Use the need_new_connection
stanza when dropping an attempted GSSAPI connection, instead of partially
duplicating that code. The consequences of this are pretty minor: AFAICS it
could only lead to auth_req_received or password_needed remaining set when
they shouldn't, which is not too harmful.
* Fix pg_GSS_error() to not repeat
the "mprefix" it's given multiple times, and to notice any failure return from
* Avoid gratuitous dependency on NI_MAXHOST in
pg_GSS_load_servicename(). Per report from Mikael Gustavsson. Back-patch to
v12 where this code was introduced. Discussion:
Fix assorted issues in backend's GSSAPI encryption support. Unrecoverable errors detected by GSSAPI encryption can't just be reported with elog(ERROR) or elog(FATAL), because attempting to send the error report to the client is likely to lead to infinite recursion or loss of protocol sync. Instead make this code do what the SSL encryption code has long done, which is to just report any such failure to the server log (with elevel COMMERROR), then pretend we've lost the connection by returning errno = ECONNRESET. Along the way, fix confusion about whether message translation is done by pg_GSS_error() or its callers (the latter should do it), and make the backend version of that function work more like the frontend version. Avoid allocating the port->gss struct until it's needed; we surely don't need to allocate it in the postmaster. Improve logging of "connection authorized" messages with GSS enabled. (As part of this, I back-patched the code changes from dc11f31a1.) Make BackendStatusShmemSize() account for the GSS-related space that will be allocated by CreateSharedBackendStatus(). This omission could possibly cause out-of-shared-memory problems with very high max_connections settings. Remove arbitrary, pointless restriction that only GSS authentication can be used on a GSS-encrypted connection. Improve documentation; notably, document the fact that libpq now prefers GSS encryption over SSL encryption if both are possible. Per report from Mikael Gustavsson. Back-patch to v12 where this code was introduced. Discussion: https://firstname.lastname@example.org https://git.postgresql.org/pg/commitdiff/622ae4621ece72a9f64b5602c74d7aaf373c1631
Improve log messages related to pg_hba.conf not matching a connection. Include details on whether GSS encryption has been activated; since we added "hostgssenc" type HBA entries, that's relevant info. Kyotaro Horiguchi and Tom Lane. Back-patch to v12 where GSS encryption was introduced. Discussion: https://email@example.com https://git.postgresql.org/pg/commitdiff/3995c424984e991b1069a2869af972dc07574c0b
Suppress log spam from multiple reports of SIGQUIT shutdown. When the postmaster sends SIGQUIT to its children, there's no real need for all the children to log that fact; the postmaster already made a log entry about it, so adding perhaps dozens or hundreds of child-process log entries adds nothing of value. So, let's introduce a new ereport level to specify "WARNING, but never send to log" and use that for these messages. Such a change wouldn't have been desirable before commit 7e784d1dc, because if someone manually SIGQUIT's a backend, we do want to log that. But now we can tell the difference between a signal that was issued by the postmaster and one that was not with reasonable certainty. While we're here, also clear error_context_stack before ereport'ing, to prevent error callbacks from being invoked in the signal-handler context. This should reduce the odds of getting hung up while trying to notify the client. Per a suggestion from Andres Freund. Discussion: https://firstname.lastname@example.org https://git.postgresql.org/pg/commitdiff/1f9158ba48122fa232db955a2ee324eec1848ba9
Doc: fix up PDF build warnings from over-width table columns. Addition of multirange info to tables 8.27 and 65.1 made them start throwing "exceed the available area" warnings in PDF docs builds. For 8.27, twiddling the existing column width hints was enough to fix this. For 65.1, I twiddled the widths a little, but to really fix it I had to insert a space after each comma in the table, to allow a line break to occur there. (This seemed easier to read and maintain than the alternative of inserting &zws;; entities.) Per buildfarm. https://git.postgresql.org/pg/commitdiff/f20dc2c8dd50a5c738d535205d5d44bff82d3f75
Fix up usage of krb_server_keyfile GUC parameter. secure_open_gssapi() installed the krb_server_keyfile setting as KRB5_KTNAME unconditionally, so long as it's not empty. However, pg_GSS_recvauth() only installed it if KRB5_KTNAME wasn't set already, leading to a troubling inconsistency: in theory, clients could see different sets of server principal names depending on whether they use GSSAPI encryption. Always using krb_server_keyfile seems like the right thing, so make both places do that. Also fix up secure_open_gssapi()'s lack of a check for setenv() failure --- it's unlikely, surely, but security-critical actions are no place to be sloppy. Also improve the associated documentation. This patch does nothing about secure_open_gssapi()'s use of setenv(), and indeed causes pg_GSS_recvauth() to use it too. That's nominally against project portability rules, but since this code is only built with --with-gssapi, I do not feel a need to do something about this in the back branches. A fix will be forthcoming for HEAD though. Back-patch to v12 where GSSAPI encryption was introduced. The dubious behavior in pg_GSS_recvauth() goes back further, but it didn't have anything to be inconsistent with, so let it be. Discussion: https://email@example.com https://git.postgresql.org/pg/commitdiff/860fe27ee1e2a4a1c36c2f874c37656533cccce9
Use setenv() in preference to putenv(). Since at least 2001 we've used putenv() and avoided setenv(), on the grounds that the latter was unportable and not in POSIX. However, POSIX added it that same year, and by now the situation has reversed: setenv() is probably more portable than putenv(), since POSIX now treats the latter as not being a core function. And setenv() has cleaner semantics too. So, let's reverse that old policy. This commit adds a simple src/port/ implementation of setenv() for any stragglers (we have one in the buildfarm, but I'd not be surprised if that code is never used in the field). More importantly, extend win32env.c to also support setenv(). Then, replace usages of putenv() with setenv(), and get rid of some ad-hoc implementations of setenv() wannabees. Also, adjust our src/port/ implementation of unsetenv() to follow the POSIX spec that it returns an error indicator, rather than returning void as per the ancient BSD convention. I don't feel a need to make all the call sites check for errors, but the portability stub ought to match real-world practice. Discussion: https://firstname.lastname@example.org https://git.postgresql.org/pg/commitdiff/7ca37fb0406bc2cbbd864a2ffdbdb4479e338c0c
More fixups for pg_upgrade cross-version tests. Commit 7ca37fb04 removed regress_putenv from the regress.so library, so reloading a SQL function dependent on that would not work. Fix similarly to 52202bb39. Per buildfarm. https://git.postgresql.org/pg/commitdiff/091866724cb3ee7251fa56e2517248c4b7796ca8
Doc: spell out comparison behaviors for the date/time types. The behavior of cross-type comparisons among date/time data types was not really explained anywhere. You could probably infer it if you recognized the applicability of comments elsewhere about datatype conversions, but it seems worthy of explicit documentation. Per bug #16797 from Dana Burd. Discussion: https://email@example.com https://git.postgresql.org/pg/commitdiff/319f4d54e82d15d4a0c3f4cc1328c40dba024b5c
Doc: improve explanation of EXTRACT(EPOCH) for timestamp without tz. Try to be clearer about what computation is actually happening here. Per bug #16797 from Dana Burd. Discussion: https://firstname.lastname@example.org https://git.postgresql.org/pg/commitdiff/4d3f03f42227bb351c2021a9ccea2fff9c023cfc
Alexander Korotkov pushed:
Fix bugs in comparison functions for multirange_bsearch_match(). Two functions multirange_range_overlaps_bsearch_comparison() and multirange_range_contains_bsearch_comparison() contain bugs of returning -1 instead of 1. This commit fixes these bugs and adds corresponding regression tests. https://git.postgresql.org/pg/commitdiff/a5b81b6f0006ea0b502780ce7f73d295a225842c
Implement operators for checking if the range contains a multirange. We have operators for checking if the multirange contains a range but don't have the opposite. This commit improves completeness of the operator set by adding two new operators: @> (anyrange,anymultirange) and <@(anymultirange,anyrange). Catversion is bumped. https://git.postgresql.org/pg/commitdiff/4d7684cc754f312aee468abb83ca4f7da94b30a3
Improve the signature of internal multirange functions. There is a set of
*_internal() functions exposed in include/utils/multirangetypes.h. This
commit improves the signatures of these functions in two ways.
* Add const
qualifies where applicable.
* Replace multirange typecache argument with
range typecache argument. Multirange typecache was used solely to find the
range typecache. At the same time, range typecache is easier for the
caller to find.
Add support of multirange matching to the existing range GiST indexes. 6df7a9698b has introduced a set of operators between ranges and multiranges. Existing GiST indexes for ranges could easily support majority of them. This commit adds support for new operators to the existing range GiST indexes. New operators resides the same strategy numbers as existing ones. Appropriate check function is determined using the subtype. Catversion is bumped. https://git.postgresql.org/pg/commitdiff/db6335b5b1d6654b0e3104f36817800d127c1c91
Fix selectivity estimation @> (anymultirange, anyrange) operator. Attempt to get selectivity estimation for @> (anymultirange, anyrange) operator caused an error in buildfarm, because this operator was missed in switch() of calc_hist_selectivity(). Fix that and also make regression tests reliably check that selectivity estimation for (multi)ranges doesn't fall. Previously, whether we test selectivity estimation for (multi)ranges depended on whether autovacuum managed to gather concurrently to the test. Reported-by: Michael Paquier Discussion: https://postgr.es/m/X%2BwmgjRItuvHNBeV%40paquier.xyz https://git.postgresql.org/pg/commitdiff/62097a4cc8c725fa86d3170396a8f30609acd0d3
Refactor multirange_in(). This commit preserves the logic of multirange_in() but makes it more clear what's going on. Also, this commit fixes the compiler warning spotted by the buildfarm. Reported-by: Tom Lane Discussion: https://postgr.es/m/2246043.1609290699%40sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/16d531a30a120d13cc3b460fba6570024a1fcfa8
Noah Misch pushed:
In pg_upgrade cross-version test, handle lack of oldstyle_length(). This suffices for testing v12 -> v13; some other version pairs need more changes. Back-patch to v10, which removed the function. https://git.postgresql.org/pg/commitdiff/52202bb396b1e96c43bfd767d6e434b1c6fd2ae1
In pg_upgrade cross-version test, handle postfix operators. Commit 1ed6b895634ce0dc5fd4bd040e87252b32182cba eliminated support for them, so drop them from regression databases before upgrading. This is necessary but not sufficient for testing v13 -> v14 upgrades. Discussion: https://email@example.com https://git.postgresql.org/pg/commitdiff/fa744697c79189a661f802d9a979d959b4454df0
Amit Kapila pushed:
* filter_prepare *
* stream_prepare Most of this
is a simple extension of the existing methods, with the semantic difference
that the transaction is not yet committed and maybe aborted later. Until now
two-phase transactions were translated into regular transactions on the
subscriber, and the GID was not forwarded to it. None of the two-phase
commands were communicated to the subscriber. This patch provides the
infrastructure for logical decoding plugins to be informed of two-phase
commands Like PREPARE TRANSACTION, COMMIT PREPARED and ROLLBACK PREPARED
commands with the corresponding GID. This also extends the 'test_decoding'
plugin, implementing these new methods. This commit simply adds these new
APIs and the upcoming patch to "allow the decoding at prepare time in
ReorderBuffer" will use these APIs. Author: Ajin Cherian and Amit Kapila
based on previous work by Nikhil Sontakke and Stas Kelvich Reviewed-by: Amit
Kapila, Peter Smith, Sawada Masahiko, and Dilip Kumar Discussion:
Peter Geoghegan pushed:
Fix index deletion latestRemovedXid bug. The logic for determining the latest removed XID for the purposes of generating recovery conflicts in REDO routines was subtly broken. It failed to follow links from HOT chains, and so failed to consider all relevant heap tuple headers in some cases. To fix, expand the loop that deals with LP_REDIRECT line pointers to also deal with HOT chains. The new version of the loop is loosely based on a similar loop from heap_prune_chain(). The impact of this bug is probably quite limited, since the horizon code necessarily deals with heap tuples that are pointed to by LP_DEAD-set index tuples. The process of setting LP_DEAD index tuples (e.g. within the kill_prior_tuple mechanism) is highly correlated with opportunistic pruning of pointed-to heap tuples. Plus the question of generating a recovery conflict usually comes up some time after index tuple LP_DEAD bits were initially set, unlike heap pruning, where a latestRemovedXid is generated at the point of the pruning operation (heap pruning has no deferred "would-be page split" style processing that produces conflicts lazily). Only backpatch to Postgres 12, the first version where this logic runs during original execution (following commit 558a9165e08). The index latestRemovedXid mechanism has had the same bug since it first appeared over 10 years ago (in commit a760893d), but backpatching to all supported versions now seems like a bad idea on balance. Running the new improved code during recovery seems risky, especially given the lack of complaints from the field. Author: Peter Geoghegan firstname.lastname@example.org Discussion: https://postgr.es/m/CAH2-Wz=Eib393+HHcERK_9MtgNS7Ew1HY=RDC_g6GL46zM5C6Q@mail.gmail.com Backpatch: 12- https://git.postgresql.org/pg/commitdiff/422881744997417944634a7f84af7a66a608de9a
Get heap page max offset with buffer lock held. On further reflection it seems better to call PageGetMaxOffsetNumber() after acquiring a buffer lock on the page. This shouldn't really matter, but doing it this way is cleaner. Follow-up to commit 42288174. Backpatch: 12-, just like commit 42288174 https://git.postgresql.org/pg/commitdiff/32d6287d2eef6b6a4dde07e0513f3e4f321792ad
Noah Misch sent in a patch to merge similar algorithms into roles_is_member_of().
Vigneshwaran C sent in a patch atop the existing patch to parallelize parts of COPY which delegates the task of finding line boundaries to workers.
Bharath Rupireddy sent in a patch to implement EXPLAIN [ANALYZE] for REFRESH MATERIALIZED VIEW.
Masahiko Sawada sent in another revision of a patch to add new FDW APIs to support 2PC, introduce a global transaction manager, and implement those FDW APIs in the PostgreSQL FDW.
Peter Geoghegan sent in another revision of a patch to make the btvacuumstrategy() bottom-up index deletion changes.
Bharath Rupireddy sent in two more revisions of a patch to make it possible for CTAS to use parallel inserts.
Luc Vlaming sent in two revisions of a patch to generate JIT IR code lazily, which issue came up in the case where a lot of JIT IR code was being generated unecessarily for the case of partitioned tables where the IR for many of the partitions was never executed because those partitions got pruned.
Thomas Munro sent in another revision of a patch to make it possible to use parallel hashing to execute Full and Right JOINs.
Andrey Borodin sent in another revision of a patch to add LZ4 as a possible compression scheme for WALL FPIs.
David Rowley sent in two more revisions of a patch to reduce the number of special cases to build contrib modules on Windows.
Noah Misch sent in a patch to dump public schema ownership and security labels.
Paul Martinez sent in a patch to simplify the permission checking logic in user.c.
Bharath Rupireddy sent in another revision of a patch to allow parallel mode in the planning of REFRESH MATERIALIZED VIEW.
Thomas Munro sent in another revision of a patch to track relation sizes in shared memory, this capability being controlled by a new GUC, smgr_shared_relation, which limits the number of the newly introduced pool of SMgrSharedRelation objects, and provide a lock-free fast path for smgrnblocks().
Peter Smith sent in another revision of a patch to allow the table-sync worker to use multiple transactions.
Andrey V. Lepikhov sent in another revision of a patch to speed up COPY for the case where a table has foreign partitions by adding three new routines to the FDW API: BeginForeignCopy, EndForeignCopy, and ExecForeignCopy, and adding same to the PostgreSQL FDW.
Andrey Borodin sent in another revision of a patch to reorganize pglz compression code to make it more efficient by converting macro functions to regular functions for readability, Using a more compact hash table with uint16 indexes instead of pointers, avoiding prev pointer in hash tables, and using 4-byte comparisons in a search instead of 1-byte comparisons.
Justin Pryzby sent in another revision of a patch to implement CREATE TABLE (LIKE .. INCLUDING ACCESS METHOD).
Luc Vlaming sent in another revision of a patch to allow partial UNION ALL, improving parallel subquery costing thereby.
Rui Zhao sent in another revision of a patch to refactor the way is called RelationClose after RelationIdGetRelation.
Peter Eisentraut sent in another revision of a patch to implement dynamic result sets from procedures.
David Fetter sent in two revisions of a patch to surface popcount to SQL.
Joe Wildish sent in another revision of a patch to allow queries in WHEN expression of FOR EACH STATEMENT triggers.
David Fetter sent in another revision of a patch to make it possible to set pg_hba.conf parameters from initdb.
Greg Sabino Mullane sent in another revision of a patch to get psql's \df to help choose functions by input type.
David Fetter and Krasiyan Andreev traded patches to implement NULL treatement for window functions.
Dmitry Dolgov sent in three more revisions of a patch to use the generic type subscripting infrastructure for JSONB.
David Fetter sent in a WIP patch to document the hooks system.
Michael Banck sent in a patch to add a new PGC_ADMINSET guc context and pg_change_role_settings default role, creating a space between 'superuser' and 'user' for GUC contexts.
Álvaro Herrera sent in another revision of a patch to implement MERGE.
Peter Geoghegan sent in another revision of a patch to pass down a "logically unchanged index" hint, and use same to implement bottom-up index deletion.
Soumyadeep Chakraborty sent in another revision of a patch to add a routine to the table AM API that accepts column projection lists.
Bruce Momjian and Michaël Paquier traded patches to move the other hex functions to a common place in the source tree.
Etsuro Fujita sent in two more revisions of a patch to implement asynchronous append on postgres_fdw nodes.
David Fetter sent in another revision of a patch to implement range scans for TIDs.
Pavel Stěhule sent in another revision of a patch to reduce the execution overhead of CALL statements in no atomic mode from PL/pgSQL.
Pavel Stěhule sent in another revision of a patch to implement schema variables.
Josef Šimánek sent in another revision of a patch to add a pg_stat_progress_copy view with COPY progress report.
Pavel Stěhule sent in a patch to make it possible to write window functions in PLs, as they are currently limited to C.
Luc Vlaming sent in a patch to make bulk loading more efficient for heap tables by allocating to each backend a standalone set of blocks that are local to that backend, and reduces the time spent on locking the partition buffers by shifting around the logic to make each set of 128 blocks use the same buffer partition, and then adding a custom function to get buffer blocks specifically for extension, whilst keeping a previous partition lock, thereby reducing the amount of time we spent on futexes.
Michael Banck sent in a patch to move --data-checksums to the common options in initdb's --help output.
Thomas Munro sent in a patch to pgbench to add pthread barrier emulation for platforms that lack it.