PostgreSQL Weekly News - July 18, 2021

PostgreSQL Weekly News - July 18, 2021

Person of the week

PostgreSQL Jobs for July

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

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

Michaël Paquier pushed:

• pageinspect: Improve page_header() for pages of 32kB. ld_upper, ld_lower, pd_special and the page size have been using smallint as return type, which could cause those fields to return negative values in certain cases for builds configures with a page size of 32kB. Bump pageinspect to 1.10. page_header() is able to handle the correct return type of those fields at runtime when using an older version of the extension, with some tests are added to cover that. Author: Quan Zongliang Reviewed-by: Michael Paquier, Bharath Rupireddy Discussion: https://postgr.es/m/8b8ec36e-61fe-14f9-005d-07bc85aa4eed@yeah.net https://git.postgresql.org/pg/commitdiff/127404fbe28455d6e8183fa58f3b7aefeba8f909

• Fix issues with Windows' stat() for files pending on deletion. The code introduced by bed9075 to enhance the stat() implementation on Windows for file sizes larger than 4GB fails to properly detect files pending for deletion with its method based on NtQueryInformationFile() or GetFileInformationByHandleEx(), as proved by Alexander Lakhin in a custom TAP test of his own. The method used in the implementation of open() to sleep and loop when when failing on ERROR_ACCESS_DENIED (EACCES) is showing much more stability, so switch to this method. This could still lead to issues if the permission problem stays around for much longer than the timeout of 1 second used, but that should (hopefully) never happen in performance-critical paths. Still, there could be a point in increasing the timeouts for the sake of machines that handle heavy loads. Note that WIN32's open() now uses microsoft_native_stat() as it should be similar to stat() when working around issues with concurrent file deletions. I have spent some time testing this patch with pgbench in combination of the SQL functions from genfile.c, as well as running the TAP test provided on the thread with MSVC builds, and this looks much more stable than the previous method. Author: Alexander Lakhin Reviewed-by: Tom Lane, Michael Paquier, Justin Pryzby Discussion: https://postgr.es/m/c3427edf-d7c0-ff57-90f6-b5de3bb62709@gmail.com Backpatch-through: 14 https://git.postgresql.org/pg/commitdiff/54fb8c7ddf152629021cab3ac3596354217b7d81

• Revert "Fix issues with Windows' stat() for files pending on deletion". This reverts commit 54fb8c7, as per the issues reported by fairywren when it comes to MinGW because of the lack of microsoft_native_stat() there. Using just stat() for MSVC is not sufficient to take care of the concurrency problems with files pending on deletion. It may be possible to paint some MINGW64 in the code to switch to a different implementation of stat() in this build context, but I am not sure either if relying on the implementation of stat() in MinGW to take care of the problems we are trying to fix is enough or not. So this needs more study. Discussion: https://postgr.es/m/YOvOlfRrIO0yGtgw@paquier.xyz Backpatch-through: 14 https://git.postgresql.org/pg/commitdiff/2c9b46c090e76c62f24563b9be2c34e6b92e9329

• Install properly fe-auth-sasl.h. The internals of the frontend-side callbacks for SASL are visible in libpq-int.h, but the header was not getting installed. This would cause compilation failures for applications playing with the internals of libpq. Issue introduced in 9fd8557. Author: Mikhail Kulagin Reviewed-by: Jacob Champion Discussion: https://postgr.es/m/05ce01d777cb$40f31d60$c2d95820$@postgrespro.ru https://git.postgresql.org/pg/commitdiff/6c9c2831668345122fd0f92280b30f3bbe2dd4e6

• Remove unnecessary assertion in postmaster.c. A code path asserted that the archiver was dead, but a check made that impossible to happen. Author: Bharath Rupireddy Discussion: https://postgr.es/m/CALj2ACW=CYE1ars+2XyPTEPq0wQvru4c0dPZ=Nrn3EqNBkksvQ@mail.gmail.com Backpatch-throgh: 14 https://git.postgresql.org/pg/commitdiff/dc2db1eac365b97c9129393acfe11102859f9e23

• Add TAP tests for ZLIB compression for pg_receivewal. There is a non-trivial amount of code that handles ZLIB compression in pg_receivewal, from basics like the format name, the calculation of the start streaming position and of course the compression itself, but there was no automated coverage for it. This commit introduces a set of conditional tests (if the build supports ZLIB) to cover the creation of ZLIB-compressed WAL segments, the handling of the partial, compressed, WAL segments and the compression operation in itself. Note that there is an extra phase checking the validity of the generated files by using directly a gzip command, passed down by the Makefile of pg_receivewal. This part is skipped if the command cannot be found, something likely going to happen on Windows with MSVC except if one sets the variable GZIP_PROGRAM in the environment of the test. This set of tests will become handy for upcoming patches that add more options for the compression methods used by pg_receivewal, like LZ4, to make sure that no existing facilities are broken. Author: Georgios Kokolatos Reviewed-by: Gilles Darold, Michael Paquier Discussion: https://postgr.es/m/07BK3Mk5aEOsTwGaY77qBVyf9GjoEzn8TMgHLyPGfEFPIpTEmoQuP2P4c7teesjSg-LPeUafsp1flnPeQYINMSMB_UpggJDoduB5EDYBqaQ=@protonmail.com https://git.postgresql.org/pg/commitdiff/ffc9ddaea33f6dfd3dfa95828a0970fbb617bf8a

• Fix portability issue with gzip in TAP test of pg_receivewal. The OpenBSD implementation of gzip considers only files suffixed by "Z", "gz", "z", "tgz" or "taz" as valid targets, discarding anything else and making a command using --test exit with an error code of 512 if anything invalid is found. The test introduced in ffc9dda tested a WAL segment suffixed as .gz.partial, enough to make the test fail. Testing only a full segment is fine enough in terms of coverage, so simplify the code by discarding the .gz.partial segment in this check. This should be enough to make the test pass with OpenBSD environments. Per report from curculio. Discussion: https://postgr.es/m/YPAdf9r5aJbDoHoq@paquier.xyz https://git.postgresql.org/pg/commitdiff/0da3c1bc3f7261d5157f5b86ade88e8b379f8686

• Disable tests involving ZLIB on Windows for pg_receivewal. As reported by buildfarm member bowerbird, those tests are unstable on Windows. The failure produced there points to a problem with gzflush(), that fails to sync a file freshly-opened, with a gzFile properly opened. While testing this myself with MSVC, I bumped into a different error where a file could simply not be opened, so this makes me rather doubtful that testing this area on Windows is a good idea if this finishes with random concurrency failures. This requires more investigation, and keeping this buildfarm member red is not a good thing in the long-term, so for now this just disables this set of tests on Windows. Discussion: https://postgr.es/m/YPDLz2x3o1aX2wRh@paquier.xyz https://git.postgresql.org/pg/commitdiff/6cea447e6a10cd7ef511470e809a894a013e6a18

Heikki Linnakangas pushed:

• Remove dead assignment to local variable. This should have been removed in commit 7e30c186da, which split the loop into two. Only the first loop uses the 'from' variable; updating it in the second loop is bogus. It was never read after the first loop, so this was harmless and surely optimized away by the compiler, but let's be tidy. Backpatch to all supported versions. Author: Ranier Vilela Discussion: https://www.postgresql.org/message-id/CAEudQAoWq%2BAL3BnELHu7gms2GN07k-np6yLbukGaxJ1vY-zeiQ%40mail.gmail.com https://git.postgresql.org/pg/commitdiff/4c64b51dc51f8ff7e3e51eebfe8d10469a577df1

• In psql \copy from, send data to server in larger chunks. Previously, we would send each line as a separate CopyData message. That's pretty wasteful if the table is narrow, as each CopyData message has 5 bytes of overhead. For efficiency, buffer up and pack 8 kB of input data into each CopyData message. The server also sends each line as a separate CopyData message in COPY TO STDOUT, and that's similarly wasteful. But that's documented in the FE/BE protocol description, so changing that would be a wire protocol break. Reviewed-by: Aleksander Alekseev Discussion: https://www.postgresql.org/message-id/40b2cec0-d0fb-3191-2ae1-9a3fe16a7e48%40iki.fi https://git.postgresql.org/pg/commitdiff/eec57115e4c866f26bdc8bcbe3e2e7be4c6d0450

Peter Eisentraut pushed:

• doc: Fix typo in function prototype. https://git.postgresql.org/pg/commitdiff/5b60cf35f566697030a2895dfe27dde2e34dd370

• Fix lack of message pluralization. https://git.postgresql.org/pg/commitdiff/55b2a2340758da8ff11357d719d169f264ac7112

• Fix some nonstandard C code indentation in grammar file. https://git.postgresql.org/pg/commitdiff/9aa8268faa0ec2904f55e85be5ec7b365c98edd1

• doc: Spell checking. https://git.postgresql.org/pg/commitdiff/46111fb7b57876a87eb0630ec12ad30950e38a39

Tom Lane pushed:

• Replace RelationOpenSmgr() with RelationGetSmgr(). The idea behind this patch is to design out bugs like the one fixed by commit 9d523119f. Previously, once one did RelationOpenSmgr(rel), it was considered okay to access rel->rd_smgr directly for some not-very-clear interval. But since that pointer will be cleared by relcache flushes, we had bugs arising from overreliance on a previous RelationOpenSmgr call still being effective. Now, very little code except that in rel.h and relcache.c should ever touch the rd_smgr field directly. The normal coding rule is to use RelationGetSmgr(rel) and not expect the result to be valid for longer than one smgr function call. There are a couple of places where using the function every single time seemed like overkill, but they are now annotated with large warning comments. Amul Sul, after an idea of mine. Discussion: https://postgr.es/m/CANiYTQsU7yMFpQYnv=BrcRVqK_3U3mtAzAsJCaqtzsDHfsUbdQ@mail.gmail.com https://git.postgresql.org/pg/commitdiff/f10f0ae420ee62400876ab34dca2c09c20dcd030

• Probe for preadv/pwritev in a more macOS-friendly way. Apple's mechanism for dealing with functions that are available in only some OS versions confuses AC_CHECK_FUNCS, and therefore AC_REPLACE_FUNCS. We can use AC_CHECK_DECLS instead, so long as we enable -Werror=unguarded-availability-new. This allows people compiling for macOS to control whether or not preadv/pwritev are used by setting MACOSX_DEPLOYMENT_TARGET, rather than supplying a back-rev SDK. (Of course, the latter still works, too.) James Hilliard Discussion: https://postgr.es/m/20210122193230.25295-1-james.hilliard1@gmail.com https://git.postgresql.org/pg/commitdiff/f014b1b9bb8eda4e82c1805969dbae2b07b7d54d

• Rename debug_invalidate_system_caches_always to debug_discard_caches. The name introduced by commit 4656e3d66 was agreed to be unreasonably long. To match this change, rename initdb's recently-added --clobber-cache option to --discard-caches. Discussion: https://postgr.es/m/1374320.1625430433@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/d68a00391214be2020e49be4b55f761d47a5c229

• Copy a Param's location field when replacing it with a Const. This allows Param substitution to produce just the same result as writing a constant value literally would have done. While it hardly matters so far as the current core code is concerned, extensions might take more interest in node location fields. Julien Rouhaud Discussion: https://postgr.es/m/20170311220932.GJ15188@nol.local https://git.postgresql.org/pg/commitdiff/be850f1822e4b54d1d570eefa8a7242788011634

• Ensure HAVE_DECL_XXX macros in MSVC builds match those in Unix. Autoconf's AC_CHECK_DECLS() always defines HAVE_DECL_whatever as 1 or 0, but some of the entries in msvc/Solution.pm showed such symbols as "undef" instead of 0. Fix that for consistency. There's no live bug in current usages AFAICS, but it's not hard to imagine one creeping in if more-complex #if tests get added. Back-patch to v13, which is as far back as Solution.pm contains this data. The inconsistency still exists in the manually-filled pg_config_ext.h.win32 files of older branches; but as long as the problem is only latent, it doesn't seem worth the trouble to clean things up there. Discussion: https://postgr.es/m/3185430.1626133592@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/e529b2dc37ac80ccebd76cdbb14966d3b40819c9

• Replace explicit PIN entries in pg_depend with an OID range test. As of v14, pg_depend contains almost 7000 "pin" entries recording the OIDs of built-in objects. This is a fair amount of bloat for every database, and it adds time to pg_depend lookups as well as initdb. We can get rid of all of those entries in favor of an OID range check, i.e. "OIDs below FirstUnpinnedObjectId are pinned". (template1 and the public schema are exceptions. Those exceptions are now wired into IsPinnedObject() instead of initdb's code for filling pg_depend, but it's the same amount of cruft either way.) The contents of pg_shdepend are modified likewise. Discussion: https://postgr.es/m/3737988.1618451008@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/a49d081235997c67e8aab7a523b17e8d1cb93184

• Doc: document the current-transaction-modes GUCs. We had documentation of default_transaction_isolation et al, but for some reason not of transaction_isolation et al. AFAICS this is just an ancient oversight, so repair. Per bug #17077 from Yanliang Lei. Discussion: https://postgr.es/m/17077-ade8e166a01e1374@postgresql.org https://git.postgresql.org/pg/commitdiff/665c5855e5820531f500dad50f29f377af537ecc

Thomas Munro pushed:

• Add PSQL_WATCH_PAGER for psql's \watch command. Allow a pager to be used by the \watch command. This works but isn't very useful with traditional pagers like "less", so use a different environment variable. The popular open source tool "pspg" (also by Pavel) knows how to display the output if you set PSQL_WATCH_PAGER="pspg --stream". To make \watch react quickly when the user quits the pager or presses ^C, and also to increase the accuracy of its timing and decrease the rate of useless context switches, change the main loop of the \watch command to use sigwait() rather than a sleeping/polling loop, on Unix. Supported on Unix only for now (like pspg). Author: Pavel Stehule pavel.stehule@gmail.com Author: Thomas Munro thomas.munro@gmail.com Discussion: https://postgr.es/m/CAFj8pRBfzUUPz-3gN5oAzto9SDuRSq-TQPfXU_P6h0L7hO%2BEhg%40mail.gmail.com https://git.postgresql.org/pg/commitdiff/7c09d2797ecdf779e5dc3289497be85675f3d134

• Portability fixes for sigwait. Build farm animals running ancient HPUX and Solaris have a non-standard sigwait() from draft versions of POSIX, so they didn't like commit 7c09d279. To avoid the problem in general, only try to use sigwait() if it's declared by <signal.h> and matches the expected declaration. To select the modern declaration on Solaris (even in non-threaded programs), move -D_POSIX_PTHREAD_SEMANTICS into the right place to affect all translation units. Also fix the error checking. Modern sigwait() doesn't set errno. Thanks to Tom Lane for help with this. Discussion: https://postgr.es/m/3187588.1626136248%40sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/5865e064abfbbe11ebfc09881be009c0f69b4dc2

David Rowley pushed:

• Fix theoretical bug in tuplesort. This fixes a theoretical bug in tuplesort.c which, if a bounded sort was used in combination with a byval Datum sort (tuplesort_begin_datum), when switching the sort to a bounded heap in make_bounded_heap(), we'd call free_sort_tuple(). The problem was that when sorting Datums of a byval type, the tuple is NULL and free_sort_tuple() would free the memory for it regardless of that. This would result in a crash. Here we fix that simply by adding a check to see if the tuple is NULL before trying to disassociate and free any memory belonging to it. The reason this bug is only theoretical is that nowhere in the current code base do we do tuplesort_set_bound() when performing a Datum sort. However, let's backpatch a fix for this as if any extension uses the code in this way then it's likely to cause problems. Author: Ronan Dunklau Discussion: https://postgr.es/m/CAApHDvpdoqNC5FjDb3KUTSMs5dg6f+XxH4Bg_dVcLi8UYAG3EQ@mail.gmail.com Backpatch-through: 9.6, oldest supported version https://git.postgresql.org/pg/commitdiff/41469253e970b539a4ae75226dd4f226b7b2bc8c

• Robustify tuplesort's free_sort_tuple function. 41469253e went to the trouble of removing a theoretical bug from free_sort_tuple by checking if the tuple was NULL before freeing it. Let's make this a little more robust by also setting the tuple to NULL so that should we be called again we won't end up doing a pfree on the already pfree'd tuple. Per advice from Tom Lane. Discussion: https://postgr.es/m/3188192.1626136953@sss.pgh.pa.us Backpatch-through: 9.6, same as 41469253e https://git.postgresql.org/pg/commitdiff/5bd38d2f2846c7e387d04a16c4372da0de7b1221

• Remove useless range checks on INT8 sequences. There's no point in checking if an INT8 sequence has a seqmin and seqmax value is outside the range of the minimum and maximum values for an int64 type. These both use the same underlying types so an INT8 certainly cannot be outside the minimum and maximum values supported by int64. This code is fairly harmless and it seems likely that most compilers would optimize it out anyway, never-the-less, let's remove it replacing it with a small comment to mention why the check is not needed. Author: Greg Nancarrow, with the comment revised by David Rowley Discussion: https://postgr.es/m/CAJcOf-c9KBUZ8ow_6e%3DWSfbbEyTKfqV%3DVwoFuODQVYMySHtusw%40mail.gmail.com https://git.postgresql.org/pg/commitdiff/e0271d5f1e871dd61efc26bda8a0b556c7935901

• Change the name of the Result Cache node to Memoize. "Result Cache" was never a great name for this node, but nobody managed to come up with another name that anyone liked enough. That was until David Johnston mentioned "Node Memoization", which Tom Lane revised to just "Memoize". People seem to like "Memoize", so let's do the rename. Reviewed-by: Justin Pryzby Discussion: https://postgr.es/m/20210708165145.GG1176@momjian.us Backpatch-through: 14, where Result Cache was introduced https://git.postgresql.org/pg/commitdiff/83f4fcc65503c5d4e5d5eefc8e7a70d3c9a6496f

Amit Kapila pushed:

• Add support for prepared transactions to built-in logical replication. To add support for streaming transactions at prepare time into the built-in logical replication, we need to do the following things: * Modify the output plugin (pgoutput) to implement the new two-phase API callbacks, by leveraging the extended replication protocol. * Modify the replication apply worker, to properly handle two-phase transactions by replaying them on prepare. * Add a new SUBSCRIPTION option "two_phase" to allow users to enable two-phase transactions. We enable the two_phase once the initial data sync is over. We however must explicitly disable replication of two-phase transactions during replication slot creation, even if the plugin supports it. We don't need to replicate the changes accumulated during this phase, and moreover, we don't have a replication connection open so we don't know where to send the data anyway. The streaming option is not allowed with this new two_phase option. This can be done as a separate patch. We don't allow to toggle two_phase option of a subscription because it can lead to an inconsistent replica. For the same reason, we don't allow to refresh the publication once the two_phase is enabled for a subscription unless copy_data option is false. Author: Peter Smith, Ajin Cherian and Amit Kapila based on previous work by Nikhil Sontakke and Stas Kelvich Reviewed-by: Amit Kapila, Sawada Masahiko, Vignesh C, Dilip Kumar, Takamichi Osumi, Greg Nancarrow Tested-By: Haiying Tang Discussion: https://postgr.es/m/02DA5F5E-CECE-4D9C-8B4B-418077E2C010@postgrespro.ru Discussion: https://postgr.es/m/CAA4eK1+opiV4aFTmWWUF9h_32=HfPOW9vZASHarT0UA5oBrtGw@mail.gmail.com https://git.postgresql.org/pg/commitdiff/a8fd13cab0ba815e9925dc9676e6309f699b5f72

Magnus Hagander pushed:

• Clarify description of pg_stat_statements columns. Reported-By: Peter Eisentraut Backpatch-through: 14 Discussion: https://postgr.es/m/8f5e63b8-e8ed-0f80-d8c4-68222624c200@enterprisedb.com https://git.postgresql.org/pg/commitdiff/b4deefc39b933b9808645667117f2d8208092794

John Naylor pushed:

• Remove unused function parameter in get_qual_from_partbound. Commit 0563a3a8b changed how partition constraints were generated such that this function no longer computes the mapping of parent attnos to child attnos. This is an external function that extensions could use, so this is potentially a breaking change. No external callers are known, however, and this will make it simpler to write such callers in the future. Author: Hou Zhijie Reviewed-by: David Rowley, Michael Paquier, Soumyadeep Chakraborty Discussion: https://www.postgresql.org/message-id/flat/OS0PR01MB5716A75A45BE46101A1B489894379@OS0PR01MB5716.jpnprd01.prod.outlook.com https://git.postgresql.org/pg/commitdiff/c203dcddf997180000bc574a60313f3437e35af9

Dean Rasheed pushed:

• Improve reporting of "conflicting or redundant options" errors. When reporting "conflicting or redundant options" errors, try to ensure that errposition() is used, to help the user identify the offending option. Formerly, errposition() was invoked in less than 60% of cases. This patch raises that to over 90%, but there remain a few places where the ParseState is not readily available. Using errdetail() might improve the error in such cases, but that is left as a task for the future. Additionally, since this error is thrown from over 100 places in the codebase, introduce a dedicated function to throw it, reducing code duplication. Extracted from a slightly larger patch by Vignesh C. Reviewed by Bharath Rupireddy, Alvaro Herrera, Dilip Kumar, Hou Zhijie, Peter Smith, Daniel Gustafsson, Julien Rouhaud and me. Discussion: https://postgr.es/m/CALDaNm33FFSS5tVyvmkoK2cCMuDVxcui=gFrjti9ROfynqSAGA@mail.gmail.com https://git.postgresql.org/pg/commitdiff/2bfb50b3df11399ed80347dd03bfaf8cd5acf962

• Improve error checking of CREATE COLLATION options. Check for conflicting or redundant options, as we do for most other commands. Specifying any option more than once is at best redundant, and quite likely indicates a bug in the user's code. While at it, improve the error for conflicting locale options by adding detail text (the same as for CREATE DATABASE). Bharath Rupireddy, reviewed by Vignesh C. Some additional hacking by me. Discussion: https://postgr.es/m/CALj2ACWtL6fTLdyF4R_YkPtf1YEDb6FUoD5DGAki3rpD+sWqiA@mail.gmail.com https://git.postgresql.org/pg/commitdiff/ba620760c4c8ca90ff83ecf7e4d46f5ec4dabd7b

Alexander Korotkov pushed:

• Fix small inconsistencies in catalog definition of multirange operators. This commit fixes the description of a couple of multirange operators and oprjoin for another multirange operator. The change of oprjoin is more cosmetic since both old and new functions return the same constant. These cosmetic changes don't worth catalog incompatibility between 14beta2 and 14beta3. So, catversion isn't bumped. Discussion: https://postgr.es/m/CAPpHfdv9OZEuZDqOQoUKpXhq%3Dmc-qa4gKCPmcgG5Vvesu7%3Ds1w%40mail.gmail.com Backpatch-throgh: 14 https://git.postgresql.org/pg/commitdiff/768ea9bcf98120eef01a6deea9c5c6997b153ab1

• Support for unnest(multirange). It has been spotted that multiranges lack of ability to decompose them into individual ranges. Subscription and proper expanded object representation require substantial work, and it's too late for v14. This commit provides the implementation of unnest(multirange), which is quite trivial. unnest(multirange) is defined as a polymorphic procedure. Catversion is bumped. Reported-by: Jonathan S. Katz Discussion: https://postgr.es/m/flat/60258efe-bd7e-4886-82e1-196e0cac5433%40postgresql.org Author: Alexander Korotkov Reviewed-by: Justin Pryzby, Jonathan S. Katz, Zhihong Yu, Tom Lane Reviewed-by: Alvaro Herrera https://git.postgresql.org/pg/commitdiff/9e3c217bd98da803709872a8612577d88a39329a

• Forgotten catversion bump for 9e3c217bd9. https://git.postgresql.org/pg/commitdiff/f157db862225a7bfe041ca3f7b73e913e2a8d8d6

Daniel Gustafsson pushed:

• docs: fix inconsistencies in markup and case. Ensure to properly mark up function parameters in text with <parameter>, avoid using <acronym> for terms which aren't acronyms and properly place the ", and" in a value list. The acronym removal is a follow-up to commit fb72a7b8c3 which removed it for minmax-multi. In passing, also fix an incorrectly cased word. Author: Ekaterina Kiryanova e.kiryanova@postgrespro.ru Reviewed-by: Laurenz Albe laurenz.albe@cybertec.at Discussion: https://postgr.es/m/c050ecbc-80b2-b360-3c1d-9fe6a6a11bb5@postgrespro.ru Backpatch-through: v14 https://git.postgresql.org/pg/commitdiff/830ef61bd8d0ac4c89c21a895047c1b3a6b202f3

Álvaro Herrera pushed:

• Advance old-segment horizon properly after slot invalidation. When some slots are invalidated due to the max_slot_wal_keep_size limit, the old segment horizon should move forward to stay within the limit. However, in commit c6550776394e we forgot to call KeepLogSeg again to recompute the horizon after invalidating replication slots. In cases where other slots remained, the limits would be recomputed eventually for other reasons, but if all slots were invalidated, the limits would not move at all afterwards. Repair. Backpatch to 13 where the feature was introduced. Author: Kyotaro Horiguchi horikyota.ntt@gmail.com Reported-by: Marcin Krupowicz mk@071.ovh Discussion: https://postgr.es/m/17103-004130e8f27782c9@postgresql.org https://git.postgresql.org/pg/commitdiff/ead9e51e82363a0e492d56aee83ed11b3759a615

• Preserve firing-on state when cloning row triggers to partitions. When triggers are cloned from partitioned tables to their partitions, the 'tgenabled' flag (origin/replica/always/disable) was not propagated. Make it so that the flag on the trigger on partition is initially set to the same value as on the partitioned table. Add a test case to verify the behavior. Backpatch to 11, where this appeared in commit 86f575948c77. Author: Álvaro Herrera alvherre@alvh.no-ip.org Reported-by: Justin Pryzby pryzby@telsasoft.com Discussion: https://postgr.es/m/20200930223450.GA14848@telsasoft.com https://git.postgresql.org/pg/commitdiff/df80fa2ee50478391445cef03e42c1b3d64fccca

• Fix pg_dump for disabled triggers on partitioned tables. pg_dump failed to preserve the 'enabled' flag (which can be not only disabled, but also REPLICA or ALWAYS) for partitions which had it changed from their respective parents. Attempt to handle that by including a definition for such triggers in the dump, but replace the standard CREATE TRIGGER line with an ALTER TRIGGER line. Backpatch to 11, where these triggers can exist. In branches 11 and 12, pick up a few test lines from commit b9b408c48724 to verify that pg_upgrade is okay with these arrangements. Co-authored-by: Justin Pryzby pryzby@telsasoft.com Co-authored-by: Álvaro Herrera alvherre@alvh.no-ip.org Discussion: https://postgr.es/m/20200930223450.GA14848@telsasoft.com https://git.postgresql.org/pg/commitdiff/f0e21f2f61675f4e56ae53d32ea54d587a7c2257

• Make new replication slot test code less racy. The new test code added in ead9e51e8236 is racy -- it hinges on shared-memory state, which changes before the WARNING message is logged. Put it the other way around. Backpatch to 13. Author: Álvaro Herrera alvherre@alvh.no-ip.org Discussion: https://postgr.es/m/202107161809.zclasccpfcg3@alvherre.pgsql https://git.postgresql.org/pg/commitdiff/8589299e03fff012e0bbb9716693750a0d68eef7

Pending Patches

David Rowley sent in another revision of a patch to track non-pruned partitions in a Bitmapset added to the RelOptInfo struct, and allow ordered partition scans in more cases.

Hayato Kuroda sent in another revision of a patch to fix DEALLOCATE and DESCRIBE in ECPG.

David Rowley sent in another revision of a patch to speed up transaction completion faster after many relations are accessed in a transaction.

Hou Zhijie sent in another revision of a patch to make it possible to use parallel operations in things that write.

Rahila Syed sent in two more revisions of a patch to make it possible to filter logical replication by column.

Álvaro Herrera sent in a patch intended to fix a bug that manifested as partition check not updated when insert into a partition.

Jeevan Ladhe sent in two revisions of a patch to specify in error messages which DBs had errors in pg_upgrade.

Magnus Hagander sent in another revision of a patch to add support for PROXY protocol.

Bharath Rupireddy sent in a patch to use WaitLatch for {pre, post}_auth_delay instead of pg_usleep.

Vigneshwaran C sent in three more revisions of a patch to add schema level granularity to PUBLICATIONs.

John Naylor sent in four more revisions of a patch to speed up verifying UTF-8.

David Rowley sent in four more revisions of a patch to add proper planner support for ORDER BY / DISTINCT aggregates.

Ronan Dunklau, David Rowley, and Ranier Vilela traded patches to use the optimized single-datum tuplesort in ExecSort.

Masahiro Ikeda sent in two revisions of a patch to fix some mistaken comments in heap_prune_chain().

Alexander Lakhin sent in another revision of a patch intended to fix a bug that manifested as more time spent in "delete pending" on Win32.

Tom Lane sent in another revision of a patch to reduce memory consumption for pending inval messages.

Peter Smith sent in a patch to avoid unnecessary calls to PGserverVersion.

Craig Ringer sent in a patch to teach pgflex.pl and pgbision.pl to read buildenv.pl for tool names, and support extra preprocessor definitions in config.pl.

Maxim Orlov sent in another revision of a patch to fix a parallel worker failed assertion and core dump.

Tom Lane sent in another revision of a patch intended to fix a bug that manifested as merge join on tables with different DB collation behind postgres_fdw fails.

Peter Smith and Euler Taveira de Oliveira traded patches to add row filtering for logical replication.

Ian Barwick sent in a patch to document pg_encoding_to_char() and pg_char_to_encoding().

Fabien COELHO sent in another revision of a patch to psql to factor out the echo code.

Heikki Linnakangas sent in another revision of a patch to refactor LogicalTapeSet/LogicalTape interface in a way that stops requiring knowing how many tapes will be created up front, and replace the polyphase merge algorithm with a simple balanced k-way merge.

Heikki Linnakangas sent in another revision of a patch to move a few ResourceOwnerEnlarge() calls for safety and clarity, make resowners more easily extensible, and optimize the hash function used in same.

Daniil Zakhlystov sent in another revision of a patch to add zlib and zstd streaming compression generally, then use this infrastructure to add them to libpq.

Zhihong Yu sent in two revisions of a patch to pfree() a palloc()ed string.

Ajin Cherian sent in another revision of a patch to skip empty transactions for logical replication.

Kyotaro HORIGUCHI sent in another revision of a patch to be strict in checking numeric parameters on command line, in environment variables, and document the effect of those changes on PGCTLTIMEOUT.

Hou Zhijie sent in a patch to avoid repeated calls to PQfnumber in pg_dump.

Gilles Darold sent in two more revisions of a patch to allows user-defined code to be executed at the start of any command through a xact registered callback.

Peter Smith sent in another revision of a patch to add prepare API support for streaming transactions.

Huailing Liu sent in a patch to remove a confusing SI inval.

Kyotaro HORIGUCHI sent in another revision of a patch to remove read_page callback from XLogReadRecord.

Heikki Linnakangas sent in another revision of a patch to add amcheck for GIN.

enis Hirn sent in another revision of a patch to allow multiple linear recursive self-references in common table expressions.

Haiying Tang sent in a patch to tighten up the help output for the things that psql tab-completes.

Vigneshwaran C sent in another revision of a patch to identify missing publications from publishers during CREATE/ALTER SUBSCRIPTION.

Arne Roland sent in another revision of a patch to rename triggers on partitioned tables recursively.

Andrey V. Lepikhov sent in another revision of a patch to teach optimizer to consider partitionwise joins of non-partitioned table with each partition of partitioned table, and disallow asymmetric machinery for joining of two partitioned (or appended) relations because it could cause huge consumption of CPU and memory during reparameterization of the NestLoop path.

Bharath Rupireddy sent in another revision of a patch to disambiguate error messages that use "non-negative".

Magnus Hagander sent in a patch to tag tarballs with their git revision.

Andrey V. Lepikhov sent in another revision of a patch to remove inner joins of a relation to itself if it can be proven that the join can be replaced with a scan.

Ryohei Takahashi sent in two revisions of a patch to speed up COMMIT PREPARED.

Mark Dilger sent in a patch to stop ignoring failures on file close.

Justin Pryzby sent in two more revisions of a patch to add new metacommands \dn+ to show the size of each schema and \dA+ for AMs.

Aleksander Alekseev sent in another revision of a patch to refactor procarray.

Yugo Nagata sent in another revision of a patch to fix bugs that manifested as pgbench errors and serialization/deadlock retries.

Justin Pryzby sent in another revision of a patch to support ALTER TABLE ... ACCESS METHOD and allow specifying access methods of partitioned tables to be inherited by partitions.

Dilip Kumar added a missing bbsink_forward_end_archive(sink) to src/backend/replication/basebackup_gzip.c.

Kyotaro HORIGUCHI sent in a patch to fix a number of repeated typos in the char-mapping tables.

Bertrand Drouvot sent in another revision of a patch to implement minimal logical decoding on standbys.

Li Japin and Amit Kapila traded patches to disallow setting a replication slot name to be an empty string.

Ranier Vilela sent in a patch to un-shadow some variables, and reduce some -Wsign-compare warnings from modern compilers.

Vigneshwaran C sent in another revision of a patch to include the actual datatype used in logical replication message description.

James Coleman sent in another revision of a patch to allow parallel LATERAL subqueries with LIMIT/OFFSET.

Dinesh Chemuduru sent in a patch to add new diagnostics, PG_PARSE_SQL_STATEMENT, and PG_PARSE_SQL_STATEMENT_POSITION, to PL/pgsql.

Andrew Dunstan sent in three more revisions of a patch to clean up PostgresNode.pm.

Álvaro Herrera and Ranier Vilela traded patches to remove a pointless strlen from slot.c.

Kyotaro HORIGUCHI sent in two more revisions of a patch to make FPI_FOR_HINT follow standard FPI emitting policy.

Yugo Nagata sent in a patch intended to fix a bug that manifested as by preparing commands at the start of a script instead of at the first execution of the command.

Soumyadeep Chakraborty sent in another revision of a patch to make ProcSendSignal() more efficient by referring target processes using pgprocno in order to avoid scanning ProcArray and keeping track of the startup process.

Yugo Nagata and Ranier Vilela traded patches intended to fix a bug that manifested as corruption of WAL page header is never reported.

Atsushi Torikoshi sent in a patch to document the necessity for superuser privileges to execute pg_import_system_collations().

Peter Smith sent in a patch to prevent potential buffer overruns when using strcpy to gid buffer.

Atsushi Torikoshi sent in another revision of a patch to add a function to log the complete query string and its plan for the query currently running on the backend with the specified process ID.