PostgreSQL Weekly News - June 6, 2021PostgreSQL Weekly News - June 6, 2021PG 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 NewsPL/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 JuneJobs PostgreSQL in the NewsPlanet 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 PatchesMichaël Paquier pushed: Improve some error wording with multirange type parsing. Braces were referred in some error messages as only brackets (not curly brackets or curly braces), which can be confusing as other types of brackets could be used. While on it, add one test to check after the case of junk characters detected after a right brace. Author: Kyotaro Horiguchi Discussion: https://postgr.es/m/20210514.153153.1814935914483287479.horikyota.ntt@gmail.com https://git.postgresql.org/pg/commitdiff/12cc956664f159e97be71e33f15ec5f42e46b24e Ignore more environment variables in TAP tests. Various environment variables were not getting reset in the TAP tests, which would cause failures depending on the tests or the environment variables involved. For example, PGSSL{MAX,MIN}PROTOCOLVERSION could cause failures in the SSL tests. Even worse, a junk value of PGCLIENTENCODING makes a server startup fail. The list of variables reset is adjusted in each stable branch depending on what is supported. While on it, simplify a bit the code per a suggestion from Andrew Dunstan, using a list of variables instead of doing single deletions. Reviewed-by: Andrew Dunstan, Daniel Gustafsson Discussion: https://postgr.es/m/YLbjjRpucIeZ78VQ@paquier.xyz Backpatch-through: 9.6 https://git.postgresql.org/pg/commitdiff/8279f68a1b13d58a0c9869a60081009d8995e4df Reduce risks of conflicts in internal queries of REFRESH MATVIEW CONCURRENTLY. The internal SQL queries used by REFRESH MATERIALIZED VIEW CONCURRENTLY include some aliases for its diff and temporary relations with rather-generic names: diff, newdata, newdata2 and mv. Depending on the queries used for the materialized view, using CONCURRENTLY could lead to some internal failures if the matview query and those internal aliases conflict. Those names have been chosen in 841c29c8. This commit switches instead to a naming pattern which is less likely going to cause conflicts, based on an idea from Thomas Munro, by appending _$ to those aliases. This is not perfect as those new names could still conflict, but at least it has the advantage to keep the code readable and simple while reducing the likelihood of conflicts to be close to zero. Reported-by: Mathis Rudolf Author: Bharath Rupireddy Reviewed-by: Bernd Helmle, Thomas Munro, Michael Paquier Discussion: https://postgr.es/m/109c267a-10d2-3c53-b60e-720fcf44d9e8@credativ.de Backpatch-through: 9.6 https://git.postgresql.org/pg/commitdiff/187682c3217375c9b70417bf3235790f639e8e7e doc: Fix link reference for PGSSLMAXPROTOCOLVERSION. The link was pointing to the minimum protocol version. Incorrect as of ff8ca5f. Author: Daniel Gustafsson Discussion: https://postgr.es/m/F893F184-C645-4C21-A2BA-583441B7288F@yesql.se Backpatch-through: 13 https://git.postgresql.org/pg/commitdiff/77e9d1b4884262fa09cd8d141c7eadad3affde8b doc: Add description for PGSSLCRLDIR. This was missing in the section dedicated to the supported environment variables of libpq. Oversight in f5465fa. Reviewed-by: Daniel Gustafsson, Kyotaro Horiguchi Discussion: https://postgr.es/m/YLhI0mLoRkY3u4Wj@paquier.xyz https://git.postgresql.org/pg/commitdiff/1e809db86b160e697a56bf47358f7733475840d3
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: Fix RADIUS error reporting in hba file parsing. The RADIUS-related checks in parse_hba_line() did not respect elevel and did not fill in *err_msg . Also, verify_option_list_length() pasted together error messages in an untranslatable way. To fix the latter, remove the function and do the error checking inline. It's a bit more verbose but only minimally longer, and it makes fixing the first two issues straightforward. Reviewed-by: Magnus Hagander magnus@hagander.net Discussion: https://www.postgresql.org/message-id/flat/8381e425-8c23-99b3-15ec-3115001db1b2%40enterprisedb.com https://git.postgresql.org/pg/commitdiff/7c544ecdad814ccda709cfb6aa7d62840c3a7486 doc: Group options in pg_amcheck reference page. The previous arrangement was just one big list, and the internal order was not all consistent either. Now arrange the options by group and sort them, the way it's already done in the --help output and one other reference pages. Also fix some ordering in the --help output. https://git.postgresql.org/pg/commitdiff/cb3cffe694b6041c1de47b12b225e05f664c7285 Fix subtransaction test for Python 3.10. Starting with Python 3.10, the stacktrace looks differently: - PL/Python function "subtransaction_exit_subtransaction_in_with", line 3, in <module> - s.exit(None, None, None) + PL/Python function "subtransaction_exit_subtransaction_in_with", line 2, in <module> + with plpy.subtransaction() as s: Using try/except specifically makes the error look always the same. (See https://github.com/python/cpython/pull/25719 for the discussion of this change in Python.) Author: Honza Horak hhorak@redhat.com Discussion: https://www.postgresql.org/message-id/flat/853083.1620749597%40sss.pgh.pa.us RHBZ: https://bugzilla.redhat.com/show_bug.cgi?id=1959080 https://git.postgresql.org/pg/commitdiff/4a682d85a1408e48ac529295c329ba2c17a44724 gitattributes: Add new entry to silence whitespace error. https://git.postgresql.org/pg/commitdiff/e6159885b78e9b91b2adc3161c5f827d081f2b13 doc: Make terminology in glossary consistent. Use "reside in" rather than "belong to" for objects in a schema. Previous use was a mix of the two. Author: Alvaro Herrera alvherre@alvh.no-ip.org Discussion: https://www.postgresql.org/message-id/202106021932.idmbjjaqk7ke@alvherre.pgsql https://git.postgresql.org/pg/commitdiff/01ddd2f7e411ba434473faebf00f5b5af84c0f64 doc: Simplify COMMENT and SECURITY LABEL documentation. Just say that objects that reside in schemas can be schema-qualified. Don't list all possible such objects. The existing lists weren't complete anyway. Discussion: https://www.postgresql.org/message-id/flat/b2ec2234-67fe-d861-5cea-f526cd18c086%40enterprisedb.com https://git.postgresql.org/pg/commitdiff/5c25fd650a774cc4f16ac9c635830d9bc5797f61
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 PatchesEmre 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. |