# 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](https://github.com/lzlabs/pg_statement_rollback/releases/tag/v1.3). # PostgreSQL Jobs for October [https://archives.postgresql.org/pgsql-jobs/2021-10/](https://archives.postgresql.org/pgsql-jobs/2021-10/) # PostgreSQL in the News Planet PostgreSQL: [https://planet.postgresql.org/](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://postgr.es/m/18708360.4lzOvYHigE@aivenronan) [https://git.postgresql.org/pg/commitdiff/b4ada4e19fd7bedb433e46516ccd0ca4213d2719](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://postgr.es/m/18708360.4lzOvYHigE@aivenronan) [https://git.postgresql.org/pg/commitdiff/f61e1dd2cee6b1a1da75c2bb0ca3bc72f18748c1](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](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://postgr.es/m/[email protected]) [https://git.postgresql.org/pg/commitdiff/8af09daf5629e9b85f37cc23983819b8ccd11b43](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://postgr.es/m/[email protected]) [https://git.postgresql.org/pg/commitdiff/70bfc5ae537c8bfeed4849b7d9f814de89a155fe](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]](https://postgr.es/m/[email protected]) Backpatch-through: 10 [https://git.postgresql.org/pg/commitdiff/cc1853b30048307d93f8aa30f4d64f88b527f04d](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=1...@mail.gmail.com](https://postgr.es/m/CAAJ_b95R_c4T5moq30qsybSU=eDzDHm=4SPiAWaiMWc2OW7=1...@mail.gmail.com) [https://git.postgresql.org/pg/commitdiff/46dea2419ee7895a4eb3d048317682e6f18a17e1](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://postgr.es/m/[email protected]) [https://git.postgresql.org/pg/commitdiff/d680992af5406245f769b697fbb4e130e6220664](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](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...@mail.gmail.com](http://postgr.es/m/caaj_b97fysj6srsqefohj-y8jfd5upqogo74qast89b4wfd...@mail.gmail.com) [https://git.postgresql.org/pg/commitdiff/a75dbf7f9ee6ff0c0e2ab4b224b04fc50c4e6577](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...@mail.gmail.com](http://postgr.es/m/caaj_b97fysj6srsqefohj-y8jfd5upqogo74qast89b4wfd...@mail.gmail.com) [https://git.postgresql.org/pg/commitdiff/18e0913a420349d373cfd8e45b91b4777501fb74](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-7dropdhodar4i9gzxb3scbr5qjnl...@mail.gmail.com](http://postgr.es/m/ca+tgmobswzaces+r6c-7dropdhodar4i9gzxb3scbr5qjnl...@mail.gmail.com) [https://git.postgresql.org/pg/commitdiff/902a2c280012557b85c7e0fce3f6f0e355cb2d69](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/[email protected]](http://postgr.es/m/[email protected]) Discussion: [http://postgr.es/m/CA+TgmoYqSF5sCNrgTom9r3Nh=at4WmYFD=gsv-omstz60s0...@mail.gmail.com](http://postgr.es/m/CA+TgmoYqSF5sCNrgTom9r3Nh=at4WmYFD=gsv-omstz60s0...@mail.gmail.com) [https://git.postgresql.org/pg/commitdiff/732e6677a667c03b1551a855e3216644b0f125ec](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-ubuee1...@mail.gmail.com](https://postgr.es/m/ca+tgmoahqrgdfobwgy16xcomtxxsrvgfb2jncvb7-ubuee1...@mail.gmail.com) Discussion: [https://postgr.es/m/camm1awahf7ve69572_olq+mgpt5ruiudgf1x5rrtkjbldpr...@mail.gmail.com](https://postgr.es/m/camm1awahf7ve69572_olq+mgpt5ruiudgf1x5rrtkjbldpr...@mail.gmail.com) [https://git.postgresql.org/pg/commitdiff/9ce346eabf350a130bba46be3f8c50ba28506969](https://git.postgresql.org/pg/commitdiff/9ce346eabf350a130bba46be3f8c50ba28506969) - Initialize variable to placate compiler. Per Nathan Bossart. Discussion: [http://postgr.es/m/[email protected]](http://postgr.es/m/[email protected]) [https://git.postgresql.org/pg/commitdiff/a030a0c5ccb113ccd09d0f0b82f1edb5e49ed607](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-pprubvmutgfml2...@mail.gmail.com](http://postgr.es/m/CA+TgmoZRNWGWYDX9RgTXMG6_nwSdB=pb-pprubvmutgfml2...@mail.gmail.com) [https://git.postgresql.org/pg/commitdiff/2f5c4397c39dea49c5608ba583868e26d767fc32](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_jvwc8...@mail.gmail.com](https://postgr.es/m/ca+tgmoyq38i6iazfrlvxa6cm+wmcf4wm8wc3o_a+x_jvwc8...@mail.gmail.com) [https://git.postgresql.org/pg/commitdiff/5ccceb2946d4104804f8dca67515b602f5e78cdd](https://git.postgresql.org/pg/commitdiff/5ccceb2946d4104804f8dca67515b602f5e78cdd) Thomas Munro pushed: - Reject huge_pages=on if shared_memory_type=sysv. It doesn't work (it could, but hasn't been implemented). Back-patch to 12, where shared_memory_type arrived. Reported-by: Alexander Lakhin <[email protected]> Reviewed-by: Alexander Lakhin <[email protected]> Discussion: [https://postgr.es/m/[email protected]](https://postgr.es/m/[email protected]) [https://git.postgresql.org/pg/commitdiff/8781b0ce25e702ba4a4f032d00da7acdef8dbfe1](https://git.postgresql.org/pg/commitdiff/8781b0ce25e702ba4a4f032d00da7acdef8dbfe1) 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 <[email protected]> Discussion: [https://postgr.es/m/caj7c6tp0aowkugnl6zcak-s5hysvhvbrwfu69frubppfwzg...@mail.gmail.com](https://postgr.es/m/caj7c6tp0aowkugnl6zcak-s5hysvhvbrwfu69frubppfwzg...@mail.gmail.com) Backpatch-through: 14 [https://git.postgresql.org/pg/commitdiff/e63ce9e8d6ac8dced20592c4134004640f9f5644](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 <[email protected]> Discussion: [https://postgr.es/m/[email protected]](https://postgr.es/m/[email protected]) [https://git.postgresql.org/pg/commitdiff/349cd8c582a1e666c9c804850cf5b532b86cd1b4](https://git.postgresql.org/pg/commitdiff/349cd8c582a1e666c9c804850cf5b532b86cd1b4) - Fix typos in comments. Author: Peter Smith <[email protected]> Discussion: [https://postgr.es/m/CAHut+PsN_gmKu-KfeEb9NDARoTPbs4AN4PPu=6lzxfzrj13...@mail.gmail.com](https://postgr.es/m/CAHut+PsN_gmKu-KfeEb9NDARoTPbs4AN4PPu=6lzxfzrj13...@mail.gmail.com) [https://git.postgresql.org/pg/commitdiff/8af57ad81578f825ac8c46840c841833db205106](https://git.postgresql.org/pg/commitdiff/8af57ad81578f825ac8c46840c841833db205106) 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://postgr.es/m/[email protected]) [https://git.postgresql.org/pg/commitdiff/5fedf7417b69295294b154a219edd8a26eaa6ab6](https://git.postgresql.org/pg/commitdiff/5fedf7417b69295294b154a219edd8a26eaa6ab6) 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/[email protected]](https://postgr.es/m/[email protected]) [https://git.postgresql.org/pg/commitdiff/f0b051e322d530a340e62f2ae16d99acdbcb3d05](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 <[email protected]> Reviewed-by: Nathan Bossart <[email protected]> Discussion: [https://postgr.es/m/calj2acwazo3ar_evsn2zf9irg+hyk3cmh1kwhzs_od45nd0...@mail.gmail.com](https://postgr.es/m/calj2acwazo3ar_evsn2zf9irg+hyk3cmh1kwhzs_od45nd0...@mail.gmail.com) [https://git.postgresql.org/pg/commitdiff/77ea4f94393eb4a16df32b573bf053bedaef2e09](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/caldanm0oanxuj6rxqwzsm1msy4s19nuh3734j4a72etdwvb...@mail.gmail.com](https://www.postgresql.org/message-id/caldanm0oanxuj6rxqwzsm1msy4s19nuh3734j4a72etdwvb...@mail.gmail.com) [https://git.postgresql.org/pg/commitdiff/5a2832465fd8984d089e8c44c094e6900d987fcd](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://www.postgresql.org/message-id/CALDaNm0OANxuJ6RXqwZsM1MSY4s19nuH3734j4a72etDwvBETQ%40mail.gmail.com) [https://git.postgresql.org/pg/commitdiff/6b0f6f79eef2168ce38a8ee99c3ed76e3df5d7ad](https://git.postgresql.org/pg/commitdiff/6b0f6f79eef2168ce38a8ee99c3ed76e3df5d7ad) Magnus Hagander pushed: - Clarify that --system reindexes system catalogs *only*. Make this more clear both in the help message and docs. Reviewed-By: Michael Paquier Backpatch-through: 9.6 Discussion: [https://postgr.es/m/cabuevew6je0wuftlhpkok4+bobudre-fkw3n4ckqgdbmfu4...@mail.gmail.com](https://postgr.es/m/cabuevew6je0wuftlhpkok4+bobudre-fkw3n4ckqgdbmfu4...@mail.gmail.com) [https://git.postgresql.org/pg/commitdiff/eff61383b982be8dc71d942340a839bea88a9eab](https://git.postgresql.org/pg/commitdiff/eff61383b982be8dc71d942340a839bea88a9eab) 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_m5xjk1um0govjxmxpn8jusvwcuoihu...@mail.gmail.com](https://postgr.es/m/cah2-wzkrsy_kjyd1_m5xjk1um0govjxmxpn8jusvwcuoihu...@mail.gmail.com) Backpatch: 13-, where nbtree deduplication was introduced. [https://git.postgresql.org/pg/commitdiff/a5213adf3d351a31c5f5eae1a756a9d3555dc31c](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](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](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 14. Demote to an assertion, just in case. Backpatch: 14-, where index deletion was overhauled. [https://git.postgresql.org/pg/commitdiff/5f55fc5a346e1ab54f3d756e368d276b95be8c4a](https://git.postgresql.org/pg/commitdiff/5f55fc5a346e1ab54f3d756e368d276b95be8c4a) 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://postgr.es/m/[email protected]) [https://git.postgresql.org/pg/commitdiff/7f580aa5d88a9b03d66fcb9a1d7c4fcd69d9e126](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]](https://postgr.es/m/[email protected]) Discussion: [https://postgr.es/m/[email protected]](https://postgr.es/m/[email protected]) [https://git.postgresql.org/pg/commitdiff/3c17926eedd51c4094db7c62f59950918044ab1c](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](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://postgr.es/m/[email protected]) [https://git.postgresql.org/pg/commitdiff/a2a731d6c9db0ba650aa6f7c4fe349ccf712f74d](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](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](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](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](https://git.postgresql.org/pg/commitdiff/fd2706589a7da4be6f6998befdf8e5fdea1565b8)
