# PostgreSQL Weekly News - November 21, 2021 Nordic PGDay 2022 will be held in Helsinki, Finland at the Hilton Helsinki Strand Hotel on March 22, 2022. The CfP is open through December 31, 2021 [here](https://2022.nordicpgday.org/cfp/)
# PostgreSQL Product News PGroonga 2.3.4 a full text search platform for all languages, [released](https://pgroonga.github.io/). Pgpool-II 4.2.6, 4.1.9, 4.0.16, 3.7.21 and 3.6.28, a connection pooler and statement replication system for PostgreSQL, [re](https://www.pgpool.net/docs/42/en/html/release-4-2-6.html) [l](https://www.pgpool.net/docs/41/en/html/release-4-1-9.html) [ea](https://www.pgpool.net/docs/40/en/html/release-4-0-16.html) [s](https://www.pgpool.net/docs/37/en/html/release-3-7-21.html) [ed](https://www.pgpool.net/docs/36/en/html/release-3-6-28.html). Ora2Pg 23.0, a tool for migrating Oracle databases to PostgreSQL, released. [https://github.com/darold/ora2pg/blob/master/changelog](https://github.com/darold/ora2pg/blob/master/changelog) BigAnimal, a managed PostgreSQL database on Azure, [released](https://www.enterprisedb.com/products/biganimal-cloud-postgresql). pgAdmin4 6.2, a web- and native GUI control center for PostgreSQL, [released](https://www.pgadmin.org/docs/pgadmin4/6.2/release_notes_6_2.html). # PostgreSQL Jobs for November [https://archives.postgresql.org/pgsql-jobs/2021-11/](https://archives.postgresql.org/pgsql-jobs/2021-11/) # 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 Robert Haas pushed: - Fix thinko in bbsink_throttle_manifest_contents. Report and diagnosis by Dmitry Dolgov. Discussion: [http://postgr.es/m/20211115162641.dmo6l32fklh64gnw@localhost](http://postgr.es/m/20211115162641.dmo6l32fklh64gnw@localhost) [https://git.postgresql.org/pg/commitdiff/1b098da2009362e0e8d9a1d0a6aac2f2bd3e2f0b](https://git.postgresql.org/pg/commitdiff/1b098da2009362e0e8d9a1d0a6aac2f2bd3e2f0b) - Move InitXLogInsert() call from InitXLOGAccess() to BaseInit(). At present, there is an undocumented coding rule that you must call RecoveryInProgress(), or do something else that results in a call to InitXLogInsert(), before trying to write WAL. Otherwise, the WAL construction buffers won't be initialized, resulting in failures. Since it's not good to rely on a status inquiry function like RecoveryInProgress() having the side effect of initializing critical data structures, instead do the initialization eariler, when the backend first starts up. Patch by me. Reviewed by Nathan Bossart and Michael Paquier. Discussion: [http://postgr.es/m/ca+tgmoy7b65qrjzhn_twuk8b4sjqk1vj1d31uepvzmgpnzk...@mail.gmail.com](http://postgr.es/m/ca+tgmoy7b65qrjzhn_twuk8b4sjqk1vj1d31uepvzmgpnzk...@mail.gmail.com) [https://git.postgresql.org/pg/commitdiff/e51c46991f0ee99cca222305619dee5543a1290a](https://git.postgresql.org/pg/commitdiff/e51c46991f0ee99cca222305619dee5543a1290a) Amit Kapila pushed: - Invalidate relcache when changing REPLICA IDENTITY index. When changing REPLICA IDENTITY INDEX to another one, the target table's relcache was not being invalidated. This leads to skipping update/delete operations during apply on the subscriber side as the columns required to search corresponding rows won't get logged. Author: Tang Haiying, Hou Zhijie Reviewed-by: Euler Taveira, Amit Kapila Backpatch-through: 10 Discussion: [https://postgr.es/m/os0pr01mb61133ca11630dae45bc6ad95fb...@os0pr01mb6113.jpnprd01.prod.outlook.com](https://postgr.es/m/os0pr01mb61133ca11630dae45bc6ad95fb...@os0pr01mb6113.jpnprd01.prod.outlook.com) [https://git.postgresql.org/pg/commitdiff/354a1f8d220fbbb07b0ded32c5ade72646afb801](https://git.postgresql.org/pg/commitdiff/354a1f8d220fbbb07b0ded32c5ade72646afb801) - Fix parallel operations that prevent oldest xmin from advancing. While determining xid horizons, we skip over backends that are running Vacuum. We also ignore Create Index Concurrently, or Reindex Concurrently for the purposes of computing Xmin for Vacuum. But we were not setting the flags corresponding to these operations when they are performed in parallel which was preventing Xid horizon from advancing. The optimization related to skipping Create Index Concurrently, or Reindex Concurrently operations was implemented in PG-14 but the fix is the same for the Parallel Vacuum as well so back-patched till PG-13. Author: Masahiko Sawada Reviewed-by: Amit Kapila Backpatch-through: 13 Discussion: [https://postgr.es/m/CAD21AoCLQqgM1sXh9BrDFq0uzd3RBFKi=vfo6cjjkodm0on...@mail.gmail.com](https://postgr.es/m/CAD21AoCLQqgM1sXh9BrDFq0uzd3RBFKi=vfo6cjjkodm0on...@mail.gmail.com) [https://git.postgresql.org/pg/commitdiff/0f0cfb494004befb0f6e89d3129347869420c509](https://git.postgresql.org/pg/commitdiff/0f0cfb494004befb0f6e89d3129347869420c509) Álvaro Herrera pushed: - Fix headerscheck failure in replication/worker_internal.h. Broken by 31c389d8de91 [https://git.postgresql.org/pg/commitdiff/ad26ee28250c4cd357a7420161a2be321c3dd536](https://git.postgresql.org/pg/commitdiff/ad26ee28250c4cd357a7420161a2be321c3dd536) Michaël Paquier pushed: - Remove global variable "LastRec" in xlog.c. This variable is used only by StartupXLOG() now, so let's make it local to simplify the code. Author: Amul Sul Reviewed-by: Tom Lane, Michael Paquier Discussion: [https://postgr.es/m/caaj_b96qd023iterbrn9z7p2sandt3cyvgumo8rxwndvnn6...@mail.gmail.com](https://postgr.es/m/caaj_b96qd023iterbrn9z7p2sandt3cyvgumo8rxwndvnn6...@mail.gmail.com) [https://git.postgresql.org/pg/commitdiff/f975fc3a3542005ed0dd689bdb5bd9ed4e1f4d52](https://git.postgresql.org/pg/commitdiff/f975fc3a3542005ed0dd689bdb5bd9ed4e1f4d52) - Add table to regression tests for binary-compatibility checks in pg_upgrade. This commit adds to the main regression test suite a table with all the in-core data types (some exceptions apply). This table is not dropped, so as pg_upgrade would be able to check the binary compatibility of the types tracked in the table. If a new type is added in core, this part of the tests would need a refresh but the tests are designed to fail if that were to happen. As this is useful for upgrades and that these rely on the objects created in the regression test suite of the old version upgraded from, a backpatch down to 12 is done, which is the last point where a binary incompatible change has been done (7c15cef). This will hopefully be enough to find out if something gets broken during the development of a new version of Postgres, so as it is possible to take actions in pg_upgrade itself in this case (like 0ccfc28 for sql_identifier). An area that is not covered yet is related to external modules, which may create their own types. The testing infrastructure of pg_upgrade is not integrated yet with the external modules stored in core (src/test/modules/ or contrib/, all use the same database name for their tests so there would be an overlap). This could be improved in the future. Author: Justin Pryzby Reviewed-by: Jacob Champion, Peter Eisentraut, Tom Lane, Michael Paquier Discussion: [https://postgr.es/m/[email protected]](https://postgr.es/m/[email protected]) Backpatch-through: 12 [https://git.postgresql.org/pg/commitdiff/835bcba8b8d72a00cecc5431b67e70bbea93f947](https://git.postgresql.org/pg/commitdiff/835bcba8b8d72a00cecc5431b67e70bbea93f947) - Fix quoting of ACL item in table for upgrade binary compatibility checks. Per buildfarm member prion, that runs the regression tests under a role name that uses a hyphen. Issue introduced by 835bcba. Discussion: [https://postgr.es/m/[email protected]](https://postgr.es/m/[email protected]) Backpatch-through: 12 [https://git.postgresql.org/pg/commitdiff/ac1c7458b17633d1e53a01393d12774c10cb6a91](https://git.postgresql.org/pg/commitdiff/ac1c7458b17633d1e53a01393d12774c10cb6a91) - Improve psql tab completion for transforms, domains and sequences. The following improvements are done: - Addition of some tab completion for CREATE DOMAIN. - Addition of some tab completion for CREATE TRANSFORM. - Addition of type completion for CREATE SEQUENCE AS. Author: Ken Kato Reviewed-by: Kyotaro Horiguchi, Michael Paquier Discussion: [https://postgr.es/m/[email protected]](https://postgr.es/m/[email protected]) [https://git.postgresql.org/pg/commitdiff/0cd6d3b3c5aeac81903aa7de92e406f8567898a2](https://git.postgresql.org/pg/commitdiff/0cd6d3b3c5aeac81903aa7de92e406f8567898a2) Peter Eisentraut pushed: - Fix incorrect format placeholders. [https://git.postgresql.org/pg/commitdiff/303d4eb1c548f1d0821e168a6e7c7e9bd02c8088](https://git.postgresql.org/pg/commitdiff/303d4eb1c548f1d0821e168a6e7c7e9bd02c8088) Daniel Gustafsson pushed: - Doc: add see-also references to CREATE PUBLICATION. The "See also" section on the reference page for CREATE PUBLICATION didn't match the cross references on CREATE SUBSCRIPTION and their ALTER counterparts. Fixed by adding an xref to the CREATE and ALTER SUBSCRIPTION pages. Backpatch down to v10 where CREATE PUBLICATION was introduced. Author: Peter Smith <[email protected]> Reviewed-by: Masahiko Sawada <[email protected]> Discussion: [https://postgr.es/m/CAHut+PvGWd3-Ktn96c-z6uq-8TGVVP=tpokeovkefntoo2m...@mail.gmail.com](https://postgr.es/m/CAHut+PvGWd3-Ktn96c-z6uq-8TGVVP=tpokeovkefntoo2m...@mail.gmail.com) Backpatch-through: 10 [https://git.postgresql.org/pg/commitdiff/3374a87b62cc553fa65f57ade019dcf3104ae639](https://git.postgresql.org/pg/commitdiff/3374a87b62cc553fa65f57ade019dcf3104ae639) - Improve publication error messages. Commit 81d5995b4b introduced more fine-grained errormessages for incorrect relkinds for publication, while unlogged and temporary tables were reported with using the same message. This provides separate error messages for these types of relpersistence. Author: Bharath Rupireddy <[email protected]> Reviewed-by: Peter Eisentraut <[email protected]> Reviewed-by: Jeevan Ladhe <[email protected]> Reviewed-by: Euler Taveira <[email protected]> Discussion: [https://postgr.es/m/CALj2ACW9S=aswyqhjto6wmcsergmkcbttzxgrm8dx26dzfe...@mail.gmail.com](https://postgr.es/m/CALj2ACW9S=aswyqhjto6wmcsergmkcbttzxgrm8dx26dzfe...@mail.gmail.com) [https://git.postgresql.org/pg/commitdiff/aa12781b0d039d93e1a851ece4bc75c3746cbd43](https://git.postgresql.org/pg/commitdiff/aa12781b0d039d93e1a851ece4bc75c3746cbd43) Tom Lane pushed: - Fix display of SQL-standard function's arguments in INSERT/SELECT. If a SQL-standard function body contains an INSERT ... SELECT statement, any function parameters referenced within the SELECT were always printed in $N style, rather than using the parameter name if any. While not strictly incorrect, this wasn't the intention, and it's inconsistent with the way that such parameters would be printed in any other kind of statement. The cause is that the recursion to get_query_def from get_insert_query_def neglected to pass down the context->namespaces list, passing constant NIL instead. This is a very ancient oversight, but AFAICT it had no visible consequences before commit e717a9a18 added an outermost namespace with function parameters. We don't allow INSERT ... SELECT as a sub-query, except in a top-level WITH clause, where it couldn't contain any outer references that might need to access upper namespaces. So although that's arguably a bug, I don't see any point in changing it before v14. In passing, harden the code added to get_parameter by e717a9a18 so that it won't crash if a PARAM_EXTERN Param appears in an unexpected place. Per report from Erki Eessaar. Code fix by me, regression test case by Masahiko Sawada. Discussion: [https://postgr.es/m/am9pr01mb8268347bed344848555167fafe...@am9pr01mb8268.eurprd01.prod.exchangelabs.com](https://postgr.es/m/am9pr01mb8268347bed344848555167fafe...@am9pr01mb8268.eurprd01.prod.exchangelabs.com) [https://git.postgresql.org/pg/commitdiff/a8d8445a7b2f80f6d0bfe97b19f90bd2cbef8759](https://git.postgresql.org/pg/commitdiff/a8d8445a7b2f80f6d0bfe97b19f90bd2cbef8759) - Handle close() failures more robustly in pg_dump and pg_basebackup. Coverity complained that applying get_gz_error after a failed gzclose, as we did in one place in pg_basebackup, is unsafe. I think it's right: it's entirely likely that the call is touching freed memory. Change that to inspect errno, as we do for other gzclose calls. Also, be careful to initialize errno to zero immediately before any gzclose() call where we care about the error status. (There are some calls where we don't, because we already failed at some previous step.) This ensures that we don't get a misleadingly irrelevant error code if gzclose() fails in a way that doesn't set errno. We could work harder at that, but it looks to me like all such cases are basically can't-happen if we're not misusing zlib, so it's not worth the extra notational cruft that would be required. Also, fix several places that simply failed to check for close-time errors at all, mostly at some remove from the close or gzclose itself; and one place that did check but didn't bother to report the errno. Back-patch to v12. These mistakes are older than that, but between the frontend logging API changes that happened in v12 and the fact that frontend code can't rely on %m before that, the patch would need substantial revision to work in older branches. It doesn't quite seem worth the trouble given the lack of related field complaints. Patch by me; thanks to Michael Paquier for review. Discussion: [https://postgr.es/m/[email protected]](https://postgr.es/m/[email protected]) [https://git.postgresql.org/pg/commitdiff/3cac2c8caaefc642332e6994ce80032cc7d4cfdf](https://git.postgresql.org/pg/commitdiff/3cac2c8caaefc642332e6994ce80032cc7d4cfdf) - Clean up error handling in pg_basebackup's walmethods.c. The error handling here was a mess, as a result of a fundamentally bad design (relying on errno to keep its value much longer than is safe to assume) as well as a lot of just plain sloppiness, both as to noticing errors at all and as to reporting the correct errno. Moreover, the recent addition of LZ4 compression broke things completely, because liblz4 doesn't use errno to report errors. To improve matters, keep the error state in the DirectoryMethodData or TarMethodData struct, and add a string field so we can handle cases that don't set errno. (The tar methods already had a version of this, but it can be done more efficiently since all these cases use a constant error string.) Make the dir and tar methods handle errors in basically identical ways, which they didn't before. This requires copying errno into the state struct in a lot of places, which is a bit tedious, but it has the virtue that we can get rid of ad-hoc code to save and restore errno in a number of places ... not to mention that it fixes other places that should've saved/restored errno but neglected to. In passing, fix some pointlessly static buffers to be ordinary local variables. There remains an issue about exactly how to handle errors from fsync(), but that seems like material for its own patch. While the LZ4 problems are new, all the rest of this is fixes for old bugs, so backpatch to v10 where walmethods.c was introduced. Patch by me; thanks to Michael Paquier for review. Discussion: [https://postgr.es/m/[email protected]](https://postgr.es/m/[email protected]) [https://git.postgresql.org/pg/commitdiff/248c3a937dd018a72095f407cff727c9f08db0c1](https://git.postgresql.org/pg/commitdiff/248c3a937dd018a72095f407cff727c9f08db0c1) - Add a planner support function for starts_with(). This fills in some gaps in planner support for starts_with() and the equivalent ^@ operator: * A condition such as "textcol ^@ constant" can now use a regular btree index, not only an SP-GiST index, so long as the index's collation is C. (This works just like "textcol LIKE 'foo%'".) * "starts_with(textcol, constant)" can be optimized the same as "textcol ^@ constant". * Fixed-prefix LIKE and regex patterns are now more like starts_with() in another way: if you apply one to an SPGiST-indexed column, you'll get an index condition using ^@ rather than two index conditions with >= and <. Per a complaint from Shay Rojansky. Patch by me; thanks to Nathan Bossart for review. Discussion: [https://postgr.es/m/[email protected]](https://postgr.es/m/[email protected]) [https://git.postgresql.org/pg/commitdiff/a148f8bc04b9980f019ea0d4b89311cf0bdc22b7](https://git.postgresql.org/pg/commitdiff/a148f8bc04b9980f019ea0d4b89311cf0bdc22b7) - Provide a variant of simple_prompt() that can be interrupted by ^C. Up to now, you couldn't escape out of psql's \password command by typing control-C (or other local spelling of SIGINT). This is pretty user-unfriendly, so improve it. To do so, we have to modify the functions provided by pg_get_line.c; but we don't want to mess with psql's SIGINT handler setup, so provide an API that lets that handler cause the cancel to occur. This relies on the assumption that we won't do any major harm by longjmp'ing out of fgets(). While that's obviously a little shaky, we've long had the same assumption in the main input loop, and few issues have been reported. psql has some other simple_prompt() calls that could usefully be improved the same way; for now, just deal with \password. Nathan Bossart, minor tweaks by me Discussion: [https://postgr.es/m/[email protected]](https://postgr.es/m/[email protected]) [https://git.postgresql.org/pg/commitdiff/5f1148224bd78bcf3bf7d916b8fe85dd820c52c6](https://git.postgresql.org/pg/commitdiff/5f1148224bd78bcf3bf7d916b8fe85dd820c52c6) - Use appropriate -Wno-warning switches when compiling bitcode. We use "clang" to compile bitcode files for LLVM inlining. That might be different from the build's main C compiler, so it needs its own set of compiler flags. To simplify configure, we don't bother adding any -W switches to that flag set; there's little need since the main build will show us any warnings. However, if we don't want to see unwanted warnings, we still have to add any -Wno-warning switches we'd normally use with clang. This escaped notice before commit 9ff47ea41, which tried to add -Wno-compound-token-split-by-macro; buildfarm animals using mismatched CC and CLANG still showed those warnings. I'm not sure why we never saw any effects from the lack of -Wno-unused-command-line-argument (maybe that's only activated by -Wall?). clang does not currently support -Wno-format-truncation or -Wno-stringop-truncation, although in the interests of future-proofing and consistency I included tests for those. Back-patch to v11 where we started building bitcode files. Discussion: [https://postgr.es/m/[email protected]](https://postgr.es/m/[email protected]) [https://git.postgresql.org/pg/commitdiff/276517a96484f9e39a7a1095ab39fa76ef1ee8cc](https://git.postgresql.org/pg/commitdiff/276517a96484f9e39a7a1095ab39fa76ef1ee8cc) - Allow psql's other uses of simple_prompt() to be interrupted by ^C. This fills in the work left un-done by 5f1148224. \prompt can be canceled out of now, and so can password prompts issued during \connect. (We don't need to do anything for password prompts issued during startup, because we aren't yet trapping SIGINT at that point.) Nathan Bossart Discussion: [https://postgr.es/m/[email protected]](https://postgr.es/m/[email protected]) [https://git.postgresql.org/pg/commitdiff/46d665bc26ce57b5afecbc218c8fc3c6848211d8](https://git.postgresql.org/pg/commitdiff/46d665bc26ce57b5afecbc218c8fc3c6848211d8) - Fix SP-GiST scan initialization logic for binary-compatible cases. Commit ac9099fc1 rearranged the logic in spgGetCache() that determines the index's attType (nominal input data type) and leafType (actual type stored in leaf index tuples). Turns out this broke things for the case where (a) the actual input data type is different from the nominal type, (b) the opclass's config function leaves leafType defaulted, and (c) the opclass has no "compress" function. (b) caused us to assign the actual input data type as leafType, and then since that's not attType, we complained that a "compress" function is required. For non-polymorphic opclasses, condition (a) arises in binary-compatible cases, such as using SP-GiST text_ops for a varchar column, or using any opclass on a domain over its nominal input type. To fix, use attType for leafType when the index's declared column type is different from but binary-compatible with attType. Do this only in the defaulted-leafType case, to avoid overriding any explicit selection made by the opclass. Per bug #17294 from Ilya Anfimov. Back-patch to v14. Discussion: [https://postgr.es/m/[email protected]](https://postgr.es/m/[email protected]) [https://git.postgresql.org/pg/commitdiff/f4e7ae2b8a67ad6801726553a024a3306716ef80](https://git.postgresql.org/pg/commitdiff/f4e7ae2b8a67ad6801726553a024a3306716ef80) - Doc: update some things relevant to minimum Test::More version. Oversights in commit 405f32fc4. Also, add a tip (discovered the hard way) about getting Test::More 0.98 to pass its regression tests on recent Linux platforms. [https://git.postgresql.org/pg/commitdiff/92e70796e91e2f9086fad0156e0e91513e54a66b](https://git.postgresql.org/pg/commitdiff/92e70796e91e2f9086fad0156e0e91513e54a66b) - pg_receivewal, pg_recvlogical: allow canceling initial password prompt. Previously it was impossible to terminate these programs via control-C while they were prompting for a password. We can fix that trivially for their initial password prompts, by moving setup of the SIGINT handler from just before to just after their initial GetConnection() calls. This fix doesn't permit escaping out of later re-prompts, but those should be exceedingly rare, since the user's password or the server's authentication setup would have to have changed meanwhile. We considered applying a fix similar to commit 46d665bc2, but that seemed more complicated than it'd be worth. Moreover, this way is back-patchable, which that wasn't. The misbehavior exists in all supported versions, so back-patch to all. Tom Lane and Nathan Bossart Discussion: [https://postgr.es/m/[email protected]](https://postgr.es/m/[email protected]) [https://git.postgresql.org/pg/commitdiff/282b6d00abf5cebece6f94c796a4ed807a0176db](https://git.postgresql.org/pg/commitdiff/282b6d00abf5cebece6f94c796a4ed807a0176db) Andres Freund pushed: - Initialize backend status reporting during bootstrap. This allows a later commit to reduce the number of branches in performance sensitive functions during normal running, compared to a very minor saving during bootstrapping. Author: Melanie Plageman <[email protected]> Reviewed-By: Andres Freund <[email protected]> Discussion: [https://postgr.es/m/CAAKRu_Yeg+vh6SHNEo1+=O7e-BPX35cU0XQM=ywqrnkfyv_...@mail.gmail.com](https://postgr.es/m/CAAKRu_Yeg+vh6SHNEo1+=O7e-BPX35cU0XQM=ywqrnkfyv_...@mail.gmail.com) [https://git.postgresql.org/pg/commitdiff/3b34645678d1a516c148e3e27c26325708e92f6f](https://git.postgresql.org/pg/commitdiff/3b34645678d1a516c148e3e27c26325708e92f6f) Andrew Dunstan pushed: - Require version 0.98 of Test::More for TAP tests. This means that the subtest feature will be available for use. We expect that this change will make prairiedog go red until it is updated, but other buildfarm animals should be fine. Discussion: [https://postgr.es/m/[email protected]](https://postgr.es/m/[email protected]) [https://git.postgresql.org/pg/commitdiff/405f32fc49609eb94fa39e7b5e7c1fe2bb2b73aa](https://git.postgresql.org/pg/commitdiff/405f32fc49609eb94fa39e7b5e7c1fe2bb2b73aa)
