psycopg2 3.0.0, a Python connector for PostgreSQL, released
pg_partman 4.6.0, a management system for partitioned tables, released.
pgAdmin4 6.0, a web- and native GUI control center for PostgreSQL, released.
Percona Distribution for PostgreSQL Operator 1.0.0, a Kubernetes operator based on Crunchy Data's, for PostgreSQL, released.
https://archives.postgresql.org/pgsql-jobs/2021-10/
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 [email protected].
Tom Lane pushed:
Doc: update testing recipe in src/test/perl/README. The previous text didn't provide any clear explanation of our policy around TAP test portability. The recipe for using perlbrew had some problems, too: it resulted in a non-shared libperl (preventing testing of plperl) and it caused some modules to be updated to current when the point of the recipe is to build an old environment. Discussion: https://postgr.es/m/[email protected] https://git.postgresql.org/pg/commitdiff/3eb1f4d09745433c70ccac411cad24d0374b9c3b
Fix EXPLAIN of SEARCH BREADTH FIRST queries some more. Commit 3f50b8263 had an oversight: formerly, to deparse expressions attached to a plan node, it was only necessary to update the deparse_namespace ancestors list alongside calling set_deparse_plan. Now it's necessary to update the ancestors list first, because set_deparse_plan consults it, and one call site got that wrong. This error was masked in most cases because explain.c uses just one List object for the ancestors list, updating it in-place as the plan is scanned, so that we accidentally had the right List assigned to dpns->ancestors before it was needed. It would fail only if a WorkTableScan node were the first one that we tried to deparse a subexpression of. Per report from Markus Winand. Like the previous patch, back-patch to v14. Discussion: https://postgr.es/m/[email protected] https://git.postgresql.org/pg/commitdiff/39ae0ef8561362304ee512963aa51d5a705e5616
Make configure check for minimum required version of IPC::Run. Per the discussion around 3eb1f4d09, let's have configure verify that the available IPC::Run version is at least 0.79, the agreed-on minimum. It seems unlikely that this could bite anybody anymore, but it's useful as documentation. (Based on that, there's little need to back-patch.) For consistency, also supply a minimum version for the other Perl module we have an explicit check for, Time::HiRes. I used the version that ships with Perl 5.8.3. Discussion: https://postgr.es/m/[email protected] https://git.postgresql.org/pg/commitdiff/4a235efddaa78ec78a47614ddc6161644e089290
Fix planner error with pulling up subquery expressions into function RTEs. If a function-in-FROM laterally references the output of some sub-SELECT earlier in the FROM clause, and we are able to flatten that sub-SELECT into the outer query, the expression(s) copied into the function RTE missed being processed by eval_const_expressions. This'd lead to trouble and probable crashes at execution if such expressions contained named-argument function call syntax or functions with defaulted arguments. The bug is masked if the query contains any explicit JOIN syntax, which may help explain why we'd not noticed. Per bug #17227 from Bernd Dorn. This is an oversight in commit 7266d0997, so back-patch to v13 where that came in. Discussion: https://postgr.es/m/[email protected] https://git.postgresql.org/pg/commitdiff/4d5f651f1d651c6fa79f9188e7b9a04654c7125a
Make pg_dump acquire lock on partitioned tables that are to be dumped. It was clearly the intent to do so all along, but the original coding fat-fingered this by checking the wrong array element. We fixed it in passing in 403a3d91c, but that later got reverted, and we forgot to keep this bug fix. Most of the time this'd be relatively harmless, since once we lock any of the partitioned table's leaf partitions, that would suffice to prevent major DDL on the partitioned table itself. However, a childless partitioned table would get dumped with no relevant lock whatsoever, possibly allowing dump failure or inconsistent output. Unlike 403a3d91c, there are no versioning concerns, since every server version that has partitioned tables will allow you to lock one. Back-patch to v10 where partitioned tables were introduced. Discussion: https://postgr.es/m/[email protected] https://git.postgresql.org/pg/commitdiff/e2ff7d9a83d4b489806281dc6dfce88510b40ad7
Avoid core dump in pg_dump when dumping from pre-8.3 server. Commit f0e21f2f6 missed adding a tgisinternal output column to getTriggers' query for pre-8.3 servers. Back-patch to v11, like that commit. https://git.postgresql.org/pg/commitdiff/40dfac4fc4776213a02291f13046d36e318f2629
Michaël Paquier pushed:
Clean up more code using "(expr) ? true : false". This is similar to fd0625c, taking care of any remaining code paths that are worth the cleanup. This also changes some cases using opposite expression patterns. Author: Justin Pryzby, Masahiko Sawada Discussion: https://postgr.es/m/CAD21AoCdF8dnUvr-BUWWGvA_XhKSoANacBMZb6jKyCk4TYfQ2Q@mail.gmail.com https://git.postgresql.org/pg/commitdiff/68f7c4b57a27dbcd3e93ba3ff7b0b49664b25e09
Add more $Test::Builder::Level in the TAP tests. Incrementing the level of the call stack reported is useful for debugging purposes as it allows to control which part of the test is exactly failing, especially if a test is structured with subroutines that call routines from Test::More. This adds more incrementations of $Test::Builder::Level where debugging gets improved (for example it does not make sense for some paths like pg_rewind where long subroutines are used). A note is added to src/test/perl/README about that, based on a suggestion from Andrew Dunstan and a wording coming from both of us. Usage of Test::Builder::Level has spread in 12, so a backpatch down to this version is done. Reviewed-by: Andrew Dunstan, Peter Eisentraut, Daniel Gustafsson Discussion: https://postgr.es/m/[email protected] Backpatch-through: 12 https://git.postgresql.org/pg/commitdiff/f9c4cb686800d46ef9e9e90ed5133493b23962af
Fix tests of pg_upgrade across different major versions. This fixes a set of issues that cause different breakages or annoyances when using pg_upgrade's test.sh to do upgrades across different major versions: - test.sh is completely broken when using v14 as new version because of the removal of testtablespace/ as Makefile rule. Older versions of pg_regress don't support --make-tablespacedir, blocking the creation of the tablespace. In order to fix that, it is simple enough to create those directories in the script itself, but only do that when an old version is involved. This fix is needed on HEAD and REL_14_STABLE. - The script would fail when using PG <= v11 as old version because of WITH OIDS relations not supported in v12. In order to fix this, this steals a method from the buildfarm that uses a DO block to change all the relations marked as WITH OIDS, allowing pg_upgrade to pass. This is more portable than using ALTER TABLE queries on the relations causing issues. This is fixed down to v12, and authored originally by Andrew Dunstan. - Not using --extra-float-digits=0 with v11 as old version causes a lot of diffs in the dumps, making the whole unreadable. This gets only done when using v11 as old version. This is fixed down to v12. The buildfarm code uses that already. Note that the addition of --wal-segsize and --allow-group-access breaks the script when using v10 or older at initdb time as these got added in 11. 10 would be EOL'd next year and nobody has complained about those problems yet, so nothing is done about that. This means that this commit fixes upgrade tests using test.sh with v11 as minimum older version, up to HEAD, and that it is enough to apply this change down to 12. The old and new dumps still generate diffs, still require manual checks, and more could be done to reduce the noise, but this allows the tests to run with a rather minimal amount of them. I have tested this commit and test.sh with v11 as minimum across all the branches where this is applied. Note that this commit has no impact on the normal pg_upgrade test run with a simple "make check". Author: Justin Pryzby, Andrew Dunstan, Michael Paquier Discussion: https://postgr.es/m/[email protected] Backpatch-through: 12 https://git.postgresql.org/pg/commitdiff/fa66b6dee0843d2bca5bf9c9b8b7be32defbffae
Fix use-after-free with multirange types in CREATE TYPE. The code was freeing the name of the multirange type function stored in the parse tree but it should not do that. Event triggers could for example look at such a corrupted parsed tree with a ddl_command_end event. Author: Alex Kozhemyakin, Sergey Shinderuk Reviewed-by: Peter Eisentraut, Michael Paquier Discussion: https://postgr.es/m/[email protected] Backpatch-through: 14 https://git.postgresql.org/pg/commitdiff/5b0e7fe1d67235a092be1132bc5c97f1d7f29aaf
Peter Geoghegan pushed:
amcheck: Skip unlogged relations in Hot Standby. Have verify_heapam.c treat unlogged relations as if they were simply empty when in Hot Standby mode. This brings it in line with verify_nbtree.c, which has handled unlogged relations in the same way since bugfix commit 6754fe65a4. This was an oversight in commit 866e24d47d, which extended contrib/amcheck to check heap relations. In passing, lower the verbosity used when reporting that a relation has been skipped like this, from NOTICE to DEBUG1. This is appropriate because the skipping behavior is only an implementation detail, needed to work around the fact that unlogged tables don't have smgr-level storage for their main fork when in Hot Standby mode. Affected unlogged relations should be considered "trivially verified", not skipped over. They are verified in the same sense that a totally empty relation can be verified. This behavior seems least surprising overall, since unlogged relations on a replica will initially be empty if and when the replica is promoted and Hot Standby ends. Author: Mark Dilger mark.dilger@enterprisedb.com Reviewed-By: Peter Geoghegan pg@bowt.ie Discussion: https://postgr.es/m/CAH2-Wzk_pukOFY7JmdiFLsrz+Pd3V8OwgC1TH2Vd5BH5ZgK4bA@mail.gmail.com Backpatch: 14-, where heapam verification was introduced. https://git.postgresql.org/pg/commitdiff/292698f158ddb3f9a88f536e6eecb9e55d9619c9
Doc: normalize vacuum_multixact_failsafe_age ID. Author: Pavel Luzanov p.luzanov@postgrespro.ru Discussion: https://postgr.es/m/[email protected] Backpatch: 14-, where the failsafe was introduced. https://git.postgresql.org/pg/commitdiff/00c61a74bcdbc04a3db721d53c7aff62244da198
pg_amcheck: avoid unhelpful verification attempts. Avoid calling contrib/amcheck functions with relations that are unsuitable for checking. Specifically, don't attempt verification of temporary relations, or indexes whose pg_index entry indicates that the index is invalid, or not ready. These relations are not supported by any of the contrib/amcheck functions, for reasons that are pretty fundamental. For example, the implementation of REINDEX CONCURRENTLY can add its own "transient" pg_index entries, which has rather unclear implications for the B-Tree verification functions, at least in the general case -- so they just treat it as an error. It falls to the amcheck caller (in this case pg_amcheck) to deal with the situation at a higher level. pg_amcheck now simply treats these conditions as additional "visibility concerns" when it queries system catalogs. This is a little arbitrary. It seems to have the least problems among any of the available alternatives. Author: Mark Dilger mark.dilger@enterprisedb.com Reported-By: Alexander Lakhin exclusion@gmail.com Reviewed-By: Peter Geoghegan pg@bowt.ie Reviewed-By: Robert Haas robertmhaas@gmail.com Bug: #17212 Discussion: https://postgr.es/m/[email protected] Backpatch: 14-, where pg_amcheck was introduced. https://git.postgresql.org/pg/commitdiff/d2bf06db377967b0d671ae372d513806e2a28052
Remove unstable pg_amcheck tests. Recent pg_amcheck bugfix commit d2bf06db added a test case that the buildfarm has shown to be non-portable. It doesn't particularly seem worth keeping anyway. Remove it. Discussion: https://postgr.es/m/CAH2-Wz=7HKJ9WzAh7+M0JfwJ1yfT9qoE+KPa3P7iGToPOtGhXg@mail.gmail.com Backpatch: 14-, just like the original commit. https://git.postgresql.org/pg/commitdiff/cd3f429d9565b2e5caf0980ea7c707e37bc3b317
Remove obsolete nbtree deduplication comments. Follow up to commit 2903f140. https://git.postgresql.org/pg/commitdiff/b76c1d6e849779e4a5a6c24d159a42125e522154
Fujii Masao pushed:
Peter Eisentraut pushed:
psql: More tests. Add some basic tests for command-line option handling and help output, similar to what we have for other command-line programs. This also creates a place to put some more one-off test cases later. Discussion: https://www.postgresql.org/message-id/[email protected] https://git.postgresql.org/pg/commitdiff/c0280bc3edeb9e9958efc14083b6f301d2ef79d5
psql: Add test for handling of replication commands. Add a test for the clean handling of unsupported replication command responses. This was once accidentally broken, and it seems unusual enough that it's easy to forget when testing manually. Discussion: https://www.postgresql.org/message-id/[email protected] https://git.postgresql.org/pg/commitdiff/67c069848a998de1436cad2d67baedbf31c3a28c
psql: Fix test. The test didn't work on platforms where getopt() doesn't support non-option arguments before options. https://git.postgresql.org/pg/commitdiff/d9ddc50bafc062ec1ae7f98b886b7950102d87fc
psql: Fix some scan-build warnings. A repeated complaint was that scan-build thought that if the \timing setting changes during processing of a query, the post-processing might read garbage time values. This is probably not possible right now, but it's not entirely inconceivable given the code structure. So silence this warning with small restructuring that makes this more robust. The other warnings were a few dead stores that are easy to remove. Discussion: https://www.postgresql.org/message-id/[email protected] https://git.postgresql.org/pg/commitdiff/390edeeb570c01de1a14e2985ffed96de001e42e
Fix incorrect format placeholder. https://git.postgresql.org/pg/commitdiff/780054bf31a0a6ba781f46c454f0116efee8a74c
Robert Haas pushed:
Refactor basebackup.c's _tarWriteDir() function. Sometimes, we replace a
symbolic link that we find in the data directory with an actual directory
within the tarfile that we create. _tarWriteDir was responsible both for
making this substitution and also for writing the tar header for the resulting
directory into the tar file. Make it do only the first of those things, and
rename to convert_link_to_directory. Substantially larger refactoring of this
source file is planned, but this little bit seemed to make sense to commit
independently. Discussion:
http://postgr.es/m/CA+Tgmobz6tuv5tr-WxURe5JA1vVcGz85k4kkvoWxcyHvDpEqFA@mail.gmail.com
https://git.postgresql.org/pg/commitdiff/967a17fe2fa77b61061c8fb1183f64a5df4e080a
Refactor some end-of-recovery code out of StartupXLOG(). Create a new function PerformRecoveryXLogAction() and move the code which either writes an end-of-recovery record or requests a checkpoint there. Also create a new function CleanupAfterArchiveRecovery() to perform a few tasks that we want to do after we've actually exited archive recovery but before we start accepting new WAL writes. More refactoring of this file is planned, but this commit is just straightforward code movement to make StartupXLOG() a little bit shorter and a little bit easier to understand. Robert Haas and Amul Sul Discussion: http://postgr.es/m/CAAJ_b97abMuq=470Wahun=aS1PHTSbStHtrjjPaD-C0YQ1AqVw@mail.gmail.com https://git.postgresql.org/pg/commitdiff/6df1543abfed6f6a86b76a48fa11a6f019111c01
Postpone some end-of-recovery operations related to allowing WAL. CreateOverwriteContrecordRecord(), UpdateFullPageWrites(), PerformRecoveryXLogAction(), and CleanupAfterArchiveRecovery() are moved somewhat later in StartupXLOG(). This is preparatory work for a future patch that wants to allow recovery to end at one time and only later start to allow WAL writes. To do that, it's necessary to separate code that has to do with allowing WAL writes from other things that need to happen simply because recovery is ending, such as initializing shared memory data structures that depend on information that might not be accurate before redo is complete. This commit does not achieve that goal, but it is a step in that direction. For example, there are a few different bits of code that write things into WAL once we have finished recovery, and with this change, those bits of code are closer to each other than previously, with fewer unrelated bits of code interspersed. Robert Haas and Amul Sul Discussion: http://postgr.es/m/CAAJ_b97abMuq=470Wahun=aS1PHTSbStHtrjjPaD-C0YQ1AqVw@mail.gmail.com https://git.postgresql.org/pg/commitdiff/811051c2e7af1b030467760baf7ee0f4a22bc992
shm_mq: Update mq_bytes_written less often. Do not update shm_mq's mq_bytes_written until we have written an amount of data greater than 1/4th of the ring size, unless the caller of shm_mq_send(v) requests a flush at the end of the message. This reduces the number of calls to SetLatch(), and also the number of CPU cache misses, considerably, and thus makes shm_mq significantly faster. Dilip Kumar, reviewed by Zhihong Yu and Tomas Vondra. Some minor cosmetic changes by me. Discussion: http://postgr.es/m/CAFiTN-tVXqn_OG7tHNeSkBbN+iiCZTiQ83uakax43y1sQb2OBA@mail.gmail.com https://git.postgresql.org/pg/commitdiff/46846433a03dff4f2e08c8a161e54a842da360d6
Etsuro Fujita pushed:
Álvaro Herrera pushed:
Jeff Davis pushed:
Andrew Dunstan pushed: