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

PostgreSQL Weekly News - May 2, 2021

Posted on 2021-05-03 by PWN
PWN

PostgreSQL Weekly News - May 2, 2021

Person of the week: https://postgresql.life/post/corey_huinker/

PostgreSQL Product News

The observability diagram at pgstats.dev has been updated. https://pgstats.dev/

PostgreSQL Jobs for May

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

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

Tom Lane pushed:

  • Update comments for rewriteTargetListIU(). This function's behavior for UPDATE on a trigger-updatable view was justified by analogy to what preptlist.c used to do for UPDATE on regular tables. Since preptlist.c hasn't done that since 86dc90056, that argument is no longer sensible, let alone convincing. I think we do still need it to act that way, so update the comment to explain why. https://git.postgresql.org/pg/commitdiff/08a986966524e522914b96e4398a4bebf942b298

  • Doc: document EXTRACT(JULIAN ...), improve Julian Date explanation. For some reason, the "julian" option for extract()/date_part() has never gotten listed in the manual. Also, while Appendix B mentioned in passing that we don't conform to the usual astronomical definition that a Julian date starts at noon UTC, it was kind of vague about what we do instead. Clarify that, and add an example showing how to get the astronomical definition if you want it. It's been like this for ages, so back-patch to all supported branches. Discussion: https://postgr.es/m/1197050.1619123213@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/79a5928ebcb726b7061bf265b5c6990e835e8c4f

  • Remove rewriteTargetListIU's expansion of view targetlists in UPDATE. Commit 2ec993a7c, which added triggers on views, modified the rewriter to add dummy entries like "SET x = x" for all columns that weren't actually being updated by the user in any UPDATE directed at a view. That was needed at the time to produce a complete "NEW" row to pass to the trigger. Later it was found to cause problems for ordinary updatable views, so commit cab5dc5da restricted it to happen only for trigger-updatable views. But in the wake of commit 86dc90056, we really don't need it at all. nodeModifyTable.c populates the trigger "OLD" row from the whole-row variable that is generated for the view, and then it computes the "NEW" row using that old row and the UPDATE targetlist. So there is no need for the UPDATE tlist to have dummy entries, any more than it needs them for regular tables or other types of views. (The comments for rewriteTargetListIU suggest that we must do this for correct expansion of NEW references in rules, but I now think that that was just lazy comment editing in 2ec993a7c. If we didn't need it for rules on views before there were triggers, we don't need it after that.) This essentially propagates 86dc90056's decision that we don't need dummy column updates into the view case. Aside from making the different cases more uniform and hence possibly forestalling future bugs, it ought to save a little bit of rewriter/planner effort. Discussion: https://postgr.es/m/2181213.1619397634@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/04942bffd0aa9bd0d143d99b473342eb9ecee88b

  • Doc: fix discussion of how to get real Julian Dates. Somehow I'd convinced myself that rotating to UTC-12 was the way to do this, but upon further review, it's definitely UTC+12. Discussion: https://postgr.es/m/1197050.1619123213@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/c93f8f3b8d3bc780892e2bf11192fbdd136fddfe

  • Add heuristic incoming-message-size limits in the server. We had a report of confusing server behavior caused by a client bug that sent junk to the server: the server thought the junk was a very long message length and waited patiently for data that would never come. We can reduce the risk of that by being less trusting about message lengths. For a long time, libpq has had a heuristic rule that it wouldn't believe large message size words, except for a small number of message types that are expected to be (potentially) long. This provides some defense against loss of message-boundary sync and other corrupted-data cases. The server does something similar, except that up to now it only limited the lengths of messages received during the connection authentication phase. Let's do the same as in libpq and put restrictions on the allowed length of all messages, while distinguishing between message types that are expected to be long and those that aren't. I used a limit of 10000 bytes for non-long messages. (libpq's corresponding limit is 30000 bytes, but given the asymmetry of the FE/BE protocol, there's no good reason why the numbers should be the same.) Experimentation suggests that this is at least a factor of 10, maybe a factor of 100, more than we really need; but plenty of daylight seems desirable to avoid false positives. In any case we can adjust the limit based on beta-test results. For long messages, set a limit of MaxAllocSize - 1, which is the most that we can absorb into the StringInfo buffer that the message is collected in. This just serves to make sure that a bogus message size is reported as such, rather than as a confusing gripe about not being able to enlarge a string buffer. While at it, make sure that non-mainline code paths (such as COPY FROM STDIN) are as paranoid as SocketBackend is, and validate the message type code before believing the message length. This provides an additional guard against getting stuck on corrupted input. Discussion: https://postgr.es/m/2003757.1619373089@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/9626325da5e8e23ff90091bc96535495d350f06e

  • Fix some more omissions in pg_upgrade's tests for non-upgradable types. Commits 29aeda6e4 et al closed up some oversights involving not checking for non-upgradable types within container types, such as arrays and ranges. However, I only looked at version.c, failing to notice that there were substantially-equivalent tests in check.c. (The division of responsibility between those files is less than clear...) In addition, because genbki.pl does not guarantee that auto-generated rowtype OIDs will hold still across versions, we need to consider that the composite type associated with a system catalog or view is non-upgradable. It seems unlikely that someone would have a user column declared that way, but if they did, trying to read it in another PG version would likely draw "no such pg_type OID" failures, thanks to the type OID embedded in composite Datums. To support the composite and reg*-type cases, extend the recursive query that does the search to allow any base query that returns a column of pg_type OIDs, rather than limiting it to exactly one starting type. As before, back-patch to all supported branches. Discussion: https://postgr.es/m/2798740.1619622555@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/57c081de0afcd01bf47c46f015bf8886b01c2c21

  • Improve wording of some pg_upgrade failure reports. Don't advocate dropping a whole table when dropping a column would serve. While at it, try to make the layout of these messages a bit cleaner and more consistent. Per suggestion from Daniel Gustafsson. No back-patch, as we generally don't like to churn translatable messages in released branches. Discussion: https://postgr.es/m/2798740.1619622555@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/c9c37ae03fea0c8ad467392ddf03940b61974935

  • Disallow calling anything but plain functions via the fastpath API. Reject aggregates, window functions, and procedures. Aggregates failed anyway, though with a somewhat obscure error message. Window functions would hit an Assert or null-pointer dereference. Procedures seemed to work as long as you didn't try to do transaction control, but (a) transaction control is sort of the point of a procedure, and (b) it's not entirely clear that no bugs lurk in that path. Given the lack of testing of this area, it seems safest to be conservative in what we support. Also reject proretset functions, as the fastpath protocol can't support returning a set. Also remove an easily-triggered assertion that the given OID isn't 0; the subsequent lookups can handle that case themselves. Per report from Theodor-Arsenij Larionov-Trichkin. Back-patch to all supported branches. (The procedure angle only applies in v11+, of course.) Discussion: https://postgr.es/m/2039442.1615317309@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/2efcd502e56a528f75ec8e88c02a287ad3457d77

  • Doc: update libpq's documentation for PQfn(). Mention specifically that you can't call aggregates, window functions, or procedures this way (the inability to call SRFs was already mentioned). Also, the claim that PQfn doesn't support NULL arguments or results has been a lie since we invented protocol 3.0. Not sure why this text was never updated for that, but do it now. Discussion: https://postgr.es/m/2039442.1615317309@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/386e64ea5abf346d887c21ea8869317838ba19b5

  • Doc: add an example of a self-referential foreign key to ddl.sgml. While we've always allowed such cases, the documentation didn't say you could do it. Discussion: https://postgr.es/m/161969805833.690.13680986983883602407@wrigleys.postgresql.org https://git.postgresql.org/pg/commitdiff/e6f9539dc32473793c03cbe95bc099ee0a199c73

Amit Kapila pushed:

Peter Eisentraut pushed:

Michaël Paquier pushed:

Álvaro Herrera pushed:

Fujii Masao pushed:

  • Don't pass "ONLY" options specified in TRUNCATE to foreign data wrapper. Commit 8ff1c94649 allowed TRUNCATE command to truncate foreign tables. Previously the information about "ONLY" options specified in TRUNCATE command were passed to the foreign data wrapper. Then postgres_fdw constructed the TRUNCATE command to issue the remote server and included "ONLY" options in it based on the passed information. On the other hand, "ONLY" options specified in SELECT, UPDATE or DELETE have no effect when accessing or modifying the remote table, i.e., are not passed to the foreign data wrapper. So it's inconsistent to make only TRUNCATE command pass the "ONLY" options to the foreign data wrapper. Therefore this commit changes the TRUNCATE command so that it doesn't pass the "ONLY" options to the foreign data wrapper, for the consistency with other statements. Also this commit changes postgres_fdw so that it always doesn't include "ONLY" options in the TRUNCATE command that it constructs. Author: Fujii Masao Reviewed-by: Bharath Rupireddy, Kyotaro Horiguchi, Justin Pryzby, Zhihong Yu Discussion: https://postgr.es/m/551ed8c1-f531-818b-664a-2cecdab99cd8@oss.nttdata.com https://git.postgresql.org/pg/commitdiff/8e9ea08bae93a754d5075b7bc9c0b2bc71958bfd

  • doc: Review for "Allow TRUNCATE command to truncate foreign tables". Typos, corrections and language improvements in the docs. Author: Justin Pryzby, Fujii Masao Reviewed-by: Bharath Rupireddy, Justin Pryzby, Fujii Masao Discussion: https://postgr.es/m/20210411041658.GB14564@telsasoft.com https://git.postgresql.org/pg/commitdiff/0c8f40863acb94963df9fd6a4369eb71efe9a93b

Andrew Dunstan pushed:

  • Improve logic in PostgresVersion.pm. Handle the situation where perl swaps the order of operands of the comparison operator. See perldoc overload for details: The third argument is set to TRUE if (and only if) the two operands have been swapped. Perl may do this to ensure that the first argument ($self) is an object implementing the overloaded operation, in line with general object calling conventions. https://git.postgresql.org/pg/commitdiff/fa26eba221a9e837493df47d0255ce615129e9a8

David Rowley pushed:

  • Adjust EXPLAIN output for parallel Result Cache plans. Here we adjust the EXPLAIN ANALYZE output for Result Cache so that we don't show any Result Cache stats for parallel workers who don't contribute anything to Result Cache plan nodes. I originally had ideas that workers who don't help could still have their Result Cache stats displayed. The idea with that was so that I could write some parallel Result Cache regression tests that show the EXPLAIN ANALYZE output. However, I realized a little too late that such tests would just not be possible to have run in a stable way on the buildfarm. With that knowledge, before 9eacee2e6 went in, I had removed all of the tests that were showing the EXPLAIN ANALYZE output of a parallel Result Cache plan, however, I forgot to put back the code that adjusts the EXPLAIN output to hide the Result Cache stats for parallel workers who were not fast enough to help out before query execution was over. All other nodes behave this way and so should Result Cache. Additionally, with this change, it now seems safe enough to remove the SET force_parallel_mode = off that I had added to the regression tests. Also, perform some cleanup in the partition_prune tests. I had adjusted the explain_parallel_append() function to sanitize the Result Cache EXPLAIN ANALYZE output. However, since I didn't actually include any parallel Result Cache tests that show their EXPLAIN ANALYZE output, that code does nothing and can be removed. In passing, move the setting of memPeakKb into the scope where it's used. Reported-by: Amit Khandekar Author: David Rowley, Amit Khandekar Discussion: https://postgr.es/m/CAJ3gD9d8SkfY95GpM1zmsOtX2-Ogx5q-WLsf8f0ykEb0hCRK3w@mail.gmail.com https://git.postgresql.org/pg/commitdiff/3c80e96dffd4df7f66fffa5f265cbd87becb7ef5

Bruce Momjian pushed:

Pending Patches

Bharath Rupireddy sent in a patch to pass BLCKSZ instead of pageSize to gistinitpage, bringing it into line with peers BloomInitPage, brin_page_init and SpGistInitPage.

Yugo Nagata sent in two more revisions of a patch to implement incremental view maintenance.

Peter Eisentraut sent in another revision of a patch to fix ALTER TABLE / INHERIT with generated columns.

Tang sent in another revision of a patch to support tab completion with a query result for uppercase character inputs in psql.

Tom Lane sent in a patch to remove some useless work being done in the rewriter.

Andrey V. Lepikhov sent in a patch to avoid unneeded work when not scanning foreign partitions.

Masahiro Ikeda sent in another revision of a patch to speed up reporting wal stats without introducing a new variable, and change the data type of XXXUsage from long to int64.

Michaël Paquier sent in a patch to track authenticated identity in pg_stat_activity, now that the capability is there for logging purposes.

Mark Dilger and Robert Haas traded patches to modify toast and test pg_amcheck and modify toast corruption test expected output.

Vigneshwaran C and Bharath Rupireddy traded patches to enhanced error messages to include hint messages for redundant options error.

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

Bharath Rupireddy sent in a patch to check for duplicate options and error out in the CREATE COLLATION command.

Andrey V. Lepikhov sent in another revision of a patch to fix an infelicity between the executor and async append nodes.

Andres Freund sent in a patch to fix a performance degradation of REFRESH MATERIALIZED VIEW.

David Fetter and Aleksander Alekseev traded patches to ensure that tab completion of ALTER TABLE ... VALIDATE CONSTRAINT ... only completes with constraints that are not already valid.

Joel Jacobson and Michaël Paquier traded patches to ensure that pg_identify_object_as_address() supports pg_event_trigger oids.

Vigneshwaran C, Masahiko Sawada, and Amit Kapila traded patches to fix the computation of slot stats for 'total_bytes', which had a potential to count things more than once, by using the size of the changes decoded for a transaction to compute it.

Hou Zhijie sent in another revision of a patch to implement { CREATE | ALTER } TABLE ... PARALLEL DML { UNSAFE | RESTRICTED | SAFE };

Kyotaro HORIGUCHI sent in another revision of a patch to fix some dubious error messages and comments.

David Christensen sent in another revision of a patch to Expand the supported units in pg_size_bytes to cover all units.

Tom Lane sent in a patch intended to fix a bug that manifested as pg_upgrade fails to detect unsupported arrays and ranges by checking for uses of composite types.

Hou Zhijie sent in another revision of a patch to fix some parallel safety checks.

Peter Geoghegan sent in a patch intended to fix a bug that manifested as "FailedAssertion" reported in lazy_scan_heap() when running logical replication by setting all_visible_according_to_vm to false in lazy_scan_heap.

Amul Sul sent in a patch to hide the internal temp schema name in EXPLAIN, and change the table descriptions for same.

Peter Smith sent in another revision of a patch to add support for prepared transactions to built-in logical replication, and add prepare API support for streaming transactions.

Vaibhav Dalvi sent in a patch to remove an unneeded post-increment in function quote_identifier of pg_upgrade.

Richard Yen sent in three revisions of a patch to make it possible for pg_filedump to read pg_filenode.map.

Vigneshwaran C sent in a patch to include xid in restoring reorder buffer changes from disk.

Álvaro Herrera sent in another revision of a patch to fix a bug that manifested as a race condition in InvalidateObsoleteReplicationSlots().

Peter Eisentraut sent in another revision of a patch to prevent lwlock dtrace probes from unnecessary work.

Álvaro Herrera sent in another revision of a patch to remove a redundant variable from transformCreateStmt.

Thomas Munro sent in a patch to add WL_SOCKET_CLOSED for socket shutdown events, and use WL_SOCKET_CLOSED for client_connection_check_interval.

Amit Kapila sent in a patch to tighten the concurrent abort check during decoding by covering cases that detecting ERRCODE_TRANSACTION_ROLLBACK does not.

Andres Freund sent in a patch to help make an upcoming WAL prefetch patch work.

David Rowley sent in another revision of a patch to use simplehash.h hashtables in SMgr.

Justin Pryzby sent in another revision of a patch to add the changes needed to allow testing upgrade to v14dev from v9.5-v13, add some more more changes needed to allow upgrade testing, add a test to exercise binary compatibility, and move pg_upgrade kludges to an SQL script.

Julien Rouhaud sent in a patch to add a hook for extensible parsing.

Tom Lane sent in two revisions of a patch to fix a regex performance regression induced by the new match-all code.

Vigneshwaran C sent in two more revisions of a patch to identify missing publications from publisher during CREATE/ALTER SUBSCRIPTION.

Alexander Korotkov sent in four revisions of a patch to make websearch_to_tsquery() parse text in quotes as a single token.

Noah Misch sent in another revision of a patch to dump public schema ownership and security labels, and dump COMMENT ON SCHEMA public.