14th September 2023: PostgreSQL 16 Released!

PostgreSQL Weekly News - December 20, 2020

Posted on 2020-12-20 by PWN

PostgreSQL Weekly News - December 20, 2020

FOSDEM PGDay 2021,will be held on line, on Feb 6-7, 2021. https://fosdem.org/2021/

The Code of Conduct Committee welcomes new member Michael Goldberg https://www.postgresql.org/about/policies/coc_committee/

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

PostgreSQL Product News

PoWA 4.1, a performance tool for PostgreSQL, released. https://github.com/powa-team/powa-archivist/releases/tag/REL_4_1_0

Database .NET v31.5, a multi-database management tool, now with support for PostgreSQL, released. https://fishcodelib.com/Database.htm

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

PostgreSQL Jobs for December


PostgreSQL in the News

Planet PostgreSQL: http://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 david@fetter.org.

Applied Patches

Michaël Paquier pushed:

Jeff Davis pushed:

Tom Lane pushed:

  • Improve hash_create()'s API for some added robustness. Invent a new flag bit HASH_STRINGS to specify C-string hashing, which was formerly the default; and add assertions insisting that exactly one of the bits HASH_STRINGS, HASH_BLOBS, and HASH_FUNCTION be set. This is in hopes of preventing recurrences of the type of oversight fixed in commit a1b8aa1e4 (i.e., mistakenly omitting HASH_BLOBS). Also, when HASH_STRINGS is specified, insist that the keysize be more than 8 bytes. This is a heuristic, but it should catch accidental use of HASH_STRINGS for integer or pointer keys. (Nearly all existing use-cases set the keysize to NAMEDATALEN or more, so there's little reason to think this restriction should be problematic.) Tweak hash_create() to insist that the HASH_ELEM flag be set, and remove the defaults it had for keysize and entrysize. Since those defaults were undocumented and basically useless, no callers omitted HASH_ELEM anyway. Also, remove memset's zeroing the HASHCTL parameter struct from those callers that had one. This has never been really necessary, and while it wasn't a bad coding convention it was confusing that some callers did it and some did not. We might as well save a few cycles by standardizing on "not". Also improve the documentation for hash_create(). In passing, improve reinit.c's usage of a hash table by storing the key as a binary Oid rather than a string; and, since that's a temporary hash table, allocate it in CurrentMemoryContext for neatness. Discussion: https://postgr.es/m/590625.1607878171@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/b3817f5f774663d55931dd4fab9c5a94a15ae7ab

  • Fix varchar_2.out to match reality in cs_CZ locale. Seems to be a copy-and-pasteo in c06d6aa4c. Per buildfarm. https://git.postgresql.org/pg/commitdiff/88e014c149cc396fb218b08eda17c47d5b33e94f

  • Avoid memcpy() with same source and destination during relmapper init. A narrow reading of the C standard says that memcpy(x,x,n) is undefined, although it's hard to envision an implementation that would really misbehave. However, analysis tools such as valgrind might whine about this; accordingly, let's band-aid relmapper.c to not do it. See also 5b630501e, d3f4e8a8a, ad7b48ea0, and other similar fixes. Apparently, none of those folk tried valgrinding initdb? This has been like this for long enough that I'm surprised it hasn't been reported before. Back-patch, just in case anybody wants to use a back branch on a platform that complains about this; we back-patched those earlier fixes too. Discussion: https://postgr.es/m/161790.1608310142@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/53d4f5fef04653fc495ae385a9c2f78c77e5c7d9

  • Add a couple of missed .gitignore entries. Any subdirectory that's ignoring /output_iso/ should also ignore /tmp_check_iso/, which could be left behind by a failed pg_isolation_regress_check run. I think these have been wrong for awhile, but it doesn't seem important to fix in back branches. https://git.postgresql.org/pg/commitdiff/8afca702ecbf0730b6175afda0cecbbf0a1967e7

  • Remove now-useless ALWAYS_SUBDIRS entry in src/test/Makefile. Commit 257836a75 added the "locale" subdirectory to SUBDIRS, but neglected to remove it from ALWAYS_SUBDIRS. This oversight had no functional effect because the filter-out function would remove it anyway. Still, it's confusing to readers to list a subdirectory in both places, especially because it makes the associated comment into a partial lie. https://git.postgresql.org/pg/commitdiff/08b01d4dd982b491a2f9641804b368185b8f4c53

  • Avoid memcpy() with same source and destination in pgstat_recv_replslot. Same type of issue as in commit 53d4f5fef and earlier fixes; also found by apparently-more-picky-than-the-buildfarm valgrind testing. This one is an oversight in commit 986816750. Since that's new in HEAD, no need for a back-patch. https://git.postgresql.org/pg/commitdiff/ed6329cfa975a13c58c44676464585cda5d317b3

  • Doc: improve description of pgbench script weights. Point out the workaround to be used if you want to write a script file name that includes "@". Clean up the text a little. Fabien Coelho, additional wordsmithing by me Discussion: https://postgr.es/m/1c4e81550d214741827a03292222db8d@G08CNEXMBPEKD06.g08.fujitsu.local https://git.postgresql.org/pg/commitdiff/1990ce186ee64f24dcff885a87cea04e3dfd8166

Peter Eisentraut pushed:

Tomáš Vondra pushed:

Álvaro Herrera pushed:

Peter Geoghegan pushed:

Bruce Momjian pushed:

Fujii Masao pushed:

  • Revert "Get rid of the dedicated latch for signaling the startup process". Revert ac22929a26, as well as the followup fix 113d3591b8. Because it broke the assumption that the startup process waiting for the recovery conflict on buffer pin should be waken up only by buffer unpin or the timeout enabled in ResolveRecoveryConflictWithBufferPin(). It caused, for example, SIGHUP signal handler or walreceiver process to wake that startup process up unnecessarily frequently. Additionally, add the comments about why that dedicated latch that the reverted patch tried to get rid of should not be removed. Thanks to Kyotaro Horiguchi for the discussion. Author: Fujii Masao Discussion: https://postgr.es/m/d8c0c608-021b-3c73-fffd-3240829ee986@oss.nttdata.com https://git.postgresql.org/pg/commitdiff/00f690a239932e477f25120d19b08aacdc30deb7

  • pg_stat_statements: Track time at which all statistics were last reset. This commit adds "stats_reset" column into the pg_stat_statements_info view. This column indicates the time at which all statistics in the pg_stat_statements view were last reset. Per discussion, this commit also changes pg_stat_statements_info code so that "dealloc" column is reset at the same time as "stats_reset" is reset, i.e., whenever all pg_stat_statements entries are removed, for the sake of consistency. Previously "dealloc" was reset only when pg_stat_statements_reset(0, 0, 0) is called and was not reset when pg_stat_statements_reset() with non-zero value argument discards all entries. This was confusing. Author: Naoki Nakamichi, Yuki Seino Reviewed-by: Yuki Seino, Kyotaro Horiguchi, Li Japin, Fujii Masao Discussion: https://postgr.es/m/c102cf3180d0ee73c1c5a0f7f8558322@oss.nttdata.com https://git.postgresql.org/pg/commitdiff/2e0fedf0362cc964c4dae42258455b6391051e70

Amit Kapila pushed:

Alexander Korotkov pushed:

Pending Patches

Michaël Paquier sent in another revision of a patch to introduce SHA1 cryptohash infrastructure.

Andrey Borodin sent in another revision of a patch to use a shared lock in GetMultiXactIdMembers for offsets and members, make the MultiXact local cache size configurable, add a condition variable to wait for next MultXact offset in a corner case, and add GUCs to tune MultiXact SLRUs.

Kyotaro HORIGUCHI sent in a patch to ensure that invalid-page-header error messages are cancelled only when appropriate, i.e. only on non-standby nodes.

Andrey V. Lepikhov sent in another revision of a patch to move the multi-insert decision logic into the executor, and use same along with three new methods to the FDW API: BeginForeignCopy, ExecForeignCopy, and EndForeignCopy, to speed up COPY FROM as applied to tables with remote partitions.

Fujii Masao sent in two more revisions of a patch to add a log_recovery_conflict_waits GUC that does what it says on the label.

Tom Lane sent in two revisions of a patch to fix the way PL/pgsql does assignments.

Bharath Rupireddy and Hou Zhijie traded patches to make it possible to use parallel INSERTs in CTAS.

Nathan Bossart and Kyotaro HORIGUCHI traded patches to intended to fix a bug that manifested as archive status ".ready" files may be created too early.

Kirk Jamison sent in another revision of a patch to make dropping relation buffers more efficient using dlist.

Amit Kapila sent in three more revisions of a patch to implement logical decoding of two-phase transactions.

Yuzuko Hosoya sent in another revision of a patch to make autoanalyze work better on partitioned tables.

Atsushi Torikoshi sent in a patch to add a wait_start colunmn to the pg_locks view.

Iwata Aya sent in another revision of a patch to add tracing to libpq.

Laurenz Albe sent in two more revisions of a patch to add session statistics to pg_stat_database.

Peter Eisentraut sent in another revision of a patch to change return type of EXTRACT to numeric.

Fabrízio de Royes Mello sent in another revision of a patch to implement minimal logical decoding on standbys.

Justin Pryzby sent in two more revisions of a patch to allow CLUSTER, VACUUM FULL and REINDEX to change tablespace on the fly.

Jeff Davis sent in a patch to enforce ASCII restore point names.

Bharath Rupireddy sent in a patch intended to fix a bug that manifested as - cached connection leaks from the postgres_fdw if the associated user mapping is dropped by moving the place to close all the invalid connections to pgfdw_xact_callback().

Peter Smith sent in two more revisions of a patch to make it possible to use multiple workers on tablesync.

Bruce Momjian and Neil Chen traded patches to implement key management.

Tomáš Vondra sent in another revision of a patch to consider unsorted paths in generate_useful_gather_paths, check parallel safety in generate_useful_gather_paths, disallow SRFs when considering sorts below Gather Merge, avoid searching for volatile expr in find_em_expr_usable_for_sorting_rel, and improve find_em_expr_usable_for_sorting_rel comment by clarifying the relationship between find_em_expr_usable_for_sorting_rel and prepare_sort_from_pathkeys, i.e. what restrictions need to be shared between those two places.

Laurenz Albe sent in another revision of a patch to fix a large violation of POLA in psql's \e family of functions, namely that if the editor was exited before any changes were made, the previous query would be executed. The fix is to clear the query buffer.

Heikki Linnakangas sent in another revision of a patch to make resowners more easily extensible by using a single array and hash instead of one for each object kind.

Justin Pryzby sent in another revision of a patch to pg_upgrade/test.sh which makes some changes needed to allow testing upgrade from v11.

Heikki Linnakangas sent in a patch to do COPY FROM encoding conversion/verification in larger chunks.

Fujii Masao sent in three revisions of a patch to intended to fix a bug that manifested as failure to detect a deadlock between backend and recovery by adding a check for that situation.

Michaël Paquier sent in two revisions of a patch to refactor the HMAC implementations.

Konstantin Knizhnik sent in two more revisions of a patch to add a way to fire triggers on login.

David Fetter sent in a patch to implement \gsetenv, analogous to \gset, only for environment variables in psql.

Önder Kalacı sent in another revision of a patch to implement row filtering for logical replication.

Denis Smirnov and Konstantin Knizhnik traded patches to implement compression for libpq.

Peter Eisentraut sent in a patch to hange seconds argument of make_*() functions to numeric.

Fujii Masao sent in a patch to intended to fix a bug that manifested as STANDBY_LOCK_TIMEOUT may not interrupt ProcWaitForSignal() by ensuring that StandbyLockTimeoutHandler() sets a latch.

Bharath Rupireddy sent in another revision of a patch to fail fast in CTAS/CMV if the relation already exists.

Gilles Darold sent in another revision of a patch to fix a race condition in DDL where a function's namespace can disappear while leaving the function in place.

Michaël Paquier sent in a patch to remove duplicate checks of ASCII-ness and make the one left more efficient.

Justin Pryzby sent in two more revisions of a patch to allow psql's \dti+ to look at pg_toast.*.

David CARLIER sent in a patch to implements SPIN_LOCK on ARM.

Michaël Paquier and Heikki Linnakangas traded patches to intended to fix a bug that manifested as incorrect allocation handling for cryptohash functions with OpenSSL.

Tom Lane and Alexander Lakhin traded patches to show examples of using Valgrind for TAP tests.

Craig Ringer sent in a patch to identify LWLocks in tracepoints by fixing a bogus tracepoint where an lwlock__acquire event would be fired from LWLockWaitForVar despite that function never actually acquiring the lock, adding the tranche id and lock pointer for each trace hit, adding a single-path tracepoint for all lock acquires and releases, and adding some comments on LWLock tranches to clarify matters for future readers.

Konstantin Knizhnik sent in a patch to reimplement BufferAlloc, removing double locking in the process.

Pavel Stěhule sent in another revision of a patch to implement schema variables.

Tom Lane sent in a patch to fix a corner case bug in jsonb_concat().

Thomas Munro sent in two revisions of a patch to add pg_preadv() and pg_pwritev(), which provide synchronous scatter/gather I/O routines, and then use same to use vectored I/O to zero WAL segments.

Tomáš Vondra sent in another revision of a patch to implement BRIN multi-range indexes.