PostgreSQL Weekly News - August 15, 2021PostgreSQL Weekly News - August 15, 2021Security 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 Newspgbouncer 1.16.0, a connection pooler and more for PostgreSQL released PostgreSQL Jobs for Augusthttps://archives.postgresql.org/pgsql-jobs/2021-08/ 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 PatchesBruce 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: 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: 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: |