Security releases 13.2, 12.6, 11.11, 10.16, 9.6.21, and 9.5.25 are out. Please upgrade ASAP. 9.2.25 is the last release of PostgreSQL 9.5. https://www.postgresql.org/about/news/postgresql-132-126-1111-1016-9621-and-9525-released-2165/
Person of the week: https://postgresql.life/post/hubert_lubaczewski/
check_pgbackrest 2.0, a Nagios-compatible monitor for pgBackRest, released. https://github.com/dalibo/check_pgbackrest/releases
AGE 0.3.0, a PostgreSQL extension that provides graph database functionality, released. https://github.com/apache/incubator-age/releases/tag/v0.3.0.rc0-incubating
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.
Heikki Linnakangas pushed:
Tom Lane pushed:
Fix mishandling of column-level SELECT privileges for join aliases. scanNSItemForColumn, expandNSItemAttrs, and ExpandSingleTable would pass the wrong RTE to markVarForSelectPriv when dealing with a join ParseNamespaceItem: they'd pass the join RTE, when what we need to mark is the base table that the join column came from. The end result was to not fill the base table's selectedCols bitmap correctly, resulting in an understatement of the set of columns that are read by the query. The executor would still insist on there being at least one selectable column; but with a correctly crafted query, a user having SELECT privilege on just one column of a table would nonetheless be allowed to read all its columns. To fix, make markRTEForSelectPriv fetch the correct RTE for itself, ignoring the possibly-mismatched RTE passed by the caller. Later, we'll get rid of some now-unused RTE arguments, but that risks API breaks so we won't do it in released branches. This problem was introduced by commit 9ce77d75c, so back-patch to v13 where that came in. Thanks to Sven Klemm for reporting the problem. Security: CVE-2021-20229 https://git.postgresql.org/pg/commitdiff/c028faf2a62ce476c8d689a33b12471dc98b80f2
Remove no-longer-used RTE argument of markVarForSelectPriv(). In the wake of c028faf2a, this is no longer needed. I left it out of that patch since the API change would be undesirable in a released branch; but there's no reason not to do it in HEAD. https://git.postgresql.org/pg/commitdiff/d4c746516b8b4eb0bf993e3729ccc04d1febdb1e
Simplify jsonfuncs.c code by using strtoint() not strtol(). Explicitly testing for INT_MIN and INT_MAX isn't particularly good style; it's tedious and may draw useless compiler warnings on machines where int and long are the same width. We invented strtoint() precisely for this usage, so use that instead. While here, remove gratuitous variations in the way the tests for did-strtoint-succeed were spelled. Also, avoid attempting to negate INT_MIN; that would probably work given that the result is implicitly cast to uint32, but I think it's nominally undefined behavior. Per gripe from Ranier Vilela, though this isn't his proposed patch. Discussion: https://postgr.es/m/CAEudQAqge3QfzoBRhe59QrB_5g+NmQUj2QpzqZ9Nc7QepXGAEw@mail.gmail.com https://git.postgresql.org/pg/commitdiff/69036aafb9a8f425fb489125b5075ba7719d20d0
Remove dead code in ECPGconnect(), and improve documentation. The stanza in ECPGconnect() that intended to allow specification of a Unix socket directory path in place of a port has never executed since it was committed, nearly two decades ago; the preceding strrchr() already found the last colon so there cannot be another one. The lack of complaints about that is doubtless related to the fact that no user-facing documentation suggested it was possible. Rather than try to fix that up, let's just remove the unreachable code, and instead document the way that does work to write a socket directory path, namely specifying it as a "host" option. In support of that, make another pass at clarifying the syntax documentation for ECPG connection targets, particularly documenting which things are parsed as identifiers and where to use double quotes. Rearrange some things that seemed poorly ordered, and fix a couple of minor doc errors. Kyotaro Horiguchi, per gripe from Shenhao Wang (docs changes mostly by me) Discussion: https://postgr.es/m/ae52a416bbbf459c96bab30b3038e06c@G08CNEXMBPEKD06.g08.fujitsu.local https://git.postgresql.org/pg/commitdiff/62535cae9723afc48173ba1be65f1c7491813fc2
Avoid divide-by-zero in regex_selectivity() with long fixed prefix. Given a regex pattern with a very long fixed prefix (approaching 500 characters), the result of pow(FIXED_CHAR_SEL, fixed_prefix_len) can underflow to zero. Typically the preceding selectivity calculation would have underflowed as well, so that we compute 0/0 and get NaN. In released branches this leads to an assertion failure later on. That doesn't happen in HEAD, for reasons I've not explored yet, but it's surely still a bug. To fix, just skip the division when the pow() result is zero, so that we'll (most likely) return a zero selectivity estimate. In the edge cases where "sel" didn't yet underflow, perhaps this isn't desirable, but I'm not sure that the case is worth spending a lot of effort on. The results of regex_selectivity_sub() are barely worth the electrons they're written on anyway :-( Per report from Alexander Lakhin. Back-patch to all supported versions. Discussion: https://email@example.com https://git.postgresql.org/pg/commitdiff/ae4867ec749671dadb5eb49a1158ded9b8ca0b29
Tweak compiler version cutoff for no_sanitize("alignment") support. Buildfarm results show that gcc up through 7.x produces annoying warnings for this construct (and, presumably, wouldn't do the right thing anyway). clang seems okay with the cutoff we have, though. Discussion: https://postgr.es/m/CAPpHfdsne3%3DT%3DfMNU45PtxdhSL_J2PjLTeS8rwKnJzUR4YNd4w%40mail.gmail.com Discussion: https://postgr.es/m/475514.1612745257%40sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/ad2ad698ac16f36a9784bc98f312cf5b5266c6aa
Peter Geoghegan pushed:
Correct pgstattuple B-Tree page comments. https://git.postgresql.org/pg/commitdiff/5fd590021d268190e4c8f377370c7e7f1e7d9229
Fix obsolete FSM remarks in nbtree README. The free space map has used a dedicated relation fork rather than shared memory segments for over a decade. https://git.postgresql.org/pg/commitdiff/31c7fb41e26bf03dae231c7165a1a16388b2e366
Remove obsolete IndexBulkDeleteResult stats field. The pages_removed field is no longer used for anything. It hasn't been possible for an index to physically shrink since old-style VACUUM FULL was removed by commit 0a469c87. https://git.postgresql.org/pg/commitdiff/3063eb17593c3ad498ce4e89db3358862ea2dbb6
Michaël Paquier pushed:
Add option PROCESS_TOAST to VACUUM. This option controls if toast tables associated with a relation are vacuumed or not when running a manual VACUUM. It was already possible to trigger a manual VACUUM on a toast relation without processing its main relation, but a manual vacuum on a main relation always forced a vacuum on its toast table. This is useful in scenarios where the level of bloat or transaction age of the main and toast relations differs a lot. This option is an extension of the existing VACOPT_SKIPTOAST that was used by autovacuum to control if toast relations should be skipped or not. This internal flag is renamed to VACOPT_PROCESS_TOAST for consistency with the new option. A new option switch, called --no-process-toast, is added to vacuumdb. Author: Nathan Bossart Reviewed-by: Kirk Jamison, Michael Paquier, Justin Pryzby Discussion: https://postgr.es/m/BA8951E9-1524-48C5-94AF-73B1F0D7857F@amazon.com https://git.postgresql.org/pg/commitdiff/7cb3048f38e26b39dd5fd412ed8a4981b6809b35
Preserve pg_attribute.attstattarget across REINDEX CONCURRENTLY. For an index, attstattarget can be updated using ALTER INDEX SET STATISTICS. This data was lost on the new index after REINDEX CONCURRENTLY. The update of this field is done when the old and new indexes are swapped to make the fix back-patchable. Another approach we could look after in the long-term is to change index_create() to pass the wanted values of attstattarget when creating the new relation, but, as this would cause an ABI breakage this can be done only on HEAD. Reported-by: Ronan Dunklau Author: Michael Paquier Reviewed-by: Ronan Dunklau, Tomas Vondra Discussion: https://postgr.es/m/16628084.uLZWGnKmhe@laptop-ronand Backpatch-through: 12 https://git.postgresql.org/pg/commitdiff/bd12080980297dbc8ae926a3bd5b2ef9cc47932b
Simplify code related to compilation of SSL and OpenSSL. This commit makes more generic some comments and code related to the compilation with OpenSSL and SSL in general to ease the addition of more SSL implementations in the future. In libpq, some OpenSSL-only code is moved under USE_OPENSSL and not USE_SSL. While on it, make a comment more consistent in libpq-fe.h. Author: Daniel Gustafsson Discussion: https://postgr.es/m/5382CB4A-9CF3-4145-BA46-C802615935E0@yesql.se https://git.postgresql.org/pg/commitdiff/092b785fad3de3f81355a4b2420aa39a1bc0ccd5
Fix ORDER BY clause in new regression test of REINDEX CONCURRENTLY. Oversight in bd12080. Reported-by: Justin Pryzby Discussion: https://postgr.es/m/20210210065805.GG20012@telsasoft.com Backpatch-through: 12 https://git.postgresql.org/pg/commitdiff/4f4726005082c07c004506a1ddfca29896375206
Fix copy-paste error with SHA256 digest length in checksum_helper.c. Issue introduced by 87ae969, noticed while working on the area. While on it, fix some grammar in the surrounding static assertions. https://git.postgresql.org/pg/commitdiff/42d74e0c443760af91875af99d2655c9eb4c40df
doc: Mention NO DEPENDS ON EXTENSION in its supported ALTER commands. This grammar flavor has been added by 5fc7039. Author: Ian Lawrence Barwick Discussion: https://postgr.es/m/CAB8KJ=ii6JScodxkA6-DO8bjatsMYU3OcewnL0mdN9geR+tTaw@mail.gmail.com Backpatch-through: 13 https://git.postgresql.org/pg/commitdiff/8063d0f6f56e53edd991f53aadc8cb7f8d3fdd8f
Fujii Masao pushed:
Display the time when the process started waiting for the lock, in pg_locks. This commit adds new column "waitstart" into pg_locks view. This column reports the time when the server process started waiting for the lock if the lock is not held. This information is useful, for example, when examining the amount of time to wait on a lock by subtracting "waitstart" in pg_locks from the current time, and identify the lock that the processes are waiting for very long. This feature uses the current time obtained for the deadlock timeout timer as "waitstart" (i.e., the time when this process started waiting for the lock). Since getting the current time newly can cause overhead, we reuse the already-obtained time to avoid that overhead. Note that "waitstart" is updated without holding the lock table's partition lock, to avoid the overhead by additional lock acquisition. This can cause "waitstart" in pg_locks to become NULL for a very short period of time after the wait started even though "granted" is false. This is OK in practice because we can assume that users are likely to look at "waitstart" when waiting for the lock for a long time. Bump catalog version. Author: Atsushi Torikoshi Reviewed-by: Ian Lawrence Barwick, Robert Haas, Justin Pryzby, Fujii Masao Discussion: https://firstname.lastname@example.org https://git.postgresql.org/pg/commitdiff/3b733fcd04195399db56f73f0616b4f5c6828e18
Revert "Display the time when the process started waiting for the lock, in pg_locks.". This reverts commit 3b733fcd04195399db56f73f0616b4f5c6828e18. Per buildfarm members prion and rorqual. https://git.postgresql.org/pg/commitdiff/890d2182a2c425aaa80f9bf9f7116d31e0c6538e
Amit Kapila pushed:
Make pg_replication_origin_drop safe against concurrent drops. Currently, we get the origin id from the name and then drop the origin by taking ExclusiveLock on ReplicationOriginRelationId. So, two concurrent sessions can get the id from the name at the same time and then when they try to drop the origin, one of the sessions will get the either "tuple concurrently deleted" or "cache lookup failed for replication origin ..". To prevent this race condition we do the entire operation under lock. This obviates the need for replorigin_drop() API and we have removed it so if any extension authors are using it they need to instead use replorigin_drop_by_name. See it's usage in pg_replication_origin_drop(). Author: Peter Smith Reviewed-by: Amit Kapila, Euler Taveira, Petr Jelinek, and Alvaro Herrera Discussion: https://www.postgresql.org/message-id/CAHut%2BPuW8DWV5fskkMWWMqzt-x7RPcNQOtJQBp6SdwyRghCk7A%40mail.gmail.com https://git.postgresql.org/pg/commitdiff/cd142e032ebd50ec7974b3633269477c2c72f1cc
Allow multiple xacts during table sync in logical replication. For the initial table data synchronization in logical replication, we use a single transaction to copy the entire table and then synchronize the position in the stream with the main apply worker. There are multiple downsides of this approach: (a) We have to perform the entire copy operation again if there is any error (network breakdown, error in the database operation, etc.) while we synchronize the WAL position between tablesync worker and apply worker; this will be onerous especially for large copies, (b) Using a single transaction in the synchronization-phase (where we can receive WAL from multiple transactions) will have the risk of exceeding the CID limit, (c) The slot will hold the WAL till the entire sync is complete because we never commit till the end. This patch solves all the above downsides by allowing multiple transactions during the tablesync phase. The initial copy is done in a single transaction and after that, we commit each transaction as we receive. To allow recovery after any error or crash, we use a permanent slot and origin to track the progress. The slot and origin will be removed once we finish the synchronization of the table. We also remove slot and origin of tablesync workers if the user performs DROP SUBSCRIPTION .. or ALTER SUBSCRIPTION .. REFERESH and some of the table syncs are still not finished. The commands ALTER SUBSCRIPTION ... REFRESH PUBLICATION and ALTER SUBSCRIPTION ... SET PUBLICATION ... with refresh option as true cannot be executed inside a transaction block because they can now drop the slots for which we have no provision to rollback. This will also open up the path for logical replication of 2PC transactions on the subscriber side. Previously, we can't do that because of the requirement of maintaining a single transaction in tablesync workers. Bump catalog version due to change of state in the catalog (pg_subscription_rel). Author: Peter Smith, Amit Kapila, and Takamichi Osumi Reviewed-by: Ajin Cherian, Petr Jelinek, Hou Zhijie and Amit Kapila Discussion: https://postgr.es/m/CAA4eK1KHJxaZS-fod-0fey=0tq3=Gkn4ho=8N4-5HWiCfu0H1A@mail.gmail.com https://git.postgresql.org/pg/commitdiff/ce0fdbfe9722867b7fad4d3ede9b6a6bfc51fb4e
Fix Subscription test added by commit ce0fdbfe97. We want to test the variants of Alter Subscription that are not allowed in the transaction block but for that, we don't need to create a subscription that tries to connect to the publisher. As such, there is no problem with this test but it is good to allow such tests to run with wal_level = minimal and max_wal_senders = 0 so as to keep them consistent with other tests. Reported by buildfarm. Author: Amit Kapila Reviewed-by: Ajin Cherian Discussion: https://postgr.es/m/CAA4eK1Lw0V+e1JPGHDq=+hVACv=14H8sR+2eJ1k3PEgwKmU-jQ@mail.gmail.com https://git.postgresql.org/pg/commitdiff/c8b21b0378141948a2dc0ddde6f57407321787ea
Peter Eisentraut pushed:
Fix lack of message pluralization. https://git.postgresql.org/pg/commitdiff/4ad5611055c8381d6a9b54fc11838418a1f14bc9
pg_dump: Add const decorations. Add const decorations to the info arguments of the dump functions, to clarify that they don't modify that argument. Many other nearby functions modify their arguments, so this can help clarify these different APIs a bit. Discussion: https://www.postgresql.org/message-id/flat/012d3030-9a2c-99a1-ed2d-988978b5632f%40enterprisedb.com https://git.postgresql.org/pg/commitdiff/64990081504661ff5c04dbf20cc4252be66ab149
Add test case for abbrev(cidr). This will in particular add some good test coverage for inet_cidr_ntop.c, which was previously completely uncovered. Reviewed-by: Tom Lane email@example.com Discussion: https://www.postgresql.org/message-id/flat/cb0c4662-4596-dab4-7f64-839c5e8582c8%40enterprisedb.com https://git.postgresql.org/pg/commitdiff/69273c8f88d7e6295ed7b38114969fa3ea03eff2
Magnus Hagander pushed:
Alexander Korotkov pushed:
Bruce Momjian pushed:
Tang Haiying sent in another revision of a patch to support tab completion for upper case inputs in psql when using set/reset/show.
Pavel Borisov sent in three revisions of a patch to make amcheck check the UNIQUE constraint for btree indexes.
Vigneshwaran C sent in three more revisions of a patch to make the libpq connection parameter "target_session_attrs" support new values: read-only, primary, standby, and prefer-standby.
Iwata Aya sent in two more revisions of a patch to add tracing to libpq.
Amit Langote, Greg Nancarrow, and Hou Zhijie traded patches to implement parallel execution for INSERT ... SELECT.
Scott Mead sent in another revision of a patch to make autovacuum dynamically decrease cost_limit and cost_delay.
Matthias van de Meent and Josef Šimánek traded patches to enhance COPY progress reporting.
Heikki Linnakangas and John Naylor traded patches to speed up utf-8 checking with SIMD instructions.
Mark Rofail and Joel Jacobson traded patches to implement foreign key arrays.
Amit Langote sent in another revision of a patch to set ForeignScanState.resultRelInfo and initialize result relation information lazily.
Peter Eisentraut sent in a patch to add routine usage information schema tables.
Heikki Linnakangas sent in another revision of a patch to add a 'noError' argument to encoding conversion functions, and use same to do COPY FROM encoding conversion/verification in larger chunks.
Alexey Bashtanov sent in a patch to add a bit_xor aggregate.
Daniel Gustafsson sent in another revision of a patch to make it possible to use NSS for libpq's TLS implementation.
Jacob Champion sent in two more revisions of a patch to log authenticated identity from all auth backends.
Kyotaro HORIGUCHI and Dilip Kumar traded patches to provide a new interface to get the recovery pause status.
Tom Lane sent in a patch to disallow some bug-prone characters from being used as the names of custom GUCs.
Nathan Bossart sent in a patch to broaden the scope of the heap-only tuples (HOT) optimization be more discerning about updating only indexes where the indexed value actually changed. Before this, HOT could only work on completely unindexed columns, as the alternative was to update all indexes regardless of whether anything in them had actually changed.
Peter Geoghegan sent in three revisions of a patch to use 64-bit XIDs in deleted nbtree pages, and add pages_newly_deleted to VACUUM VERBOSE.
Takayuki Tsunakawa sent in another revision of a patch to speed up COPY FROM when the target table has remote partitions.
Justin Pryzby sent in another revision of a patch to make CLUSTER work on partitioned tables.
Stephen Frost sent in another revision of a patch to include the I/O timing if track_io_timing is enabled in logs for autovacuum and autoanalyze along with the read rate and the dirty rate for autoanalyze.
Peter Smith sent in two more revisions of a patch to implement logical decoding of two-phase transactions.
Andy Fan sent in a patch to Introduce notnullattrs field in RelOptInfo to indicate which attributes are not null in current query.
Etsuro Fujita sent in two more revisions of a patch to implement synchronous append on PostgreSQL FDW nodes.
Ranier Vilela and Michaël Paquier traded patches to fix a possible out-of-bounds access in pg_cryptohash_final by adding a length argument to same.
Dilip Kumar, Robert Haas, and Justin Pryzby traded patches to add custom compression methods for tables.
Michail Nikolaev sent in another revision of a patch to add full support for index LP_DEAD hint bits on standbys.
Justin Pryzby sent in a patch to touch up the documentation for 14.
Peter Eisentraut sent in another revision of a patch to implement SQL standard function bodies for SQL functions.
Peter Eisentraut sent in a patch to add tests for the bytea LIKE operator.
Fujii Masao sent in a patch to intended to fix a bug that manifested as ERROR: invalid spinlock number: 0 by moving the assignment of written_lsn to a place where it can use pg_atomic_read_u64(&WalRcv->writtenUpto) better.
Tomáš Vondra sent in another revision of a patch to implement BRIN multi-range indexes.
Anastasia Lubennikova sent in another revision of a patch to intended to fix a bug that manifested as pg_upgrade fails with non-standard ACL.
Melanie Plageman sent in another revision of a patch to update comments and phase naming for parallel hash joins.
Noah Misch sent in two more revisions of a patch to dump public schema ownership and security labels, and dump COMMENT ON SCHEMA public.
Zhihong Yu and Ranier Vilela traded patches to fix a possible dereference null return in src/backend/replication/logical/reorderbuffer.c.
Tom Lane sent in two revisions of a patch to invent rainbow arcs for regexes and
short-circuit character-by-character scanning when matching a sub-NFA that is
".*" or variants of that, ie it will match any sequence of some
number of characters.
Thomas Munro sent in a patch to try to hold onto buffers between WAL records, which if successful amortizes the cost of looking up, pinning, locking, unlocking and unpinning said buffers over multiple executions.
Erik Rijkers and Amit Kapila traded patches to fix a recent breakage of logical replication.
Bharath Rupireddy sent in a patch to emove unnecessary wrapping of MakeTupleTableSlot in MakeSingleTupleTableSlot.
Li Japin sent in another revision of a patch to implement ALTER SUBSCRIPTION ... ADD/DROP PUBLICATION.
Noah Misch sent in a patch to add a public schema default ACL.