November 21, 2024: PostgreSQL 17.2, 16.6, 15.10, 14.15, 13.18, and 12.22 Released!

PostgreSQL Weekly News - June 6, 2021

Posted on 2021-06-07 by PWN
PWN

PostgreSQL Weekly News - June 6, 2021

PG Day Russia will be in on line on July 8-9, 2021. The CfP is open at Submit your talk by June 7, 2021!

Person of the week

PostgreSQL Product News

PL/R 8.4.2, an embedding of the R language in PostgreSQL, released.

WAL-G 1.0 a backup management system for PostgreSQL and other databases written in Go, released.

pgtt 2.4, an extension to implement global temporary tables, released.

Database .NET v32.6, a multi-database management tool, now with support for PostgreSQL, released.

pg_partman 4.5.1, a management system for partitioned tables, released.

hypopg 1.3.0, an extension which implements hypothetical indexes, released.

PostgreSQL Jobs for June

Jobs

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:

Noah Misch pushed:

Tom Lane pushed:

  • Fix mis-planning of repeated application of a projection. create_projection_plan contains a hidden assumption (here made explicit by an Assert) that a projection-capable Path will yield a projection-capable Plan. Unfortunately, that assumption is violated only a few lines away, by create_projection_plan itself. This means that two stacked ProjectionPaths can yield an outcome where we try to jam the upper path's tlist into a non-projection-capable child node, resulting in an invalid plan. There isn't any good reason to have stacked ProjectionPaths; indeed the whole concept is faulty, since the set of Vars/Aggs/etc needed by the upper one wouldn't necessarily be available in the output of the lower one, nor could the lower one create such values if they weren't available from its input. Hence, we can fix this by adjusting create_projection_path to strip any top-level ProjectionPath from the subpath it's given. (This amounts to saying "oh, we changed our minds about what we need to project here".) The test case added here only fails in v13 and HEAD; before that, we don't attempt to shove the Sort into the parallel part of the plan, for reasons that aren't entirely clear to me. However, all the directly-related code looks generally the same as far back as v11, where the hazard was introduced (by d7c19e62a). So I've got no faith that the same type of bug doesn't exist in v11 and v12, given the right test case. Hence, back-patch the code changes, but not the irrelevant test case, into those branches. Per report from Bas Poot. Discussion: https://postgr.es/m/534fca83789c4a378c7de379e9067d4f@politie.nl https://git.postgresql.org/pg/commitdiff/6ee41a301e70fc8e4ad383bad22d695f66ccb0ac

  • Reject SELECT ... GROUP BY GROUPING SETS (()) FOR UPDATE. This case should be disallowed, just as FOR UPDATE with a plain GROUP BY is disallowed; FOR UPDATE only makes sense when each row of the query result can be identified with a single table row. However, we missed teaching CheckSelectLocking() to check groupingSets as well as groupClause, so that it would allow degenerate grouping sets. That resulted in a bad plan and a null-pointer dereference in the executor. Looking around for other instances of the same bug, the only one I found was in examine_simple_variable(). That'd just lead to silly estimates, but it should be fixed too. Per private report from Yaoguang Chen. Back-patch to all supported branches. https://git.postgresql.org/pg/commitdiff/1103033aedc10295eb689a4b7158f21ef4c14a11

  • Teach tab-complete.c about recently-added CREATE TYPE options. Commit c7aba7c14 missed adding SUBSCRIPT here, and commit 6df7a9698 missed adding MULTIRANGE_TYPE_NAME. Haiying Tang and Tom Lane Discussion: https://postgr.es/m/OS0PR01MB6113F9EDA46FA53BAA5445BDFB3D9@OS0PR01MB6113.jpnprd01.prod.outlook.com https://git.postgresql.org/pg/commitdiff/9e3b3ff2664dd0b349d2a6d6f047128cb3489cf2

  • Update plannodes.h's comments about PlanRowMark. The reference here to different physical column numbers in inherited UPDATE/DELETE plans is obsolete as of 86dc90056; remove it. Also rework the text about inheritance cases to make it clearer. https://git.postgresql.org/pg/commitdiff/79c50ca57828e9f8375766b36cce1e2960eebf87

  • Fix planner's row-mark code for inheritance from a foreign table. Commit 428b260f8 broke planning of cases where row marks are needed (SELECT FOR UPDATE, etc) and one of the query's tables is a foreign table that has regular table(s) as inheritance children. We got the reverse case right, but apparently were thinking that foreign tables couldn't be inheritance parents. Not so; so we need to be able to add a CTID junk column while adding a new child, not only a wholerow junk column. Back-patch to v12 where the faulty code came in. Amit Langote Discussion: https://postgr.es/m/CA+HiwqEmo3FV1LAQ4TVyS2h1WM=kMkZUmbNuZSCnfHvMcUcPeA@mail.gmail.com https://git.postgresql.org/pg/commitdiff/889592344c48d3965567f331b4ea89dfe6447bce

  • Re-allow custom GUC names that have more than two components. Commit 3db826bd5 disallowed this case, but it turns out that some people are depending on it. Since the core grammar has allowed it since 3dc37cd8d, it seems like this code should fall in line. Per bug #17045 from Robert Sosinski. Discussion: https://postgr.es/m/17045-6a4a9f0d1513f72b@postgresql.org https://git.postgresql.org/pg/commitdiff/2955c2be79b35fa369c83fa3b5f44661cb88afa9

  • Fix incorrect permissions on pg_subscription. The documented intent is for all columns except subconninfo to be publicly readable. However, this has been overlooked twice. subsynccommit has never been readable since it was introduced, nor has the oid column (which is important for joining). Given the lack of previous complaints, it's not clear that it's worth doing anything about this in the back branches. But there's still time to fix it inexpensively for v14. Per report from Israel Barth (via Euler Taveira). Patch by Euler Taveira, possibly-vain comment updates by me. Discussion: https://postgr.es/m/b8f7c17c-0041-46b6-acfe-2d1f5a985ab4@www.fastmail.com https://git.postgresql.org/pg/commitdiff/3590680b85a8e51ef8df550e5a10dedd0d2dfd88

  • Doc: fix bogus intarray index example. The siglen parameter is provided by gist__intbig_ops not gist__int_ops. Simon Norris Discussion: https://postgr.es/m/11BF2AA9-17AE-432A-AFE1-584FB9FB079D@hillcrestgeo.ca https://git.postgresql.org/pg/commitdiff/e4539386decae1c435767a69507cc7cbb11ac3ff

  • Fix postgres_fdw failure with whole-row Vars of type RECORD. Commit 86dc90056 expects that FDWs can cope with whole-row Vars for their tables, even if the Vars are marked with vartype RECORDOID. Previously, whole-row Vars generated by the planner had vartype equal to the relevant table's rowtype OID. (The point behind this change is to enable sharing of resjunk columns across inheritance child tables.) It turns out that postgres_fdw fails to cope with this, though through bad fortune none of its test cases exposed that. Things mostly work, but when we try to read back a value of such a Var, the expected rowtype is not available to record_in(). Fortunately, it's not difficult to hack up the tupdesc that controls this process to substitute the foreign table's rowtype for RECORDOID. Thus we can solve the runtime problem while still sharing the resjunk column with other tables. Per report from Alexander Pyhalov. Discussion: https://postgr.es/m/7817fb9ebd6661cdf9b67dec6e129a78@postgrespro.ru https://git.postgresql.org/pg/commitdiff/f61db909dfb94f3411f8719916601a11a905b95e

Peter Eisentraut pushed:

Thomas Munro pushed:

Amit Kapila pushed:

Fujii Masao pushed:

Tomáš Vondra pushed:

David Rowley pushed:

Andrew Dunstan pushed:

  • In PostgresNode.pm, don't pass SQL to psql on the command line. The Msys shell mangles certain patterns in its command line, so avoid handing arbitrary SQL to psql on the command line and instead use IPC::Run's redirection facility for stdin. This pattern is already mostly whats used, but query_poll_until() was not doing the right thing. Problem discovered on the buildfarm when a new TAP test failed on msys. https://git.postgresql.org/pg/commitdiff/11e9caff82bc7326e2bc9782937cb03875050cc4

Pending Patches

Emre Hasegeli sent in a patch to handle boolean comparison predicates in the PostgreSQL FDW, as not all were recognized.

Pavel Stěhule sent in another revision of a patch to get the PL/ppgsql debug API to returns the text value of variable contents.

Dilip Kumar sent in four more revisions of a patch to plug a memory leak when decoding speculative insert with TOAST.

Hou Zhijie sent in three more revisions of a patch to make it possible for INSERT ... SELECT to execute in parallel.

Peter Smith and Ajin Cherian traded patches to support prepared transactions in built-in logical replication.

Dilip Kumar sent in three revisions of a patch to Extract unchanged replica identity key if it is stored externally. If replica identity is set to key and the key is not modified we don't log key separately because it should be logged along with the updated tuple. But if the key is stored externally we must have to detoast and log it separately.

Justin Pryzby sent in another revision of a patch to implement CREATE TABLE (LIKE .. INCLUDING ACCESS METHOD).

Zhihong Yu sent in a patch to return the correct error code from pgtls_init().

Peter Smith and Takamichi Osumi traded patches to document the deadlock risk that AELs on the catalog can cause in logical decoding in synchronous mode.

Etsuro Fujita sent in another revision of a patch to fix rescan of async appends.

Bharath Rupireddy sent in two more revisions of a patch to refactor parse_subscription_options to make them easier to extend and make mutually exclusive options easier to detect via a bitmap.

Hou Zhijie and Amit Langote traded patches to skip partition tuple routing with constant partition key.

Kyotaro HORIGUCHI sent in two revisions of a patch to fix a bug where it was possible to get a duplicate history file.

Aleksander Alekseev sent in another revision of a patch to add a way to specify column projection lists to the table access method API.

Joe Wildish sent in another revision of a patch to Allow queries in WHEN expression of FOR EACH STATEMENT triggers.

Greg Sabino Mullane sent in two more revisions of a patch to speed up pg_checksums in cases where the checksum is already set.

Andrew Dunstan sent in three revisions of a patch to clarify and update the short version installation documentation.

Matthias van de Meent sent in a patch to report phase progress for the sort phase in parallel btree construction.

Jim Mlodgenski sent in a patch to implement CREATE MODULE.

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

Thomas Munro sent in a patch to remove more obsolete comments about semaphores.

Thomas Munro sent in another revision of a patch to make ProcSendSignal() more efficient by changing from referring to target processes by pid to using pgprocno, which makes it possible to avoid scanning ProcArray and keeping track of the startup process.

Greg Nancarrow sent in another revision of a patch to implement event triggers for client connect.

Amit Langote sent in another revision of a patch to add an assertion to ensure that child row marks don't come into existence prematurely.

Nitin Jadhav sent in another revision of a patch to implement multi-column list partitioning.

David Christensen sent in another revision of a patch to expand the units that pg_size_pretty(numeric) knows about up through YB, and expand the supported units in pg_size_bytes to cover all units.

Zhihong Yu sent in two revisions of a patch to document the reason that it's not necessary to check the return value from unlink in write_relcache_init_file.

Robert Haas and Dilip Kumar traded patches to fix a corner case failure of a new standby to follow the new primary.

Bharath Rupireddy sent in a patch to use (void) when the return value of fsm_set_and_search is ignored.

David Christensen sent in a patch to implement DELETE...CASCADE.

Kyotaro HORIGUCHI sent in a patch to make an error message in pg_waldump clearer and more explicit as to what went wrong and where.

Andrey V. Lepikhov sent in another revision of a patch to use COPY for bulk writes on tables with foreign partitions.

Zeng Wenjing sent in another revision of a patch to implement global temporary tables.

Tomáš Vondra sent in a patch to add PQ_QUERY_PARAM_MAX_LIMIT, create a copy of a descriptor for batching, and initialize slots only once for batching.

Nitin Jadhav sent in a patch to create a progress indicator for the startup process.

Aleksander Alekseev sent in a patch to add the ZSON extension to contrib.

Andrew Dunstan sent in a patch to ensure that pg_upgrade echos Windows commands.

Maxim Orlov sent in a patch intended to fix a bug that manifested as a core dump in parallel scan with SubTransGetTopmostTransaction assert.

Ajin Cherian sent in a patch to remove the two-phase option from the CreateReplicationSlotCmd struct, and add an option to set two-phase in CREATE_REPLICATION_SLOT.

Tom Lane sent in another revision of a patch to fix an infelicity between CALL and procedures with output-only arguments.

Melanie Plageman sent in another revision of a patch to add a system view tracking shared buffer actions.

Jehan-Guillaume de Rorthais sent in another revision of a patch to add a pg_stat_waitaccum view, and change the measuring method of wait event time from INSTR_TIME to rdtsc.

Michaël Paquier sent in a patch to prevent VACUUM from re-compressing.

Álvaro Herrera sent in a patch to make toast_get_compression_id inline.

Álvaro Herrera sent in another revision of a patch to reduce the overhead of TOAST recompression on table rewrite.

David Rowley sent in a patch to fix a few typos in brin_minmax_multi.c.

Vigneshwaran C sent in another revision of a patch to add schema level support for publication, and add tests for same.

Ranier Vilela sent in another revision of a patch to reduce the overhead of TOAST recompression on table rewrite.

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

Julien Rouhaud sent in another revision of a patch to add a parser_hook hook, add a sqlol parser based on same, add a new MODE_SINGLE_QUERY to the core parser and use it in pg_parse_query, and teach sqlol to use the new MODE_SINGLE_QUERY parser mode.

Julien Rouhaud sent in a patch to fix SQL-standard body empty statements handling.

Michaël Paquier sent in a patch intended to fix a misplaced superuser check in pg_log_backend_memory_contexts().

Tomáš Vondra sent in another revision of a patch to fix a bug in psql's \dX (extended statistics) which was failing to check for object visibility.