8th February 2024: PostgreSQL 16.2, 15.6, 14.11, 13.14, and 12.18 Released!

PostgreSQL Weekly News - February 28, 2021

Posted on 2021-03-01 by PWN
PWN

PostgreSQL Weekly News - February 28, 2021

Database Lab 2.2.1, a tool for fast cloning of large PostgreSQL databases to build non-production environments, released: https://gitlab.com/postgres-ai/database-lab/-/releases

dbMigration .NET v13.4, a database migration and sync tool, released. https://fishcodelib.com/DBMigration.htm

Joe 0.9.0, a Slack chatbot that helps backend developers and DBAs troubleshoot and optimize PostgreSQL queries, releaesed. https://gitlab.com/postgres-ai/joe/-/releases#0.9.0

pgAdmin4 5.0, a web- and native GUI control center for PostgreSQL, released. https://www.pgadmin.org/docs/pgadmin4/5.0/release_notes_5_0.html

pgagroal 1.2.0, a high-performance protocol-native connection pool for PostgreSQL, released. https://agroal.github.io/pgagroal/release/announcement/2021/02/23/pgagroal-1.2.0.html

Person of the week: https://postgresql.life/post/takayuki_tsunakawa/

PostgreSQL Product News

PostgreSQL Jobs for February

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

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

Tom Lane pushed:

  • Fix invalid array access in trgm_regexp.c. Brown-paper-bag bug in 08c0d6ad6: I missed one place that needed to guard against RAINBOW arc colors. Remarkably, nothing noticed the invalid array access except buildfarm member thorntail. Thanks to Noah Misch for assistance with tracking this down. https://git.postgresql.org/pg/commitdiff/6ee479abfc27a18c37fe77140d16d3ac31f4ac31

  • Simplify memory management for regex DFAs a little. Coverity complained that functions in regexec.c might leak DFA storage. It's wrong, but this logic is confusing enough that it's not so surprising Coverity couldn't make sense of it. Rewrite in hopes of making it more legible to humans as well as machines. https://git.postgresql.org/pg/commitdiff/190c79884aae540c92f017701726ed69265e2dab

  • Suppress compiler warning in new regex match-all detection code. gcc 10 is smart enough to notice that control could reach this "hasmatch[depth]" assignment with depth < 0, but not smart enough to know that that would require a badly broken NFA graph. Change the assert() to a plain runtime test to shut it up. Per report from Andres Freund. Discussion: https://postgr.es/m/20210223173437.b3ywijygsy6q42gq@alap3.anarazel.de https://git.postgresql.org/pg/commitdiff/3db05e76f92846d4b54d7de251b0875cf1e23aa4

  • Allow complemented character class escapes within regex brackets. The complement-class escapes \D, \S, \W are now allowed within bracket expressions. There is no semantic difficulty with doing that, but the rather hokey macro-expansion-based implementation previously used here couldn't cope. Also, invent "word" as an allowed character class name, thus "\w" is now equivalent to "[[:word:]]" outside brackets, or "[:word:]" within brackets. POSIX allows such implementation-specific extensions, and the same name is used in e.g. bash. One surprising compatibility issue this raises is that constructs such as "[\w-_]" are now disallowed, as our documentation has always said they should be: character classes can't be endpoints of a range. Previously, because \w was just a macro for "[:alnum:]_", such a construct was read as "[[:alnum:]_-_]", so it was accepted so long as the character after "-" was numerically greater than or equal to "_". Some implementation cleanup along the way: * Remove the lexnest() hack, and in consequence clean up wordchrs() to not interact with the lexer. * Fix colorcomplement() to not be O(N^2) in the number of colors involved. * Get rid of useless-as-far-as-I-can-see calls of element() on single-character character element names in brackpart(). element() always maps these to the character itself, and things would be quite broken if it didn't --- should "[a]" match something different than "a" does? Besides, the shortcut path in brackpart() wasn't doing this anyway, making it even more inconsistent. Discussion: https://postgr.es/m/2845172.1613674385@sss.pgh.pa.us Discussion: https://postgr.es/m/3220564.1613859619@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/2a0af7fe460eb46f9af996075972bf7c2e3f211d

  • Change regex \D and \W shorthands to always match newlines. Newline is certainly not a digit, nor a word character, so it is sensible that it should match these complemented character classes. Previously, \D and \W acted that way by default, but in newline-sensitive mode ('n' or 'p' flag) they did not match newlines. This behavior was previously forced because explicit complemented character classes don't match newlines in newline-sensitive mode; but as of the previous commit that implementation constraint no longer exists. It seems useful to change this because the primary real-world use for newline-sensitive mode seems to be to match the default behavior of other regex engines such as Perl and Javascript ... and their default behavior is that these match newlines. The old behavior can be kept by writing an explicit complemented character class, i.e. [^[:digit:]] or [^[:word:]]. (This means that \D and \W are not exactly equivalent to those strings, but they weren't anyway.) Discussion: https://postgr.es/m/3220564.1613859619@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/7dc13a0f0805a353cea0455ed95701322b39d4dd

  • Doc: remove src/backend/regex/re_syntax.n. We aren't publishing this file as documentation, and it's been much more haphazardly maintained than the real docs in func.sgml, so let's just drop it. I think the only reason I included it in commit 7bcc6d98f was that the Berkeley-era sources had had a man page in this directory. Discussion: https://postgr.es/m/4099447.1614186542@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/301ed8812e3f8b102b85e1f5a59e95990ed9a868

  • Fix list-manipulation bug in WITH RECURSIVE processing. makeDependencyGraphWalker and checkWellFormedRecursionWalker thought they could hold onto a pointer to a list's first cons cell while the list was modified by recursive calls. That was okay when the cons cell was actually separately palloc'd ... but since commit 1cff1b95a, it's quite unsafe, leading to core dumps or incorrect complaints of faulty WITH nesting. In the field this'd require at least a seven-deep WITH nest to cause an issue, but enabling DEBUG_LIST_MEMORY_USAGE allows the bug to be seen with lesser nesting depths. Per bug #16801 from Alexander Lakhin. Back-patch to v13. Michael Paquier and Tom Lane Discussion: https://postgr.es/m/16801-393c7922143eaa4d@postgresql.org https://git.postgresql.org/pg/commitdiff/80ca8464fe02296c8efefd53746e6d6a3f456d1e

  • Improve memory management in regex compiler. The previous logic here created a separate pool of arcs for each state, so that the out-arcs of each state were physically stored within it. Perhaps this choice was driven by trying to not include a "from" pointer within each arc; but Spencer gave up on that idea long ago, and it's hard to see what the value is now. The approach turns out to be fairly disastrous in terms of memory consumption, though. In the first place, NFAs built by this engine seem to have about 4 arcs per state on average, with a majority having only one or two out-arcs. So pre-allocating 10 out-arcs for each state is already cause for a factor of two or more bloat. Worse, the NFA optimization phase moves arcs around with abandon. In a large NFA, some of the states will have hundreds of out-arcs, so towards the end of the optimization phase we have a significant number of states whose arc pools have room for hundreds of arcs each, even though only a few of those arcs are in use. We have seen real-world regexes in which this effect bloats the memory requirement by 25X or even more. Hence, get rid of the per-state arc pools in favor of a single arc pool for the whole NFA, with variable-sized allocation batches instead of always asking for 10 at a time. While we're at it, let's batch the allocations of state structs too, to further reduce the malloc traffic. This incidentally allows moveouts() to be optimized in a similar way to moveins(): when moving an arc to another state, it's now valid to just re-link the same arc struct into a different outchain, where before the code invariants required us to make a physically new arc and then free the old one. These changes reduce the regex compiler's typical space consumption for average-size regexes by about a factor of two, and much more for large or complicated regexes. In a large test set of real-world regexes, we formerly had half a dozen cases that failed with "regular expression too complex" due to exceeding the REG_MAX_COMPILE_SPACE limit (about 150MB); we would have had to raise that limit to something close to 400MB to make them work with the old code. Now, none of those cases need more than 13MB to compile. Furthermore, the test set is about 10% faster overall due to less malloc traffic. Discussion: https://postgr.es/m/168861.1614298592@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/0fc1af174cf7113445e116feb2813405b838a47d

  • Doc: further clarify libpq's description of connection string URIs. Break the synopsis into named parts to make it less confusing. Make more than zero effort at applying SGML markup. Do a bit of copy-editing of nearby text. The synopsis revision is by Alvaro Herrera and Paul Förster, the rest is my fault. Back-patch to v10 where multi-host connection strings appeared. Discussion: https://postgr.es/m/6E752D6B-487C-463E-B6E2-C32E7FB007EA@gmail.com https://git.postgresql.org/pg/commitdiff/4e90052c46c7751779ed83627676ed5e74ebe6d4

Thomas Munro pushed:

Michaël Paquier pushed:

Peter Eisentraut pushed:

Fujii Masao pushed:

Magnus Hagander pushed:

  • Fix docs build for website styles. Building the docs with STYLE=website referenced a stylesheet that long longer exists on the website, since we changed it to use versioned references. To make it less likely for this to happen again, point to a single stylesheet on the website which will in turn import the required one. That puts the process entirely within the scope of the website repository, so next time a version is switched that's the only place changes have to be made, making them less likely to be missed. Per (off-list) discussion with Peter Geoghegan and Jonathan Katz. https://git.postgresql.org/pg/commitdiff/d22d0fa937616a3112f69ebd6a6ed4f039162441

Álvaro Herrera pushed:

Amit Kapila pushed:

Peter Geoghegan pushed:

  • Use full 64-bit XIDs in deleted nbtree pages. Otherwise we risk "leaking" deleted pages by making them non-recyclable indefinitely. Commit 6655a729 did the same thing for deleted pages in GiST indexes. That work was used as a starting point here. Stop storing an XID indicating the oldest bpto.xact across all deleted though unrecycled pages in nbtree metapages. There is no longer any reason to care about that condition/the oldest XID. It only ever made sense when wraparound was something _bt_vacuum_needs_cleanup() had to consider. The btm_oldest_btpo_xact metapage field has been repurposed and renamed. It is now btm_last_cleanup_num_delpages, which is used to remember how many non-recycled deleted pages remain from the last VACUUM (in practice its value is usually the precise number of pages that were _newly deleted_ during the specific VACUUM operation that last set the field). The general idea behind storing btm_last_cleanup_num_delpages is to use it to give _some_ consideration to non-recycled deleted pages inside _bt_vacuum_needs_cleanup() -- though never too much. We only really need to avoid leaving a truly excessive number of deleted pages in an unrecycled state forever. We only do this to cover certain narrow cases where no other factor makes VACUUM do a full scan, and yet the index continues to grow (and so actually misses out on recycling existing deleted pages). These metapage changes result in a clear user-visible benefit: We no longer trigger full index scans during VACUUM operations solely due to the presence of only 1 or 2 known deleted (though unrecycled) blocks from a very large index. All that matters now is keeping the costs and benefits in balance over time. Fix an issue that has been around since commit 857f9c36, which added the "skip full scan of index" mechanism (i.e. the _bt_vacuum_needs_cleanup() logic). The accuracy of btm_last_cleanup_num_heap_tuples accidentally hinged upon when the source value gets stored. We now always store btm_last_cleanup_num_heap_tuples in btvacuumcleanup(). This fixes the issue because IndexVacuumInfo.num_heap_tuples (the source field) is expected to accurately indicate the state of the table _after_ the VACUUM completes inside btvacuumcleanup(). A backpatchable fix cannot easily be extracted from this commit. A targeted fix for the issue will follow in a later commit, though that won't happen today. I (pgeoghegan) have chosen to remove any mention of deleted pages in the documentation of the vacuum_cleanup_index_scale_factor GUC/param, since the presence of deleted (though unrecycled) pages is no longer of much concern to users. The vacuum_cleanup_index_scale_factor description in the docs now seems rather unclear in any case, and it should probably be rewritten in the near future. Perhaps some passing mention of page deletion will be added back at the same time. Bump XLOG_PAGE_MAGIC due to nbtree WAL records using full XIDs now. Author: Peter Geoghegan pg@bowt.ie Reviewed-By: Masahiko Sawada <sawada.mshk@gmail.com> Discussion: https://postgr.es/m/CAH2-WznpdHvujGUwYZ8sihX=d5u-tRYhi-F4wnV2uN2zHpMUXw@mail.gmail.com https://git.postgresql.org/pg/commitdiff/e5d8a999030418a1b9e53d5f15ccaca7ed674877

  • VACUUM VERBOSE: Count "newly deleted" index pages. Teach VACUUM VERBOSE to report on pages deleted by the current VACUUM operation -- these are newly deleted pages. VACUUM VERBOSE continues to report on the total number of deleted pages in the entire index (no change there). The former is a subset of the latter. The distinction between each category of deleted index page only arises with index AMs where page deletion is supported and is decoupled from page recycling for performance reasons. This is follow-up work to commit e5d8a999, which made nbtree store 64-bit XIDs (not 32-bit XIDs) in pages at the point at which they're deleted. Note that the btm_last_cleanup_num_delpages metapage field added by that commit usually gets set to pages_newly_deleted. The exceptions (the scenarios in which they're not equal) all seem to be tricky cases for the implementation (of page deletion and recycling) in general. Author: Peter Geoghegan pg@bowt.ie Discussion: https://postgr.es/m/CAH2-WznpdHvujGUwYZ8sihX%3Dd5u-tRYhi-F4wnV2uN2zHpMUXw%40mail.gmail.com https://git.postgresql.org/pg/commitdiff/2376361839091b0dcdcc0b77f938b809b5f21646

David Rowley pushed:

  • Add TID Range Scans to support efficient scanning ranges of TIDs. This adds a new executor node named TID Range Scan. The query planner will generate paths for TID Range scans when quals are discovered on base relations which search for ranges on the table's ctid column. These ranges may be open at either end. For example, WHERE ctid >= '(10,0)'; will return all tuples on page 10 and over. To support this, two new optional callback functions have been added to table AM. scan_set_tidrange is used to set the scan range to just the given range of TIDs. scan_getnextslot_tidrange fetches the next tuple in the given range. For AMs were scanning ranges of TIDs would not make sense, these functions can be set to NULL in the TableAmRoutine. The query planner won't generate TID Range Scan Paths in that case. Author: Edmund Horner, David Rowley Reviewed-by: David Rowley, Tomas Vondra, Tom Lane, Andres Freund, Zhihong Yu Discussion: https://postgr.es/m/CAMyN-kB-nFTkF=VA_JPwFNo08S0d-Yk0F741S2B7LDmYAi8eyA@mail.gmail.com https://git.postgresql.org/pg/commitdiff/bb437f995d47405ecd92cf66df71f7f7e40ed460

  • Add missing TidRangeScan readfunc. Mistakenly forgotten in bb437f995 https://git.postgresql.org/pg/commitdiff/977b2c08535f2a82ba7c310c88420cbbca1772e8

Noah Misch pushed:

Pending Patches

Justin Pryzby sent in another revision of a patch to make INSERT SELECT use BulkInsertState and multi_insert, check for volatile defaults to ensure that any dependencies on them not be lost, make COPY flush the multi-insert buffer based on accumulated size of tuples, rather than line length, and check tuple size for a more accurate measure of chunk size when computing when to flush the buffer.

Hou Zhijie sent in another revision of a patch to add one GUC and one per-table option, both named enable_parallel_dml, to control whether DMLs include an option to execute in parallel.

Bharath Rupireddy sent in another revision of a patch to add GUCs both at the FDW level and at the foreign server level called keep_connections.

Masahiko Sawada sent in a patch to add a check whether or not to do index vacuum (and heap vacuum) based on whether or not 1% of all heap pages have an LP_DEAD line pointer.

Shenhao Wang sent in a patch to make --enable-coverage succeed without finding lcov, as the actual coverage tests can run without it.

Jim Mlodgenski sent in a patch to add a parser hook.

Mats Kindahl sent in a patch to a callback to TableAccessMethod that is called when the table should be scheduled for unlinking, and implements the method for the heap access method.

Justin Pryzby sent in three more revisions of a patch to report text parameters during errors in typinput, and exercise parameter output on error with binary parameters.

Daniel Gustafsson sent in two more revisions of a patch to make it possible to use NSS for libpq's TLS backend.

Jan Wieck sent in another revision of a patch to make the wire protocol pluggable and use same to answer via telnet.

Justin Pryzby sent in another revision of a patch to touch up the documentation for the upcoming release.

Iwata Aya and Álvaro Herrera traded patches to improve libpq tracing capabilities.

Amit Kapila sent in a patch to update the docs and comments for decoding of prepared xacts to match the current behavior.

Daniel Gustafsson sent in another revision of a patch to check the version of target cluster binaries in pg_upgrade.

Mark Rofail sent in another revision of a patch to implement foreign key arrays.

Matthias van de Meent sent in another revision of a patch to add progress-reported components for COPY progress reporting including a new view, pg_stat_progress_copy, add backlinks to progress reporting documentation, and add regression tests for same.

Dilip Kumar sent in three more revisions of a patch to provide a new interface to get the recovery pause status, pg_get_wal_replay_pause_state, that returns the actual status of the recovery pause i.e.'not paused' if pause is not requested, 'pause requested' if pause is requested but recovery is not yet paused and 'paused' if recovery is actually paused.

KaiGai Kohei sent in a patch to add binary input/output handlers to contrib/cube.

Georgios Kokolatos sent in another revision of a patch to make dbsize more consistent.

Mark Dilger sent in another revision of a patch to add pg_amcheck, a command line interface for running amcheck's verifications against tables and indexes.

John Naylor sent in two more revisions of a patch to make it possible to verify utf-8 using SIMD instructions.

Hayato Kuroda sent in three revisions of a patch to refactor ECPGconnect and allow IPv6 connections there.

Amit Langote, Greg Nancarrow, and Amit Kapila traded patches to make it possible to execute INSERT (INTO ... SELECT ...) with multiple workers.

Julien Rouhaud sent in another revision of a patch to add a new COLLATION option to REINDEX.

John Naylor sent in two revisions of a patch to allow inserting tuples into almost-empty pages.

Paul Martinez sent in two more revisions of a patch to document the effect of max_replication_slots on the subscriber side.

Ajin Cherian and Amit Kapila traded patches to avoid repeated decoding of prepared transactions after the restart, and add an option to enable two-phase commits in pg_create_logical_replication_slot.

Peter Eisentraut sent in another revision of a patch to fix use of cursor sensitivity terminology to match that in the SQL standard, removes the claim that sensitive cursors are supported, and adds a new option, ASENSITIVE, to cursors, that being the default behavior.

Benoit Lobréau sent in a patch to document in more detail how archive_command fails based on the signal it was sent, and whether it's reported in pg_stat_archiver.

Peter Eisentraut sent in another revision of a patch to set SNI for SSL connections from the client, which allows an SNI-aware proxy to route connections.

Peter Smith sent in three more revisions of a patch to implement logical decoding of two-phase transactions.

Amit Kapila sent in another revision of a patch to update documentation of logical replication to include the recently added logical replication configuration settings, and mention the fact that table synchronization workers are now using replication origins to track progress.

Thomas Munro sent in another revision of a patch to replace buffer I/O locks with condition variables.

Amit Langote sent in another revision of a patch to fix a misbehavior of partition row movement by ensuring that foreign key triggers are created on partitioned tables, and use same to enforce foreign keys correctly during cross-partition updates.

Thomas Munro sent in another revision of a patch to prevent latches from sending signals to processes that aren't currently sleeping, use SIGURG rather than SIGUSR1 for latches, use signalfd for epoll latches, which cuts down on system calls and other overheads by waiting on a signalfd instead of a signal handler and self-pipe, and use EVFILT_SIGNAL for kqueue latches.

Michaël Paquier sent in a patch to add a --tablespace option to reindexdb, matching the recently added capability for REINDEX.

Kota Miyake sent in a patch to fix pgbench's reporting of database name in errors when both PGUSER and PGPORT are set.

Amul Sul sent in another revision of a patch to implement wal prohibit state using a global barrier, error or Assert before START_CRIT_SECTION for WAL write, and document same.

Justin Pryzby sent in another revision of a patch to make it possible to use CREATE INDEX CONCURRENTLY on a partitioned table.

Jacob Champion sent in another revision of a patch to save the user's original authenticated identity for logging.

Daniel Gustafsson sent in another revision of a patch to disallow SSL compression by ignoring the option that would have turned it on. A later patch will remove the option entirely, now that it's deprecated.

Daniel Gustafsson sent in a patch to remove the defaults from libpq's authtype parameter, as it has been deprecated.

Álvaro Herrera sent in another revision of a patch to implement ALTER TABLE .. DETACH PARTITION CONCURRENTLY.

Dilip Kumar sent in two more revisions of a patch to make it possible to set the compression type for a table.

Euler Taveira de Oliveira sent in another revision of a patch to implement row filtering for logical replication using an optional WHERE clause in the DDL for PUBLICATIONs.

Thomas Munro sent in another revision of a patch to introduce symbolic names for FeBeWaitSet positions, and use FeBeWaitSet for walsender.c.

Thomas Munro sent in another revision of a patch to use condition variables for ProcSignalBarriers, allow condition variables to be used in interrupt code, and use a global barrier to fix DROP TABLESPACE on Windows by making it by force all backends to close all fds on that platform.

Andrey Borodin sent in a patch to use different compression methods for FPI.

Julien Rouhaud sent in a patch to change the explicit alignment use in pg_prewarm and pg_stat_statements to CACHELINEALIGN, and updates the alignment in hash_estimate_size() to an estimate of what ShmemInitHash will actually consume based on CACHELINEALIGN.

Thomas Munro sent in a patch to remove latch.c workaround for Linux < 2.6.27.

Peter Eisentraut sent in another revision of a patch to psql which makes it show all query results by default.

Jeff Janes sent in a patch to make SCRAM's behavior match MD5's by reporting in a DETAIL message when the password does not match for a user.

Joel Jacobson sent in a patch to implement a regexp_positions() function.

Paul Förster sent in a patch to mention database URIs in psql's --help output.

Justin Pryzby sent in a patch to refactor ATExec{En,Dis}ableRowSecurity in the style of ATExecForceNoForceRowSecurity, and do some further refactoring.

Justin Pryzby sent in a patch to implement ALTER TABLE SET TABLE ACCESS METHOD.