PostreSQL.org

PostgreSQL Weekly News - May 30, 2021

pgSCV, a Prometheus-compatible monitoring agent and metrics exporter for PostgreSQL, released.

Pgpool-II 4.2.3, 4.1.7, 4.0.14, 3.7.19 and 3.6.26, a connection pooler and statement replication system for PostgreSQL, released.

sqlite_fdw 1.2.0 released.

Crunchy PostgreSQL Operator 4.7.0, a system for deploying and managing open source PostgreSQL clusters on Kubernetes, released.

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

InfluxDB fdw 1.0.0 released https://github.com/pgspider/influxdb_fdw

griddb_fdw 2.0 released.

PostgreSQL Product News 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

David Rowley pushed:

• Add missing NULL check when building Result Cache paths. Code added in 9e215378d to disable building of Result Cache paths when not all join conditions are part of the parameterization of a unique join failed to first check if the inner path's param_info was set before checking the param_info's ppi_clauses. Add a check for NULL values here and just bail on trying to build the path if param_info is NULL. lateral_vars are not considered when deciding if the join is unique, so we're not missing out on doing the optimization when there are lateral_vars and no param_info. Reported-by: Coverity, via Tom Lane Discussion: https://postgr.es/m/457998.1621779290@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/99c5852e20a0987eca1c38ba0c09329d4076b6a0

• Fix setrefs.c code for Result Cache nodes. Result Cache, added in 9eacee2e6 neglected to properly adjust the plan references in setrefs.c. This could lead to the following error during EXPLAIN: ERROR: cannot decompile join alias var in plan tree Fix that. Bug: 17030 Reported-by: Hans Buschmann Discussion: https://postgr.es/m/17030-5844aecae42fe223@postgresql.org https://git.postgresql.org/pg/commitdiff/cba5c70b956810c61b3778f7041f92fbb8065acb

Tom Lane pushed:

• Doc: move some catalogs.sgml entries to the right place. pg_statistic_ext_data.stxdexpr was listed under the wrong catalog, as was pg_stats_ext.exprs. Also there was a bogus entry for pg_statistic_ext_data.stxexprs. Apparently a merge failure in commit a4d75c86b. Guillaume Lelarge and Tom Lane Discussion: https://postgr.es/m/CAECtzeUHw+w64eUFVeV_2FJviAw6oZ0wNLkmU843ZH4hAQfiWg@mail.gmail.com https://git.postgresql.org/pg/commitdiff/713a431c781fbfe1a22fae4991836077f0f4c513

• Fix use of uninitialized variable in inline_function(). Commit e717a9a18 introduced a code path that bypassed the call of get_expr_result_type, which is not good because we need its rettupdesc result to pass to check_sql_fn_retval. We'd failed to notice right away because the code path in which check_sql_fn_retval uses that argument is fairly hard to reach in this context. It's not impossible though, and in any case inline_function would have no business assuming that check_sql_fn_retval doesn't need that value. To fix, move get_expr_result_type out of the if-block, which in turn requires moving the construction of the dummy FuncExpr out of it. Per report from Ranier Vilela. (I'm bemused by the lack of any compiler complaints...) Discussion: https://postgr.es/m/CAEudQAqBqQpQ3HruWAGU_7WaMJ7tntpk0T8k_dVtNB46DqdBgw@mail.gmail.com https://git.postgresql.org/pg/commitdiff/e30e3fdea873e4e9517c490232ea1d3bcef6c643

• Rethink definition of pg_attribute.attcompression. Redefine '\0' (InvalidCompressionMethod) as meaning "if we need to compress, use the current setting of default_toast_compression". This allows '\0' to be a suitable default choice regardless of datatype, greatly simplifying code paths that initialize tupledescs and the like. It seems like a more user-friendly approach as well, because now the default compression choice doesn't migrate into table definitions, meaning that changing default_toast_compression is usually sufficient to flip an installation's behavior; one needn't tediously issue per-column ALTER SET COMPRESSION commands. Along the way, fix a few minor bugs and documentation issues with the per-column-compression feature. Adopt more robust APIs for SetIndexStorageProperties and GetAttributeCompression. Bump catversion because typical contents of attcompression will now be different. We could get away without doing that, but it seems better to ensure v14 installations all agree on this. (We already forced initdb for beta2, anyway.) Discussion: https://postgr.es/m/626613.1621787110@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/e6241d8e030fbd2746b3ea3f44e728224298f35b

• Reduce the range of OIDs reserved for genbki.pl. Commit ab596105b increased FirstBootstrapObjectId from 12000 to 13000, but we've had some push-back about that. It's worrisome to reduce the daylight between there and FirstNormalObjectId, because the number of OIDs consumed during initdb for collation objects is hard to predict. We can improve the situation by abandoning the assumption that these OIDs must be globally unique. It should be sufficient for them to be unique per-catalog. (Any code that's unhappy about that is broken anyway, since no more than per-catalog uniqueness can be guaranteed once the OID counter wraps around.) With that change, the largest OID assigned during genbki.pl (starting from a base of 10000) is a bit under

• This allows reverting FirstBootstrapObjectId to 12000 with reasonable confidence that that will be sufficient for many years to come. We are not, at this time, abandoning the expectation that hand-assigned OIDs (below 10000) are globally unique. Someday that'll likely be necessary, but the need seems years away still. This is late for v14, but it seems worth doing it now so that downstream software doesn't have to deal with the consequences of a change in FirstBootstrapObjectId. In any case, we already bought into forcing an initdb for beta2, so another catversion bump won't hurt. Discussion: https://postgr.es/m/1665197.1622065382@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/a4390abecf0f5152cff864e82b67e5f6c8489698

• Doc: improve libpq service-file docs, avoid overspecifying pathnames. Clarify libpq.sgml's description of service file locations and semantics. Avoid use of backtick'ed pg_config calls to describe paths; that doesn't work on Windows, and even on Unix it's an idiom that not all readers may be instantly familiar with. Don't overspecify the locations of include files, instead writing only as much as you'd use in #include directives. The previous text in these places was incorrect for some installations, depending on where "postgresql" is in the install path. Our convention for referencing the user's home directory seems to be "~", so change the one place that spelled it "$HOME". install-windows.sgml follows the platform convention of spelling file paths with "\", so change the one place that used "/". Haiying Tang and Tom Lane Discussion: https://postgr.es/m/162149020918.26174.7150424047314144297@wrigleys.postgresql.org https://git.postgresql.org/pg/commitdiff/ba356a397de565c014384aa01a945aab7d50928c

Peter Geoghegan pushed:

• Consider triggering VACUUM failsafe during scan. The wraparound failsafe mechanism added by commit 1e55e7d1 handled the one-pass strategy case (i.e. the "table has no indexes" case) by adding a dedicated failsafe check. This made up for the fact that the usual one-pass checks inside lazy_vacuum_all_indexes() cannot ever be reached during a one-pass strategy VACUUM. This approach failed to account for two-pass VACUUMs that opt out of index vacuuming up-front. The INDEX_CLEANUP off case in the only case that works like that. Fix this by performing a failsafe check every 4GB during the first scan of the heap, regardless of the details of the VACUUM. This eliminates the special case, and will make the failsafe trigger more reliably. Author: Peter Geoghegan pg@bowt.ie Reported-By: Andres Freund andres@anarazel.de Reviewed-By: Masahiko Sawada sawada.mshk@gmail.com Discussion: https://postgr.es/m/20210424002921.pb3t7h6frupdqnkp@alap3.anarazel.de https://git.postgresql.org/pg/commitdiff/c242baa4a831ac2e7dcaec85feb410aefa3a996e

• Fix VACUUM VERBOSE's LP_DEAD item pages output. Oversight in commit 5100010e. https://git.postgresql.org/pg/commitdiff/9afdea982420f9672b88e5c17d1ee8eec64105fc

Michaël Paquier pushed:

• Disallow SSL renegotiation. SSL renegotiation is already disabled as of 48d23c72, however this does not prevent the server to comply with a client willing to use renegotiation. In the last couple of years, renegotiation had its set of security issues and flaws (like the recent CVE-2021-3449), and it could be possible to crash the backend with a client attempting renegotiation. This commit takes one extra step by disabling renegotiation in the backend in the same way as SSL compression (f9264d15) or tickets (97d3a0b0). OpenSSL 1.1.0h has added an option named SSL_OP_NO_RENEGOTIATION able to achieve that. In older versions there is an option called SSL3_FLAGS_NO_RENEGOTIATE_CIPHERS that was undocumented, and could be set within the SSL object created when the TLS connection opens, but I have decided not to use it, as it feels trickier to rely on, and it is not official. Note that this option is not usable in OpenSSL < 1.1.0h as the internal contents of the *SSL object are hidden to applications. SSL renegotiation concerns protocols up to TLSv1.2. Per original report from Robert Haas, with a patch based on a suggestion by Andres Freund. Author: Michael Paquier Reviewed-by: Daniel Gustafsson Discussion: https://postgr.es/m/YKZBXx7RhU74FlTE@paquier.xyz Backpatch-through: 9.6 https://git.postgresql.org/pg/commitdiff/01e6f1a842f406170e5f717305e4a6cf0e84b3ee

• Fix memory leak when de-toasting compressed values in VACUUM FULL/CLUSTER. VACUUM FULL and CLUSTER can be used to enforce the use of the existing compression method of a toastable column if a value currently stored is compressed with a method that does not match the column's defined method. The code in charge of decompressing and recompressing toast values at rewrite left around the detoasted values, causing an accumulation of memory allocated in TopTransactionContext. When processing large relations, this could cause the system to run out of memory. The detoasted values are not needed once their tuple is rewritten, and this commit ensures that the necessary cleanup happens. Issue introduced by bbe0a81d. The comments of the area are reordered a bit while on it. Reported-by: Andres Freund Analyzed-by: Andres Freund Author: Michael Paquier Reviewed-by: Dilip Kumar Discussion: https://postgr.es/m/20210521211929.pcehg6f23icwstdb@alap3.anarazel.de https://git.postgresql.org/pg/commitdiff/fb0f5f0172edf9f63c8f70ea9c1ec043b61c770e

• Fix typo in heapam.c. Author: Hou Zhijie Discussion: https://postgr.es/m/OS0PR01MB571612191738540B27A8DE5894249@OS0PR01MB5716.jpnprd01.prod.outlook.com https://git.postgresql.org/pg/commitdiff/190fa5a00a8f9ecee8eef2c8e26136b772b94e19

• doc: Fix description of some GUCs in docs and postgresql.conf.sample. The following parameters have been imprecise, or incorrect, about their description (PGC_POSTMASTER or PGC_SIGHUP): - autovacuum_work_mem (docs, as of 9.6~) - huge_page_size (docs, as of 14~) - max_logical_replication_workers (docs, as of 10~) - max_sync_workers_per_subscription (docs, as of 10~) - min_dynamic_shared_memory (docs, as of 14~) - recovery_init_sync_method (postgresql.conf.sample, as of 14~) - remove_temp_files_after_crash (docs, as of 14~) - restart_after_crash (docs, as of 9.6~) - ssl_min_protocol_version (docs, as of 12~) - ssl_max_protocol_version (docs, as of 12~) This commit adjusts the description of all these parameters to be more consistent with the practice used for the others. Revewed-by: Justin Pryzby Discussion: https://postgr.es/m/YK2ltuLpe+FbRXzA@paquier.xyz Backpatch-through: 9.6 https://git.postgresql.org/pg/commitdiff/2941138e60fc711bd221b3264807f36cc079dfbb

• Fix MSVC scripts when building with GSSAPI/Kerberos. The deliverables of upstream Kerberos on Windows are installed with paths that do not match our MSVC scripts. First, the include folder was named "inc/" in our scripts, but the upstream MSIs use "include/". Second, the build would fail with 64-bit environments as the libraries are named differently. This commit adjusts the MSVC scripts to be compatible with the latest installations of upstream, and I have checked that the compilation was able to work with the 32-bit and 64-bit installations. Special thanks to Kondo Yuta for the help in investigating the situation in hamerkop, which had an incorrect configuration for the GSS compilation. Reported-by: Brian Ye Discussion: https://postgr.es/m/162128202219.27274.12616756784952017465@wrigleys.postgresql.org Backpatch-through: 9.6 https://git.postgresql.org/pg/commitdiff/025110663448a8c877f4b591495f2e5d187d8936

Amit Kapila pushed:

• Improve docs and error messages for parallel vacuum. The error messages, docs, and one of the options were using 'parallel degree' to indicate parallelism used by vacuum command. We normally use 'parallel workers' at other places so change it for parallel vacuum accordingly. Author: Bharath Rupireddy Reviewed-by: Dilip Kumar, Amit Kapila Backpatch-through: 13 Discussion: https://postgr.es/m/CALj2ACWz=PYrrFXVsEKb9J1aiX4raA+UBe02hdRp_zqDkrWUiw@mail.gmail.com https://git.postgresql.org/pg/commitdiff/0734b0e983443882ec509ab4501c30ba9b706f5f

• Doc: Update logical decoding stats information. Add the information of pg_stat_replication_slots view along with other system catalogs related to logical decoding. Author: Vignesh C Reviewed-by: Amit Kapila Discussion: https://postgr.es/m/20210319185247.ldebgpdaxsowiflw@alap3.anarazel.de https://git.postgresql.org/pg/commitdiff/0c6b92d9c6fb74255467573fde54f65139b26603

• Fix assertion during streaming of multi-insert toast changes. While decoding the multi-insert WAL we can't clean the toast untill we get the last insert of that WAL record. Now if we stream the changes before we get the last change, the memory for toast chunks won't be released and we expect the txn to have streamed all changes after streaming. This restriction is mainly to ensure the correctness of streamed transactions and it doesn't seem worth uplifting such a restriction just to allow this case because anyway we will stream the transaction once such an insert is complete. Previously we were using two different flags (one for toast tuples and another for speculative inserts) to indicate partial changes. Now instead we replaced both of them with a single flag to indicate partial changes. Reported-by: Pavan Deolasee Author: Dilip Kumar Reviewed-by: Pavan Deolasee, Amit Kapila Discussion: https://postgr.es/m/CABOikdN-_858zojYN-2tNcHiVTw-nhxPwoQS4quExeweQfG1Ug@mail.gmail.com https://git.postgresql.org/pg/commitdiff/6f4bdf81529fdaf6744875b0be99ecb9bfb3b7e0

Peter Eisentraut pushed:

• postgresql.conf.sample: Make vertical spacing consistent. https://git.postgresql.org/pg/commitdiff/8673a37c85fef00dd5b9c04197538142bec10542

• Replace run-time error check with assertion. The error message was checking that the structures returned from the parser matched expectations. That's something we usually use assertions for, not a full user-facing error message. So replace that with an assertion (hidden inside lfirst_node()). Reviewed-by: Tom Lane tgl@sss.pgh.pa.us Discussion: https://www.postgresql.org/message-id/flat/452e9df8-ec89-e01b-b64a-8cc6ce830458%40enterprisedb.com https://git.postgresql.org/pg/commitdiff/388e75ad33489b77cfb9a8590a91e9287d8fb960

• Add NO_INSTALL option to pgxs. Apply in libpq_pipeline test makefile, so that the test file is not installed into tmp_install. Reviewed-by: Alvaro Herrera alvherre@alvh.no-ip.org Reviewed-by: Tom Lane tgl@sss.pgh.pa.us Discussion: https://www.postgresql.org/message-id/flat/cb9d16a6-760f-cd44-28d6-b091d5fb6ca7%40enterprisedb.com https://git.postgresql.org/pg/commitdiff/6abc8c2596dbfcb24f9b4d954a1465b8015118c3

• Fix vpath build in libpq_pipeline test. The path needs to be set to refer to the build directory, not the current directory, because that's actually the source directory at that point. fix for 6abc8c2596dbfcb24f9b4d954a1465b8015118c3 https://git.postgresql.org/pg/commitdiff/a717e5c771610cf8607f2423ab3ab6b5d30f44ea

Álvaro Herrera pushed:

• Make detach-partition-concurrently-3 less timing-sensitive. This recently added test has shown to be too sensitive to timing when sending a cancel to a session waiting for a lock. We fix this by running a no-op query in the blocked session immediately after the cancel; this avoids the session that sent the cancel sending another query immediately before the cancel has been reported. Idea by Noah Misch. With that fix, we sometimes see that the cancel error report is shown only relative to the step that is cancelled, instead of together with the step that sends the cancel. To increase the probability that both steps are shown togeter, add a 0.1s sleep to the cancel. In normal conditions this appears sufficient to silence most failures, but we'll see that the slower buildfarm members say about it. Reported-by: Takamichi Osumi osumi.takamichi@fujitsu.com Discussion: https://postgr.es/m/OSBPR01MB4888C4ABA361C7E81094AC66ED269@OSBPR01MB4888.jpnprd01.prod.outlook.com https://git.postgresql.org/pg/commitdiff/5e0b1aeb2dfed4f1eb7ac5154c1573885a70db41

• Make detach-partition-concurrently-4 less timing sensitive. Same as 5e0b1aeb2dfe, for the companion test file. This one seems lower probability (only two failures in a month of runs); I was hardly able to reproduce a failure without a patch, so the fact that I was also unable to reproduce one with it doesn't say anything. We'll have to wait for further buildfarm results to see if we need any further adjustments. Discussion: https://postgr.es/m/20210524090712.GA3771394@rfd.leadboat.com https://git.postgresql.org/pg/commitdiff/eb43bdbf5104c183412aac0fccf8e515e60d9212

Andrew Dunstan pushed:

• Report configured port in MSVC built pg_config. This is a long standing omission, discovered when trying to write code that relied on it. Backpatch to all live branches. https://git.postgresql.org/pg/commitdiff/fb424ae85f6b1e32e545f13902d3ba3429be44df

• fix syntax error. https://git.postgresql.org/pg/commitdiff/d69fcb9caef1ac1f38241645d4fb9f7e0ce02a70

Thomas Munro pushed:

• Fix race condition when sharing tuple descriptors. Parallel query processes that called BlessTupleDesc() for identical tuple descriptors at the same moment could crash. There was code to handle that rare case, but it dereferenced a bogus DSA pointer. Repair. Back-patch to 11, where commit cc5f8136 added support for sharing tuple descriptors in parallel queries. Reported-by: Eric Thinnes e.thinnes@gmx.de Discussion: https://postgr.es/m/99aaa2eb-e194-bf07-c29a-1a76b4f2bcf9%40gmx.de https://git.postgresql.org/pg/commitdiff/b1d6538903504904db44679355ac109aeda01737

Pending Patches

Fabien COELHO and Aleksander Alekseev traded patches to replace the rand48 pseudo-random number generator with something more appropriate to the 64-bit era.

Greg Nancarrow and Pavel Borisov traded patches to fix a parallel worker failed assertion and coredump.

Vigneshwaran C sent in another revision of a patch to add schema level support for PUBLICATIONs.

Hou Zhijie and Amit Langote traded patches to skip partition tuple routing in the case where the partition key is constant.

Dilip Kumar, Tsutomu Yamada, and Kyotaro HORIGUCHI traded patches to fix a race condition in recovery.

Hou Zhijie sent in another revision of a patch to make it possible to parallelize INSERT ... SELECT.

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

Justin Pryzby sent in another revision of a patch to make WAL compression methods pluggable and default to lz4.

Andy Fan sent in a patch to use planner_rt_fetch instead of rt_fetch when root->simple_rte_array.

Ajin Cherian sent in five more revisions of a patch to skip empty transactions for logical replication.

Mark Dilger sent in another revision of a patch to delegate superuser tasks to new security roles.

Hou Zhijie, Bharath Rupireddy, and Tomáš Vondra traded patches to make sure postgres_fdw batching does not use too many parameters.

Bharath Rupireddy sent in a patch to provide TDE nonce size as an initdb option, add TDE nonce bytes to page pd_special structure, and adjust tests to account for configurable TDE nonce size.

Bharath Rupireddy sent in another revision of a patch to disambiguate error messages that use "non-negative."

Antonin Houska sent in a patch to narrow the cases where a concurrent UPDATE restarts heap_lock_tuple(), reducing unneeded calls to same.

Greg Sabino Mullane sent in a patch to speed up pg_checksums in cases where checksum already set by avoiding writing same when it's already set to the value expected.

Michaël Paquier sent in a patch intended to fix a bug that manifested as incorrect snapshots while promoting hot standby node when 2PC is used.

Bharath Rupireddy sent in two more revisions of a patch to check for duplicate options and error out if they're found in CREATE COLLATION.

Andrey V. Lepikhov sent in a patch to teach the optimizer to consider a partition-wise join of a non-partitioned table with each partition of a partitioned table.

Andrey V. Lepikhov sent in another revision of a patch to remove unneeded self-joins via a new equivalence class.

Peter Eisentraut sent in a patch to fix RADIUS error reporting in hba file parsing.

Vigneshwaran C and Bharath Rupireddy traded patches to improve publication error messages.

Tom Lane sent in another revision of a patch to replace pg_depend PIN entries with a fixed range check.

Kyotaro HORIGUCHI sent in two revisions of a patch to change confusing 'bracket' use to clearer wording, and add test cases for trailing garbage of (multi)range types.

Bharath Rupireddy and Hou Zhijie traded patches to make it possible to use parallel inserts in CREATE TABLE AS.

Dilip Kumar sent in a patch to fix a memory leak in decoding speculative insert with toast.

Peter Geoghegan sent in a patch to Generalize VACUUM's INDEX_CLEANUP option in a way that lets users disable the index vacuum bypassing optimization added by commit 5100010e, as well as any similar optimizations that may be added in the future.

Paul Guo sent in another revision of a patch to fsync only the affected files/directories and use copy_file_range() for file copying in pg_rewind.

Takamichi Osumi sent in another revision of a patch to log the plans of long-running queries.

Daniel Gustafsson sent in another revision of a patch to support NSS as a libpq TLS backend.

Etsuro Fujita sent in a patch to fix rescans in asynchronous appends in the PostgreSQL FDW.

Vigneshwaran C sent in a patch to add alias types regpublication and regsubscription.

Fabien COELHO sent in a patch to reduce some duplication in psql's echo code.

Laurenz Albe sent in another revision of a patch to expand the docs on PostgreSQL extension coding and background worker development a little so that key topics like allocation, interrupt handling, exit callbacks, transaction callbacks, PG_TRY()/PG_CATCH(), resource owners, transaction and snapshot state, etc are at least briefly mentioned with a few pointers to where to learn more.

Tom Lane sent in a patch to reduce memory consumption for pending inval messages.

Andreas Karlsson sent in a patch to shrink GISTSTATE.

Yura Sokolov sent in a patch to clear empty space in a page.

Tomáš Vondra sent in a patch to revert part of the COPY FREEZE improvements, namely tweaking heap_multi_insert, and removing most of 39b66a91bd (except for the heap_xlog_multi_insert bit).

Thomas Munro sent in two revisions of a patch to support direct I/O on macOS.

Zurich, Switzerland - June 7th, 2021

Server side rollback at statement level for PostgreSQL

pg_statement_rollback is a PostgreSQL extension to add server side transaction with rollback at statement level like in Oracle or DB2.

Release v1.2 of pg_statement_rollback was released past week. This is a maintenance release to fix portability issues on different PostgreSQL version as well as taking care of parallelism.

See ChangeLog for a complete list of changes.

Links & Credits

pg_statement_rollback is an open project from LzLabs (https://www.lzlabs.com/). Any contribution to build a better tool is welcome. You just have to send your ideas, features requests or patches using the GitHub tools.

Links :

• Documentation: https://github.com/lzlabs/pg_statement_rollback/#readme
• Download: https://github.com/lzlabs/pg_statement_rollback/releases/
• Support: use GitHub report tool at https://github.com/lzlabs/pg_statement_rollback/issues
• Authors: https://github.com/lzlabs/pg_statement_rollback/#authors

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:

• 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:

• Fix error handling in replacement pthread_barrier_init(). Commit 44bf3d50 incorrectly used an errno-style interface when supplying missing pthread functionality (i.e. on macOS), but it should check for and return error numbers directly. https://git.postgresql.org/pg/commitdiff/a40646e30d85e51a76fb620822d4d921b6802157

Amit Kapila pushed:

• pgoutput: Fix memory leak due to RelationSyncEntry.map. Release memory allocated when creating the tuple-conversion map and its component TupleDescs when its owning sync entry is invalidated. TupleDescs must also be freed when no map is deemed necessary, to begin with. Reported-by: Andres Freund Author: Amit Langote Reviewed-by: Takamichi Osumi, Amit Kapila Backpatch-through: 13, where it was introduced Discussion: https://postgr.es/m/MEYP282MB166933B1AB02B4FE56E82453B64D9@MEYP282MB1669.AUSP282.PROD.OUTLOOK.COM https://git.postgresql.org/pg/commitdiff/eb89cb43a0d0e401e71b8e2345b5f5bc8b2755a1

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:

• Revert most of 39b66a91bd. Reverts most of commit 39b66a91bd, which was found to cause significant regression for REFRESH MATERIALIZED VIEW. This means only rows inserted by heap_multi_insert will benefit from the optimization, implemented in commit 7db0cd2145. Reported-by: Masahiko Sawada Discussion: https://postgr.es/m/CAD21AoA%3D%3Df2VSw3c-Cp_y%3DWLKHMKc1D6s7g3YWsCOvgaYPpJcg%40mail.gmail.com https://git.postgresql.org/pg/commitdiff/8e03eb92e9ad54e2f1ed8b5a73617601f6262f81

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:

• 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.

Grenoble - June 4th, 2021

PostgreSQL Global Temporary Tables

pgtt is a PostgreSQL extension to create, manage and use DB2 or Oracle-style Global Temporary Tables. Once created the use is just like with the other RDBMS.

pgtt v2.4 has been released, this version allow use of the extension by non superuser and especially the creation and maintenance of GTT. It also allow compatibility with PostgreSQL v14.

Complete list of changes is available here

Links & Credits

pgtt is an open project. Any contribution to build a better tool is welcome. You just have to send your ideas, features requests or patches using the GitHub tools or directly to gilles@darold.net.

Links :

• Download: https://github.com/darold/pgtt/releases/
• Support: use GitHub report tool at https://github.com/darold/pgtt/issues or contact gilles@darold.net.

About pgtt

Docs & Download at https://github.com/darold/pgtt/

HypoPG is a PostgreSQL extension adding support for hypothetical indexes.

Version 1.3 has been released, and brings the following changes:

New features:

• Add support for hypothetical hash indexes (pg10+)

Links

For more thorough information about this version or the extension, please consult the official documentation.

Database .NET v32 is an innovative, powerful and intuitive multiple database management tool. With it you can easily and intuitive manage your PostgreSQL databases. (Free for non-commercial and a single executable file without installation)

Major New features from version 31.6 to 32.6:

• Added support for PostgreSQL 14 beta 1
• Added support for Asynchronous Processing
• Added the public schema from the default search path for PostgreSQL
• Added support for the hstore and ghstore types of PostgreSQL
• Improved user experience and user interface
• Improved support of high-DPI displays
• Improved Transaction Mode
• Improved Data Export and Import
• Improved Data Browser and Editor
• Redesign Vertical Tabs Mode
• Performance improvements
• Updated to Npgsql.dll 4.1.9
• ...and more

The new version is immediately available for download.

The PostgreSQL Community Code of Conduct Committee is pleased to announce that the Chinese translation of the Code of Conduct Policy updated August 18, 2020 has been posted:

https://www.postgresql.org/about/policies/coc/zh/

The English version of the Policy is at: https://www.postgresql.org/about/policies/coc/

The Committee thanks Wensheng Zhang (张文升) and Yandong Yao (姚延栋) for their work in preparing the translation.

If you are interested in contributing a translation, the process overview is here.

Dear friends,

We would like to announce that PG Day’21 Russia will take place online on 8-9 July!

Submit your talk by 7 June!

We look forward to receiving your submissions on the following topics:

• Interesting use-cases of operating PostgreSQL in high volume systems.
• PostgreSQL internals.
• Migration to PostgreSQL from Oracle, MySQL, DB2, MSSQL.
• PostgreSQL and database training.
• Using PostgreSQL in enterprise systems.
• Tuning and performance optimization of PostgreSQL.
• Extending PostgreSQL's functionality.
• Scaling PostgreSQL.

Register! Participation is free, but we encourage you to register on our Slack channel to receive updates regarding the programme and links to the conference stream.

We missed you greatly and look forward to welcoming you at this online PostgreSQL community event on 8-9 July.

Team PG Day'21 Russia

(Ilya, Roman, Maryana, Valeria)

PostgreSQL Partition Manager (pg_partman) v4.5.1 has been released. This release contains a critical security fix and other important bug fixes, so it is highly recommended to update this extension as soon as possible.

• CVE-2021-33204 - Fixed security issue that could allow arbitrary code execution using SECURITY DEFINER functions. https://nvd.nist.gov/vuln/detail/CVE-2021-33204
• Allow relation options set on the template table to be inherited on the child table. As of PG13 and earlier, relation options set on the parent are not being set on the child tables.
• Fixed several bugs in sub-partitioning when using a mixture of epoch and regular integer partitioning in the same partition set

Additional details can be found in the CHANGELOG.

Links:

Website - https://github.com/pgpartman/pg_partman

Installation - https://github.com/pgpartman/pg_partman#installation

Documentation - https://github.com/pgpartman/pg_partman/blob/master/doc/pg_partman.md

Release Notes - https://github.com/pgpartman/pg_partman/blob/master/CHANGELOG.txt

Crunchy Data - https://www.crunchydata.com

Crunchy Data is proud to support the development and maintenance of pg_partman.

Hello world!

This time we decided to bump the number to 1.0 to switch to a common versioning pattern. WAL-G 1.0 is compatible with its versions v0.2.0+. Also, WAL-G 1.0 can restore WAL-E backups and WALs. Good news for non-PostgreSQL databases: WAL-G is now production-ready for MS SQL, MySQL databases, meanwhile the API can be changed in future releases. MongoDB and Redis support is in the beta stage.

Some interesting features/fixes:

#780 PostgreSQL remote backups #905 Improve compatibility with PostgreSQL 13, introduced WALG_PREFETCH_DIR option #824 New option was added to wal-verify, now it requires an argument timeline|integrity #903 It's now not allowed to create non-permanent delta backups from permanent backups #842 Fix compatibility with S3 implementations that do not support ListObjectsV2 #950 It's now again possible to tell WAL-G to rename .ready files to .done directly #898, #962 WAL prefetch is now slightly faster #858 Catchup-list command to list delta backups taken specifically for catchup #847 Support for Yandex Cloud KMS #817, #833 Multipart retries for GCS storage #873 Support for AzureUSGovernmentCloud, AzureChinaCloud, AzureGermanCloud #899 Add metadata for WAL files #913, #917 New option to delete single backup for PostgreSQL Add Redis backups. Now we have Redis support with backup-fetch, backup-push, backup-list, and delete commands. Add MySQL catchup mode: allow replay binlogs while uploading new ones Add SQLServer single database restore with renaming Huge amount of bug fixes, tests, and improvements for Redis/MongoDB/SQLServer/etc

The new release is available here.

Thanks to all WAL-G users, developers, and the PostgreSQL community in general.

Best regards, WAL-G team.

The PL/R team is excited to announce the release of version 8.4.2

• Added support for transactions in procedures.

• Added support for pg custom type (tuple) arguments in window functions

• Windows builds with R 4.10.0

We would like to thank everyone who contributed!

Dave

Hello,

The PGCon 2021 Unconference is this upcoming Friday, May 28th starting at 10 AM.

Times are based in Ottawa, Canada which is UTC-4 - the schedule is at:

https://www.pgcon.org/events/pgcon_2021/schedule/

This schedule is also available in iCalendar format as well as an XML format suitable for scheduling apps. See the above URL.

This URL helps with additional information about the Unconference:

https://wiki.postgresql.org/wiki/PgConUnconferenceFAQ

To propose a topic, you need to be involved with the first session of the day - the 'Pitches and Scheduling' session.

You can participate over IRC or Zoom.

We'll be on the irc.libera.chat IRC network - the website will have an embedded IRC client if you prefer to use that. Channel details will be available on the day via the website.

At present, the links to the streams go to the home page. They will ready in time for the Unconference.

The original post contained the wrong month and the wrong UTC offset. Sorry about that.

Thank you.

We have just released version 2.0.0 of the Foreign Data Wrapper for GridDB.

This release can work with PostgreSQL 9.6, 10, 11, 12 and 13.
This release improves following item :

• Support UPDATE
• Support LIMIT OFFSET clause pushdown
• Support ORDER BY pushdown
• Support function pushdown in WHERE clause
• Support aggregation pushdown
• Support column_name options
• Refactored tests

The FDW supports following features :

• SELECT, INSERT, UPDATE and DELETE of foreign tables
• Function is push-downed in WHERE clause
• LIMIT and OFFSET are push-downed when having LIMIT clause only or both LIMIT and OFFSET.
• ORDER BY is push-downed.
• Aggregation is push-downed.

This is developed by Toshiba Software Engineering & Technology Center.
Please see the repository for details.

Source repository : https://github.com/pgspider/griddb_fdw

GridDB is KVS and time series database.
Please see the repository for details.

https://github.com/griddb/griddb_nosql

pgSCV is a Prometheus-compatible monitoring agent and metrics exporter for PostgreSQL environment. The goal of the project is to provide a single tool (exporter) for collecting metrics from PostgreSQL and related services.

Features:

• Wide range of builtin metrics about system, PostgreSQL and Pgbouncer services.
• Pull mode, by default all metrics are exposed through /metrics HTTP endpoint.
• Push mode, metrics can be pushed to a HTTP service.
• Local services auto-discovery.
• Remote services support.
• User-defined metrics.

More information on project page and wiki:

• pgSCV on Github
• pgSCV wiki
• Getting started
• Available collectors
• Available metrics

We have just released version 2.0.0 of the Foreign Data Wrapper for SQLite.

This release can work with PostgreSQL 9.6, 10, 11, 12 and 13.
This release improves following item :

• Support JOIN pushdown (LEFT,RIGHT,INNER)
• Support direct modification (UPDATE/DELETE)
• Support pushdown nest functions
• Support pushdown scalar operator ANY/ALL (ARRAY)
• Support pushdown ON CONFLICT DO NOTHING
• Refactored tests

Bug fixings :

• Don't push down lower/upper function
• Fix processing for DATE data type
• Do not prepare SQL statement during EXPLAIN

The FDW supports following features :

• SELECT, INSERT, UPDATE and DELETE of foreign tables
• WHERE clauses is push-downed
• Aggregate is push-downed
• ORDER BY is push-downed
• JOIN is push-downed (LEFT,RIGHT,INNER)
• nest functions is push-downed
• LIMIT and OFFSET are push-downed (*when all tables queried are fdw)
• scalar operator ANY/ALL (ARRAY) are push-downed
• ON CONFLICT DO NOTHING is push-downed
• Transactions

This is developed by Toshiba Software Engineering & Technology Center.
Please see the repository for details.

Source repository : https://github.com/pgspider/sqlite_fdw

Best Regards,
Mototaka Kanematsu

We have just released version 1.0.0 of the Foreign Data Wrapper for InfluxDB.

This release can work with PostgreSQL 9.6, 10, 11, 12 and 13.
This release supports following features :

• Support INSERT/DELETE features
• Support add more functions to pushdown
• Support LIMIT OFFSET clause pushdown
• Support pushdown scalar operator ANY/ALL (ARRAY)
• Refactored tests

The FDW supports following features :

• InfluxDB FDW supports pushed down some aggregate functions: count, stddev, sum, max, min.
• InfluxDB FDW supports INSERT, DELETE statements.
• time and time_text column can used for INSERT, DELETE statements.
• time column can express timestamp with precision down to microseconds.
• time_text column can express timestamp with precision down to nanoseconds.
• WHERE clauses including timestamp, interval and now() functions are pushed down.
• LIMIT...OFFSET clauses are pushed down when there is LIMIT clause only or both LIMIT and OFFSET.

Limitations :

• UPDATE is not supported.
• WITH CHECK OPTION constraints is not supported. Following limitations originate from data model and query language of InfluxDB.
• Result sets have different number of rows depending on specified target list. For example, SELECT field1 FROM t1 and SELECT field2 FROM t1 returns different number of rows if the number of points with field1 and field2 are different in InfluxDB database.
• Timestamp precision may be lost because timestamp resolution of PostgreSQL is microseconds while that of InfluxDB is nanoseconds.
• Conditions like WHERE time + interval '1 day' < now() do not work. Please use WHERE time < now() - interval '1 day'.

This is developed by Toshiba Software Engineering & Technology Center.
Please see the repository for details.

Source repository : https://github.com/pgspider/influxdb_fdw

Crunchy Data is pleased to announce the release of version 4.7 of PGO, the Postgres Operator from Crunchy Data, which automates and simplifies deploying and managing open source Postgres clusters on Kubernetes and other Kubernetes-enabled Platforms. Instructions for getting started with the Postgres Operator can be found here:

https://access.crunchydata.com/documentation/postgres-operator/latest/quickstart/

or get started by running:

kubectl create ns pgo; kubectl apply -f https://raw.githubusercontent.com/CrunchyData/postgres-operator/v4.7.0/installers/kubectl/postgres-operator.yml

PGO 4.7, designed for high availability PostgreSQL workloads on Kubernetes, adds support for more "Day 2" functions, such as PVC resize operations with minimal downtime, and enhancements to the monitoring system with a focus on query performance metrics, backups, and recovery. PGO 4.7 expands its support for GitOps methods and includes additional extensions, including, pg_partman, pg_cron, and TimescaleDB.

Major features of the PGO 4.7 release include:

• Resize PVCs, which uses a rolling update to minimize or eliminate downtime and work with older versions of Kubernetes.
• Store Backups in Google Cloud Storage (GCS), providing an additional blog storage system for storing backups. PGO GCS support also works for multi-cluster Kubernetes + Postgres deployments.
• Clone Databases Across Namespaces, allowing for workloads to move between environments.
• Toggle TLS in Active Clusters so you can add TLS to an existing Postgres cluster.
• New Monitoring features, including query performance analytics, more insights into backups + recovery, and a new alerting dashboard.
• New and Updated Extensions: PGO 4.7 includes PostGIS 3.1, pg_partman, pg_cron, and TimescaleDB.

and more.

For a full list of features, including detailed descriptions of these changes, please see the release notes.

Links

• Project Repo
• Download
• Documentation
• Tutorial
• Release Notes

Crunchy Data is proud to support the development and maintenance of PGO, the Postgres Operator from Crunchy Data.

The pgAdmin Development Team is pleased to announce pgAdmin 4 version 5.3. This release of pgAdmin 4 includes 25 bug fixes and new features. For more details please see the release notes.

pgAdmin is the leading Open Source graphical management tool for PostgreSQL. For more information, please see the website.

Notable changes in this release include:

Features:

• Connect PostgreSQL servers using Kerberos authentication

  This feature provides the user to connect to the database servers using Kerberos authentication. Make sure 'kerberos' must be set as AUTHENTICATION_SOURCES in the config file. Added ‘Kerberos authentication?’ switch control in the server dialog if it is set to True then the user can connect to the database server using Kerberos authentication.

• Auto width of columns by content size in the data output window

  This feature provides the user to set the auto width option of the columns by content size. Added the ‘Resize by data?’ option in the preferences. If set to True then data columns will auto-size to the maximum width of the data in the column as loaded in the first batch. If False, the column will be sized to the widest of the data type or column name.

Bugs/Housekeeping:

• Fixed an issue where data is displayed in the wrong order when executing the query repeatedly.
• Fixed an issue where the wrong SQL is showing for the child partition tables.
• Fixed an issue where CSV download quotes the numeric columns.
• Ensure that pgAdmin should not allow opening external files that are dragged into it.
• Fixed an issue where schema diff does not create DROP DEFAULT statement for columns.
• Fixed an issue where the filter 'Include/Exclude By Selection' not working for null values.
• Added support for the creation of Nested Table and Varying Array Type for Advanced Server.
• Fixed ModuleNotFoundError when running setup.py from outside of the root.
• Fixed an issue where the current debug line is not visible in the 'Dark' theme.
• Fixed an issue where duplicate columns are visible in the browser tree if dependent on multiple sequences.
• Ensure that pgAdmin4 shuts down completely on the Quit command on macOS.

Builds for Windows and macOS are available now, along with a Python Wheel, Docker Container, RPM, DEB Package, and source code tarball from the tarball area.

What is Pgpool-II?

Pgpool-II is a tool to add useful features to PostgreSQL, including:

• connection pooling
• load balancing
• automatic fail over and more.

For more information, please see the website.

Minor releases

Pgpool Global Development Group is pleased to announce the availability of following versions of Pgpool-II:

• 4.2.3
• 4.1.7
• 4.0.14
• 3.7.19
• 3.6.26

Please take a look at release notes.

You can download the source code and RPMs.

PostgreSQL Weekly News - May 23, 2021

PostgreSQL 14 Beta 1 released. Test!

The official IRC channels have moved from Freenode to Libera. Details here.

PostgreSQL Product News

DBD::Pg 3.15.0, a Perl driver for PostgreSQL, released.

pg_back 2.0.1, is a tool that can dump PostgreSQL databases to files, released.

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

Bruce Momjian pushed:

• doc: update PG 14 relnotes from feedback by Tom, Alvaro, Julien. https://git.postgresql.org/pg/commitdiff/07af57dbad589bbef9d7178d9b1cb354412e823f

• doc: PG 14 relnotes adjustments from Fujii Masao. https://git.postgresql.org/pg/commitdiff/fe2fb9ebcae8445fdb3915ecf8402a3a887effc2

• doc: add PG 14 rel item about vacuum_cleanup_index_scale_factor. https://git.postgresql.org/pg/commitdiff/2e7c17837064297f25c427d58154dce8d4287302

• doc: partial completion of XML markup for PG 14 release notes. https://git.postgresql.org/pg/commitdiff/6a5bde7d4f96ef153578eaeb624ae12e48b46e85

• doc: add xreflabel for libpq chapter, needed for PG 14 relnotes. https://git.postgresql.org/pg/commitdiff/1e7d53bd019e9d86ef1013308715622a2e400d3b

• doc: revert 1e7d53bd01 so libpq chapter number is accessable. Fix PG 14 relnotes to use <link> instead of <xref>. This was discussed in commit message 59fa7eb603. https://git.postgresql.org/pg/commitdiff/4f7d1c30966cc02fd5eba2f0d51d1f53be07d457

• doc: change PG 14 relnotes as suggested by Justin Pryzby. https://git.postgresql.org/pg/commitdiff/4f586fe244a296d7c781de3f06c54755f2ae222b

• doc: more XML markup for PG 14 release notes. https://git.postgresql.org/pg/commitdiff/55370f8db96c8416940ad0b05be7a00a9f059a9f

• doc: complete adding XML markup to PG 14 relnotes. https://git.postgresql.org/pg/commitdiff/0cdaa05b40e9f28e5d6d58ccd06fe19f3cd920c9

• Update PG 14 relnotes for vacuum_cost_page_miss. Reported-by: Peter Geoghegan Discussion: https://postgr.es/m/CAH2-WzmgSnDX9WVoxRZxuKeCy2MzLO9Dmo4+go0RzNW0VBdhmw@mail.gmail.com https://git.postgresql.org/pg/commitdiff/8dcae7f0a3d6aba1afad1599ab18d259c417b4ee

• doc: PG 14 relnotes, adjust pg_{read|write}_all_data entry. Reported-by: Stephen Frost Discussion: https://postgr.es/m/20210522232945.GO20766@tamriel.snowman.net https://git.postgresql.org/pg/commitdiff/7ce7d07e1c5fb33ee56bda235ae3d53f162f3bc0

• doc: word-wrap and indent PG 14 relnotes. https://git.postgresql.org/pg/commitdiff/8f73ed6b659464274eb9cc8358588b569960d0be

Etsuro Fujita pushed:

• Doc: Update documentation for asynchronous execution. Add a note of caution on the performance of asynchronous execution by postgres_fdw. Follow-up for commit 27e1f1456. Stephen Frost, a little bit expanded by me. Discussion: https://postgr.es/m/20210506171224.GV20766%40tamriel.snowman.net https://git.postgresql.org/pg/commitdiff/15fcd33e0694428d0567a6796891b759bc91e6f9

Magnus Hagander pushed:

• Fix wording in description of pg_stat_statements.toplevel. Incorrect wording got applied in 7531fcb1fcf. Reported-By: Fujii Masao Discussion: https://postgr.es/m/e5512912-eac9-b163-df2b-e2601ce06d27@oss.nttdata.com https://git.postgresql.org/pg/commitdiff/f9e6d00df029144fd8f4ec70c52b5a1d2444f895

• Remove obsolete reference to winflex download. We used to distribute a binary version of flex for windows on our download site, but it hasn't been working for many years. The "old documentation" referenced was also for versions that have been EOL for many years. So, remove it. Discussion: https://postgr.es/m/CABUevEwXLJpVpab62f7AFXNWQ5=U0kvErCLq4VEsikidLyzSQg@mail.gmail.com https://git.postgresql.org/pg/commitdiff/cff8436f19e1c0c278f1ee96d450507fbd43f9ef

Peter Eisentraut pushed:

• Translation updates. Source-Git-URL: git://git.postgresql.org/git/pgtranslation/messages.git Source-Git-Hash: 9bbd9c3714d0c76daaa806588b1fbf744aa60496 https://git.postgresql.org/pg/commitdiff/6292b83074243db94df89271842bda0877cbc4ce

• Put some psql documentation pieces back into alphabetical order. https://git.postgresql.org/pg/commitdiff/124966c1a35b950210e12048e64533963960febd

Tom Lane pushed:

• Stamp 14beta1. https://git.postgresql.org/pg/commitdiff/e4f9737fac77a5cb03a84d1f4038d300ffd28afd

• Avoid creating testtablespace directories where not wanted. Recently we refactored things so that pg_regress makes the "testtablespace" subdirectory used by the core regression tests, instead of doing that in the makefiles. That had the undesirable side effect of making such a subdirectory in every directory that has "input" or "output" test files. Since these subdirectories remain empty, git doesn't complain about them, but nonetheless they're clutter. To fix, invent an explicit --make-testtablespace-dir switch, so that pg_regress only makes the subdirectory when explicitly told to. Discussion: https://postgr.es/m/2854388.1621284789@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/413c1ef98e0c9c708c4a9a13a838a55b65b16a80

• Clean up cpluspluscheck violation. "typename" is a C++ keyword, so pg_upgrade.h fails to compile in C++. Fortunately, there seems no likely reason for somebody to need to do that. Nonetheless, it's project policy that all .h files should pass cpluspluscheck, so rename the argument to fix that. Oversight in 57c081de0; back-patch as that was. (The policy requiring pg_upgrade.h to pass cpluspluscheck only goes back to v12, but it seems best to keep this code looking the same in all branches.) https://git.postgresql.org/pg/commitdiff/6d59a218c38adf5b993200a804713df4982a0c75

• Avoid detoasting failure after COMMIT inside a plpgsql FOR loop. exec_for_query() normally tries to prefetch a few rows at a time from the query being iterated over, so as to reduce executor entry/exit overhead. Unfortunately this is unsafe if we have COMMIT or ROLLBACK within the loop, because there might be TOAST references in the data that we prefetched but haven't yet examined. Immediately after the COMMIT/ROLLBACK, we have no snapshots in the session, meaning that VACUUM is at liberty to remove recently-deleted TOAST rows. This was originally reported as a case triggering the "no known snapshots" error in init_toast_snapshot(), but even if you miss hitting that, you can get "missing toast chunk", as illustrated by the added isolation test case. To fix, just disable prefetching in non-atomic contexts. Maybe there will be performance complaints prompting us to work harder later, but it's not clear at the moment that this really costs much, and I doubt we'd want to back-patch any complicated fix. In passing, adjust that error message in init_toast_snapshot() to be a little clearer about the likely cause of the problem. Patch by me, based on earlier investigation by Konstantin Knizhnik. Per bug #15990 from Andreas Wicht. Back-patch to v11 where intra-procedure COMMIT was added. Discussion: https://postgr.es/m/15990-eee2ac466b11293d@postgresql.org https://git.postgresql.org/pg/commitdiff/f21fadafaf0fb5ea4c9622d915972651273d62ce

• Restore the portal-level snapshot after procedure COMMIT/ROLLBACK. COMMIT/ROLLBACK necessarily destroys all snapshots within the session. The original implementation of intra-procedure transactions just cavalierly did that, ignoring the fact that this left us executing in a rather different environment than normal. In particular, it turns out that handling of toasted datums depends rather critically on there being an outer ActiveSnapshot: otherwise, when SPI or the core executor pop whatever snapshot they used and return, it's unsafe to dereference any toasted datums that may appear in the query result. It's possible to demonstrate "no known snapshots" and "missing chunk number N for toast value" errors as a result of this oversight. Historically this outer snapshot has been held by the Portal code, and that seems like a good plan to preserve. So add infrastructure to pquery.c to allow re-establishing the Portal-owned snapshot if it's not there anymore, and add enough bookkeeping support that we can tell whether it is or not. We can't, however, just re-establish the Portal snapshot as part of COMMIT/ROLLBACK. As in normal transaction start, acquiring the first snapshot should wait until after SET and LOCK commands. Hence, teach spi.c about doing this at the right time. (Note that this patch doesn't fix the problem for any PLs that try to run intra-procedure transactions without using SPI to execute SQL commands.) This makes SPI's no_snapshots parameter rather a misnomer, so in HEAD, rename that to allow_nonatomic. replication/logical/worker.c also needs some fixes, because it wasn't careful to hold a snapshot open around AFTER trigger execution. That code doesn't use a Portal, which I suspect someday we're gonna have to fix. But for now, just rearrange the order of operations. This includes back-patching the recent addition of finish_estate() to centralize the cleanup logic there. This also back-patches commit 2ecfeda3e into v13, to improve the test coverage for worker.c (it was that test that exposed that worker.c's snapshot management is wrong). Per bug #15990 from Andreas Wicht. Back-patch to v11 where intra-procedure COMMIT was added. Discussion: https://postgr.es/m/15990-eee2ac466b11293d@postgresql.org https://git.postgresql.org/pg/commitdiff/84f5c2908dad81e8622b0406beea580e40bb03ac

• Fix usage of "tableoid" in GENERATED expressions. We consider this supported (though I've got my doubts that it's a good idea, because tableoid is not immutable). However, several code paths failed to fill the field in soon enough, causing such a GENERATED expression to see zero or the wrong value. This occurred when ALTER TABLE adds a new GENERATED column to a table with existing rows, and during regular INSERT or UPDATE on a foreign table with GENERATED columns. Noted during investigation of a report from Vitaly Ustinov. Back-patch to v12 where GENERATED came in. Discussion: https://postgr.es/m/CAM_DEiWR2DPT6U4xb-Ehigozzd3n3G37ZB1+867zbsEVtYoJww@mail.gmail.com https://git.postgresql.org/pg/commitdiff/2b0ee126bbf01cbfd657bd53c94f9284ba903ca2

• Disallow whole-row variables in GENERATED expressions. This was previously allowed, but I think that was just an oversight. It's a clear violation of the rule that a generated column cannot depend on itself or other generated columns. Moreover, because the code was relying on the assumption that no such cross-references exist, it was pretty easy to crash ALTER TABLE and perhaps other places. Even if you managed not to crash, you got quite unstable, implementation-dependent results. Per report from Vitaly Ustinov. Back-patch to v12 where GENERATED came in. Discussion: https://postgr.es/m/CAM_DEiWR2DPT6U4xb-Ehigozzd3n3G37ZB1+867zbsEVtYoJww@mail.gmail.com https://git.postgresql.org/pg/commitdiff/4b10074453d182b5fc11a5667bab2ef8532ff3a6

• Remove plpgsql's special-case code paths for SET/RESET. In the wake of 84f5c2908, it's no longer necessary for plpgsql to handle SET/RESET specially. The point of that was just to avoid taking a new transaction snapshot prematurely, which the regular code path through _SPI_execute_plan() now does just fine (in fact better, since it now does the right thing for LOCK too). Hence, rip out a few lines of code, going back to the old way of treating SET/RESET as a generic SQL command. This essentially reverts all but the test cases from b981275b6. Discussion: https://postgr.es/m/15990-eee2ac466b11293d@postgresql.org https://git.postgresql.org/pg/commitdiff/30168be8f75b95183abccf48f0da7a64a0cfbd9f

• Fix access to no-longer-open relcache entry in logical-rep worker. If we redirected a replicated tuple operation into a partition child table, and then tried to fire AFTER triggers for that event, the relation cache entry for the child table was already closed. This has no visible ill effects as long as the entry is still there and still valid, but an unluckily-timed cache flush could result in a crash or other misbehavior. To fix, postpone the ExecCleanupTupleRouting call (which is what closes the child table) until after we've fired triggers. This requires a bit of refactoring so that the cleanup function can have access to the necessary state. In HEAD, I took the opportunity to simplify some of worker.c's function APIs based on use of the new ApplyExecutionData struct. However, it doesn't seem safe/practical to back-patch that aspect, at least not without a lot of analysis of possible interactions with a04daa97a. In passing, add an Assert to afterTriggerInvokeEvents to catch such cases. This seems worthwhile because we've grown a number of fairly unstructured ways of calling AfterTriggerEndQuery. Back-patch to v13, where worker.c grew the ability to deal with partitioned target tables. Discussion: https://postgr.es/m/3382681.1621381328@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/b39630fd41f25b414d0ea9b30804f4105f2a0aff

• Be more verbose when the postmaster unexpectedly quits. Emit a LOG message when the postmaster stops because of a failure in the startup process. There already is a similar message if we exit for that reason during PM_STARTUP phase, so it seems inconsistent that there was none if the startup process fails later on. Also emit a LOG message when the postmaster stops after a crash because restart_after_crash is disabled. This seems potentially helpful in case DBAs (or developers) forget that that's set. Also, it was the only remaining place where the postmaster would do an abnormal exit without any comment as to why. In passing, remove an unreachable call of ExitPostmaster(0). Discussion: https://postgr.es/m/194914.1621641288@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/bc2a389efb3b52d259cefd53c16cfa00742116f2

• Re-order pg_attribute columns to eliminate some padding space. Now that attcompression is just a char, there's a lot of wasted padding space after it. Move it into the group of char-wide columns to save a net of 4 bytes per pg_attribute entry. While we're at it, swap the order of attstorage and attalign to make for a more logical grouping of these columns. Also re-order actions in related code to match the new field ordering. This patch also fixes one outright bug: equalTupleDescs() failed to compare attcompression. That could, for example, cause relcache reload to fail to adopt a new value following a change. Michael Paquier and Tom Lane, per a gripe from Andres Freund. Discussion: https://postgr.es/m/20210517204803.iyk5wwvwgtjcmc5w@alap3.anarazel.de https://git.postgresql.org/pg/commitdiff/f5024d8d7b04de2f5f4742ab433cc38160354861

David Rowley pushed:

• Fix typo and outdated information in README.barrier. README.barrier didn't seem to get the memo when atomics were added. Fix that. Author: Tatsuo Ishii, David Rowley Discussion: https://postgr.es/m/20210516.211133.2159010194908437625.t-ishii%40sraoss.co.jp Backpatch-through: 9.6, oldest supported release https://git.postgresql.org/pg/commitdiff/2ded19fa3a4dafbae80245710fa371d5163bdad4

• Fix planner's use of Result Cache with unique joins. When the planner considered using a Result Cache node to cache results from the inner side of a Nested Loop Join, it failed to consider that the inner path's parameterization may not be the entire join condition. If the join was marked as inner_unique then we may accidentally put the cache in singlerow mode. This meant that entries would be marked as complete after caching the first row. That was wrong as if only part of the join condition was parameterized then the uniqueness of the unique join was not guaranteed at the Result Cache's level. The uniqueness is only guaranteed after Nested Loop applies the join filter. If subsequent rows were found, this would lead to: ERROR: cache entry already complete This could have been fixed by only putting the cache in singlerow mode if the entire join condition was parameterized. However, Nested Loop will only read its inner side so far as the first matching row when the join is unique, so that might mean we never get an opportunity to mark cache entries as complete. Since non-complete cache entries are useless for subsequent lookups, we just don't bother considering a Result Cache path in this case. In passing, remove the XXX comment that claimed the above ERROR might be better suited to be an Assert. After there being an actual case which triggered it, it seems better to keep it an ERROR. Reported-by: David Christensen Discussion: https://postgr.es/m/CAOxo6X+dy-V58iEPFgst8ahPKEU+38NZzUuc+a7wDBZd4TrHMQ@mail.gmail.com https://git.postgresql.org/pg/commitdiff/9e215378d7fbb7d4615be917917c52f246cc6c61

Michaël Paquier pushed:

• Add --no-toast-compression to pg_dumpall. This is an oversight from bbe0a81d, where the equivalent option exists in pg_dump. This is useful to be able to reset the compression methods cluster-wide when restoring the data based on default_toast_compression. Reviewed-by: Daniel Gustafsson, Tom Lane Discussion: https://postgr.es/m/YKHC+qCJvzCRVCpY@paquier.xyz https://git.postgresql.org/pg/commitdiff/694da1983e9569b2a2f96cd786ead6b8dba31f1d

Fujii Masao pushed:

• Fix issues in pg_stat_wal. 1) Previously there were both pgstat_send_wal() and pgstat_report_wal() in order to send WAL activity to the stats collector. With the former being used by wal writer, the latter by most other processes. They were a bit redundant and so this commit merges them into pgstat_send_wal() to simplify the code. 2) Previously WAL global statistics counters were calculated and then compared with zero-filled buffer in order to determine whether any WAL activity has happened since the last submission. These calculation and comparison were not cheap. This was regularly exercised even in read-only workloads. This commit fixes the issue by making some WAL activity counters directly be checked to determine if there's WAL activity stats to send. 3) Previously pgstat_report_stat() did not check if there's WAL activity stats to send as part of the "Don't expend a clock check if nothing to do" check at the top. It's probably rare to have pending WAL stats without also passing one of the other conditions, but for safely this commit changes pgstat_report_stats() so that it checks also some WAL activity counters at the top. This commit also adds the comments about the design of WAL stats. Reported-by: Andres Freund Author: Masahiro Ikeda Reviewed-by: Kyotaro Horiguchi, Atsushi Torikoshi, Andres Freund, Fujii Masao Discussion: https://postgr.es/m/20210324232224.vrfiij2rxxwqqjjb@alap3.anarazel.de https://git.postgresql.org/pg/commitdiff/d8735b8b4651f5ed50afc472e236a8e6120f07f2

• Make standby promotion reset the recovery pause state to 'not paused'. If a promotion is triggered while recovery is paused, the paused state ends and promotion continues. But previously in that case pg_get_wal_replay_pause_state() returned 'paused' wrongly while a promotion was ongoing. This commit changes a standby promotion so that it marks the recovery pause state as 'not paused' when it's triggered, to fix the issue. Author: Fujii Masao Reviewed-by: Dilip Kumar, Kyotaro Horiguchi Discussion: https://postgr.es/m/f706876c-4894-0ba5-6f4d-79803eeea21b@oss.nttdata.com https://git.postgresql.org/pg/commitdiff/167bd4804995afd654bd97ca9486acbece24377e

Amit Kapila pushed:

• Fix 020_messages.pl test. We were not waiting for a publisher to catch up with the subscriber after creating a subscription. Now, it can happen that apply worker starts replication even after we have disabled the subscription in the test. This will make the test expect that there is no active slot whereas there exists one. Fix this symptom by allowing the publisher to wait for catching up with the subscription. It is not a good idea to ensure if the slot is still active by checking for walsender existence as we release the slot after we clean up the walsender related memory. Fix that by checking the slot status in pg_replication_slots. Also, it is better to avoid repeated enabling/disabling of the subscription. Finally, we make autovacuum off for this test to avoid any empty transaction appearing in the test while consuming changes. Reported-by: as per buildfarm Author: Vignesh C Reviewed-by: Amit Kapila, Michael Paquier Discussion: https://postgr.es/m/CAA4eK1+uW1UGDHDz-HWMHMen76mKP7NJebOTZN4uwbyMjaYVww@mail.gmail.com https://git.postgresql.org/pg/commitdiff/0a442a408b40d2c6710de7e5397cb2e769d8c630

• Fix deadlock for multiple replicating truncates of the same table. While applying the truncate change, the logical apply worker acquires RowExclusiveLock on the relation being truncated. This allowed truncate on the relation at a time by two apply workers which lead to a deadlock. The reason was that one of the workers after updating the pg_class tuple tries to acquire SHARE lock on the relation and started to wait for the second worker which has acquired RowExclusiveLock on the relation. And when the second worker tries to update the pg_class tuple, it starts to wait for the first worker which leads to a deadlock. Fix it by acquiring AccessExclusiveLock on the relation before applying the truncate change as we do for normal truncate operation. Author: Peter Smith, test case by Haiying Tang Reviewed-by: Dilip Kumar, Amit Kapila Backpatch-through: 11 Discussion: https://postgr.es/m/CAHut+PsNm43p0jM+idTvWwiGZPcP0hGrHMPK9TOAkc+a4UpUqw@mail.gmail.com https://git.postgresql.org/pg/commitdiff/6d0eb38557155855539cd007f04736dc3b2ba16f

Dean Rasheed pushed:

• Fix pgbench permute tests. One of the tests for the pgbench permute() function added by 6b258e3d68 fails on some 32-bit platforms, due to variations in the floating point computations in getrand(). The remaining tests give sufficient coverage, so just remove the failing test. Reported by Christoph Berg. Analysis by Thomas Munro and Tom Lane. Based on patch by Fabien Coelho. Discussion: https://postgr.es/m/YKQnUoYV63GRJBDD@msg.df7cb.de https://git.postgresql.org/pg/commitdiff/0f516d039d8023163e82fa51104052306068dd69

Andrew Dunstan pushed:

• Use a more portable way to get the version string in PostgresNode. Older versions of perl on Windows don't like the list form of pipe open, and perlcritic doesn't like the string form of open, so we avoid both with a simpler formulation using qx{}. Per complaint from Amit Kapila. https://git.postgresql.org/pg/commitdiff/8bdd6f563aa2456de602e78991e6a9f61b8ec86d

• Install PostgresVersion.pm. A lamentable oversight on my part meant that when PostgresVersion.pm was added in commit 4c4eaf3d19 provision to install it was not added to the Makefile, so it was not installed along with the other perl modules. https://git.postgresql.org/pg/commitdiff/bdbb2ce7d51e93ca2ec68e25e2fafb271b34e72d

Pending Patches

Yugo Nagata sent in another revision of a patch to implement incrementally materialized views.

Amul Sul sent in another revision of a patch to separate the WAL writing code from StartupXLOG(), implement WAL prohibit state using global barriers, error or Assert before START_CRIT_SECTION for WAL write, and document all this. This is infrastructure for, among other things, ALTER SYSTEM READ {ONLY | WRITE}.

Pavel Stěhule sent in another revision of a patch to implement schema variables.

Bharath Rupireddy sent in another revision of a patch to avoid catalog accesses in slot_store_error_callback and conversion_error_callback.

Amit Langote sent in a patch to reword some comments in pathnodes.h for clarity.

Ranier Vilela sent in another revision of a patch to fix a possible memory corruption in zic.

Bharath Rupireddy sent in three revisions of a patch to tighten up batch_size and fetch_size options against non-numeric values in the PostgreSQL FDW.

Masahiro Ikeda sent in two more revisions of a patch to improve the performance of reporting WAL stats without introducing a new variable.

Hou Zhijie and Amit Langote traded patches to skip partition tuple routing when there is a constant partition key.

Peter Smith and Ajin Cherian traded patches to add support for prepared transactions to built-in logical replication, add prepare API support for streaming transactions, and skip empty transactions for logical replication.

Amit Langote sent in four more revisions of a patch to pgoutput to fix memory management of RelationSyncEntry.map by releasing memory allocated when creating the tuple-conversion map and its component TupleDescs when its owning sync entry is invalidated and freeing TupleDescs when no map is deemed necessary to begin with.

Nitin Jadhav sent in two more revisions of a patch to remove an extra malloc from create_list_bounds(), allocate the PartitionListValue as a single chunk, do the same in create_hash_bounds for PartitionHashBound, allocate datum arrays in bulk to avoid palloc overhead, and pfree intermediate results in create_range_bounds().

Bertrand Drouvot sent in another revision of a patch to keep oldestxid in pgupgrade.

Andrew Dunstan sent in another revision of a patch to implement SQL/JSON functions.

Andrew Dunstan sent in another revision of a patch to implement SQL/JSON JSON_TABLE.

Matthias van de Meent sent in another revision of a patch to improve the usage of line pointer array truncation in heapam.

Heikki Linnakangas sent in a patch to allow specifying pg_waldump --rmgr option multiple times.

Robert Haas, Dilip Kumar, and Kyotaro HORIGUCHI traded patches intended to fix a bug that manifested as a race condition in recovery.

Takashi Menjo sent in another revision of a patch to map WAL segment files on PMEM as WAL buffers.

Justin Pryzby sent in another revision of a patch to implement different compression methods for FPI.

Takamichi Osumi sent in a patch to disallow TRUNCATE on user_catalog_table.

Peter Eisentraut and Álvaro Herrera traded patches to add a NO_INSTALL option to pgxs.

Bharath Rupireddy sent in three more revisions of a patch to disambiguate error messages that use "non-negative".

Daniel Gustafsson sent in two revisions of a patch to extend configure_test_server_for_ssl to add extensions, and add tests for sslinfo.

Mathis Rudolf sent in a patch intended to fix a bug that manifested as an alias collision in REFRESH MATERIALIZED VIEW CONCURRENTLY by adding adds the prefix _pg_internal_ to aliases like 'mv' and 'newdata' in 'refresh_by_match_merge()', which makes it unlikely to cause any collisions with user-created MVs.

Yura Sokolov sent in a patch to add a PortalDrop call to exec_execute_message().

Bharath Rupireddy and Peter Smith traded patches to refactor "mutually exclusive options" error reporting code in parse_subscription_options.

Michaël Paquier sent in another revision of a patch to switch tests of pg_upgrade to use TAP.

Greg Nancarrow sent in another revision of a patch to fix a parallel worker failed assertion and coredump.

Kirill Reshke sent in a patch intended to fix a bug that manifested as slow standby snapshot by using a doubly linked list in KnownAssignedXids.

Paul Guo sent in a patch to fix a pg_rewind failure due to read only file open() error by making it writable.

Alexander Pyhalov sent in a patch to make it possible to push joins with function RTEs to PostgreSQL data sources.

Nitin Jadhav sent in another revision of a patch to support tzh tzm patterns.

Michaël Paquier sent in a patch to force disable of SSL renegotiation in the server.

Ivan Panchenko sent in another revision of a patch to make it possible to trigger actions on login.

Takayuki Tsunakawa sent in another revision of a patch to propagate CTE property flags in the rewriter.

Ashutosh Bapat sent in two revisions of a patch to report new catalog_xmin candidate in LogicalIncreaseXminForSlot().

Michaël Paquier sen in another revision of a patch to add authenticated data to pg_stat_activity.

Bharath Rupireddy sent in another revision of a patch to reword error messages and docs for parallel vacuum.

Hou Zhijie sent in two revisions of a patch intended to fix a bug that manifested as caused FDW batched inserts to fail when batch_size > 65535.

Dmitry Dolgov sent in another revision of a patch to implement index skip scans.

Tomáš Vondra sent in a patch intended to fix a bug that manifested as performance degradation of REFRESH MATERIALIZED VIEW.

Michaël Paquier and Tom Lane traded patches to reduce the memory footprint of the pg_attribute struct.

David Rowley sent in another revision of a patch to speed up NOT IN() with a set of Consts.

Vigneshwaran C sent in another revision of a patch to add tab completion for missing options in PUBLICATION and SUBSCRIPTION commands.