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

PostgreSQL Weekly News - October 31, 2021

Posted on 2021-11-01 by PWN
PWN

PostgreSQL Weekly News - October 31, 2021

Happy Hallowe'en!

PostgreSQL Product News

pg_statement_rollback v1.3, an extension that adds server side transaction with rollback at statement level, released.

PostgreSQL Jobs for October

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

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

Michaël Paquier pushed:

  • Add replication command READ_REPLICATION_SLOT. The command is supported for physical slots for now, and returns the type of slot, its restart_lsn and its restart_tli. This will be useful for an upcoming patch related to pg_receivewal, to allow the tool to be able to stream from the position of a slot, rather than the last WAL position flushed by the backend (as reported by IDENTIFY_SYSTEM) if the archive directory is found as empty, which would be an advantage in the case of switching to a different archive locations with the same slot used to avoid holes in WAL segment archives. Author: Ronan Dunklau Reviewed-by: Kyotaro Horiguchi, Michael Paquier, Bharath Rupireddy Discussion: https://postgr.es/m/18708360.4lzOvYHigE@aivenronan https://git.postgresql.org/pg/commitdiff/b4ada4e19fd7bedb433e46516ccd0ca4213d2719

  • Allow pg_receivewal to stream from a slot's restart LSN. Prior to this patch, when running pg_receivewal, the streaming start point would be the current location of the archives if anything is found in the local directory where WAL segments are written, and pg_receivewal would fall back to the current WAL flush location if there are no archives, as of the result of an IDENTIFY_SYSTEM command. If for some reason the WAL files from pg_receivewal were moved, it is better to try a restart where we left at, which is the replication slot's restart_lsn instead of skipping right to the current flush location, to avoid holes in the WAL backed up. This commit changes pg_receivewal to use the following sequence of methods to determine the starting streaming LSN: - Scan the local archives. - Use the slot's restart_lsn, if supported by the backend and if a slot is defined. - Fallback to the current flush LSN as reported by IDENTIFY_SYSTEM. To keep compatibility with older server versions, we only attempt to use READ_REPLICATION_SLOT if the backend version is at least 15, and fallback to the older behavior of streaming from the current flush LSN if the command is not supported. Some TAP tests are added to cover this feature. Author: Ronan Dunklau Reviewed-by: Kyotaro Horiguchi, Michael Paquier, Bharath Rupireddy Discussion: https://postgr.es/m/18708360.4lzOvYHigE@aivenronan https://git.postgresql.org/pg/commitdiff/f61e1dd2cee6b1a1da75c2bb0ca3bc72f18748c1

  • Fix overly-lax regex pattern in TAP test of READ_REPLICATION_SLOT. The case checking for a NULL output when a slot does not exist was too lax, as it was passing for any output generated by the query. This fixes the matching pattern to be what it should be, matching only on "||". Oversight in b4ada4e. https://git.postgresql.org/pg/commitdiff/0db343dc13bc8657976c39ddbf7e0c7db8b2efff

  • doc: Fix grammar in page of pg_receivewal. Introduced by f61e1dd. Author: Kyotaro Horiguchi Discussion: https://postgr.es/m/[email protected] https://git.postgresql.org/pg/commitdiff/8af09daf5629e9b85f37cc23983819b8ccd11b43

  • Add test for copy of shared dependencies from template database. As 98ec35b has proved, there has never been any coverage in this area of the code. This commit adds a new TAP test with a template database that includes a small set of shared dependencies copied to a new database. The test is added in createdb, where we have never tested that -T generates a query with TEMPLATE, either. Reviewed-by: Tom Lane Discussion: https://postgr.es/m/[email protected] https://git.postgresql.org/pg/commitdiff/70bfc5ae537c8bfeed4849b7d9f814de89a155fe

  • doc: Fix link to SELinux user guide in sepgsql page. Reported-by: Anton Voloshin Discussion: https://postgr.es/m/[email protected] Backpatch-through: 10 https://git.postgresql.org/pg/commitdiff/cc1853b30048307d93f8aa30f4d64f88b527f04d

  • Add TAP test for archive_cleanup_command and recovery_end_command. This adds tests checking for the execution of both commands. The recovery test 002_archiving.pl is nicely adapted to that, as promotion is triggered already twice there, and even if any of those commands fail they don't affect recovery or promotion. A command success is checked using a file generated by an "echo" command, that should be able to work in all the buildfarm environments, even Msys (but we'll know soon about that). Command failure is tested with an "echo" command that points to a path that does not exist, scanning the backend logs to make sure that the failure happens. Both rely on the backend triggering the commands from the root of the data folder, making its logic more robust. Thanks to Neha Sharma for the extra tests on Windows. Author: Amul Sul, Michael Paquier Reviewed-by: Andres Freund, Euler Taveira Discussion: https://postgr.es/m/CAAJ_b95R_c4T5moq30qsybSU=eDzDHm=4SPiAWaiMWc2OW7=1Q@mail.gmail.com https://git.postgresql.org/pg/commitdiff/46dea2419ee7895a4eb3d048317682e6f18a17e1

  • Speed up TAP tests of pg_receivewal. This commit improves the speed of those tests by 25~30%, using some simple ideas to reduce the amount of data written by pg_receivewal: - Use a segment size of 1MB. While reducing the amount of data zeroed by pg_receivewal for the new segments, this improves the code coverage with a non-default segment size. - In the last test involving a slot's restart_lsn, generate a checkpoint to advance the redo LSN and the WAL retained by the slot created, reducing the number of segments that need to be archived. This counts for most of the gain. - Minimize the amount of data inserted into the dummy table. Reviewed-by: Ronan Dunklau Discussion: https://postgr.es/m/[email protected] https://git.postgresql.org/pg/commitdiff/d680992af5406245f769b697fbb4e130e6220664

Heikki Linnakangas pushed:

  • Clarify the logic in a few places in the new balanced merge code. In selectnewtape(), use 'nOutputTapes' rather than 'nOutputRuns' in the check for whether to start a new tape or to append a new run to an existing tape. Until 'maxTapes' is reached, nOutputTapes is always equal to nOutputRuns, so it doesn't change the logic, but it seems more logical to compare # of tapes with # of tapes. Also, currently maxTapes is never modified after the merging begins, but written this way, the code would still work if it was. (Although the nOutputRuns == nOutputTapes assertion would need to be removed and using nOutputRuns % nOutputTapes to distribute the runs evenly across the tapes wouldn't do a good job anymore). Similarly in mergeruns(), change to USEMEM(state->tape_buffer_mem) to account for the memory used for tape buffers. It's equal to availMem currently, but tape_buffer_mem is more direct and future-proof. For example, if we changed the logic to only allocate half of the remaining memory to tape buffers, USEMEM(state->tape_buffer_mem) would still be correct. Coverity complained about these. Hopefully this patch helps it to understand the logic better. Thanks to Tom Lane for initial analysis. https://git.postgresql.org/pg/commitdiff/166f94377c886516ca986ef8a623cd2e854fe911

Robert Haas pushed:

Thomas Munro pushed:

Daniel Gustafsson pushed:

Fujii Masao pushed:

  • Improve HINT message that FDW reports when there are no valid options. The foreign data wrapper's validator function provides a HINT message with list of valid options for the object specified in CREATE or ALTER command, when the option given in the command is invalid. Previously postgresql_fdw_validator() and the validator functions for postgres_fdw and dblink_fdw worked in that way even there were no valid options in the object, which could lead to the HINT message with empty list (because there were no valid options). For example, ALTER FOREIGN DATA WRAPPER postgres_fdw OPTIONS (format 'csv') reported the following ERROR and HINT messages. This behavior was confusing. ERROR: invalid option "format" HINT: Valid options in this context are: There is no such issue in file_fdw. The validator function for file_fdw reports the HINT message "There are no valid options in this context." instead in that case. This commit improves postgresql_fdw_validator() and the validator functions for postgres_fdw and dblink_fdw so that they do likewise. For example, this change causes the above ALTER FOREIGN DATA WRAPPER command to report the following messages. ERROR: invalid option "nonexistent" HINT: There are no valid options in this context. Author: Kosei Masumura Reviewed-by: Bharath Rupireddy, Fujii Masao Discussion: https://postgr.es/m/[email protected] https://git.postgresql.org/pg/commitdiff/5fedf7417b69295294b154a219edd8a26eaa6ab6

Jeff Davis pushed:

Amit Kapila pushed:

Magnus Hagander pushed:

Peter Geoghegan pushed:

Tom Lane pushed:

  • Improve contrib/amcheck's tests for CREATE INDEX CONCURRENTLY. Commits fdd965d07 and 3cd9c3b92 tested CREATE INDEX CONCURRENTLY by launching two separate pgbench runs concurrently. This was needed so that only a single client thread would run CREATE INDEX CONCURRENTLY, avoiding deadlock between two CICs. However, there's a better way, which is to use an advisory lock to prevent concurrent CICs. That's better in part because the test code is shorter and more readable, but mostly because it automatically scales things to launch an appropriate number of CICs relative to the number of INSERT transactions. As committed, typically half to three-quarters of the CIC transactions were pointless because the INSERT transactions had already stopped. In passing, remove background_pgbench, which was added to support these tests and isn't needed anymore. We can always put it back if we find a use for it later. Back-patch to v12; older pgbench versions lack the conditional-execution features needed for this method. Tom Lane and Andrey Borodin Discussion: https://postgr.es/m/[email protected] https://git.postgresql.org/pg/commitdiff/7f580aa5d88a9b03d66fcb9a1d7c4fcd69d9e126

  • Speed up printing of integers in snprintf.c. Since the only possible divisors are 8, 10, and 16, it doesn't cost much code space to replace the division loop with three copies using constant divisors. On most machines, division by a constant can be done a lot more cheaply than division by an arbitrary value. A microbenchmark testing just snprintf("foo %d") with a 9-digit value showed about a 2X speedup for me (tgl). Most of Postgres isn't too dependent on the speed of snprintf, so that the effect in real-world cases is barely measurable. Still, a cycle saved is a cycle earned. Arjan van de Ven Discussion: https://postgr.es/m/[email protected] Discussion: https://postgr.es/m/[email protected] https://git.postgresql.org/pg/commitdiff/3c17926eedd51c4094db7c62f59950918044ab1c

  • Update time zone data files to tzdata release 2021e. DST law changes in Fiji, Jordan, Palestine, and Samoa. Historical corrections for Barbados, Cook Islands, Guyana, Niue, Portugal, and Tonga. Also, the Pacific/Enderbury zone has been renamed to Pacific/Kanton. The following zones have been merged into nearby, more-populous zones whose clocks have agreed since 1970: Africa/Accra, America/Atikokan, America/Blanc-Sablon, America/Creston, America/Curacao, America/Nassau, America/Port_of_Spain, Antarctica/DumontDUrville, and Antarctica/Syowa. https://git.postgresql.org/pg/commitdiff/937aafd6d5580b81134c7f303d04cf7561ad0309

  • Test and document the behavior of initialization cross-refs in plpgsql. We had a test showing that a variable isn't referenceable in its own initialization expression, nor in prior ones in the same block. It is referenceable in later expressions in the same block, but AFAICS there is no test case exercising that. Add one, and also add some error cases. Also, document that this is possible, since the docs failed to cover the point. Per question from tomás at tuxteam. I don't feel any need to back-patch this, but we should ensure we don't break it in future. Discussion: https://postgr.es/m/[email protected] https://git.postgresql.org/pg/commitdiff/a2a731d6c9db0ba650aa6f7c4fe349ccf712f74d

Peter Eisentraut pushed: