8th February 2024: PostgreSQL 16.2, 15.6, 14.11, 13.14, and 12.18 Released!

PostgreSQL Weekly News - September 5, 2021

Posted on 2021-09-06 by PWN
PWN

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.

dbForge Data Compare for PostgreSQL v3.4 released

pgmoneta 0.5.0, a backup and restore system for PostgreSQL, released

pgspider_ext, an extension to create a cluster engine for distributed data based on PostgreSQL foreign data wrappers, released.

psycopg2 3.0.0 beta 1, a Python connector for PostgreSQL, released.

postgresql-wheel, a Python package containing an entire compiled PostgreSQL server in a single pip installable file, released

PostgreSQL Jobs for September

https://archives.postgresql.org/pgsql-jobs/2021-09/

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:

Amit Kapila pushed:

Fujii Masao pushed:

Álvaro Herrera pushed:

Daniel Gustafsson pushed:

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

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

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

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://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://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://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://git.postgresql.org/pg/commitdiff/537ca68dbb2463f7b1c44e9466b8fbdd7505b2e1

John Naylor pushed:

Peter Geoghegan pushed:

Peter Eisentraut pushed:

  • Fix incorrect format placeholders. 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@gospodinov.ch Discussion: https://www.postgresql.org/message-id/flat/c7108bde-e051-11d5-a234-99beec01ce2a@gospodinov.ch 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/ 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/ 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@gmx.net Co-authored-by: Peter Eisentraut peter.eisentraut@enterprisedb.com Discussion: https://www.postgresql.org/message-id/flat/20200305213827.GA25135%40darth.lan 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 Reviewed-by: Fabien COELHO, Laurenz Albe, Peter Geoghegan, Dean Rasheed https://git.postgresql.org/pg/commitdiff/06ba4a63b85e5aa47b325c3235c16c05a0b58b96