8th February 2024: PostgreSQL 16.2, 15.6, 14.11, 13.14, and 12.18 Released!

PostgreSQL Weekly News - November 21, 2021

Posted on 2021-11-23 by PWN
PWN

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

PostgreSQL Product News

PGroonga 2.3.4 a full text search platform for all languages, released.

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 l ea s ed.

Ora2Pg 23.0, a tool for migrating Oracle databases to PostgreSQL, released. https://github.com/darold/ora2pg/blob/master/changelog

BigAnimal, a managed PostgreSQL database on Azure, released.

pgAdmin4 6.2, a web- and native GUI control center for PostgreSQL, released.

PostgreSQL Jobs for November

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

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

Robert Haas pushed:

Amit Kapila pushed:

Álvaro Herrera pushed:

Michaël Paquier pushed:

Peter Eisentraut pushed:

Daniel Gustafsson pushed:

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/AM9PR01MB8268347BED344848555167FAFE949@AM9PR01MB8268.eurprd01.prod.exchangelabs.com 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/1343113.1636489231@sss.pgh.pa.us 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/1343113.1636489231@sss.pgh.pa.us 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/232599.1633800229@sss.pgh.pa.us 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/747443.1635536754@sss.pgh.pa.us 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/2921539.1637254619@sss.pgh.pa.us 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/747443.1635536754@sss.pgh.pa.us 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/17294-8f6c7962ce877edc@postgresql.org 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

  • 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/747443.1635536754@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/282b6d00abf5cebece6f94c796a4ed807a0176db

Andres Freund pushed:

Andrew Dunstan pushed: