November 21, 2024: PostgreSQL 17.2, 16.6, 15.10, 14.15, 13.18, and 12.22 Released!

PostgreSQL Weekly News - April 25, 2021

Posted on 2021-04-26 by PWN
PWN

PostgreSQL Weekly News - April 25, 2021

Person of the week: https://postgresql.life/post/pavlo_golub/

PostgreSQL Product News

JDBC 42.2.20 released https://jdbc.postgresql.org/documentation/changelog.html#version_42.2.20

kubgres 1.1, a Kubernetes operator which makes it possible to deploy a cluster of PostgreSQL pods with replication and failover configured, released. https://github.com/reactive-tech/kubegres/releases/tag/v1.1

pspg 4.6.1 a pager designed for PostgreSQL, released. https://github.com/okbob/pspg/releases/tag/4.6.1

pgAdmin4 5.2, a web- and native GUI control center for PostgreSQL, released. https://www.pgadmin.org/docs/pgadmin4/5.2/release_notes_5_2.html

pg_log_statements 0.2, an extension that makes is possible to log statements for specific server processes, released. https://github.com/pierreforstmann/pg_log_statements/releases

PostgreSQL Jobs for April

https://archives.postgresql.org/pgsql-jobs/2021-04/

PostgreSQL in the News

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 [email protected].

Applied Patches

Thomas Munro pushed:

Michaël Paquier pushed:

Amit Kapila pushed:

  • Fix test case added by commit f5fc2f5b23. In the new test after resetting the stats, we were not waiting for the stats message to be delivered. Also, we need to decode the results for the new test, otherwise, it will show the old stats. In passing, a. Change docs added by commit f5fc2f5b23 as per suggestion by Justin Pryzby. b. Bump the PGSTAT_FILE_FORMAT_ID as commit f5fc2f5b23 changes the file format of stats. Reported-by: Tom Lane based on buildfarm reports Author: Vignesh C, Justin Pryzby Reviewed-by: Amit Kapila Discussion: https://postgr.es/m/[email protected] https://git.postgresql.org/pg/commitdiff/c64dcc7fee5f8a7941a4fd098a969de1f457cc79

Peter Eisentraut pushed:

Peter Geoghegan pushed:

  • Document LP_DEAD accounting issues in VACUUM. Document VACUUM's soft assumption that any LP_DEAD items encountered during pruning will become LP_UNUSED items before VACUUM finishes up. This is integral to the accounting used by VACUUM to generate its final report on the table to the stats collector. It also affects how VACUUM determines which heap pages are truncatable. In both cases VACUUM is concerned with the likely contents of the page in the near future, not the current contents of the page. This state of affairs created the false impression that VACUUM's dead tuple accounting had significant difference with similar accounting used during ANALYZE. There were and are no substantive differences, at least when the soft assumption completely works out. This is far clearer now. Also document cases where things don't quite work out for VACUUM's dead tuple accounting. It's possible that a significant number of LP_DEAD items will be left behind by VACUUM, and won't be recorded as remaining dead tuples in VACUUM's statistics collector report. This behavior dates back to commit a96c41fe, which taught VACUUM to run without index and heap vacuuming at the user's request. The failsafe mechanism added to VACUUM more recently by commit 1e55e7d1 takes the same approach to dead tuple accounting. Reported-By: Masahiko Sawada sawada.mshk@gmail.com Discussion: https://postgr.es/m/CAH2-Wz=Jmtu18PrsYq3EvvZJGOmZqSO2u3bvKpx9xJa5uhNp=Q@mail.gmail.com https://git.postgresql.org/pg/commitdiff/7136bf34f28892362144ae2e350714836a5c0c0c

  • amcheck: MAXALIGN() nbtree special area offset. This isn't strictly necessary, but in theory it might matter if in the future the width of the nbtree special area changes -- its total size might not be an even number of MAXALIGN() quantums, even with padding. PageInit() MAXALIGN()s all special area offsets, but amcheck uses the offset to perform initial basic validation of line pointers, so we don't rely on the offset from the page header. The real reason to do this is to set a good example for new code that adds amcheck coverage for other index AMs. Reported-By: Bharath Rupireddy bharath.rupireddyforpostgres@gmail.com Discussion: https://postgr.es/m/CALj2ACUMqTR9nErh99FbOBmzCXE9=gXNqhBiwYOhejJJS1LXqQ@mail.gmail.com https://git.postgresql.org/pg/commitdiff/bb3ecc8c961896ecb2ad3d5ba705c2877b933945

Magnus Hagander pushed:

Andrew Dunstan pushed:

Tom Lane pushed:

  • Fix planner failure in some cases of sorting by an aggregate. An oversight introduced by the incremental-sort patches caused "could not find pathkey item to sort" errors in some situations where a sort key involves an aggregate or window function. The basic problem here is that find_em_expr_usable_for_sorting_rel isn't properly modeling what prepare_sort_from_pathkeys will do later. Rather than hoping we can keep those functions in sync, let's refactor so that they actually share the code for identifying a suitable sort expression. With this refactoring, tlist.c's tlist_member_ignore_relabel is unused. I removed it in HEAD but left it in place in v13, in case any extensions are using it. Per report from Luc Vlaming. Back-patch to v13 where the problem arose. James Coleman and Tom Lane Discussion: https://postgr.es/m/[email protected] https://git.postgresql.org/pg/commitdiff/375398244168add84a884347625d14581a421e71

  • Rename find_em_expr_usable_for_sorting_rel. I didn't particularly like this function name, as it fails to express what's going on. Also, returning the sort expression alone isn't too helpful --- typically, a caller would also need some other fields of the EquivalenceMember. But the sole caller really only needs a bool result, so let's make it "bool relation_can_be_sorted_early()". Discussion: https://postgr.es/m/[email protected] https://git.postgresql.org/pg/commitdiff/7645376774c8532159f5f0f905e5e734d4ccbb18

  • Fix under-parenthesized XLogRecHasBlockRef() macro. Commit f003d9f87 left this macro with inadequate (or, one could say, too much) parenthesization. Which was catastrophic to the correctness of calls such as "if (!XLogRecHasBlockRef(record, 1)) ...". There are only a few of those, which perhaps explains why we didn't notice immediately (with our general weakness of WAL replay testing being another factor). I found it by debugging intermittent replay failures like 2021-04-08 14:33:30.191 EDT [29463] PANIC: failed to locate backup block with ID 1 2021-04-08 14:33:30.191 EDT [29463] CONTEXT: WAL redo at 0/95D3438 for SPGist/ADD_NODE: off 1; blkref #0: rel 1663/16384/25998, blk 1 https://git.postgresql.org/pg/commitdiff/9e41148229192dccc4bcc40f53af588b73d8ffea

  • Improve WAL record descriptions for SP-GiST records. While tracking down the bug fixed in the preceding commit, I got quite annoyed by the low quality of spg_desc's output. Add missing fields, try to make the formatting consistent. https://git.postgresql.org/pg/commitdiff/783be78ca91166ac7f80c953f2bbc5af1f61c6cd

  • Doc: document the tie-breaking behavior of the round() function. Back-patch to v13; the table layout in older branches is unfriendly to adding such details. Laurenz Albe Discussion: https://postgr.es/m/[email protected] https://git.postgresql.org/pg/commitdiff/82b13dbc4d4b46f71ca95ce1cc15c425deff5957

  • Don't crash on reference to an un-available system column. Adopt a more consistent policy about what slot-type-specific getsysattr functions should do when system attributes are not available. To wit, they should all throw the same user-oriented error, rather than variously crashing or emitting developer-oriented messages. This closes a identifiable problem in commits a71cfc56b and 3fb93103a (in v13 and v12), so back-patch into those branches, along with a test case to try to ensure we don't break it again. It is not known that any of the former crash cases are reachable in HEAD, but this seems like a good safety improvement in any case. Discussion: https://postgr.es/m/[email protected] https://git.postgresql.org/pg/commitdiff/d479d00285255d422a2b38f1cfaa35808968a08c

Bruce Momjian pushed:

Álvaro Herrera pushed:

  • Don't add a redundant constraint when detaching a partition. On ALTER TABLE .. DETACH CONCURRENTLY, we add a new table constraint that duplicates the partition constraint. But if the partition already has another constraint that implies that one, then that's unnecessary. We were already avoiding the addition of a duplicate constraint if there was an exact 'equal' match -- this just improves the quality of the check. Author: Justin Pryzby pryzby@telsasoft.com Reviewed-by: Álvaro Herrera alvherre@alvh.no-ip.org Discussion: https://postgr.es/m/[email protected] https://git.postgresql.org/pg/commitdiff/7b357cc6ae553c0ecacdc11b2e5278b7bf477dba

  • Add comment about extract_autovac_opts not holding lock. Per observation from Tom Lane. Discussion: https://postgr.es/m/[email protected] https://git.postgresql.org/pg/commitdiff/7c298c6573a0f181963ddcb40c850fa9c7da0ada

  • Fix relcache inconsistency hazard in partition detach. During queries coming from ri_triggers.c, we need to omit partitions that are marked pending detach -- otherwise, the RI query is tricked into allowing a row into the referencing table whose corresponding row is in the detached partition. Which is bogus: once the detach operation completes, the row becomes an orphan. However, the code was not doing that in repeatable-read transactions, because relcache kept a copy of the partition descriptor that included the partition, and used it in the RI query. This commit changes the partdesc cache code to only keep descriptors that aren't dependent on a snapshot (namely: those where no detached partition exist, and those where detached partitions are included). When a partdesc-without- detached-partitions is requested, we create one afresh each time; also, those partdescs are stored in PortalContext instead of CacheMemoryContext. find_inheritance_children gets a new output *detached_exist boolean, which indicates whether any partition marked pending-detach is found. Its "include_detached" input flag is changed to "omit_detached", because that name captures desired the semantics more naturally. CreatePartitionDirectory() and RelationGetPartitionDesc() arguments are identically renamed. This was noticed because a buildfarm member that runs with relcache clobbering, which would not keep the improperly cached partdesc, broke one test, which led us to realize that the expected output of that test was bogus. This commit also corrects that expected output. Author: Amit Langote amitlangote09@gmail.com Author: Álvaro Herrera alvherre@alvh.no-ip.org Discussion: https://postgr.es/m/[email protected] https://git.postgresql.org/pg/commitdiff/8aba9322511f718f12b618470d8c07f0ee5f0700

  • Fix uninitialized memory bug. Have interested callers of find_inheritance_children set the detached_exist value to false prior to calling it, so that that routine only has to set it true in the rare cases where it is necessary. Don't touch it otherwise. Per buildfarm member thorntail (which reported a UBSan failure here). https://git.postgresql.org/pg/commitdiff/43b55ec4bc3bc06596d966391f16defe016310ec

Etsuro Fujita pushed:

Fujii Masao pushed:

  • doc: Fix obsolete description about pg_basebackup. Previously it was documented that if using "-X none" option there was no guarantee that all required WAL files were archived at the end of pg_basebackup when taking a backup from the standby. But this limitation was removed by commit 52f8a59dd9. Now, even when taking a backup from the standby, pg_basebackup can wait for all required WAL files to be archived. Therefore this commit removes such obsolete description from the docs. Also this commit adds new description about the limitation when taking a backup from the standby, into the docs. The limitation is that pg_basebackup cannot force the standbfy to switch to a new WAL file at the end of backup, which may cause pg_basebackup to wait a long time for the last required WAL file to be switched and archived, especially when write activity on the primary is low. Back-patch to v10 where the issue was introduced. Reported-by: Kyotaro Horiguchi Author: Kyotaro Horiguchi, Fujii Masao Reviewed-by: Kyotaro Horiguchi, Fujii Masao Discussion: https://postgr.es/m/[email protected] https://git.postgresql.org/pg/commitdiff/eaec48b3c54eec222d64468b57af80ee4ddf76a9

  • Reorder COMPRESSION option in gram.y and parsenodes.h into alphabetical order. Commit bbe0a81db6 introduced "INCLUDING COMPRESSION" option in CREATE TABLE command, but previously TableLikeOption in gram.y and parsenodes.h didn't classify this new option in alphabetical order with the rest. Author: Fujii Masao Reviewed-by: Michael Paquier Discussion: https://postgr.es/m/[email protected] https://git.postgresql.org/pg/commitdiff/3f20d5f37086e548c32ddb9d6ae09c2e1ce300ce

Alexander Korotkov pushed:

Noah Misch pushed:

Pending Patches

Peter Smith sent in a patch to rearrange the option list for CREATE SUBSCRIPTION.

Bharath Rupireddy sent in another revision of a patch to add new table AMs for multi- and single- inserts, and use same for CTAS, REFRESH MATERIALIZED VIEW, and COPY.

Amul Sul sent in two more revisions of a patch to remove a redundant variable from transformCreateStmt.

Pavel Stěhule sent in another revision of a patch to make it possible to log seqscans in auto_explain.

David Rowley sent in another revision of a patch to reduce the number of contrib build special cases on Windows.

Amul Sul sent in two more revisions of a patch to add a RelationGetSmgr inline function.

Masahiko Sawada sent in two more revisions of a patch to skip vmbuffer for frozen tuple insertion.

Dagfinn Ilmari Mannsåker sent in a patch to create a function for stripping RelabelType nodes off an expression, consolidating many repetitions of the same pattern into one.

Masahiko Sawada and Amit Kapila traded patches to use HTAB for replication slot statistics.

Vigneshwaran C and Amit Kapila traded patches to update decoding stats during replication slot release.

Bharath Rupireddy sent in a patch to use WaitLatches for lock waiting in lazy_truncate_heap, in do_pg_stop_backup instead of a pg_usleep(), and for pre- and post- auth delay.

Bharath Rupireddy sent in a patch to move the parallel_leader_participation GUC to the resource consumption category.

Peter Smith sent in two more revisions of a patch to add support for prepared transactions to built-in logical replication, and add prepare API support for streaming transactions.

Dan Carter sent in a patch to add a GSSAPI ccache_name option to libpq.

Takamichi Osumi and Amit Kapila traded patches to fix an infelicity between TRUNCATE and synchronous logical replication.

Amit Langote sent in a patch to fix a tupdesc leak in the pgoutput plugin.

Bharath Rupireddy sent in a patch to skip VACUUM/ANALYZE of repeated relations.

Pavel Stěhule sent in another revision of a patch to add a PSQL_WATCH_PAGER setting to psql.

Masahiro Ikeda sent in another revision of a patch to make some performance improvements in reporting WAL stats without introducing a new variable.

Fujii Masao sent in four more revisions of a patch to document the way TRUNCATE works on foreign tables, and ensure that any ONLY clauses in the original query not be passed to same.

Hou Zhijie sent in a patch to fix a test case with the wrong parallel safety flag.

Zeng Wenjing sent in another revision of a patch to implement global temporary tables.

Simon Riggs sent in a patch to document the fact that altering a constraint to VALIDATE it requires a SHARE UPDATE EXCLUSIVE lock.

Simon Riggs sent in a patch to reduce the lock level for CHECK constraints when allowing them to be NOT VALID.

Daniil Zakhlystov sent in another revision of a patch to add zlib and zstd streaming compression, and implement libpq compression.

Tom Lane sent in a patch to document Julian dates better.

Álvaro Herrera sent in a patch to add tab-complete for ALTER TABLE .. DETACH PARTITION CONCURRENTLY to psql.

Tom Stellard sent in a patch to jit to work around a potential data layout mismatch on s390x.

Justin Pryzby sent in a patch to add COMPRESSION as a possibility when executing CREATE TABLE ... LIKE.

Kyotaro HORIGUCHI sent in a patch to fix up some dubious messages.

Tang sent in a patch to use pg_strncasecmp to replace strncmp when comparing things "pg_" in psql.

Tang sent in another revision of a patch to support tab completion for upper case character inputs in psql.

Hou Zhijie sent in another revision of a patch to make it possible to execute INSERT SELECT in parallel.

Mark Dilger and Robert Haas traded patches to add toast pointer corruption checks to pg_amcheck.

Bharath Rupireddy sent in a patch to MAXALIGN sizeof(BTPageOpaqueData) in PageGetItemIdCareful.

Ajin Cherian sent in another revision of a patch to skip empty transactions for logical replication.

Alexander Pyhalov sent in another revision of a patch to push down restrictinfos to CTEs, and attempt to prune partitions in the process.

Thomas Munro sent in a patch to add a new TAP test under src/test/recovery that runs the regression tests with wal_consistency_checking=all.

Joel Jacobson sent in a patch to fix pg_identify_object_as_address for event triggers.

Takamichi Osumi sent in a patch to replace the check of RelationIsLogicallyLogged in ReorderBufferProcessTXN with an Assert to the same effect.

David Rowley sent in another revision of a patch to speed up NOT IN() with a set of Consts by allowing hash tables to be used.

David Rowley and Yura Sokolov traded patches to use simplehash.h instead of dynahash in SMgr.

Julien Rouhaud sent in a patch to fix some oversights in query_id calculation.

Tom Lane sent in a patch to make it required for callers of pq_getmessage to provide an upper length bound, and installs the same sort of short-vs-long message heuristic as libpq has in the server.