PostgreSQL Weekly News - August 15, 2021

Posted on 2021-08-15 by PWN
PWN

PostgreSQL Weekly News - August 15, 2021

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.

PostgreSQL Product News

pgbouncer 1.16.0, a connection pooler and more for PostgreSQL released

PostgreSQL Jobs for August

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

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

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:

Michaël Paquier pushed:

Daniel Gustafsson pushed:

Heikki Linnakangas pushed:

  • Fix segfault during EvalPlanQual with mix of local and foreign partitions. It's not sensible to re-evaluate a direct-modify Foreign Update or Delete during EvalPlanQual. However, ExecInitForeignScan() can still get called if a table mixes local and foreign partitions. EvalPlanQualStart() left the es_result_relations array uninitialized in the child EPQ EState, but ExecInitForeignScan() still expected to find it. That caused a segfault. Fix by skipping the es_result_relations lookup during EvalPlanQual processing. To make things a bit more robust, also skip the BeginDirectModify calls, and add a runtime check that ExecForeignScan() is not called on direct-modify foreign scans during EvalPlanQual processing. This is new in v14, commit 1375422c782. Before that, EvalPlanQualStart() copied the whole ResultRelInfo array to the EPQ EState. Backpatch to v14. Report and diagnosis by Andrey Lepikhov. Discussion: https://www.postgresql.org/message-id/cb2b808d-cbaa-4772-76ee-c8809bafcf3d%40postgrespro.ru https://git.postgresql.org/pg/commitdiff/c3928b467a4f0ed2b0ef21a33848e9fcdade37b4

John Naylor pushed:

Tomáš Vondra pushed:

Thomas Munro pushed:

Michael Meskes pushed:

Peter Eisentraut pushed: