Happy Hallowe'en!
pg_statement_rollback v1.3, an extension that adds server side transaction with rollback at statement level, released.
https://archives.postgresql.org/pgsql-jobs/2021-10/
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.
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/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:
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:
Ensure that slots are zeroed before use. The previous coding relied on the memory for the slots being zeroed elsewhere, which while it was true in this case is not an contract which is guaranteed to hold. Explicitly clear the tts_isnull array to ensure that the slots are filled from a known state. Backpatch to v14 where the catalog multi-inserts were introduced. Reviewed-by: Michael Paquier michael@paquier.xyz Discussion: https://postgr.es/m/CAJ7c6TP0AowkUgNL6zcAK-s5HYsVHVBRWfu69FRubPpfwZGM9A@mail.gmail.com Backpatch-through: 14 https://git.postgresql.org/pg/commitdiff/e63ce9e8d6ac8dced20592c4134004640f9f5644
Fix VPATH builds for src/test/ssl targets. Commit b4c4a00ea refactored the gist of the sslfiles target into a separate makefile in order to override settings in Makefile.global. The invocation of this this file didn't however include the absolute path for VPATH builds, resulting in "make clean" failing. Fix by providing the path to the new makefile. Reported-by: Andres Freund andres@anarazel.de Discussion: https://postgr.es/m/20211026174152.jjcagswnbhxu7uqz@alap3.anarazel.de https://git.postgresql.org/pg/commitdiff/349cd8c582a1e666c9c804850cf5b532b86cd1b4
Fix typos in comments. Author: Peter Smith smithpb2250@gmail.com Discussion: https://postgr.es/m/CAHut+PsN_gmKu-KfeEb9NDARoTPbs4AN4PPu=6LZXFZRJ13SEw@mail.gmail.com https://git.postgresql.org/pg/commitdiff/8af57ad81578f825ac8c46840c841833db205106
Fujii Masao pushed:
Jeff Davis pushed:
Allow GRANT on pg_log_backend_memory_contexts(). Remove superuser check, allowing any user granted permissions on pg_log_backend_memory_contexts() to log the memory contexts of any backend. Note that this could allow a privileged non-superuser to log the memory contexts of a superuser backend, but as discussed, that does not seem to be a problem. Reviewed-by: Nathan Bossart, Bharath Rupireddy, Michael Paquier, Kyotaro Horiguchi, Andres Freund Discussion: https://postgr.es/m/e5cf6684d17c8d1ef4904ae248605ccd6da03e72.camel@j-davis.com https://git.postgresql.org/pg/commitdiff/f0b051e322d530a340e62f2ae16d99acdbcb3d05
Grant memory views to pg_read_all_stats. Grant privileges on views pg_backend_memory_contexts and pg_shmem_allocations to the role pg_read_all_stats. Also grant on the underlying functions that those views depend on. Author: Bharath Rupireddy bharath.rupireddyforpostgres@gmail.com Reviewed-by: Nathan Bossart bossartn@amazon.com Discussion: https://postgr.es/m/CALj2ACWAZo3Ar_EVsn2Zf9irG+hYK3cmh1KWhZS_Od45nd01RA@mail.gmail.com https://git.postgresql.org/pg/commitdiff/77ea4f94393eb4a16df32b573bf053bedaef2e09
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:
Further harden nbtree posting split code. Add more defensive checks around posting list split code. These should detect corruption involving duplicate table TIDs earlier and more reliably than any existing check. Follow up to commit 8f72bbac. Discussion: https://postgr.es/m/CAH2-WzkrSY_kjyd1_M5xJK1uM0govJXMxPn8JUSvwcUOiHuWVw@mail.gmail.com Backpatch: 13-, where nbtree deduplication was introduced. https://git.postgresql.org/pg/commitdiff/a5213adf3d351a31c5f5eae1a756a9d3555dc31c
Fix ordering of items in nbtree error message. Oversight in commit a5213adf. Backpatch: 13-, just like commit a5213adf. https://git.postgresql.org/pg/commitdiff/c2381b51049bad5dd1863ab1116b315bd7693b7c
Remove obsolete nbtree LP_DEAD item comments. Comments above
_bt_findinsertloc() that talk about LP_DEAD items are now out of place. We
already discuss index tuple deletion at an earlier point in the same comment
block. Oversight in commit d168b666.
https://git.postgresql.org/pg/commitdiff/4c6afd805b8db3492c8f409ecdba192d853fd571
Demote pg_unreachable() in heapam to an assertion. Commit d168b66682, which overhauled index deletion, added a pg_unreachable() to the end of a sort comparator used when sorting heap TIDs from an index page. This allows the compiler to apply optimizations that assume that the heap TIDs from the index AM must always be unique. That doesn't seem like a good idea now, given recent reports of corruption involving duplicate TIDs in indexes on Postgres
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:
Remove unused chunk from standalone-profile.xsl. unused since 1707a0d2aa6b2bcfe78f63836c769943a1a6b9e0 https://git.postgresql.org/pg/commitdiff/b8b62b4be28b8acd36d32d5db65162bbbcd3a754
uuid-ossp: Remove obsolete build connection with pgcrypto. unused since a8ed6bb8f4cf259b95c1bff5da09a8f4c79dca46 https://git.postgresql.org/pg/commitdiff/237c12aabe39a58f3f5364fd94e0ca8ae8824957
doc: Remove some obsolete pgcrypto documentation. The pgcrypto documentation contained acknowledgments of used external code, but some of this code has been moved to src/common/, so mentioning it with pgcrypto no longer makes sense, so remove it. https://git.postgresql.org/pg/commitdiff/e6c60719e6c6ee9bd396f430879e1de9079bf74c
pg_dump: Refactor messages. This reduces the number of separate messages for translation. https://git.postgresql.org/pg/commitdiff/fd2706589a7da4be6f6998befdf8e5fdea1565b8