PostgreSQL Weekly News - September 5, 2021

# PostgreSQL Weekly News - September 5, 2021

# PostgreSQL Product News

`pg_dbms_job` 1.1.0, an extension to create, manage and use Oracle-style
DBMS_JOB scheduled jobs,
[released](https://github.com/MigOpsRepos/pg_dbms_job#readme).

dbForge Data Compare for PostgreSQL v3.4
[released](https://www.devart.com/dbforge/postgresql/datacompare/)

pgmoneta 0.5.0, a backup and restore system for PostgreSQL,
[released](https://pgmoneta.github.io///release/announcement/2021/08/26/pgmoneta-0.5.0.html)

`pgspider_ext`, an extension to create a cluster engine for distributed data based
on PostgreSQL foreign data wrappers,
[released](https://github.com/pgspider/pgspider_ext).

psycopg2 3.0.0 beta 1, a Python connector for PostgreSQL,
[released](https://www.psycopg.org/articles/2021/08/30/psycopg-30-beta1-released/).

postgresql-wheel, a Python package containing an entire compiled PostgreSQL
server in a single pip installable file,
[released](https://github.com/michelp/postgresql-wheel)

# PostgreSQL Jobs for September

[https://archives.postgresql.org/pgsql-jobs/2021-09/](https://archives.postgresql.org/pgsql-jobs/2021-09/)

# PostgreSQL in the News

Planet PostgreSQL: [https://planet.postgresql.org/](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(at)fetter(dot)org(dot)

# Applied Patches

Michaël Paquier pushed:

- Add more tab completion support for ALTER TABLE ADD in psql. This includes the
detection of new patterns for various constraint types, with the addition of
USING INDEX for unique indexes of a table on primary keys and unique
constraints. Author: Dagfinn Ilmari Mannsåker Discussion:
[https://postgr.es/m/87bl6ehhpl.fsf@wibble.ilmari.org](https://postgr.es/m/87bl6ehhpl.fsf@wibble.ilmari.org)
[https://git.postgresql.org/pg/commitdiff/d3fa87657860a50643118c682cdbf53f508a3fab](https://git.postgresql.org/pg/commitdiff/d3fa87657860a50643118c682cdbf53f508a3fab)

- Refactor one use of IDENTIFY_SYSTEM in WAL streaming code of pg_basebackup.
0c013e0 has done a large refactoring to unify all the code paths using
replication commands, but forgot one code path doing WAL streaming that checks
the validity of a cluster connecting to with IDENTIFY_SYSTEM. There is a
generic routine able to handle that, so make use of it in this code path.
This impacts pg_receivewal and pg_basebackup. Author: Bharath Rupireddy
Discussion:
[https://postgr.es/m/CALj2ACVKKYUMC8GE72Y7BP9g1batrrq3sEwUh+1_i2krWZC_2Q@mail.gmail.com](https://postgr.es/m/CALj2ACVKKYUMC8GE72Y7BP9g1batrrq3sEwUh+1_i2krWZC_2Q@mail.gmail.com)
[https://git.postgresql.org/pg/commitdiff/99709c9b908eba99ecd787c4dc757f71edd98d87](https://git.postgresql.org/pg/commitdiff/99709c9b908eba99ecd787c4dc757f71edd98d87)

- Add tab completion for data types after ALTER TABLE ADD [COLUMN] in psql. This
allows finding data types that can be used for the creation of a new column,
completing d3fa876. Author: Dagfinn Ilmari Mannsåker Discussion:
[https://postgr.es/m/87h7f7uk6s.fsf@wibble.ilmari.org](https://postgr.es/m/87h7f7uk6s.fsf@wibble.ilmari.org)
[https://git.postgresql.org/pg/commitdiff/f2bbadce6b5052337a11a33ea6bd8d8aebe2610a](https://git.postgresql.org/pg/commitdiff/f2bbadce6b5052337a11a33ea6bd8d8aebe2610a)

- Add PostgresNode::command_fails_like(). This is useful to test for a command
failure with some default connection parameters associated to a node, in
combination with checks on error patterns expected. This routine will be used
by an upcoming future patch, but could be also plugged into some of the
existing tests. Extracted from a larger patch by the same author. Author:
Ronan Dunklau Discussion:
[https://postgr.es/m/5742739.ga3mSNWIix@aivenronan](https://postgr.es/m/5742739.ga3mSNWIix@aivenronan)
[https://git.postgresql.org/pg/commitdiff/de1d4fef71cb00370aa5f794fe05355c1d063aea](https://git.postgresql.org/pg/commitdiff/de1d4fef71cb00370aa5f794fe05355c1d063aea)

- Refactor one conversion of SQLSTATE to string in elog.c. unpack_sql_state()
has been introduced in d46bc44 to refactor the unpacking of a SQLSTATE into a
string, but it forgot one code path when sending error reports to clients that
could make use of it. This changes the code to also use unpack_sql_state()
there, simplifying a bit the code. Author: Peter Smith Discussion:
[https://postgr.es/m/CAHut+PuYituuD1-VVZUNcmCQuc3ZzZMPoO57POgm8tnXOkwJAA@mail.gmail.com](https://postgr.es/m/CAHut+PuYituuD1-VVZUNcmCQuc3ZzZMPoO57POgm8tnXOkwJAA@mail.gmail.com)
[https://git.postgresql.org/pg/commitdiff/c4f7a6b87ff350200f4b3afb9fe05e2899161070](https://git.postgresql.org/pg/commitdiff/c4f7a6b87ff350200f4b3afb9fe05e2899161070)

- doc: Replace some uses of "which" by "that" in parallel.sgml. This makes the
documentation more accurate grammatically. Author: Elena Indrupskaya
Discussion:
[https://postgr.es/m/1c994b3d-951e-59bb-1ac2-7b9221c0e4cf@postgrespro.ru](https://postgr.es/m/1c994b3d-951e-59bb-1ac2-7b9221c0e4cf@postgrespro.ru)
Backpatch-through: 9.6
[https://git.postgresql.org/pg/commitdiff/163074ea84efec6ffa4813db43cc956ac5d12565](https://git.postgresql.org/pg/commitdiff/163074ea84efec6ffa4813db43cc956ac5d12565)

Amit Kapila pushed:

- Refactor sharedfileset.c to separate out fileset implementation. Move fileset
related implementation out of sharedfileset.c to allow its usage by backends
that don't want to share filesets among different processes. After this split,
fileset infrastructure is used by both sharedfileset.c and worker.c for the
named temporary files that survive across transactions. Author: Dilip Kumar,
based on suggestion by Andres Freund Reviewed-by: Hou Zhijie, Masahiko Sawada,
Amit Kapila Discussion:
[https://postgr.es/m/E1mCC6U-0004Ik-Fs@gemulon.postgresql.org](https://postgr.es/m/E1mCC6U-0004Ik-Fs@gemulon.postgresql.org)
[https://git.postgresql.org/pg/commitdiff/dcac5e7ac157964f71f15d81c7429130c69c3f9b](https://git.postgresql.org/pg/commitdiff/dcac5e7ac157964f71f15d81c7429130c69c3f9b)

- Fix incorrect error code in StartupReplicationOrigin().
ERRCODE_CONFIGURATION_LIMIT_EXCEEDED was used for checksum failure, use
ERRCODE_DATA_CORRUPTED instead. Reported-by: Tatsuhito Kasahara Author:
Tatsuhito Kasahara Backpatch-through: 9.6, where it was introduced Discussion:
[https://postgr.es/m/CAP0=ZVLHtYffs8SOWcFJWrBGoRzT9QQbk+_aP+E5AHLNXiOorA@mail.gmail.com](https://postgr.es/m/CAP0=ZVLHtYffs8SOWcFJWrBGoRzT9QQbk+_aP+E5AHLNXiOorA@mail.gmail.com)
[https://git.postgresql.org/pg/commitdiff/bad6cef32ca2dd60c8be18f95a7157ced189ec99](https://git.postgresql.org/pg/commitdiff/bad6cef32ca2dd60c8be18f95a7157ced189ec99)

- Fix the random test failure in 001_rep_changes. The check to test whether the
subscription workers were restarting after a change in the subscription was
failing. The reason was that the test was assuming the walsender started
before it reaches the 'streaming' state and the walsender was exiting due to
an error before that. Now, the walsender was erroring out before reaching the
'streaming' state because it tries to acquire the slot before the previous
walsender has exited. In passing, improve the die messages so that it is
easier to investigate the failures in the future if any. Reported-by: Michael
Paquier, as per buildfarm Author: Ajin Cherian Reviewed-by: Masahiko Sawada,
Amit Kapila Backpatch-through: 10, where this test was introduced Discussion:
[https://postgr.es/m/YRnhFxa9bo73wfpV@paquier.xyz](https://postgr.es/m/YRnhFxa9bo73wfpV@paquier.xyz)
[https://git.postgresql.org/pg/commitdiff/8d0138ef51b51ba3e419cf49069eb72a15f74706](https://git.postgresql.org/pg/commitdiff/8d0138ef51b51ba3e419cf49069eb72a15f74706)

- Optimize fileset usage in apply worker. Use one fileset for the entire worker
lifetime instead of using separate filesets for each streaming transaction.
Now, the changes/subxacts files for every streaming transaction will be
created under the same fileset and the files will be deleted after the
transaction is completed. This patch extends the BufFileOpenFileSet and
BufFileDeleteFileSet APIs to allow users to specify whether to give an error
on missing files. Author: Dilip Kumar, based on suggestion by Thomas Munro
Reviewed-by: Hou Zhijie, Masahiko Sawada, Amit Kapila Discussion:
[https://postgr.es/m/E1mCC6U-0004Ik-Fs@gemulon.postgresql.org](https://postgr.es/m/E1mCC6U-0004Ik-Fs@gemulon.postgresql.org)
[https://git.postgresql.org/pg/commitdiff/31c389d8de915b705ee06c7a6e9246e20f36b9dc](https://git.postgresql.org/pg/commitdiff/31c389d8de915b705ee06c7a6e9246e20f36b9dc)

Fujii Masao pushed:

- pgbench: Avoid unnecessary measurement of connection delays. Commit 547f04e734
changed pgbench so that it used the measurement result of connection delays in
its benchmark report only when -C/--connect option is specified. But
previously those delays were unnecessarily measured even when that option is
not specified. Which was a waste of cycles. This commit improves pgbench so
that it avoids such unnecessary measurement. Back-patch to v14 where commit
547f04e734 first appeared. Author: Yugo Nagata Reviewed-by: Fabien COELHO,
Asif Rehman, Fujii Masao Discussion:
[https://postgr.es/m/20210614151155.a393bc7d8fed183e38c9f52a@sraoss.co.jp](https://postgr.es/m/20210614151155.a393bc7d8fed183e38c9f52a@sraoss.co.jp)
[https://git.postgresql.org/pg/commitdiff/bfd4567b88496bf9669658e5ab381b296dd9ffe1](https://git.postgresql.org/pg/commitdiff/bfd4567b88496bf9669658e5ab381b296dd9ffe1)

- pgbench: Fix bug in measurement of disconnection delays. When -C/--connect
option is specified, pgbench establishes and closes the connection for each
transaction. In this case pgbench needs to measure the times taken for all
those connections and disconnections, to include the average connection time
in the benchmark result. But previously pgbench could not measure those
disconnection delays. To fix the bug, this commit makes pgbench measure the
disconnection delays whenever the connection is closed at the end of
transaction, if -C/--connect option is specified. Back-patch to v14. Per
discussion, we concluded not to back-patch to v13 or before because changing
that behavior in stable branches would surprise users rather than providing
benefits. Author: Yugo Nagata Reviewed-by: Fabien COELHO, Tatsuo Ishii, Asif
Rehman, Fujii Masao Discussion:
[https://postgr.es/m/20210614151155.a393bc7d8fed183e38c9f52a@sraoss.co.jp](https://postgr.es/m/20210614151155.a393bc7d8fed183e38c9f52a@sraoss.co.jp)
[https://git.postgresql.org/pg/commitdiff/4dc528bfa7dacee1cfbe2ec59b25039616268b69](https://git.postgresql.org/pg/commitdiff/4dc528bfa7dacee1cfbe2ec59b25039616268b69)

- Improve tab-completion for CREATE PUBLICATION. Author: Peter Smith
Reviewed-by: Vignesh C Discussion:
[https://postgr.es/m/CAHut+Ps-vkmnWAShWSRVCB3gx8aM=bFoDqWgBNTzofK0q1LpwA@mail.gmail.com](https://postgr.es/m/CAHut+Ps-vkmnWAShWSRVCB3gx8aM=bFoDqWgBNTzofK0q1LpwA@mail.gmail.com)
[https://git.postgresql.org/pg/commitdiff/b0c066297bdac901a13a5a900a197697a026b357](https://git.postgresql.org/pg/commitdiff/b0c066297bdac901a13a5a900a197697a026b357)

- Enhance pg_stat_reset_single_table_counters function. This commit allows
pg_stat_reset_single_table_counters() to reset statistics for a single
relation shared across all databases in the cluster to zero. Bump catalog
version. Author: B Sadhu Prasad Patro Reviewed-by: Mahendra Singh Thalor,
Himanshu Upadhyaya, Dilip Kumar, Fujii Masao Discussion:
[https://postgr.es/m/CAFF0-CGy7EHeF=AqqkGMF85cySPQBgDcvNk73G2O0vL94O5U5A@mail.gmail.com](https://postgr.es/m/CAFF0-CGy7EHeF=AqqkGMF85cySPQBgDcvNk73G2O0vL94O5U5A@mail.gmail.com)
[https://git.postgresql.org/pg/commitdiff/e04267844a9bbf97c2e85c919b84dfe498ab0302](https://git.postgresql.org/pg/commitdiff/e04267844a9bbf97c2e85c919b84dfe498ab0302)

Álvaro Herrera pushed:

- psql: Fix name quoting on extended statistics. Per our message style
guidelines, for human consumption we quote qualified names as a whole rather
than each part separately; but commits bc085205c8a4 introduced a deviation for
extended statistics and a4d75c86bf15 copied it. I don't agree with this
policy applying to names shown by psql, but that's a poor reason to deviate
from the practice only in two obscure corners, so make said corners use the
same style as everywhere else. Backpatch to 14. The first of these is older,
but I'm not sure we want to destabilize the psql output in the older branches
for such a small thing. Discussion:
[https://postgr.es/m/20210828181618.GS26465@telsasoft.com](https://postgr.es/m/20210828181618.GS26465@telsasoft.com)
[https://git.postgresql.org/pg/commitdiff/a397109114c8569e21ed3c858a16143de28a1897](https://git.postgresql.org/pg/commitdiff/a397109114c8569e21ed3c858a16143de28a1897)

- Report tuple address in data-corruption error message. Most data-corruption
reports mention the location of the problem, but this one failed to. Add it.
Backpatch all the way back. In 12 and older, also assign the
ERRCODE_DATA_CORRUPTED error code as was done in commit fd6ec93bf890 for 13
and later. Discussion:
[https://postgr.es/m/202108191637.oqyzrdtnheir@alvherre.pgsql](https://postgr.es/m/202108191637.oqyzrdtnheir@alvherre.pgsql)
[https://git.postgresql.org/pg/commitdiff/961dd7565726a507d4551f7ea54ad888fc6ee93a](https://git.postgresql.org/pg/commitdiff/961dd7565726a507d4551f7ea54ad888fc6ee93a)

- Revert "Avoid creating archive status ".ready" files too early". This reverts
commit 515e3d84a0b5 and equivalent commits in back branches. This solution to
the problem has a number of problems, so we'll try again with a different
approach. Per note from Andres Freund Discussion:
[https://postgr.es/m/20210831042949.52eqp5xwbxgrfank@alap3.anarazel.de](https://postgr.es/m/20210831042949.52eqp5xwbxgrfank@alap3.anarazel.de)
[https://git.postgresql.org/pg/commitdiff/96b665083eb72570e226cf2d25c960b3acc62040](https://git.postgresql.org/pg/commitdiff/96b665083eb72570e226cf2d25c960b3acc62040)

Daniel Gustafsson pushed:

- Prohibit map and grep in void context. map and grep are not intended to be
used as mutators, iterating with side-effects should be done with for or
foreach loops. This fixes the one occurrence of the pattern, and bumps the
perlcritic policy to severity 5 for the map and grep policies. Author:
Dagfinn Ilmari Mannsåker <ilmari(at)ilmari(dot)org> Reviewed-by: Michael Paquier
<michael(at)paquier(dot)xyz> Reviewed-by: Andrew Dunstan <andrew(at)dunslane(dot)net>
Reviewed-by: Julien Rouhaud <rjuju123(at)gmail(dot)com> Discussion:
[https://postgr.es/m/87fsvzhhc4.fsf@wibble.ilmari.org](https://postgr.es/m/87fsvzhhc4.fsf@wibble.ilmari.org)
[https://git.postgresql.org/pg/commitdiff/bb466c6b0992a1a21c03239a7b0a87ebadd3bee1](https://git.postgresql.org/pg/commitdiff/bb466c6b0992a1a21c03239a7b0a87ebadd3bee1)

- Remove superfluous variable assignment. Commit a4205fa00 moved setting conn to
NULL directly after the call to PQfinish, but the original conn = NULL;
remained a few lines down. Fix by removing the superfluous assignment.
Author: Bharath Rupireddy <bharath(dot)rupireddyforpostgres(at)gmail(dot)com> Discussion:
[https://postgr.es/m/CALj2ACVRiNvMDHYQGiRrGs2Z9dOydfLh2MymEk9i8CSn23UtCg@mail.gmail.com](https://postgr.es/m/CALj2ACVRiNvMDHYQGiRrGs2Z9dOydfLh2MymEk9i8CSn23UtCg@mail.gmail.com)
[https://git.postgresql.org/pg/commitdiff/ba1b763102b89bca2711e921cf3083d8487b8c96](https://git.postgresql.org/pg/commitdiff/ba1b763102b89bca2711e921cf3083d8487b8c96)

Tom Lane pushed:

- Fix missed lock acquisition while inlining new-style SQL functions. When
starting to use a query parsetree loaded from the catalogs, we must begin by
applying AcquireRewriteLocks(), to obtain the same relation locks that the
parser would have gotten if the query were entered interactively, and to do
some other cleanup such as dealing with later-dropped columns. New-style SQL
functions are just as subject to this rule as other stored parsetrees;
however, of the places dealing with such functions, only init_sql_fcache had
gotten the memo. In particular, if we successfully inlined a new-style
set-returning SQL function that contained any relation references, we'd either
get an assertion failure or attempt to use those relation(s) sans locks. I
also added AcquireRewriteLocks calls to fmgr_sql_validator and
print_function_sqlbody. Desultory experiments didn't demonstrate any failures
in those, but I suspect that I just didn't try hard enough. Certainly we don't
expect nearby code paths to operate without locks. On the same logic of
it-ought-to-have-the-same-effects-as-the-old-code, call pg_rewrite_query() in
fmgr_sql_validator, too. It's possible that neither code path there needs to
bother with rewriting, but doing the analysis to prove that is beyond my goals
for today. Per bug #17161 from Alexander Lakhin. Discussion:
[https://postgr.es/m/17161-048a1cdff8422800@postgresql.org](https://postgr.es/m/17161-048a1cdff8422800@postgresql.org)
[https://git.postgresql.org/pg/commitdiff/589be6f6c732a20e2bcaa02560de464ebbd48af2](https://git.postgresql.org/pg/commitdiff/589be6f6c732a20e2bcaa02560de464ebbd48af2)

- Cache the results of format_type() queries in pg_dump. There's long been a
"TODO: there might be some value in caching the results" annotation on
pg_dump's getFormattedTypeName function; but we hadn't gotten around to
checking what it was costing us to repetitively look up type names. It turns
out that when dumping the current regression database, about 10% of the total
number of queries issued are duplicative format_type() queries. However,
Hubert Depesz Lubaczewski reported a not-unusual case where these account for
over half of the queries issued by pg_dump. Individually these queries aren't
expensive, but when network lag is a factor, they add up to a problem. We can
very easily add some caching to getFormattedTypeName to solve it. Since this
is such a simple fix and can have a visible performance benefit, back-patch to
all supported branches. Discussion:
[https://postgr.es/m/20210826084430.GA26282@depesz.com](https://postgr.es/m/20210826084430.GA26282@depesz.com)
[https://git.postgresql.org/pg/commitdiff/6c450a861f1a928f44c9ae80814ed9a91927c25a](https://git.postgresql.org/pg/commitdiff/6c450a861f1a928f44c9ae80814ed9a91927c25a)

- In pg_dump, avoid doing per-table queries for RLS policies. For no
particularly good reason, getPolicies() queried pg_policy separately for each
table. We can collect all the policies in a single query instead, and attach
them to the correct TableInfo objects using findTableByOid() lookups. On the
regression database, this reduces the number of queries substantially, and
provides a visible savings even when running against a local server. Per
complaint from Hubert Depesz Lubaczewski. Since this is such a simple fix and
can have a visible performance benefit, back-patch to all supported branches.
Discussion:
[https://postgr.es/m/20210826084430.GA26282@depesz.com](https://postgr.es/m/20210826084430.GA26282@depesz.com)
[https://git.postgresql.org/pg/commitdiff/bd3611db5a6f3726094872f59feab426374d2c46](https://git.postgresql.org/pg/commitdiff/bd3611db5a6f3726094872f59feab426374d2c46)

- Refactor postgresImportForeignSchema to avoid code duplication. Avoid
repeating fragments of the query we're building, along the same lines as
recent cleanup in pg_dump. I got annoyed about this because aa769f80e broke
my pending patch to change postgres_fdw's collation handling, due to each of
us having incompletely done this same refactoring. Let's finish that job in
hopes of having a more stable base.
[https://git.postgresql.org/pg/commitdiff/2dc53fe2a77d8d5f22c656fdf6590198e358a996](https://git.postgresql.org/pg/commitdiff/2dc53fe2a77d8d5f22c656fdf6590198e358a996)

- Doc: clarify how triggers relate to transactions. Laurenz Albe, per gripe from
Nathan Long. Discussion:
[https://postgr.es/m/161953360822.695.15805897835151971142@wrigleys.postgresql.org](https://postgr.es/m/161953360822.695.15805897835151971142@wrigleys.postgresql.org)
[https://git.postgresql.org/pg/commitdiff/469150a240dd79acbe7d86cb5df869d95f4d6d2d](https://git.postgresql.org/pg/commitdiff/469150a240dd79acbe7d86cb5df869d95f4d6d2d)

- Fix float4/float8 hash functions to produce uniform results for NaNs. The IEEE
754 standard allows a wide variety of bit patterns for NaNs, of which at least
two ("NaN" and "-NaN") are pretty easy to produce from SQL on most machines.
This is problematic because our btree comparison functions deem all NaNs to be
equal, but our float hash functions know nothing about NaNs and will happily
produce varying hash codes for them. That causes unexpected results from
queries that hash a column containing different NaN values. It could also
produce unexpected lookup failures when using a hash index on a float column,
i.e. "WHERE x = 'NaN'" will not find all the rows it should. To fix,
special-case NaN in the float hash functions, not too much unlike the existing
special case that forces zero and minus zero to hash the same. I arranged for
the most vanilla sort of NaN (that coming from the C99 NAN constant) to still
have the same hash code as before, to reduce the risk to existing hash
indexes. I dithered about whether to back-patch this into stable branches,
but ultimately decided to do so. It's a clear improvement for queries that
hash internally. If there is anybody who has -NaN in a hash index, they'd be
well advised to re-index after applying this patch ... but the misbehavior if
they don't will not be much worse than the misbehavior they had before. Per
bug #17172 from Ma Liangzhu. Discussion:
[https://postgr.es/m/17172-7505bea9e04e230f@postgresql.org](https://postgr.es/m/17172-7505bea9e04e230f@postgresql.org)
[https://git.postgresql.org/pg/commitdiff/ce773f230d9b5bb2e0dd23fec4e5462fd99487fe](https://git.postgresql.org/pg/commitdiff/ce773f230d9b5bb2e0dd23fec4e5462fd99487fe)

- In count_usable_fds(), duplicate stderr not stdin. We had a complaint that the
postmaster fails to start if the invoking program closes stdin. That happens
because count_usable_fds expects to be able to dup(0), and if it can't, we
conclude there are no free FDs and go belly-up. So far as I can find, though,
there is no other place in the server that touches stdin, and it's not
unreasonable to expect that a daemon wouldn't use that file. As a simple
improvement, let's dup FD 2 (stderr) instead. Unlike stdin, it `*is*`
reasonable for us to expect that stderr be open; even if we are configured not
to touch it, common libraries such as libc might try to write error messages
there. Per gripe from Mario Emmenlauer. Given the lack of previous
complaints, I'm not excited about pushing this into stable branches, but it
seems OK to squeeze it into v14. Discussion:
[https://postgr.es/m/48bafc63-c30f-3962-2ded-f2e985d93e86@emmenlauer.de](https://postgr.es/m/48bafc63-c30f-3962-2ded-f2e985d93e86@emmenlauer.de)
[https://git.postgresql.org/pg/commitdiff/c95ede41b8d47b21d58702fbc519e720f41fdaf1](https://git.postgresql.org/pg/commitdiff/c95ede41b8d47b21d58702fbc519e720f41fdaf1)

- Fix portability issue in tests from commit ce773f230. Modern POSIX seems to
require strtod() to accept "-NaN", but there's nothing about NaN in SUSv2, and
some of our oldest buildfarm members don't like it. Let's try writing it as
-'NaN' instead; that seems to produce the same result, at least on Intel
hardware. Per buildfarm.
[https://git.postgresql.org/pg/commitdiff/fd549145d5d9fba3367cbf7e3d4fc7cb3562feb0](https://git.postgresql.org/pg/commitdiff/fd549145d5d9fba3367cbf7e3d4fc7cb3562feb0)

- Disallow creating an ICU collation if the DB encoding won't support it.
Previously this was allowed, but the collation effectively vanished into the
ether because of the way lookup_collation() works: you could not use the
collation, nor even drop it. Seems better to give an error up front than to
leave the user wondering why it doesn't work. (Because this test is in
DefineCollation not CreateCollation, it does not prevent
pg_import_system_collations from creating ICU collations, regardless of the
initially-chosen encoding.) Per bug #17170 from Andrew Bille. Back-patch to
v10 where ICU support was added. Discussion:
[https://postgr.es/m/17170-95845cf3f0a9c36d@postgresql.org](https://postgr.es/m/17170-95845cf3f0a9c36d@postgresql.org)
[https://git.postgresql.org/pg/commitdiff/db2760a84191c329c0cdfaa1dae048c32b0c1752](https://git.postgresql.org/pg/commitdiff/db2760a84191c329c0cdfaa1dae048c32b0c1752)

- Remove arbitrary MAXPGPATH limit on command lengths in pg_ctl. Replace
fixed-length command buffers with psprintf() calls. We didn't have anything
as convenient as psprintf() when this code was written, but now that we do,
there's little reason for the limitation to stand. Removing it eliminates
some corner cases where (for example) starting the postmaster with a whole lot
of options fails. Most individual file names that pg_ctl deals with are still
restricted to MAXPGPATH, but we've seldom had complaints about that limitation
so long as it only applies to one filename. Back-patch to all supported
branches. Phil Krylov Discussion:
[https://postgr.es/m/567e199c6b97ee19deee600311515b86@krylov.eu](https://postgr.es/m/567e199c6b97ee19deee600311515b86@krylov.eu)
[https://git.postgresql.org/pg/commitdiff/87ad491472d6f8620d83ec9db4f515ce303052ac](https://git.postgresql.org/pg/commitdiff/87ad491472d6f8620d83ec9db4f515ce303052ac)

- Minor improvements for psql help output. Fix alphabetization of the output of
"\?", and improve one description. Update PageOutput counts where needed,
fixing breakage from previous patches. Haiying Tang (PageOutput fixes by me)
Discussion:
[https://postgr.es/m/OS0PR01MB61136018064660F095CB57A8FB129@OS0PR01MB6113.jpnprd01.prod.outlook.com](https://postgr.es/m/OS0PR01MB61136018064660F095CB57A8FB129@OS0PR01MB6113.jpnprd01.prod.outlook.com)
[https://git.postgresql.org/pg/commitdiff/ac5ea660996ecbbfbe78b881a581132a95d93d26](https://git.postgresql.org/pg/commitdiff/ac5ea660996ecbbfbe78b881a581132a95d93d26)

- Further portability tweaks for float4/float8 hash functions. Attempting to
make hashfloat4() look as much as possible like hashfloat8(), I'd figured I
could replace NaNs with get_float4_nan() before widening to float8. However,
results from protosciurus and topminnow show that on some platforms that
produces a different bit-pattern from get_float8_nan(), breaking the intent of
ce773f230. Rearrange so that we use the result of get_float8_nan() for all NaN
cases. As before, back-patch.
[https://git.postgresql.org/pg/commitdiff/b30cc0fd6d5d96c63037824c286cec561e092b6f](https://git.postgresql.org/pg/commitdiff/b30cc0fd6d5d96c63037824c286cec561e092b6f)

Tomáš Vondra pushed:

- Fix lookup error in extended stats ownership check. When an ownership check on
extended statistics object failed, the code was calling aclcheck_error_type to
report the failure, which is clearly wrong, resulting in cache lookup errors.
Fix by calling aclcheck_error. This issue exists since the introduction of
extended statistics, so backpatch all the way back to PostgreSQL 10. It went
unnoticed because there were no tests triggering the error, so add one.
Reported-by: Mark Dilger Backpatch-through: 10, where extended stats were
introduced Discussion:
[https://postgr.es/m/1F238937-7CC2-4703-A1B1-6DC225B8978A%40enterprisedb.com](https://postgr.es/m/1F238937-7CC2-4703-A1B1-6DC225B8978A%40enterprisedb.com)
[https://git.postgresql.org/pg/commitdiff/5be8ce82e84f8f3604916c06668990c524f3856d](https://git.postgresql.org/pg/commitdiff/5be8ce82e84f8f3604916c06668990c524f3856d)

- Rename the role in stats_ext to have regress_ prefix. Commit 5be8ce82e8 added
a new role to the stats_ext regression suite, but the role name did not start
with regress_ causing failures when running with
ENFORCE_REGRESSION_TEST_NAME_RESTRICTIONS. Fixed by renaming the role to start
with the expected regress_ prefix. Backpatch-through: 10, same as the new
regression test Discussion:
[https://postgr.es/m/1F238937-7CC2-4703-A1B1-6DC225B8978A%40enterprisedb.com](https://postgr.es/m/1F238937-7CC2-4703-A1B1-6DC225B8978A%40enterprisedb.com)
[https://git.postgresql.org/pg/commitdiff/628bc9d13be8c55492aef122c25dbba3b036366d](https://git.postgresql.org/pg/commitdiff/628bc9d13be8c55492aef122c25dbba3b036366d)

- Don't print extra parens around expressions in extended stats. The code
printing expressions for extended statistics doubled the parens, producing
results like ((a+1)), which is unnecessary and not consistent with how we
print expressions elsewhere. Fixed by tweaking the code to produce just a
single set of parens. Reported by Mark Dilger, fix by me. Backpatch to 14,
where support for extended statistics on expressions was added. Reported-by:
Mark Dilger Discussion:
[https://postgr.es/m/20210122040101.GF27167%40telsasoft.com](https://postgr.es/m/20210122040101.GF27167%40telsasoft.com)
[https://git.postgresql.org/pg/commitdiff/13380e1476490932c7b15530ead1f649a16e1125](https://git.postgresql.org/pg/commitdiff/13380e1476490932c7b15530ead1f649a16e1125)

- Identify simple column references in extended statistics. Until now, when
defining extended statistics, everything except a plain column reference was
treated as complex expression. So for example "a" was a column reference, but
"(a)" would be an expression. In most cases this does not matter much, but
there were a couple strange consequences. For example CREATE STATISTICS s
ON a FROM t; would fail, because extended stats require at least two columns.
But CREATE STATISTICS s ON (a) FROM t; would succeed, because that
requirement does not apply to expressions. Moreover, that statistics object is
useless - the optimizer will always use the regular statistics collected for
attribute "a". So do a bit more work to identify those expressions
referencing a single column, and translate them to a simple column reference.
Backpatch to 14, where support for extended statistics on expressions was
introduced. Reported-by: Justin Pryzby Backpatch-through: 14 Discussion:
[https://postgr.es/m/20210816013255.GS10479%40telsasoft.com](https://postgr.es/m/20210816013255.GS10479%40telsasoft.com)
[https://git.postgresql.org/pg/commitdiff/537ca68dbb2463f7b1c44e9466b8fbdd7505b2e1](https://git.postgresql.org/pg/commitdiff/537ca68dbb2463f7b1c44e9466b8fbdd7505b2e1)

John Naylor pushed:

- Mark the timestamptz variant of date_bin() as stable. Previously, it was
immutable by lack of marking. This is not correct, since the time zone could
change. Bump catversion Discussion:
[https://www.postgresql.org/message-id/CAFBsxsG2UHk8mOWL0tca%3D_cg%2B_oA5mVRNLhDF0TBw980iOg5NQ%40mail.gmail.com](https://www.postgresql.org/message-id/CAFBsxsG2UHk8mOWL0tca%3D_cg%2B_oA5mVRNLhDF0TBw980iOg5NQ%40mail.gmail.com)
Backpatch to v14, when this function came in
[https://git.postgresql.org/pg/commitdiff/543f36b43d835c819c24b2952ac4ecabbdf2b944](https://git.postgresql.org/pg/commitdiff/543f36b43d835c819c24b2952ac4ecabbdf2b944)

- Set the volatility of the timestamptz version of date_bin() back to immutable.
543f36b43d was too hasty in thinking that the volatility of date_bin() had to
match date_trunc(), since only the latter references session_timezone. Bump
catversion Per feedback from Aleksander Alekseev Backpatch to v14, as the
former commit was
[https://git.postgresql.org/pg/commitdiff/0c6a6a0ab7675e151a6e46d3ec0846bfce9e030a](https://git.postgresql.org/pg/commitdiff/0c6a6a0ab7675e151a6e46d3ec0846bfce9e030a)

Peter Geoghegan pushed:

- Add historic commit to git-blame-ignore-revs file. Add a historic pgindent
commit that was missed by the initial work done in commit 8e638845.
[https://git.postgresql.org/pg/commitdiff/aec5cc9efda00f7f418c9a068c92cab119602084](https://git.postgresql.org/pg/commitdiff/aec5cc9efda00f7f418c9a068c92cab119602084)

- Remove unneeded old_rel_pages VACUUM state field. The field hasn't been used
since commit 3d351d91, which redefined pg_class.reltuples to be -1 before the
first VACUUM or ANALYZE. Also rename a local variable of the same name
("old_rel_pages"). This is used by relation truncation to represent the
original relation size at the start of the ongoing VACUUM operation. Rename
it to orig_rel_pages, since that's a lot clearer. (This name matches similar
nearby code.)
[https://git.postgresql.org/pg/commitdiff/47029f775adf83796fecb5871ce52488996a7969](https://git.postgresql.org/pg/commitdiff/47029f775adf83796fecb5871ce52488996a7969)

- vacuumlazy.c: Correct prune state comment. Oversight in commit 7ab96cf6b3.
[https://git.postgresql.org/pg/commitdiff/6320806ac3792a297f1c4eb149c3ddeda25d3930](https://git.postgresql.org/pg/commitdiff/6320806ac3792a297f1c4eb149c3ddeda25d3930)

- Remove obsolete nbtree relation extension comment. Commit 0d1fe9f7 improved
the approach that vacuumlazy.c takes when it encounters an empty heap page.
It no acquires the relation extension lock.
[https://git.postgresql.org/pg/commitdiff/0f6aa893cb58c2a5a92016914c94865635345a22](https://git.postgresql.org/pg/commitdiff/0f6aa893cb58c2a5a92016914c94865635345a22)

- VACUUM VERBOSE: Don't report "pages removed". It doesn't make any sense to
report this information, since VACUUM VERBOSE reports on heap relation
truncation directly. This was an oversight in commit 7ab96cf6, which made
VACUUM VERBOSE output a little more consistent with nearby autovacuum-specific
log output. Adjust comments that describe how this is supposed to work in
passing. Also bring truncation-related VACUUM VERBOSE output in line with the
convention established for VACUUM VERBOSE output by commit f4f4a649. Author:
Peter Geoghegan <pg(at)bowt(dot)ie> Backpatch: 14-, where VACUUM VERBOSE's output
changed.
[https://git.postgresql.org/pg/commitdiff/b175b9cde720a81852b70c0c7fbc18c72ff1acc6](https://git.postgresql.org/pg/commitdiff/b175b9cde720a81852b70c0c7fbc18c72ff1acc6)

Peter Eisentraut pushed:

- Fix incorrect format placeholders.
[https://git.postgresql.org/pg/commitdiff/590ecd982304dec8599d6ca339903982d39a9a1a](https://git.postgresql.org/pg/commitdiff/590ecd982304dec8599d6ca339903982d39a9a1a)

- Fix pkg-config files for static linking. Since ea53100d5 (PostgreSQL 12), the
shipped pkg-config files have been broken for statically linking libpq because
libpgcommon and libpgport are missing. This patch adds those two missing
private dependencies (in a non-hardcoded way). Reported-by: Filip Gospodinov
<f(at)gospodinov(dot)ch> Discussion:
[https://www.postgresql.org/message-id/flat/c7108bde-e051-11d5-a234-99beec01ce2a(at)gospodinov(dot)ch](https://www.postgresql.org/message-id/flat/c7108bde-e051-11d5-a234-99beec01ce2a(at)gospodinov(dot)ch)
[https://git.postgresql.org/pg/commitdiff/4c2eab3a0dec2eae40892fb525830a5947a398c7](https://git.postgresql.org/pg/commitdiff/4c2eab3a0dec2eae40892fb525830a5947a398c7)

- Make pkg-config files cross-compile friendly. Currently the pc files use hard
coded paths for "includedir" and "libdir." Example: Cflags: -I/usr/include
Libs: -L/usr/lib -lpq This is not very fortunate when cross compiling inside
a buildroot, where the includes and libs are inside a staging directory,
because this introduces host paths into the build: checking for
pkg-config...
/builder/shared-workdir/build/sdk/staging_dir/host/bin/pkg-config checking
for PostgreSQL libraries via pkg_config... -L/usr/lib <---- This commit
addresses this by doing the following two things: 1. Instead of hard coding
the paths in "Cflags" and "Libs" "${includedir}" and "${libdir}" are
used. Note: these variables can be overriden on the pkg-config command
line ("--define-variable=libdir=/some/path"). 2. Add the variables
"prefix" and "exec_prefix". If "includedir" and/or "libdir" are using
these then construct them accordingly. This is done because buildroots
(for instance OpenWrt) tend to rename the real pkg-config and call it
indirectly from a script that sets "prefix", "exec_prefix" and "bindir",
like so: pkg-config.real --define-variable=prefix=${STAGING_PREFIX} \
--define-variable=exec_prefix=${STAGING_PREFIX} \
--define-variable=bindir=${STAGING_PREFIX}/bin $@ Example #1: user calls
./configure with "--libdir=/some/lib" and "--includedir=/some/include":
prefix=/usr/local/pgsql exec_prefix=${prefix} libdir=/some/lib
includedir=/some/include Name: libpq Description: PostgreSQL libpq
library Url: [http://www.postgresql.org/](http://www.postgresql.org/)
Version: 12.1 Requires: Requires.private: Cflags: -I${includedir}
Libs: -L${libdir} -lpq Libs.private: -lcrypt -lm Example #2: user calls
./configure with no arguments: prefix=/usr/local/pgsql
exec_prefix=${prefix} libdir=${exec_prefix}/lib
includedir=${prefix}/include Name: libpq Description: PostgreSQL libpq
library Url: [http://www.postgresql.org/](http://www.postgresql.org/)
Version: 12.1 Requires: Requires.private: Cflags: -I${includedir}
Libs: -L${libdir} -lpq Libs.private: -lcrypt -lm Like this the paths can
be forced into the staging directory when using a buildroot setup: checking
for pkg-config... /home/sk/tmp/openwrt/staging_dir/host/bin/pkg-config
checking for PostgreSQL libraries via pkg_config...
-L/home/sk/tmp/openwrt/staging_dir/target-mips_24kc_musl/usr/lib Author:
Sebastian Kemper <sebastian_ml(at)gmx(dot)net> Co-authored-by: Peter Eisentraut
<peter(dot)eisentraut(at)enterprisedb(dot)com> Discussion:
[https://www.postgresql.org/message-id/flat/20200305213827.GA25135%40darth.lan](https://www.postgresql.org/message-id/flat/20200305213827.GA25135%40darth.lan)
[https://git.postgresql.org/pg/commitdiff/6588d8416e4ef84fd99fb271b63116f207c6c479](https://git.postgresql.org/pg/commitdiff/6588d8416e4ef84fd99fb271b63116f207c6c479)

Tatsuo Ishii pushed:

- Use COPY FREEZE in pgbench for faster benchmark table population. While
populating the pgbench_accounts table, plain COPY was unconditionally used. By
changing it to COPY FREEZE, the time for VACUUM is significantly reduced, thus
the total time of "pgbench -i" is also reduced. This only happens if pgbench
runs against PostgreSQL 14 or later because COPY FREEZE in previous versions
of PostgreSQL does not bring the benefit. Also if partitioning is used, COPY
FREEZE cannot be used. In this case plain COPY will be used too. Author:
Tatsuo Ishii Discussion:
[https://postgr.es/m/20210308.143907.2014279678657453983.t-ishii@gmail.com](https://postgr.es/m/20210308.143907.2014279678657453983.t-ishii@gmail.com)
Reviewed-by: Fabien COELHO, Laurenz Albe, Peter Geoghegan, Dean Rasheed
[https://git.postgresql.org/pg/commitdiff/06ba4a63b85e5aa47b325c3235c16c05a0b58b96](https://git.postgresql.org/pg/commitdiff/06ba4a63b85e5aa47b325c3235c16c05a0b58b96)