PostgreSQL Weekly News - November 1, 2020

Posted on 2020-11-02 by PWN

PostgreSQL Weekly News - November 1, 2020

The next Commitfest for PostgreSQL 14 has begun. If you are the author of a patch, please make sure to follow up on the reviews so your contribution can get ready to be committed.

PostgreSQL Product News

pgbitmap 0.93, a space-optimised, non-sparse, bitmap type, released.

Pgpool-II 4.2 beta1

pglogical 2.3.3, a logical-WAL-based replication system for PostgreSQL, released.

pg_activity 1.6.2, a top-like application for PostgreSQL server activity monitoring, released.

pg_dumpbinary 2.3, a program used to dump a PostgreSQL database in binary format, released.

pgCenter 0.6.6, a command-line admin tool for observing and troubleshooting PostgreSQL, released.

PostgreSQL in the News

Planet PostgreSQL:

PostgreSQL Weekly News is brought to you this week by David Fetter

Submit news and announcements by Sunday at 3:00pm PST8PDT to

Applied Patches

David Rowley pushed:

  • Prevent overly large and NaN row estimates in relations. Given a query with enough joins, it was possible that the query planner, after multiplying the row estimates with the join selectivity that the estimated number of rows would exceed the limits of the double data type and become infinite. To give an indication on how extreme a case is required to hit this, the particular example case reported required 379 joins to a table without any statistics, which resulted in the 1.0/DEFAULT_NUM_DISTINCT being used for the join selectivity. This eventually caused the row estimates to go infinite and resulted in an assert failure in initial_cost_mergejoin() where the infinite row estimated was multiplied by an outerstartsel of 0.0 resulting in NaN. The failing assert verified that NaN <= Inf, which is false. To get around this we use clamp_row_est() to cap row estimates at a maximum of 1e100. This value is thought to be low enough that costs derived from it would remain within the bounds of what the double type can represent. Aside from fixing the failing Assert, this also has the added benefit of making it so add_path() will still receive proper numerical values as costs which will allow it to make more sane choices when determining the cheaper path in extreme cases such as the one described above. Additionally, we also get rid of the isnan() checks in the join costing functions. The actual case which originally triggered those checks to be added in the first place never made it to the mailing lists. It seems likely that the new code being added to clamp_row_est() will result in those becoming checks redundant, so just remove them. The fairly harmless assert failure problem does also exist in the backbranches, however, a more minimalistic fix will be applied there. Reported-by: Onder Kalaci Reviewed-by: Tom Lane Discussion:

  • Optimize a few list_delete_ptr calls. There is a handful of places where we called list_delete_ptr() to remove some element from a List. In many of these places we know, or with very little additional effort know the index of the ListCell that we need to remove. Here we change all of those places to instead either use one of; list_delete_nth_cell(), foreach_delete_current() or list_delete_last(). Each of these saves from having to iterate over the list to search for the element to remove by its pointer value. There are some small performance gains to be had by doing this, but in the general case, none of these lists are likely to be very large, so the lookup was probably never that expensive anyway. However, some of the calls are in fairly hot code paths, e.g process_equivalence(). So any small gains there are useful. Author: Zhijie Hou and David Rowley Discussion:

  • Fix incorrect parameter name in a function header comment. Author: Zhijie Hou Discussion: Backpatch-through: 12, where the mistake was introduced

Michaël Paquier pushed:

  • Fix potential memory leak in pgcrypto. When allocating a EVP context, it would have been possible to leak some memory allocated directly by OpenSSL, that PostgreSQL lost track of if the initialization of the context allocated failed. The cleanup can be done with EVP_MD_CTX_destroy(). Note that EVP APIs exist since OpenSSL 0.9.7 and we have in the tree equivalent implementations for older versions since ce9b75d (code removed with 9b7cd59a as of 10~). However, in 9.5 and 9.6, the existing code makes use of EVP_MD_CTX_destroy() and EVP_MD_CTX_create() without an equivalent implementation when building the tree with OpenSSL 0.9.6 or older, meaning that this code is in reality broken with such versions since it got introduced in e2838c5. As we have heard no complains about that, it does not seem worth bothering with in 9.5 and 9.6, so I have left that out for simplicity. Author: Michael Paquier Discussion: Backpatch-through: 9.5

  • Review format of code generated by 80f8eb7 has added to the normalization quick check headers some code generated by that is incompatible with the settings of gitattributes for this repository, as whitespaces followed a set of tabs for the first element of a line in the table. Instead of adding a new exception to gitattributes, rework the format generated so as a right padding with spaces is used instead of a left padding. This keeps the table generated in a readable shape with its set of columns, making unnecessary an update of gitattributes. Reported-by: Peter Eisentraut Author: John Naylor Discussion:

  • Improve performance of Unicode {de,re}composition in the backend. This replaces the existing binary search with two perfect hash functions for the composition and the decomposition in the backend code, at the cost of slightly-larger binaries there (35kB in libpgcommon_srv.a). Per the measurements done, this improves the speed of the recomposition and decomposition by up to 30~40 times for the NFC and NFKC conversions, while all other operations get at least 40% faster. This is not as "good" as what libicu has, but it closes the gap a lot as per the feedback from Daniel Verite. The decomposition table remains the same, getting used for the binary search in the frontend code, where we care more about the size of the libraries like libpq over performance as this gets involved only in code paths related to the SCRAM authentication. In consequence, note that the perfect hash function for the recomposition needs to use a new inverse lookup array back to to the existing decomposition table. The size of all frontend deliverables remains unchanged, even with --enable-debug, including libpq. Author: John Naylor Reviewed-by: Michael Paquier, Tom Lane Discussion:

  • Add tab completion for ALTER TABLE .. FORCE ROW LEVEL SECURITY in psql. This completes both the FORCE and NO FORCE options, NO INHERIT needing a small adjustment. Author: Li Japin Discussion:

  • Fix issue with --enable-coverage and the new unicode {de,re}composition code. genhtml has been generating the following warning with this new code: WARNING: function data mismatch at /path/src/common/unicode_norm.c:102 HTML coverage reports care about the uniqueness of functions defined in source files, ignoring any assumptions around CFLAGS. 783f0cc introduced a duplicated definition of get_code_entry(), leading to a warning and potentially some incorrect data generated in the reports. This refactors the code so as the code has only one function declaration, fixing the warning. Oversight in 783f0cc. Reported-by: Tom Lane Author: Michael Paquier Reviewed-by: Tom Lane Discussion:

  • Extend PageIsVerified() to handle more custom options. This is useful for checks of relation pages without having to load the pages into the shared buffers, and two cases can make use of that: page verification in base backups and the online, lock-safe, flavor. Compatibility is kept with past versions using a macro that calls the new extended routine with the set of options compatible with the original version. Extracted from a larger patch by the same author. Author: Anastasia Lubennikova Reviewed-by: Michael Paquier, Julien Rouhaud Discussion:

  • Add CheckBuffer() to check on-disk pages without shared buffer loading. CheckBuffer() is designed to be a concurrent-safe function able to run sanity checks on a relation page without loading it into the shared buffers. The operation is done using a lock on the partition involved in the shared buffer mapping hashtable and an I/O lock for the buffer itself, preventing the risk of false positives due to any concurrent activity. The primary use of this function is the detection of on-disk corruptions for relation pages. If a page is found in shared buffers, the on-disk page is checked if not dirty (a follow-up checkpoint would flush a valid version of the page if dirty anyway), as it could be possible that a page was present for a long time in shared buffers with its on-disk version corrupted. Such a scenario could lead to a corrupted cluster if a host is plugged off for example. If the page is not found in shared buffers, its on-disk state is checked. PageIsVerifiedExtended() is used to apply the same sanity checks as when a page gets loaded into shared buffers. This function will be used by an upcoming patch able to check the state of on-disk relation pages using a SQL function. Author: Julien Rouhaud, Michael Paquier Reviewed-by: Masahiko Sawada Discussion:

  • Add pg_relation_check_pages() to check on-disk pages of a relation. This makes use of CheckBuffer() introduced in c780a7a, adding a SQL wrapper able to do checks for all the pages of a relation. By default, all the fork types of a relation are checked, and it is possible to check only a given relation fork. Note that if the relation given in input has no physical storage or is temporary, then no errors are generated, allowing full-database checks when coupled with a simple scan of pg_class for example. This is not limited to clusters with data checksums enabled, as clusters without data checksums can still apply checks on pages using the page headers or for the case of a page full of zeros. This function returns a set of tuples consisting of: - The physical file where a broken page has been detected (without the segment number as that can be AM-dependent, which can be guessed from the block number for heap). A relative path from PGPATH is used. - The block number of the broken page. By default, only superusers have an access to this function but execution rights can be granted to other users. The feature introduced here is still minimal, and more improvements could be done, like: - Addition of a start and end block number to run checks on a range of blocks, which would apply only if one fork type is checked. - Addition of some progress reporting.

  • Throttling, with configuration parameters in function input or potentially some cost-based GUCs. Regression tests are added for positive cases in the main regression test suite, and TAP tests are added for cases involving the emulation of page corruptions. Bump catalog version. Author: Julien Rouhaud, Michael Paquier Reviewed-by: Masahiko Sawada, Justin Pryzby Discussion:

  • Use correct GetDatum() in pg_relation_check_pages(). UInt32GetDatum() was getting used, while the result needs Int64GetDatum(). Oversight in f2b8839. Per buildfarm member florican. Discussion:

  • Fix incorrect placement of pfree() in pg_relation_check_pages(). This would cause the function to crash when more than one page is considered as broken and reported in the SRF. Reported-by: Noriyoshi Shinoda Discussion:

  • Add error code for encryption failure in pgcrypto. PXE_DECRYPT_FAILED exists already for decryption errors, and an equivalent for encryption did not exist. There is one code path that deals with such failures for OpenSSL but it used PXE_ERR_GENERIC, which was inconsistent. This switches this code path to use the new error PXE_ENCRYPT_FAILED instead of PXE_ERR_GENERIC, making the code used for encryption more consistent with the decryption. Author: Daniel Gustafsson Discussion:

  • Preserve index data in pg_statistic across REINDEX CONCURRENTLY. Statistics associated to an index got lost after running REINDEX CONCURRENTLY, while the non-concurrent case preserves these correctly. The concurrent and non-concurrent operations need to be consistent for the end-user, and missing statistics would force to wait for a new analyze to happen, which could take some time depending on the activity of the existing autovacuum workers. This issue is fixed by copying any existing entries in pg_statistic associated to the old index to the new one. Note that this copy is already done with the data of the index in the stats collector. Reported-by: Fabrízio de Royes Mello Author: Michael Paquier, Fabrízio de Royes Mello Reviewed-by: Justin Pryzby Discussion: Backpatch-through: 12

Amit Kapila pushed:

Peter Eisentraut pushed:

Heikki Linnakangas pushed:

Magnus Hagander pushed:

Tom Lane pushed:

  • Fix list-munging bug that broke SQL function result coercions. Since commit 913bbd88d, check_sql_fn_retval() can either insert type coercion steps in-line in the Query that produces the SQL function's results, or generate a new top-level Query to perform the coercions, if modifying the Query's output in-place wouldn't be safe. However, it appears that the latter case has never actually worked, because the code tried to inject the new Query back into the query list it was passed ... which is not the list that will be used for later processing when we execute the SQL function "normally" (without inlining it). So we ended up with no coercion happening at run-time, leading to wrong results or crashes depending on the datatypes involved. While the regression tests look like they cover this area well enough, through a huge bit of bad luck all the test cases that exercise the separate-Query path were checking either inline-able cases (which accidentally didn't have the bug) or cases that are no-ops at runtime (e.g., varchar to text), so that the failure to perform the coercion wasn't obvious. The fact that the cases that don't work weren't allowed at all before v13 probably contributed to not noticing the problem sooner, too. To fix, get rid of the separate "flat" list of Query nodes and instead pass the real two-level list that is going to be used later. I chose to make the same change in check_sql_fn_statements(), although that has no actual bug, just so that we don't need that data structure at all. This is an API change, as evidenced by the adjustments needed to callers outside functions.c. That's a bit scary to be doing in a released branch, but so far as I can tell from a quick search, there are no outside callers of these functions (and they are sufficiently specific to our semantics for SQL-language functions that it's not apparent why any extension would need to call them). In any case, v13 already changed the API of check_sql_fn_retval() compared to prior branches. Per report from pinker. Back-patch to v13 where this code came in. Discussion:

  • Fix connection string handling in src/bin/scripts/ programs. When told to process all databases, clusterdb, reindexdb, and vacuumdb would reconnect by replacing their --maintenance-db parameter with the name of the target database. If that parameter is a connstring (which has been allowed for a long time, though we failed to document that before this patch), we'd lose any other options it might specify, for example SSL or GSS parameters, possibly resulting in failure to connect. Thus, this is the same bug as commit a45bc8a4f fixed in pg_dump and pg_restore. We can fix it in the same way, by using libpq's rules for handling multiple "dbname" parameters to add the target database name separately. I chose to apply the same refactoring approach as in that patch, with a struct to handle the command line parameters that need to be passed through to connectDatabase. (Maybe someday we can unify the very similar functions here and in pg_dump/pg_restore.) Per Peter Eisentraut's comments on bug #16604. Back-patch to all supported branches. Discussion:

  • Remove the option to build thread_test.c outside configure. Theoretically one could go into src/test/thread and build/run this program there. In practice, that hasn't worked since 96bf88d52, and probably much longer on some platforms (likely including just the sort of hoary leftovers where this test might be of interest). While it wouldn't be too hard to repair the breakage, the fact that nobody has noticed for two years shows that there is zero usefulness in maintaining this build pathway. Let's get rid of it and decree that thread_test.c is *only* meant to be built/used in configure. Given that decision, it makes sense to put thread_test.c under config/ and get rid of src/test/thread altogether, so that's what I did. In passing, update src/test/README, which had been ignored by some not-so-recent additions of subdirectories. Discussion:

  • Fix connection string handling in psql's \connect command. psql's \connect claims to be able to re-use previous connection parameters, but in fact it only re-uses the database name, user name, host name (and possibly hostaddr, depending on version), and port. This is problematic for assorted use cases. Notably, pg_dump[all] emits "\connect databasename" commands which we would like to have re-use all other parameters. If such a script is loaded in a psql run that initially had "-d connstring" with some non-default parameters, those other parameters would be lost, potentially causing connection failure. (Thus, this is the same kind of bug addressed in commits a45bc8a4f and 8e5793ab6, although the details are much different.) To fix, redesign do_connect() so that it pulls out all properties of the old PGconn using PQconninfo(), and then replaces individual properties in that array. In the case where we don't wish to re-use anything, get libpq's default settings using PQconndefaults() and replace entries in that, so that we don't need different code paths for the two cases. This does result in an additional behavioral change for cases where the original connection parameters allowed multiple hosts, say "psql -h host1,host2", and the \connect request allows re-use of the host setting. Because the previous coding relied on PQhost(), it would only permit reconnection to the same host originally selected. Although one can think of scenarios where that's a good thing, there are others where it is not. Moreover, that behavior doesn't seem to meet the principle of least surprise, nor was it documented; nor is it even clear it was intended, since that coding long pre-dates the addition of multi-host support to libpq. Hence, this patch is content to drop it and re-use the host list as given. Per Peter Eisentraut's comments on bug #16604. Back-patch to all supported branches. Discussion:

  • Clean up some unpleasant behaviors in psql's \connect command. The check for whether to complain about not having an old connection to get parameters from was seriously out of date: it had not been rethought when we invented connstrings, nor when we invented the -reuse-previous option. Replace it with a check that throws an error if reuse-previous is active and we lack an old connection to reuse. While that doesn't move the goalposts very far in terms of easing reconnection after a server crash, at least it's consistent. If the user specifies a connstring plus additional parameters (which is invalid per the documentation), the extra parameters were silently ignored. That seems like it could be really confusing, so let's throw a syntax error instead. Teach the connstring code path to re-use the old connection's password in the same cases as the old-style-syntax code path would, ie if we are reusing parameters and the values of username, host/hostaddr, and port are not being changed. Document this behavior, too, since it was unmentioned before. Also simplify the implementation a bit, giving rise to two new and useful properties: if there's a "password=xxx" in the connstring, we'll use it not ignore it, and by default (i.e., except with --no-password) we will prompt for a password if the re-used password or connstring password doesn't work. The previous code just failed if the re-used password didn't work. Given the paucity of field complaints about these issues, I don't think that they rise to the level of back-patchable bug fixes, and in any case they might represent undesirable behavior changes in minor releases. So no back-patch. Discussion:

  • Add documentation and tests for quote marks in ECPG literal queries. ECPG's PREPARE ... FROM and EXECUTE IMMEDIATE can optionally take the target query as a simple literal, rather than the more usual string-variable reference. This was previously documented as being a C string literal, but that's a lie in one critical respect: you can't write a data double quote as \" in such literals. That's because the lexer is in SQL mode at this point, so it'll parse double-quoted strings as SQL identifiers, within which backslash is not special, so \" ends the literal. I looked into making this work as documented, but getting the lexer to switch behaviors at just the right point is somewhere between very difficult and impossible. It's not really worth the trouble, because these cases are next to useless: if you have a fixed SQL statement to execute or prepare, you might as well write it as a direct EXEC SQL, saving the messiness of converting it into a string literal and gaining the opportunity for compile-time SQL syntax checking. Instead, let's just document (and test) the workaround of writing a double quote as an octal escape (\042) in such cases. There's no code behavioral change here, so in principle this could be back-patched, but it's such a niche case I doubt it's worth the trouble. Per report from 1250kv. Discussion:

  • Avoid premature de-doubling of quote marks in ECPG strings. If you write the literal 'abc''def' in an EXEC SQL command, that will come out the other end as 'abc'def', triggering a syntax error in the backend. Likewise, "abc""def" is reduced to "abc"def" which is wrong syntax for a quoted identifier. The cause is that the lexer thinks it should emit just one quote mark, whereas what it really should do is keep the string as-is. Add some docs and test cases, too. Although this seems clearly a bug, I fear users wouldn't appreciate changing it in minor releases. Some may well be working around it by applying an extra doubling of affected quotes, as for example sql/dyntest.pgc has been doing. Per investigation of a report from 1250kv, although this isn't exactly what he/she was on about. Discussion:

  • Sync our copy of the timezone library with IANA release tzcode2020d. There's no functional change at all here, but I'm curious to see whether this change successfully shuts up Coverity's warning about a useless strcmp(), which appeared with the previous update. Discussion:

  • Update time zone data files to tzdata release 2020d. DST law changes in Palestine, with a whopping 120 hours' notice. Also some historical corrections for Palestine.

  • Fix broken XML formatting in EXPLAIN output for incremental sorts. The ExplainCloseGroup arguments for incremental sort usage data didn't match the corresponding ExplainOpenGroup. This only matters for XML-format output, which is probably why we'd not noticed. Daniel Gustafsson, per bug #16683 from Frits Jalvingh Discussion:

  • Fix portability issues in new amcheck test. The tests added by commit 866e24d47 failed on big-endian machines due to lack of attention to endianness considerations. Fix that. While here, improve a few small cosmetic things, such as running it through perltidy. Mark Dilger Discussion:

  • Allow psql to re-use connection parameters after a connection loss. Instead of immediately PQfinish'ing a dead connection, save it aside so that we can still extract its parameters for \connect attempts. (This works because PQconninfo doesn't care whether the PGconn is in CONNECTION_BAD state.) This allows developers to reconnect with just \c after a database crash and restart. It's tempting to use the same approach instead of closing the old connection after a failed non-interactive \connect command. However, that would not be very safe: consider a script containing \c db1 user1 live_server \c db2 user2 dead_server \c db3 The script would be expecting to connect to db3 at dead_server, but if we re-use parameters from the first connection then it might successfully connect to db3 at live_server. This'd defeat the goal of not letting a script accidentally execute commands against the wrong database. Discussion:

  • Fix more portability issues in new amcheck code. verify_heapam() wasn't being careful to sanity-check tuple line pointers before using them, resulting in SIGBUS on alignment-picky architectures. Fix that, add some more test coverage. Mark Dilger, some tweaking by me Discussion:

  • Fix ancient bug in ecpg's pthread_once() emulation for Windows. We must not set the "done" flag until after we've executed the initialization function. Otherwise, other threads can fall through the initial unlocked test before initialization is really complete. This has been seen to cause rare failures of ecpg's thread/descriptor test, and it could presumably cause other sorts of misbehavior in threaded ECPG-using applications, since ecpglib relies on pthread_once() in several places. Diagnosis and patch by me, based on investigation by Alexander Lakhin. Back-patch to all supported branches (the bug dates to 2007). Discussion:

  • Fix corner case for a BEFORE ROW UPDATE trigger returning OLD. If the old row has any "missing" attributes that are supposed to be retrieved from an associated tuple descriptor, the wrong things happened because the trigger result is shoved directly into an executor slot that lacks the missing-attribute data. Notably, CHECK-constraint verification would incorrectly see those columns as NULL, and so would RETURNING-list evaluation. Band-aid around this by forcibly expanding the tuple before passing it to the trigger function. (IMO it was a fundamental misdesign to put the missing-attribute data into tuple constraints, which so much of the system considers to be optional. But we're probably stuck with that now, and will have to continue to apply band-aids as we find other places with similar issues.) Back-patch to v12. v11 would also have the issue, except that commit 920311ab1 already applied a similar band-aid. That forced expansion in more cases than seem really necessary, though, so this isn't a directly equivalent fix. Amit Langote, with some cosmetic changes by me Discussion:

  • In INSERT/UPDATE, use the table's real tuple descriptor as target. Previously, ExecInitModifyTable relied on ExecInitJunkFilter, and thence ExecCleanTypeFromTL, to build the target descriptor from the query tlist. While we just checked (in ExecCheckPlanOutput) that the tlist produces compatible output, this is not a great substitute for the relation's actual tuple descriptor that's available from the relcache. For one thing, dropped columns will not be correctly marked attisdropped; it's a bit surprising that we've gotten away with that this long. But the real reason for being concerned with this is that using the table's descriptor means that the slot will have correct attrmissing data, allowing us to revert the klugy fix of commit ba9f18abd. (This commit undoes that one's changes in trigger.c, but keeps the new test case.) Thus we can solve the bogus-trigger-tuple problem with fewer cycles rather than more. No back-patch, since this doesn't fix any additional bug, and it seems somewhat more likely to have unforeseen side effects than ba9f18abd's narrow fix. Discussion:

  • Doc: improve explanation of how to use our code coverage infrastructure. The reference to running "make coverage" in a subdirectory was a bit obscure, so clarify what happens when you do that. Do a little desultory copy-editing, too. Per a question from Peter Smith. Discussion:

  • Fix foreign-key selectivity estimation in the presence of constants. get_foreign_key_join_selectivity() looks for join clauses that equate the two sides of the FK constraint. However, if we have a query like "WHERE fktab.a = pktab.a and fktab.a = 1", it won't find any such join clause, because equivclass.c replaces the given clauses with "fktab.a = 1 and pktab.a = 1", which can be enforced at the scan level, leaving nothing to be done for column "a" at the join level. We can fix that expectation without much trouble, but then a new problem arises: applying the foreign-key-based selectivity rule produces a rowcount underestimate, because we're effectively double-counting the selectivity of the "fktab.a = 1" clause. So we have to cancel that selectivity out of the estimate. To fix, refactor process_implied_equality() so that it can pass back the new RestrictInfo to its callers in equivclass.c, allowing the generated "fktab.a = 1" clause to be saved in the EquivalenceClass's ec_derives list. Then it's not much trouble to dig out the relevant RestrictInfo when we need to adjust an FK selectivity estimate. (While at it, we can also remove the expensive use of initialize_mergeclause_eclasses() to set up the new RestrictInfo's left_ec and right_ec pointers. The equivclass.c code can set those basically for free.) This seems like clearly a bug fix, but I'm hesitant to back-patch it, first because there's some API/ABI risk for extensions and second because we're usually loath to destabilize plan choices in stable branches. Per report from Sigrid Ehrenreich. Discussion: Discussion:

  • Don't use custom OID symbols in pg_proc.dat. We have a perfectly good convention for OID macros for built-in functions already, so making custom symbols is just introducing unnecessary deviation from the convention. Remove the one case that had snuck in, and add an error check in to discourage future instances. Although this touches pg_proc.dat, there's no need for a catversion bump since the actual catalog data isn't changed. John Naylor Discussion:

  • Calculate extraUpdatedCols in query rewriter, not parser. It's unsafe to do this at parse time because addition of generated columns to a table would not invalidate stored rules containing UPDATEs on the table ... but there might now be dependent generated columns that were not there when the rule was made. This also fixes an oversight that rewriteTargetView failed to update extraUpdatedCols when transforming an UPDATE on an updatable view. (Since the new calculation is downstream of that, rewriteTargetView doesn't actually need to do anything; but before, there was a demonstrable bug there.) In v13 and HEAD, this leads to easily-visible bugs because (since commit c6679e4fc) we won't recalculate generated columns that aren't listed in extraUpdatedCols. In v12 this bitmap is mostly just used for trigger-firing decisions, so you'd only notice a problem if a trigger cared whether a generated column had been updated. I'd complained about this back in May, but then forgot about it until bug #16671 from Michael Paul Killian revived the issue. Back-patch to v12 where this field was introduced. If existing stored rules contain any extraUpdatedCols values, they'll be ignored because the rewriter will overwrite them, so the bug will be fixed even for existing rules. (But note that if someone were to update to 13.1 or 12.5, store some rules with UPDATEs on tables having generated columns, and then downgrade to a prior minor version, they might observe issues similar to what this patch fixes. That seems unlikely enough to not be worth going to a lot of effort to fix.) Discussion: Discussion:

  • Use mode "r" for popen() in psql's evaluate_backtick(). In almost all other places, we use plain "r" or "w" mode in popen() calls (the exceptions being for COPY data). This one has been overlooked (possibly because it's buried in a ".l" flex file?), but it's using PG_BINARY_R. Kensuke Okamura complained in bug #16688 that we fail to strip \r when stripping the trailing newline from a backtick result string. That's true enough, but we'd also fail to convert embedded \r\n cleanly, which also seems undesirable. Fixing the popen() mode seems like the best way to deal with this. It's been like this for a long time, so back-patch to all supported branches. Discussion:

  • Doc: clean up verify_heapam() documentation. I started with the intention of just suppressing a PDF build warning by removing the example output, but ended up doing more: correcting factual errors in the function's signature, moving a bunch of generalized handwaving into the "Using amcheck Effectively" section which seemed a better place for it, and improving wording and markup a little bit. Discussion:

  • Doc: clean up pg_relation_check_pages() documentation. Commit f2b883969 did not get the memo about the new formatting style for tables documenting built-in functions. I noticed because of a PDF build warning about an overwidth table.

  • Don't use custom OID symbols in pg_type.dat, either. On the same reasoning as in commit 36b931214, forbid using custom oid_symbol macros in pg_type as well as pg_proc, so that we always rely on the predictable macro names generated by We do continue to grant grandfather status to the names CASHOID and LSNOID, although those are now considered deprecated aliases for the preferred names MONEYOID and PG_LSNOID. This is because there's likely to be client-side code using the old names, and this bout of neatnik-ism doesn't quite seem worth breaking client code. There might be a case for grandfathering EVTTRIGGEROID, too, since externally-maintained PLs may reference that symbol. But renaming such references to EVENT_TRIGGEROID doesn't seem like a particularly heavy lift --- we make far more significant backend API changes in every major release. For now I didn't add that, but we could reconsider if there's pushback. The other names changed here seem pretty unlikely to have any outside uses. Again, we could add alias macros if there are complaints, but for now I didn't. As before, no need for a catversion bump. John Naylor Discussion:

  • Stabilize timetz test across DST transitions. The timetz test cases I added in commit a9632830b were unintentionally sensitive to whether or not DST is active in the PST8PDT time zone. Thus, they'll start failing this coming weekend, as reported by Bernhard M. Wiedemann in bug #16689. Fortunately, DST-awareness is not significant to the purpose of these test cases, so we can just force them all to PDT (DST hours) to preserve stability of the results. Back-patch to v10, as the prior patch was. Discussion:

  • Doc: clarify description for pg_constraint.convalidated. Jimmy Angelakos Discussion:

  • Fix assertion failure in check_new_partition_bound(). Commit 6b2c4e59d was overly confident about not being able to see a negative cmpval result from partition_range_bsearch(). Adjust the code to cope with that. Report and patch by Amul Sul; some additional cosmetic changes by me Discussion:

  • Avoid null pointer dereference if error result lacks SQLSTATE. Although error results received from the backend should always have a SQLSTATE field, ones generated by libpq won't, making this code vulnerable to a crash after, say, untimely loss of connection. Noted by Coverity. Oversight in commit 403a3d91c. Back-patch to 9.5, as that was.

Álvaro Herrera pushed:

Robert Haas pushed:

Bruce Momjian pushed:

Andres Freund pushed:

  • Centralize horizon determination for temp tables, fixing bug due to skew. This fixes a bug in the edge case where, for a temp table, heap_page_prune() can end up with a different horizon than heap_vacuum_rel(). Which can trigger errors like "ERROR: cannot freeze committed xmax ...". The bug was introduced due to interaction of a7212be8b9e "Set cutoff xmin more aggressively when vacuuming a temporary table." with dc7420c2c92 "snapshot scalability: Don't compute global horizons while building snapshots.". The problem is caused by lazy_scan_heap() assuming that the only reason its HeapTupleSatisfiesVacuum() call would return HEAPTUPLE_DEAD is if the tuple is a HOT tuple, or if the tuple's inserting transaction has aborted since the heap_page_prune() call. But after a7212be8b9e that was also possible in other cases for temp tables, because heap_page_prune() uses a different visibility test after dc7420c2c92. The fix is fairly simple: Move the special case logic for temp tables from vacuum_set_xid_limits() to the infrastructure introduced in dc7420c2c92. That ensures that the horizon used for pruning is at least as aggressive as the one used by lazy_scan_heap(). The concrete horizon used for temp tables is slightly different than the logic in dc7420c2c92, but should always be as aggressive as before (see comments). A significant benefit to centralizing the logic procarray.c is that now the more aggressive horizons for temp tables does not just apply to VACUUM but also to e.g. HOT pruning and the nbtree killtuples logic. Because isTopLevel is not needed by vacuum_set_xid_limits() anymore, I undid the the related changes from a7212be8b9e. This commit also adds an isolation test ensuring that the more aggressive vacuuming and pruning of temp tables keeps working. Debugged-By: Amit Kapila amit.kapila16 [AT] Debugged-By: Tom Lane tgl [AT] Debugged-By: Ashutosh Sharma ashu.coek88 [AT] Author: Andres Freund andres [AT] Discussion: Discussion:

  • Fix wrong data table horizon computation during backend startup. When ComputeXidHorizons() was called before MyDatabaseOid is set, e.g. because a dead row in a shared relation is encountered during InitPostgres(), the horizon for normal tables was computed too aggressively, ignoring all backends connected to a database. During subsequent pruning in a data table the too aggressive horizon could end up still being used, possibly leading to still needed tuples being removed. Not good. This is a bug in dc7420c2c92, which the test added in 94bc27b5768 made visible, if run with force_parallel_mode set to regress. In that case the bug is reliably triggered, because "pruning_query" is run in a parallel worker and the start of that parallel worker is likely to encounter a dead row in pg_database. The fix is trivial: Compute a more pessimistic data table horizon if MyDatabaseId is not yet known. Author: Andres Freund Discussion:

Noah Misch pushed:

Pending Patches

Peter Eisentraut sent in a patch to add a default_result_formats GUC, which specifies the default result formats by data type for rows returned in the extended query protocol when no result formats are specified in the Bind message.

Álvaro Herrera sent in another revision of a patch to add tracing to libpq.

Julien Rouhaud sent in two revisions of a patch to handle = operator in pg_trgm.

Álvaro Herrera sent in another revision of a patch to add batch and pipelining support to libpq.

Craig Ringer sent in a patch to report libpq version and configuration.

Jakub Wartak sent in two revisions of a patch to add "IO read time" log message to autoanalyze.

Alexander Korotkov and Andrey Borodin traded patches to use a shared lock, as opposed to an exclusive control lock, in GetMultiXactIdMembers for offsets and members, make the MultiXact local cache size configurable, add a conditional variable to wait for next MultXact offset in corner case, and add GUCs to tune MultiXact SLRUs.

Pavel Borisov sent in another revision of a patch to make GROUP BY more efficient by making it possible to reorder the columns.

Peter Geoghegan sent in another revision of a patch to add sort_template.h for making fast sort functions, and add delete deduplication to nbtree.

Michaël Paquier sent in another revision of a patch to fix checksum verification in base backups for zero page headers.

Ajin Cherian and Peter Smith traded patches to implement logical decoding for two-phase transactions.

Zhenghua Lyu sent in a patch to consider the case when stanullfrac is 1.0 in get_variable_numdistinct.

Konstantin Knizhnik sent in five more revisions of a patch to implement compression in libpq.

Justin Pryzby sent in a patch to remove deprecated v8.2 containment operators.

Peter Eisentraut sent in another revision of a patch to support for writing CREATE FUNCTION and CREATE PROCEDURE statements for language SQL with a function body that conforms to the SQL standard and is portable to other implementations.

Ibrar Ahmed sent in another revision of a patch to implement temporal PK/FK deletes and updates.

Justin Pryzby sent in another revision of a patch to pg_dump to output DISABLE/ENABLE for child triggers if their state does not match their parent.

Arseny Sher sent in a patch to fix a use-after-free.

Amit Kapila sent in a patch to make some minor improvements to the description of spilled counters in pg_stat_replication_slots view.

Dilip Kumar sent in three more revisions of a patch to implement custom compression methods for tables and ways to change same.

John Naylor sent in two revisions of a patch to use the standard symbol for the builtin function heap_tableam_handler().

Michaël Paquier sent in another revision of a patch to enable online checksum verification in the backend.

Greg Nancarrow sent in two more revisions of a patch to enable parallel SELECT for "INSERT INTO ... SELECT ...", where it is safe to do so, and enable parallel INSERT and/or SELECT for "INSERT INTO ... SELECT ...", where it is safe to do so.

Masahiko Sawada and Bertrand Drouvot traded patches to log the standby recovery conflict waits.

Vigneshwaran C sent in another revision of a patch to parallelize parts of COPY.

Daniel Gustafsson sent in four more revisions of a patch to support NSS as a libpq TLS backend.

Michaël Paquier, Fabrízio de Royes Mello, and Nikolay Samokhvalov traded patches to add important information about ANALYZE after creating an expressional index.

Justin Pryzby sent in a patch to implement CLUSTER for partitioned tables, reserve indisclustered on children of clustered, partitioned indexes, and invalidate indisclustered when attaching unclustered indexes.

Justin Pryzby sent in a WIP patch to implement DROP INDEX CONCURRENTLY for partitioned indexes.

Hayato Kuroda sent in a patch to fix an issue in pgbench where queries were firing before connections were established by adding a call to pthread_barrier_wait().

Hou Zhijie sent in a patch to enhance libpq to support multiple host for non hot standby.

Pavel Borisov sent in a patch to fix for bug when running Valgrind on OSX.

Amul Sul sent in another revision of a patch to implement ALTER SYSTEM READ {ONLY|WRITE}.

Justin Pryzby sent in another revision of a patch to ensure that all the pg_ls_* functions show directories and shared filesets.

Tsutomu Yamada sent in four revisions of a patch to add list extended stats to psql's \dX and \dX+.

Kirk Jamison sent in another revision of a patch to make dropping relation buffers more efficient using dlist.

Bruce Momjian sent in two more revisions of a patch to implement a key management system.

Xiao Bai Yan sent in another revision of a patch to extend the shared tuple store and add batch store module, and in passing, use atomic operations instead of LWLock for shared tuple stores when getting the next read page, and use this to implement parallel distinct union aggregates and grouping sets support using batch hash aggregate.

Amit Kapila sent in another revision of a patch to track statistics for streaming of changes from ReorderBuffer.

John Naylor sent in a patch to forbid custom pg type symbols.

Heikki Linnakangas and Andres Freund traded patches to deduplicate aggregates and transition functions in the planner.

Amit Langote sent in another revision of a patch to rearrange partition routing layering in nodeModifyTable.c.

Takamichi Osumi make it possible to disable WAL logging to speed up data loading.

John Naylor sent in four revisions of a patch to document the fact that pg_settings view doesn't display custom options.

Bharath Rupireddy sent in two more revisions of a patch to adds two new functions: pg_terminate_backend(pid, wait, timeout), which terminates and waits or times out for a given backend, and pg_wait_backend(pid, timeout), which checks for the existence of the backend with a given PID and waits or times out until it goes away.

Euler Taveira de Oliveira sent in a patch to control temporary file removal after a crash.

Vigneshwaran C sent in four revisions of a patch to add a log message to include GSS authentication, encryption & principal information.

Noah Misch sent in another revision of a patch to prevent excess SimpleLruTruncate() deletion, and unlink less in SimpleLruTruncate(), as insurance against bugs.

Takamichi Osumi sent in another revision of a patch to implement CREATE OR REPLACE TRIGGER.

Justin Pryzby sent in another revision of a patch to pg_dump: Allow child partitions to be independently restored, even if the parent doesn't exist, or has missing/incompatible columns.

Justin Pryzby sent in a patch to retire pg_standby and pg_archivecleanup.

Masahiko Sawada sent in a PoC patch that adds some xx_vacuum GUC parameters to control the method of recording TIDs.

Michaël Paquier sent in another revision of a patch to fix page verifications in base backups.

Masahiro Ikeda sent in another revision of a patch to add statistics to the pg_stat_wal view.

Justin Pryzby sent in another revision of a patch to allow INSERT SELECT to use a BulkInsertState, and use same to make INSERT SELECT use multi_insert.

Daniel Gustafsson sent in another revision of a patch to clean up contrib/sslinfo and OpenSSL error handling.

Amit Langote sent in another revision of a patch to make update/delete of inheritance trees scale better.

Tomáš Vondra and James Coleman traded patches to fix get_useful_pathkeys_for_relation for volatile exprs below joins, and add comments explaining where projections aren't necessary.

Thomas Munro sent in two more revisions of a patch to implement collation versioning.

Amit Langote sent in another revision of a patch to call BeginDirectModify from ExecInitModifyTable, and initialize result relation information lazily.

Amit Langote sent in another revision of a patch to fix a bug with RETURNING when UPDATE moves tuples.

Ashutosh Bapat sent in two more revisions of a patch to enumize logical replication message actions.

Jimmy Angelakos sent in a patch to clarify the wording for convalidated in pg_constraint.

John Naylor sent in a patch to document deviation from the standard on REVOKE ROLE.

Andrey Borodin sent in another revision of a patch to add Sortsupport for sorting GiST build for gist_btree types.

Tomáš Vondra sent in another revision of a patch to extend range type operators to cope with elements.

Heikki Linnakangas sent in a WIP patch to find all line-endings in COPY in chunks.

Jürgen Purtz, Erik Rijkers, and Justin Pryzby traded patches to add a new chapter to the tutorial.

Tomáš Vondra sent in another revision of a patch to add extra statistics to explain for Nested Loop.

Justin Pryzby sent in another revision of a patch to fix typos.

Justin Pryzby sent in another revision of a patch to allow CREATE INDEX CONCURRENTLY on partitioned table, add a SKIPVALID flag for more integration, and make eindexPartitions() set indisvalid.

Andrey V. Lepikhov sent in another revision of a patch to remove unneeded self-joins.

Ranier Vilela sent in a patch to fix a dereference before NULL check in src/backend/storage/ipc/latch.c.

Ranier Vilela sent in a patch to fix an explicit NULL dereference in src/backend/utils/adt/ruleutils.c.

Justin Pryzby sent in another revision of a patch to allow CLUSTER, VACUUM FULL and REINDEX to change tablespace on the fly.

Tom Lane sent in a patch to add a new CoercionForm variant, COERCE_SQL_SYNTAX.

Greg Sabino Mullane sent in another revision of a patch to help psql's \df choose functions by their arguments.

Tom Lane sent in a patch to get to generate macros for all pg_proc entries.

Tom Lane sent in a patch to get rid of aggregate_dummy().