8th February 2024: PostgreSQL 16.2, 15.6, 14.11, 13.14, and 12.18 Released!

PostgreSQL Weekly News - September 26, 2021

Posted on 2021-09-27 by PWN
PWN

PostgreSQL Weekly News - September 26, 2021

PostgreSQL 14 Release Candidate 1 released. Test!

PostgreSQL Product News

JDBC 42.2.24 released https://jdbc.postgresql.org/documentation/changelog.html#version_42.2.24

check_pgbackrest 2.1, a Nagios-compatible monitor for pgBackRest, released. https://github.com/dalibo/check_pgbackrest/releases

sqlite_fdw 2.1.0 released.

PostgreSQL Jobs for September

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

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 david@fetter.org.

Applied Patches

Tomáš Vondra pushed:

  • Disallow extended statistics on system columns. Since introduction of extended statistics, we've disallowed references to system columns. So for example CREATE STATISTICS s ON ctid FROM t; would fail. But with extended statistics on expressions, it was possible to work around this limitation quite easily CREATE STATISTICS s ON (ctid::text) FROM t; This is an oversight in a4d75c86bf, fixed by adding a simple check. Backpatch to PostgreSQL 14, where support for extended statistics on expressions was introduced. Backpatch-through: 14 Discussion: https://postgr.es/m/20210816013255.GS10479%40telsasoft.com https://git.postgresql.org/pg/commitdiff/c9eeef2a15c02ff7dd2bf3251dbee925b050fc0f

  • Free memory after building each statistics object. Until now, all extended statistics on a given relation were built in the same memory context, without resetting. Some of the memory was released explicitly, but not all of it - for example memory allocated while detoasting values is hard to free. This is how it worked since extended statistics were introduced in PostgreSQL 10, but adding support for extended stats on expressions made the issue somewhat worse as it increases the number of statistics to build. Fixed by adding a memory context which gets reset after building each statistics object (all the statistics kinds included in it). Resetting it after building each statistics kind would be even better, but it would require more invasive changes and copying of results, making it harder to backpatch. Backpatch to PostgreSQL 10, where extended statistics were introduced. Author: Justin Pryzby Reported-by: Justin Pryzby Reviewed-by: Tomas Vondra Backpatch-through: 10 Discussion: https://www.postgresql.org/message-id/20210915200928.GP831%40telsasoft.com https://git.postgresql.org/pg/commitdiff/83772cc78e0392a247231ba510c61b6612b93b3f

  • Release memory allocated by dependency_degree. Calculating degree of a functional dependency may allocate a lot of memory - we have released mot of the explicitly allocated memory, but e.g. detoasted varlena values were left behind. That may be an issue, because we consider a lot of dependencies (all combinations), and the detoasting may happen for each one again. Fixed by calling dependency_degree() in a dedicated context, and resetting it after each call. We only need the calculated dependency degree, so we don't need to copy anything. Backpatch to PostgreSQL 10, where extended statistics were introduced. Backpatch-through: 10 Discussion: https://www.postgresql.org/message-id/20210915200928.GP831%40telsasoft.com https://git.postgresql.org/pg/commitdiff/ad8a166ca86846ab691bd6dafc695e0f7dd96012

Tom Lane pushed:

  • Doc: minor improvements for "Formatting" section. Add more-specific links into the source tree. https://git.postgresql.org/pg/commitdiff/5577cd571ad3528471152f68636ac03c80576977

  • Fix misevaluation of STABLE parameters in CALL within plpgsql. Before commit 84f5c2908, a STABLE function in a plpgsql CALL statement's argument list would see an up-to-date snapshot, because exec_stmt_call would push a new snapshot. I got rid of that because the possibility of the snapshot disappearing within COMMIT made it too hard to manage a snapshot across the CALL statement. That's fine so far as the procedure itself goes, but I forgot to think about the possibility of STABLE functions within the CALL argument list. As things now stand, those'll be executed with the Portal's snapshot as ActiveSnapshot, keeping them from seeing updates more recent than Portal startup. (VOLATILE functions don't have a problem because they take their own snapshots; which indeed is also why the procedure itself doesn't have a problem. There are no STABLE procedures.) We can fix this by pushing a new snapshot transiently within ExecuteCallStmt itself. Popping the snapshot before we get into the procedure proper eliminates the management problem. The possibly-useless extra snapshot-grab is slightly annoying, but it's no worse than what happened before 84f5c2908. Per bug #17199 from Alexander Nawratil. Back-patch to v11, like the previous patch. Discussion: https://postgr.es/m/17199-1ab2561f0d94af92@postgresql.org https://git.postgresql.org/pg/commitdiff/4476bcb8773b306b9ca84bf2fadcf30acfa2c687

  • Doc: extend warnings about collation-mismatch hazards in postgres_fdw. Be a little more vocal about the risks of remote collations not matching local ones. Actually fixing these risks seems hard, and I've given up on the idea that it might be back-patchable. So the best we can do for the back branches is add documentation. Per discussion of bug #16583 from Jiří Fejfar. Discussion: https://postgr.es/m/2438715.1632510693@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/7b0be9fb2dddb214db2bed0e137b9b42c1479455

  • Avoid unnecessary division in interval_cmp_value(). Splitting the time field into days and microseconds is pretty useless when we're just going to recombine those values. It's unclear if anyone will notice the speedup in real-world cases, but a cycle shaved is a cycle earned. Discussion: https://postgr.es/m/2629129.1632675713@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/e94c1a55dada49772622d2be2d17a2a9973b2661

Álvaro Herrera pushed:

Andres Freund pushed:

Peter Geoghegan pushed:

  • Remove overzealous index deletion assertion. A broken HOT chain is not an unexpected condition, even when the offset number points past the end of the page's line pointer array. heap_prune_chain() does not (and never has) treated this condition as unexpected, so derivative code in heap_index_delete_tuples() shouldn't do so either. Oversight in commit 4228817449. The assertion can probably only fail on Postgres 14 and master. Earlier releases don't have commit 3c3b8a4b, which taught VACUUM to truncate the line pointer array of heap pages. Backpatch all the same, just to be consistent. Author: Peter Geoghegan pg@bowt.ie Reported-By: Alexander Lakhin exclusion@gmail.com Discussion: https://postgr.es/m/17197-9438f31f46705182@postgresql.org Backpatch: 12-, just like commit 4228817449. https://git.postgresql.org/pg/commitdiff/5e6716cde5749aea506dd3f30b099b6e9b4c5af8

  • Fix "single value strategy" index deletion issue. It is not appropriate for deduplication to apply single value strategy when triggered by a bottom-up index deletion pass. This wastes cycles because later bottom-up deletion passes will overinterpret older duplicate tuples that deduplication actually just skipped over "by design". It also makes bottom-up deletion much less effective for low cardinality indexes that happen to cross a meaningless "index has single key value per leaf page" threshold. To fix, slightly narrow the conditions under which deduplication's single value strategy is considered. We already avoided the strategy for a unique index, since our high level goal must just be to buy time for VACUUM to run (not to buy space). We'll now also avoid it when we just had a bottom-up pass that reported failure. The two cases share the same high level goal, and already overlapped significantly, so this approach is quite natural. Oversight in commit d168b666, which added bottom-up index deletion. Author: Peter Geoghegan pg@bowt.ie Discussion: https://postgr.es/m/CAH2-WznaOvM+Gyj-JQ0X=JxoMDxctDTYjiEuETdAGbF5EUc3MA@mail.gmail.com Backpatch: 14-, where bottom-up deletion was introduced. https://git.postgresql.org/pg/commitdiff/dd94c2852e6e3a246b9fd64bf2d9c7fc01020905

  • Document issue with heapam line pointer truncation. Checking that an offset number isn't past the end of a heap page's line pointer array was just a defensive sanity check for HOT-chain traversal code before commit 3c3b8a4b. It's etrictly necessary now, though. Add comments that reference the issue to code in heapam that needs to get it right. Per suggestion from Alexander Lakhin. Discussion: https://postgr.es/m/f76a292c-9170-1aef-91a0-59d9443b99a3@gmail.com https://git.postgresql.org/pg/commitdiff/c7aeb775df895db240dcd6f47242f7e08899adfb

  • nbtree README: Add note about latestRemovedXid. Point out that index tuple deletion generally needs a latestRemovedXid value for the deletion operation's WAL record. This is bound to be the most expensive part of the whole deletion operation now that it takes place up front, during original execution. This was arguably an oversight in commit 558a9165e08, which moved the work required to generate these values from index deletion REDO routines to original execution of index deletion operations. https://git.postgresql.org/pg/commitdiff/48064a8d330db259076fb7b2300544fbf65f4109

  • vacuumlazy.c: Remove obsolete 'onecall' comment. Remove obsolete reference to lazy_vacuum()'s onecall argument. The function argument was removed by commit 3499df0dee. Also remove adjoining comment block that introduces the wraparound failsafe concept. Talking about the failsafe here no longer makes sense, since lazy_vacuum() (and related functions) are no longer the only place where the failsafe might be triggered. This has been the case since commit c242baa4a8 taught VACUUM to consider triggering the failsafe mechanism during its initial heap scan. https://git.postgresql.org/pg/commitdiff/c1a47dfe2e9f814e61377f47aa79a113a4c73a63

  • Update obsolete nbtree deletion comments. _bt_delitems_delete() is no longer the high-level entry point used by index tuple deletion driven by index tuples whose LP_DEAD bits are set (now called "simple index tuple deletion"). It became a lower level routine that's only called by _bt_delitems_delete_check() following commit d168b66682. https://git.postgresql.org/pg/commitdiff/ce2a86053380f7e82dc8318ac48a22a7ab266398

Michaël Paquier pushed:

  • Introduce GUC shared_memory_size_in_huge_pages. This runtime-computed GUC shows the number of huge pages required for the server's main shared memory area, taking advantage of the work done in 0c39c29 and 0bd305e. This is useful for users to estimate the amount of huge pages required for a server as it becomes possible to do an estimation without having to start the server and potentially allocate a large chunk of shared memory. The number of huge pages is calculated based on the existing GUC huge_page_size if set, or by using the system's default by looking at /proc/meminfo on Linux. There is nothing new here as this commit reuses the existing calculation methods, and just exposes this information directly to the user. The routine calculating the huge page size is refactored to limit the number of files with platform-specific flags. This new GUC's name was the most popular choice based on the discussion done. This is only supported on Linux. I have taken the time to test the change on Linux, Windows and MacOS, though for the last two ones large pages are not supported. The first one calculates correctly the number of pages depending on the existing GUC huge_page_size or the system's default. Thanks to Andres Freund, Robert Haas, Kyotaro Horiguchi, Tom Lane, Justin Pryzby (and anybody forgotten here) for the discussion. Author: Nathan Bossart Discussion: https://postgr.es/m/F2772387-CE0F-46BF-B5F1-CC55516EB885@amazon.com https://git.postgresql.org/pg/commitdiff/43c1c4f65eab77bcfc4f535a7e9ac0421e0cf2a5

  • Fix places in TestLib.pm in need of adaptation to the output of Msys perl. Contrary to the output of native perl, Msys perl generates outputs with CRLFs characters. There are already places in the TAP code where CRLFs (\r\n) are automatically converted to LF (\n) on Msys, but we missed a couple of places when running commands and using their output for comparison, that would lead to failures. This problem has been found thanks to the test added in 5adb067 using TestLib::command_checks_all(), but after a closer look more code paths were missing a filter. This is backpatched all the way down to prevent any surprises if a new test is introduced in stable branches. Reviewed-by: Andrew Dunstan, Álvaro Herrera Discussion: https://postgr.es/m/1252480.1631829409@sss.pgh.pa.us Backpatch-through: 9.6 https://git.postgresql.org/pg/commitdiff/0d91c52a8fc71bfe5664a13368e42e1dabc5fe78

  • Fix some issues with TAP tests for postgres -C. This addresses two issues with the tests added in 0c39c292 for runtime GUCs: - Re-enable the test on Msys. The test could fail because of \r\n generated by Msys perl. 0d91c52a has taken care of this issue. - Allow the test to run in the context of a privileged account. CIs running under privileged accounts would fail on permission failures, as reported by Andres Freund. This issue is fixed by wrapping the postgres command within pg_ctl as the latter will take care of any permissions needed. The test checking a failure of postgres -C for a runtime parameter with an instance running is removed, as pg_ctl produces an unstable error code (no need for a CI to reproduce that). Discussion: https://postgr.es/m/20210921032040.lyl4lcax37aedx2x@alap3.anarazel.de https://git.postgresql.org/pg/commitdiff/1a9d802828110c87a207785aaf6b00d8917a86ad

  • doc: Add missing markup in CREATE EVENT TRIGGER page. Reported-by: rir Discussion: https://postgr.es/m/20210924183658.3syyitp3yuxjv2fp@localhost Backpatch-through: 9.6 https://git.postgresql.org/pg/commitdiff/1ab70b11e6425c955c24aa301188de32356bebb8

  • doc: Improve description of index vacuuming with GUCs. Index vacuums may happen multiple times depending on the number of dead tuples stored, as of maintenance_work_mem for a manual VACUUM. For autovacuum, this is controlled by autovacuum_work_mem instead, if set. The documentation mentioned the former, but not the latter in the context of autovacuum. Reported-by: Nikolai Berkoff Author: Laurenz Albe, Euler Taveira Discussion: https://postgr.es/m/161545365522.10134.12195402324485546870@wrigleys.postgresql.org Backpatch-through: 9.6 https://git.postgresql.org/pg/commitdiff/1ba841072ebeb1a6605395950a51c869de42a104

  • Fix typos in docs. Author: Justin Pryzby Discussion: https://postgr.es/m/20210924215827.GS831@telsasoft.com Backpatch-through: 9.6 https://git.postgresql.org/pg/commitdiff/7c1d8a243f8bd46604c9b292f392aab170eed821

Amit Kapila pushed:

Peter Eisentraut pushed:

Fujii Masao pushed:

  • postgres_fdw: Refactor transaction rollback code to avoid code duplication. In postgres_fdw, pgfdw_xact_callback() and pgfdw_subxact_callback() callback functions do almost the same thing to rollback remote toplevel- and sub-transaction. But previously their such rollback logics were implemented separately in each function and in different way. Which could decrease the readability and maintainability of the code. To fix the issue, this commit creates the common function to rollback remote transactions, and makes those callback functions use it. Which allows us to avoid unnecessary code duplication. Author: Fujii Masao Reviewed-by: Zhihong Yu, Bharath Rupireddy Discussion: https://postgr.es/m/62fbb63a-d46c-fb47-a56d-f6be1909aa44@oss.nttdata.com https://git.postgresql.org/pg/commitdiff/85c69611288f4096b7460d980bedaa777f824d24

Alexander Korotkov pushed:

John Naylor pushed: