PostgreSQL Weekly News - October 31, 2021PostgreSQL Weekly News - October 31, 2021Happy Hallowe'en! PostgreSQL Product Newspg_statement_rollback v1.3, an extension that adds server side transaction with rollback at statement level, released. PostgreSQL Jobs for Octoberhttps://archives.postgresql.org/pgsql-jobs/2021-10/ PostgreSQL in the NewsPlanet 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 PatchesMichaë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/20211026.112304.1962954080884317968.horikyota.ntt@gmail.com 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/YXDTl+PfSnqmbbkE@paquier.xyz 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/15a86d4e-a237-1acd-18a2-fd69730f1ab9@postgrespro.ru 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/YXqYKAdVEqmyTltK@paquier.xyz 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: StartupXLOG: Call CleanupAfterArchiveRecovery after XLogReportParameters. This does a better job grouping related operations together, since all of the WAL records that we need to write prior to allowing WAL writes generally and written by a single uninterrupted stretch of code. Since CleanupAfterArchiveRecovery() just (1) runs recovery_end_command, (2) removes non-parent xlog files, and (3) archives any final partial segment, this should be safe, because all of those things are pretty much unrelated to the WAL record written by XLogReportParameters(). Amul Sul, per a suggestion from me Discussion: http://postgr.es/m/CAAJ_b97fysj6sRSQEfOHj-y8Jfd5uPqOgO74qast89B4WfD+TA@mail.gmail.com https://git.postgresql.org/pg/commitdiff/a75dbf7f9ee6ff0c0e2ab4b224b04fc50c4e6577 StartupXLOG: Don't repeatedly disable/enable local xlog insertion. All the code that runs in the startup process to write WAL records before that's allowed generally is now consecutive, so there's no reason to shut the facility to write WAL locally off and then turn it on again three times in a row. Unfortunately, this requires a slight kludge in the checkpointer, which needs to separately enable writing WAL in order to write the checkpoint record. Because that code might run in the same process as StartupXLOG() if we are in single-user mode, we must save/restore the state of the LocalXLogInsertAllowed flag. Hopefully, we'll be able to eliminate this wart in further refactoring, but it's not too bad anyway. Amul Sul, with modifications by me. Discussion: http://postgr.es/m/CAAJ_b97fysj6sRSQEfOHj-y8Jfd5uPqOgO74qast89B4WfD+TA@mail.gmail.com https://git.postgresql.org/pg/commitdiff/18e0913a420349d373cfd8e45b91b4777501fb74 Remove useless code from CreateReplicationSlot. According to the comments, we initialize sendTimeLineIsHistoric and sendTimeLine here for the benefit of WalSndSegmentOpen. However, the only way that can happen is if logical_read_xlog_page calls WALRead. And since logical_read_xlog_page initializes the same global variables internally, we don't need to also do it here. These initializations have been here since replication slots were introduced in commit 858ec11858a914d4c380971985709b6d6b7dd6fc. They were certainly useless at that time, too, because logical decoding didn't yet exist then, and physical replication doesn't examine any WAL at the time of slot creation. I haven't checked all the intermediate versions, but I suspect there's no point at which this code ever did anything useful. To reduce future confusion, remove the code. Since there's no functional defect, no back-patch. Discussion: http://postgr.es/m/CA+TgmobSWzacEs+r6C-7DrOPDHoDar4i9gzxB3SCBr5qjnLmVQ@mail.gmail.com https://git.postgresql.org/pg/commitdiff/902a2c280012557b85c7e0fce3f6f0e355cb2d69 Add enable_timeout_every() to fire the same timeout repeatedly. enable_timeout_at() and enable_timeout_after() can still be used when you want to fire a timeout just once. Patch by me, per a suggestion from Tom Lane. Discussion: http://postgr.es/m/2992585.1632938816@sss.pgh.pa.us Discussion: http://postgr.es/m/CA+TgmoYqSF5sCNrgTom9r3Nh=at4WmYFD=gsV-omStZ60S0ZUQ@mail.gmail.com https://git.postgresql.org/pg/commitdiff/732e6677a667c03b1551a855e3216644b0f125ec Report progress of startup operations that take a long time. Users sometimes get concerned whe they start the server and it emits a few messages and then doesn't emit any more messages for a long time. Generally, what's happening is either that the system is taking a long time to apply WAL, or it's taking a long time to reset unlogged relations, or it's taking a long time to fsync the data directory, but it's not easy to tell which is the case. To fix that, add a new 'log_startup_progress_interval' setting, by default 10s. When an operation that is known to be potentially long-running takes more than this amount of time, we'll log a status update each time this interval elapses. To avoid undesirable log chatter, don't log anything about WAL replay when in standby mode. Nitin Jadhav and Robert Haas, reviewed by Amul Sul, Bharath Rupireddy, Justin Pryzby, Michael Paquier, and Álvaro Herrera. Discussion: https://postgr.es/m/CA+TgmoaHQrgDFOBwgY16XCoMtXxsrVGFB2jNCvb7-ubuEe1MGg@mail.gmail.com Discussion: https://postgr.es/m/CAMm1aWaHF7VE69572_OLQ+MgpT5RUiUDgF1x5RrtkJBLdpRj3Q@mail.gmail.com https://git.postgresql.org/pg/commitdiff/9ce346eabf350a130bba46be3f8c50ba28506969 Initialize variable to placate compiler. Per Nathan Bossart. Discussion: http://postgr.es/m/FECEE7FC-CB74-45A9-BB24-89FEE52A9585@amazon.com https://git.postgresql.org/pg/commitdiff/a030a0c5ccb113ccd09d0f0b82f1edb5e49ed607 When fetching WAL for a basebackup, report errors with a sensible TLI. The previous code used ThisTimeLineID, which need not even be initialized here, although it usually was in practice, because pg_basebackup issues IDENTIFY_SYSTEM before calling BASE_BACKUP, and that initializes ThisTimeLineID as a side effect. That's not really good enough, though, not only because we shoudn't be counting on side effects like that, but also because the TLI could change meanwhile. Fortunately, we have convenient access to more meaningful TLI values, so use those instead. Because of the way this logic is coded, the consequences of using a possibly-incorrect TLI here are no worse than a slightly confusing error message, I don't want to take any risk here, so no back-patch at least for now. Patch by me, reviewed by Kyotaro Horiguchi and Michael Paquier Discussion: http://postgr.es/m/CA+TgmoZRNWGWYDX9RgTXMG6_nwSdB=PB-PPRUbvMUTGfmL2sHQ@mail.gmail.com https://git.postgresql.org/pg/commitdiff/2f5c4397c39dea49c5608ba583868e26d767fc32 Fix race condition in startup progress reporting. Commit 9ce346eabf350a130bba46be3f8c50ba28506969 added startup progress reporting, but begin_startup_progress_phase has a race condition: the timeout for the previous phase might fire just before we reschedule the interrupt for the next phase. To avoid the race, disable the timeout, clear the flag, and then re-enable the timeout. Patch by me, reviewed by Nitin Jadhav. Discussion: https://postgr.es/m/CA+TgmoYq38i6iAzfRLVxA6Cm+wMCf4WM8wC3o_a+X_JvWC8bJg@mail.gmail.com https://git.postgresql.org/pg/commitdiff/5ccceb2946d4104804f8dca67515b602f5e78cdd
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/557d06cebe19081bfcc83ee2affc98d3@oss.nttdata.com https://git.postgresql.org/pg/commitdiff/5fedf7417b69295294b154a219edd8a26eaa6ab6
Jeff Davis pushed: Amit Kapila pushed: Allow publishing the tables of schema. A new option "FOR ALL TABLES IN SCHEMA" in Create/Alter Publication allows one or more schemas to be specified, whose tables are selected by the publisher for sending the data to the subscriber. The new syntax allows specifying both the tables and schemas. For example: CREATE PUBLICATION pub1 FOR TABLE t1,t2,t3, ALL TABLES IN SCHEMA s1,s2; OR ALTER PUBLICATION pub1 ADD TABLE t1,t2,t3, ALL TABLES IN SCHEMA s1,s2; A new system table "pg_publication_namespace" has been added, to maintain the schemas that the user wants to publish through the publication. Modified the output plugin (pgoutput) to publish the changes if the relation is part of schema publication. Updates pg_dump to identify and dump schema publications. Updates the \d family of commands to display schema publications and \dRp+ variant will now display associated schemas if any. Author: Vignesh C, Hou Zhijie, Amit Kapila Syntax-Suggested-by: Tom Lane, Alvaro Herrera Reviewed-by: Greg Nancarrow, Masahiko Sawada, Hou Zhijie, Amit Kapila, Haiying Tang, Ajin Cherian, Rahila Syed, Bharath Rupireddy, Mark Dilger Tested-by: Haiying Tang Discussion: https://www.postgresql.org/message-id/CALDaNm0OANxuJ6RXqwZsM1MSY4s19nuH3734j4a72etDwvBETQ@mail.gmail.com https://git.postgresql.org/pg/commitdiff/5a2832465fd8984d089e8c44c094e6900d987fcd Add tap tests for the schema publications. This adds additional tests for commit 5a2832465f ("Allow publishing the tables of schema.). This allows testing streaming of data in tables that are published via schema publications. Author: Vignesh C, Haiying Tang Reviewed-by: Greg Nancarrow, Hou Zhijie, Amit Kapila Discussion: https://www.postgresql.org/message-id/CALDaNm0OANxuJ6RXqwZsM1MSY4s19nuH3734j4a72etDwvBETQ%40mail.gmail.com https://git.postgresql.org/pg/commitdiff/6b0f6f79eef2168ce38a8ee99c3ed76e3df5d7ad
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/139687.1635277318@sss.pgh.pa.us 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/40a4b32a-b841-4667-11b2-a0baedb12714@linux.intel.com Discussion: https://postgr.es/m/6e51c644-1b6d-956e-ac24-2d1b0541d532@linux.intel.com 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/20211029121435.GA5414@tuxteam.de https://git.postgresql.org/pg/commitdiff/a2a731d6c9db0ba650aa6f7c4fe349ccf712f74d
Peter Eisentraut pushed: |
