Security releases 13.4, 12.8, 11.13, 10.18, 9.6.23, and 14 Beta 3 released Please upgrade ASAP. The 9.6 series will stop getting fixes on November 11, 2021. Plan major version upgrades now.
The Code of Conduct Committee is looking for new members to serve a 1-3 year term.
pgbouncer 1.16.0, a connection pooler and more for PostgreSQL released
https://archives.postgresql.org/pgsql-jobs/2021-08/
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.
Bruce Momjian pushed:
David Rowley pushed:
Add POPCNT support for MSVC x86_64 builds. 02a6a54ec added code to make use of
the POPCNT instruction when available for many of our common platforms. Here
we do the same for MSVC for x86_64 machines. MSVC's intrinsic functions for
popcnt seem to differ from GCCs in that they always appear to emit the popcnt
instructions. In GCC the behavior will depend on if the source file was
compiled with -mpopcnt or not. For this reason, the MSVC intrinsic function
has been lumped into the pg_popcount*_asm function, however doing that sort of
invalidates the name of that function, so let's rename it to
pg_popcount*_fast(). Author: David Rowley Reviewed-by: John Naylor
Discussion:
https://postgr.es/m/CAApHDvqL3cbbK%3DGzNcwzsNR9Gi%2BaUvTudKkC4XgnQfXirJ_oRQ%40mail.gmail.com
https://git.postgresql.org/pg/commitdiff/2e281249af6c702fd057f34150fd9ac6cb8c7a8b
Use ExplainPropertyInteger for queryid in EXPLAIN. This saves a few lines of code. Also add a comment to mention why we use ExplainPropertyInteger instead of ExplainPropertyUInteger given that queryid is a uint64 type. Author: David Rowley Reviewed-by: Julien Rouhaud Discussion: https://postgr.es/m/CAApHDvqhSLYpSU_EqUdN39w9Uvb8ogmHV7_3YhJ0S3aScGBjsg@mail.gmail.com Backpatch-through: 14, where this code was originally added https://git.postgresql.org/pg/commitdiff/4a3d806f38f99fecf8f2a2bf7990a7ebea9b6c63
Doc: Fix misleading statement about VACUUM memory limits. In ec34040af I added a mention that there was no point in setting maintenance_work_limit to anything higher than 1GB for vacuum, but that was incorrect as ginInsertCleanup() also looks at what maintenance_work_mem is set to during VACUUM and that's not limited to 1GB. Here I attempt to make it more clear that the limitation is only around the number of dead tuple identifiers that we can collect during VACUUM. I've also added a note to autovacuum_work_mem to mention this limitation. I didn't do that in ec34040af as I'd had some wrong-headed ideas about just limiting the maximum value for that GUC to 1GB. Author: David Rowley Discussion: https://postgr.es/m/CAApHDvpGwOAvunp-E-bN_rbAs3hmxMoasm5pzkYDbf36h73s7w@mail.gmail.com Backpatch-through: 9.6, same as ec34040af https://git.postgresql.org/pg/commitdiff/deb6ffd4fdeb589de7a13ac1791380a7138cf59f
Remove some special cases from MSVC build scripts. Here we add additional parsing of Makefiles to determine when to add references to libpgport and libpgcommon. We also remove the need for adding the current contrib_extrasource by adding sine very basic logic to implement the Makefile rules which add .l and .y files when they exist for a given .o file in the Makefile. This is just some very basic additional parsing of Makefiles to try to keep things more consistent between builds using make and MSVC builds. This happens to work with how our current Makefiles are laid out, but it could easily be broken in the future if someone chooses do something in the Makefile that we don't have parsing support for. We will cross that bridge when we come to it. Author: David Rowley Discussion: https://postgr.es/m/CAApHDvoPULi5JW3933NxgwxOmu9Ncvpcyt87UhEHAUX16QqmpA@mail.gmail.com https://git.postgresql.org/pg/commitdiff/76ad24400d73fa10d527844d50bedf7dacb1e87b
Fix incorrect hash table resizing code in simplehash.h. This fixes a bug in simplehash.h which caused an incorrect size mask to be used when the hash table grew to SH_MAX_SIZE (2^32). The code was incorrectly setting the size mask to 0 when the hash tables reached the maximum possible number of buckets. This would result always trying to use the 0th bucket causing an infinite loop of trying to grow the hash table due to there being too many collisions. Seemingly it's not that common for simplehash tables to ever grow this big as this bug dates back to v10 and nobody seems to have noticed it before. However, probably the most likely place that people would notice it would be doing a large in-memory Hash Aggregate with something close to at least 2^31 groups. After this fix, the code now works correctly with up to within 98% of 2^32 groups and will fail with the following error when trying to insert any more items into the hash table: ERROR: hash table size exceeded However, the work_mem (or hash_mem_multiplier in newer versions) settings will generally cause Hash Aggregates to spill to disk long before reaching that many groups. The minimal test case I did took a work_mem setting of over 192GB to hit the bug. simplehash hash tables are used in a few other places such as Bitmap Index Scans, however, again the size that the hash table can become there is also limited to work_mem and it would take a relation of around 16TB (2^31) pages and a very large work_mem setting to hit this. With smaller work_mem values the table would become lossy and never grow large enough to hit the problem. Author: Yura Sokolov Reviewed-by: David Rowley, Ranier Vilela Discussion: https://postgr.es/m/b1f7f32737c3438136f64b26f4852b96@postgrespro.ru Backpatch-through: 10, where simplehash.h was added https://git.postgresql.org/pg/commitdiff/37450f2ca9ad430d78673cc26816fc2085e65904
Amit Kapila pushed:
Tom Lane pushed:
Avoid determining regexp subexpression matches, when possible. Identifying the precise match locations for parenthesized subexpressions is a fairly expensive task given the way our regexp engine works, both at regexp compile time (where we must create an optimized NFA for each parenthesized subexpression) and at runtime (where determining exact match locations requires laborious search). Up to now we've made little attempt to optimize this situation. This patch identifies cases where we know at compile time that we won't need to know subexpression match locations, and teaches the regexp compiler to not bother creating per-subexpression regexps for parenthesis pairs that are not referenced by backrefs elsewhere in the regexp. (To preserve semantics, we obviously still have to pin down the match locations of backref references.) Users could have obtained the same results before this by being careful to write "non capturing" parentheses wherever possible, but few people bother with that. Discussion: https://postgr.es/m/2219936.1628115334@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/0e6aa8747d439bb7f08f95e358f0509c50396785
Let regexp_replace() make use of REG_NOSUB when feasible. If the replacement string doesn't contain \1...\9, then we don't need sub-match locations, so we can use the REG_NOSUB optimization here too. There's already a pre-scan of the replacement string to look for backslashes, so extend that to check for digits, and refactor to allow that to happen before we compile the regexp. While at it, try to speed up the pre-scan by using memchr() instead of a handwritten loop. It's likely that this is lost in the noise compared to the regexp processing proper, but maybe not. In any case, this coding is shorter. Also, add some test cases to improve the poor coverage of appendStringInfoRegexpSubstr(). Discussion: https://postgr.es/m/3534632.1628536485@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/18bac60ede44359a1e577df80aef196e371c902e
Fix failure of btree_gin indexscans with "char" type and <!--<= operators. As a
result of confusion about whether the "char" type is signed or unsigned, scans
for index searches like "col < 'x'" or "col <= 'x'" would start at the middle
of the index not the left end, thus missing many or all of the entries they
should find. Fortunately, this is not a symptom of index corruption. It's
only the search logic that is broken, and we can fix it without unpleasant
side-effects. Per report from Jason Kim. This has been wrong since
btree_gin's beginning, so back-patch to all supported branches. Discussion:
https://postgr.es/m/20210810001649.htnltbh7c63re42p@jasonk.me
https://git.postgresql.org/pg/commitdiff/a6bd28beb0639d4cf424e961862a65c466ca65bf
Add RISC-V spinlock support in s_lock.h. Like the ARM case, just use gcc's
__sync_lock_test_and_set(); that will compile into AMOSWAP.W.AQ which does
what we need. At some point it might be worth doing some work on atomic ops
for RISC-V, but this should be enough for a creditable port. Back-patch to
all supported branches, just in case somebody wants to try them on RISC-V.
Marek Szuba Discussion:
https://postgr.es/m/dea97b6d-f55f-1f6d-9109-504aa7dfa421@gentoo.org
https://git.postgresql.org/pg/commitdiff/c32fcac56a212b4e6bb5ba63596f60a25a18109a
Un-break s_lock_test. Commit 80abbeba2 evidently didn't bother checking this code. Also, list the generated executable in .gitignore (so it's been a REALLY long time since anyone tried this). Noted while trying out RISC-V spinlock patch. Given that this has been broken for 5 years and nobody noticed, it's likely not worth back-patching. https://git.postgresql.org/pg/commitdiff/0a208ed63ffe50a8d9d7c0b33996ec01cc4fdef6
Andres Freund pushed:
Fix bogus assertion in BootstrapModeMain(). The assertion was always true, as written, thanks to me "simplifying" it before commit. Per coverity and Tom Lane. https://git.postgresql.org/pg/commitdiff/e12694523e7e4482a052236f12d3d8b58be9a22c
Fix typo. Reported-By: Michael Paquier michael@paquier.xyz-- Discussion: https://postgr.es/m/YRIlNQhLNfx555Nx@paquier.xyz https://git.postgresql.org/pg/commitdiff/1d5135f0043b32a7d9fdc66a9553c2078900e240
Remove support for background workers without BGWORKER_SHMEM_ACCESS. Background workers without shared memory access have been broken on EXEC_BACKEND / windows builds since shortly after background workers have been introduced, without that being reported. Clearly they are not commonly used. The problem is that bgworker startup requires to be attached to shared memory in EXEC_BACKEND child processes. StartBackgroundWorker() detaches from shared memory for unconnected workers, but at that point we already have initialized subsystems referencing shared memory. Fixing this problem is not entirely trivial, so removing the option to not be connected to shared memory seems the best way forward. In most use cases the advantages of being connected to shared memory far outweigh the disadvantages. As there have been no reports about this issue so far, we have decided that it is not worth trying to address the problem in the back branches. Per discussion with Alvaro Herrera, Robert Haas and Tom Lane. Author: Andres Freund andres@anarazel.de Discussion: https://postgr.es/m/20210802065116.j763tz3vz4egqy3w@alap3.anarazel.de https://git.postgresql.org/pg/commitdiff/80a8f95b3bca6a80672d1766c928cda34e979112
Michaël Paquier pushed:
Add call to object access hook at the end of table rewrite in ALTER TABLE. ALTER TABLE .. SET {LOGGED,UNLOGGED,ACCESS METHOD} would never do a table-level object access hook, which was inconsistent with SET TABLESPACE. Note that contrary to SET TABLESPACE, the no-op case is left off for those commands as this requires tracking if commands have been called, but they may not execute a physical rewrite. Another thing worth noting is that the physical file swap at the end of a rewrite does a couple of access calls for internal objects created for the swap operation (internal objects are for example skipped by the tests of sepgsql), but this does not trigger the hook for the table on which the operation is done. f41872d, that added support for SET LOGGED/UNLOGGED in ALTER TABLE, visibly forgot to consider that. Based on what I checked, two regression tests of sepgsql in ddl.sql are going to log more information with this test, something that buildfarm member rhinoceros will tell soon enough. I am not completely sure of their format though, so these are not refreshed yet. This is arguably a bug, but no backpatch is done as this could cause a behavior change for anybody using object access hooks. Reported-by: Jeff Davis Discussion: https://postgr.es/m/YQJKV29/1a60uG68@paquier.xyz https://git.postgresql.org/pg/commitdiff/7b565843a94412395149c6add0cbfc21d2bca0d4
Fix regression test output of sepgsql. The difference is caused by 7b56584, for the tests involving a table rewrite. Per buildfarm member rhinoceros. Discussion: https://postgr.es/m/YRHxXcyFjPuPTZui@paquier.xyz https://git.postgresql.org/pg/commitdiff/1e3445237b861fee2524defde79428058d90c0d8
Add tab completion for DECLARE .. ASENSITIVE in psql. This option has been introduced in dd13ad9. Author: Shinya Kato Discussion: https://postgr.es/m/TYAPR01MB289665526B76DA29DC70A031C4F09@TYAPR01MB2896.jpnprd01.prod.outlook.com https://git.postgresql.org/pg/commitdiff/e2ce88b58f151753b094f28bc387cebae865927c
Avoid unnecessary shared invalidations in ROLLBACK PREPARED. The performance gain is minimal, but this makes the logic more consistent with AtEOXact_Inval(). No other invalidation is needed in this case as PREPARE takes already care of sending any local ones. Author: Liu Huailing Reviewed-by: Tom Lane, Michael Paquier Discussion: https://postgr.es/m/OSZPR01MB6215AA84D71EF2B3D354CF86BE139@OSZPR01MB6215.jpnprd01.prod.outlook.com https://git.postgresql.org/pg/commitdiff/710796f0542180cca18ee93889da692df642bdf2
Daniel Gustafsson pushed:
Remove unused regression test certificate server-ss. The server-ss certificate was included in e39250c64 but was never used in the TLS regression tests so remove. Author: Jacob Champion Discussion: https://postgr.es/m/d15a9838344ba090e09fd866abf913584ea19fb7.camel@vmware.com https://git.postgresql.org/pg/commitdiff/152c2e0ae1a8d0ed810b2e833b536e64b91da0a6
Disable OpenSSL EVP digest padding in pgcrypto. The PX layer in pgcrypto is handling digest padding on its own uniformly for all backend implementations. Starting with OpenSSL 3.0.0, DecryptUpdate doesn't flush the last block in case padding is enabled so explicitly disable it as we don't use it. This will be backpatched to all supported version once there is sufficient testing in the buildfarm of OpenSSL 3. Reviewed-by: Peter Eisentraut, Michael Paquier Discussion: https://postgr.es/m/FEF81714-D479-4512-839B-C769D2605F8A@yesql.se https://git.postgresql.org/pg/commitdiff/318df802355924015d4d8f21859bc0ef7a348970
Add alternative output for OpenSSL 3 without legacy loaded. OpenSSL 3 introduced the concept of providers to support modularization, and moved the outdated ciphers to the new legacy provider. In case it's not loaded in the users openssl.cnf file there will be a lot of regress test failures, so add alternative outputs covering those. Also document the need to load the legacy provider in order to use older ciphers with OpenSSL-enabled pgcrypto. This will be backpatched to all supported version once there is sufficient testing in the buildfarm of OpenSSL 3. Reviewed-by: Michael Paquier Discussion: https://postgr.es/m/FEF81714-D479-4512-839B-C769D2605F8A@yesql.se https://git.postgresql.org/pg/commitdiff/72bbff4cd6eaf55239ccef79cec61766b5f8f1d2
Fix sslsni connparam boolean check. The check for sslsni only checked for existence of the parameter but not for the actual value of the param. This meant that the SNI extension was always turned on. Fix by inspecting the value of sslsni and only activate the SNI extension iff sslsni has been enabled. Also update the docs to be more in line with how other boolean params are documented. Backpatch to 14 where sslsni was first implemented. Reviewed-by: Tom Lane Backpatch-through: 14, where sslni was added https://git.postgresql.org/pg/commitdiff/512f4ca6c6b5d2e3a1620288048ccaa712121e12
Heikki Linnakangas pushed:
John Naylor pushed:
Fix grammar mistake in hash index README. Dilip Kumar Discussion: https://www.postgresql.org/message-id/CAFiTN-tjZbuY6vy7kZZ6xO%2BD4mVcO5wOPB5KiwJ3AHhpytd8fg%40mail.gmail.com https://git.postgresql.org/pg/commitdiff/b05f7ecec44be22f6de703e5afdeb4ff3559315a
Speed up generation of Unicode hash functions. Sets of Unicode keys are picky about the primes used when generating a perfect hash function for them. Callers can spend many seconds iterating through all the possible combinations of candidate multipliers and seeds to find one that works. Unicode updates typically happen only once a year, but it still makes development and testing of Unicode scripts unnecessarily slow. To fix, iterate over the primes in the innermost loop. This does not change any existing functions checked into the tree. https://git.postgresql.org/pg/commitdiff/ba958299eaf3d2f55bddb8efb037091d14ca6fd0
Tomáš Vondra pushed:
Thomas Munro pushed:
Michael Meskes pushed:
Peter Eisentraut pushed: