PostgreSQL Weekly News - September 5, 2021PostgreSQL Weekly News - September 5, 2021PostgreSQL Product Newspg_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 Septemberhttps://archives.postgresql.org/pgsql-jobs/2021-09/ PostgreSQL in the NewsPlanet 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 PatchesMichaël Paquier pushed: 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://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://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://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://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://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://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://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://git.postgresql.org/pg/commitdiff/e04267844a9bbf97c2e85c919b84dfe498ab0302
Á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
|