# 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 [email protected]. # 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/[email protected]](https://postgr.es/m/[email protected]) [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...@mail.gmail.com](https://postgr.es/m/calj2acvkkyumc8ge72y7bp9g1batrrq3sewuh+1_i2krwzc...@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/[email protected]](https://postgr.es/m/[email protected]) [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-vvzuncmcquc3zzzmpoo57pogm8tnxokw...@mail.gmail.com](https://postgr.es/m/cahut+puyituud1-vvzuncmcquc3zzzmpoo57pogm8tnxokw...@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/[email protected]](https://postgr.es/m/[email protected]) 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/[email protected]](https://postgr.es/m/[email protected]) [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+e5ahlnxio...@mail.gmail.com](https://postgr.es/m/CAP0=zvlhtyffs8sowcfjwrbgorzt9qqbk+_ap+e5ahlnxio...@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/[email protected]](https://postgr.es/m/[email protected]) [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/[email protected]](https://postgr.es/m/[email protected]) [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/[email protected]](https://postgr.es/m/[email protected]) [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/[email protected]](https://postgr.es/m/[email protected]) [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=bfodqwgbntzofk0q1l...@mail.gmail.com](https://postgr.es/m/CAHut+Ps-vkmnWAShWSRVCB3gx8aM=bfodqwgbntzofk0q1l...@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=aqqkgmf85cyspqbgdcvnk73g2o0vl94o5...@mail.gmail.com](https://postgr.es/m/CAFF0-CGy7EHeF=aqqkgmf85cyspqbgdcvnk73g2o0vl94o5...@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/[email protected]](https://postgr.es/m/[email protected]) [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/[email protected]](https://postgr.es/m/[email protected]) [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/[email protected]](https://postgr.es/m/[email protected]) [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 <[email protected]> Reviewed-by: Michael Paquier <[email protected]> Reviewed-by: Andrew Dunstan <[email protected]> Reviewed-by: Julien Rouhaud <[email protected]> Discussion: [https://postgr.es/m/[email protected]](https://postgr.es/m/[email protected]) [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 <[email protected]> Discussion: [https://postgr.es/m/calj2acvrinvmdhyqgirrgs2z9doydflh2mymek9i8csn23u...@mail.gmail.com](https://postgr.es/m/calj2acvrinvmdhyqgirrgs2z9doydflh2mymek9i8csn23u...@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/[email protected]](https://postgr.es/m/[email protected]) [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/[email protected]](https://postgr.es/m/[email protected]) [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/[email protected]](https://postgr.es/m/[email protected]) [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/[email protected]](https://postgr.es/m/[email protected]) [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/[email protected]](https://postgr.es/m/[email protected]) [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/[email protected]](https://postgr.es/m/[email protected]) [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/[email protected]](https://postgr.es/m/[email protected]) [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/[email protected]](https://postgr.es/m/[email protected]) [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/os0pr01mb61136018064660f095cb57a8fb...@os0pr01mb6113.jpnprd01.prod.outlook.com](https://postgr.es/m/os0pr01mb61136018064660f095cb57a8fb...@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 <[email protected]> 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 <[email protected]> Discussion: [https://www.postgresql.org/message-id/flat/[email protected]](https://www.postgresql.org/message-id/flat/[email protected]) [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 <[email protected]> Co-authored-by: Peter Eisentraut <[email protected]> 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/[email protected]](https://postgr.es/m/[email protected]) Reviewed-by: Fabien COELHO, Laurenz Albe, Peter Geoghegan, Dean Rasheed [https://git.postgresql.org/pg/commitdiff/06ba4a63b85e5aa47b325c3235c16c05a0b58b96](https://git.postgresql.org/pg/commitdiff/06ba4a63b85e5aa47b325c3235c16c05a0b58b96)
