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!
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.
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.
Michaë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:
Raise a timeout to 180s, in test 010_logical_decoding_timelines.pl. Per buildfarm member hornet. Also, update Pod documentation showing the lower value. Back-patch to v10, where the test first appeared. https://git.postgresql.org/pg/commitdiff/d03eeab886baa1be73f8fc2938fb842d25a71fe8
Fix missing gettimeofday() declaration. This avoids a warning under MinGW versions having gettimeofday(), per buildfarm member walleye. https://git.postgresql.org/pg/commitdiff/49527a32ca97761d78efef732a4ac76a2fc086b2
Add Windows file version information to libpq_pipeline.exe. https://git.postgresql.org/pg/commitdiff/42344ad0ed465ea988d8310d2f413d65329f55a8
Standardize nodes/*funcs.c cosmetics for ForeignScan.resultRelation.
catversion bump due to readfuncs.c field order change.
https://git.postgresql.org/pg/commitdiff/a2dee328bbe5b1979bbc6a784deb86a336c9cd74
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:
Add regression test for recovery pause. Previously there was no regression test for recovery pause feature. This commit adds the test that checks - recovery can be paused or resumed expectedly - pg_get_wal_replay_pause_state() reports the correct pause state - the paused state ends and promotion continues if a promotion is triggered while recovery is paused Suggested-by: Michael Paquier Author: Fujii Masao Reviewed-by: Kyotaro Horiguchi, Dilip Kumar Discussion: https://postgr.es/m/YKNirzqM1HYyk5h4@paquier.xyz https://git.postgresql.org/pg/commitdiff/6bbc5c5e96b08f6b8c7d28d10ed8dfe6c49dca30
Remove unnecessary use of Time::HiRes from 013_crash_restart.pl. The regression test 013_crash_restart.pl included "use Time::HiRes qw(usleep)", but the usleep was not used there. Author: Fujii Masao Reviewed-by: Kyotaro Horiguchi Discussion: https://postgr.es/m/63ad1368-18e2-8900-8443-524bdfb1bef5@oss.nttdata.com https://git.postgresql.org/pg/commitdiff/df466d30c6caa02e2215595fd83ca70be3199cec
Tomáš Vondra pushed:
David Rowley pushed:
Standardize usages of appendStringInfo and appendPQExpBuffer. Fix a few places that were using appendStringInfo() when they should have been using appendStringInfoString(). Also some cases of appendPQExpBuffer() that would have been better suited to use appendPQExpBufferChar(), and finally, some places that used appendPQExpBuffer() when appendPQExpBufferStr() would have suited better. There are no bugs are being fixed here. The aim is just to make the code use the most optimal function for the job. All the code being changed here is new to PG14. It makes sense to fix these before we branch for PG15. There are a few other places that we could fix, but those cases are older code so fixing those seems less worthwhile as it may cause unnecessary back-patching pain in the future. Author: Hou Zhijie Discussion: https://postgr.es/m/OS0PR01MB5716732158B1C4142C6FE375943D9@OS0PR01MB5716.jpnprd01.prod.outlook.com https://git.postgresql.org/pg/commitdiff/f736e188ce70bda34f04c9f381b7c5141dc20dce
Adjust locations which have an incorrect copyright year. A few patches committed after ca3b37487 mistakenly forgot to make the copyright year 2021. Fix these. Discussion: https://postgr.es/m/CAApHDvqyLmd9P2oBQYJ=DbrV8QwyPRdmXtCTFYPE08h+ip0UJw@mail.gmail.com https://git.postgresql.org/pg/commitdiff/7fc26d11e370afe237631265714221364d7e7910
Clean up some questionable usages of DatumGet* macros. This tidies up some
questionable coding which made use of DatumGetPointer() for Datums being
passed into functions where the parameter is expected to be a cstring. We saw
no compiler warnings with the old code as the Pointer type used in
DatumGetPointer() happens to be a char * rather than a void *. However,
that's no excuse and we should be using the correct macro for the job. Here
we also make use of OutputFunctionCall() rather than using FunctionCall1()
directly to call the type's output function. OutputFunctionCall() is the
standard way to do this. It casts the returned value to a cstring for us. In
passing get rid of a duplicate call to strlen(). Most compilers will likely
optimize away the 2nd call, but there may be some that won't. In any case,
this just aligns the code to some other nearby code that already does this.
Discussion:
https://postgr.es/m/CAApHDvq1D=ehZ8hey8Hz67N+_Zth0GHO5wiVCfv1YcGPMXJq0A@mail.gmail.com
https://git.postgresql.org/pg/commitdiff/8bdb36aab287f564eac534878bc0e1d873a4e3db
Doc: Fix some spelling mistakes. Author: Daniel Gustafsson Discussion: https://postgr.es/m/7838B8EE-CFD6-48D7-AE2D-520D69FD84BD@yesql.se https://git.postgresql.org/pg/commitdiff/8f3c06c5d56fc0fa414bcf548860ac50a8fe5982
Andrew Dunstan pushed:
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.