== PostgreSQL Weekly News - July 21, 2019 ==
| От | David Fetter |
|---|---|
| Тема | == PostgreSQL Weekly News - July 21, 2019 == |
| Дата | |
| Msg-id | 20190721201114.GA3885@fetter.org обсуждение исходный текст |
| Список | pgsql-announce |
== PostgreSQL Weekly News - July 21, 2019 == 2Q PGConf 2019 will be held December 4 & 5 in Chicago. The CFP is open through August 30, 2019. https://www.2qpgconf.com/ == PostgreSQL Product News == postgres-checkup 1.2 "The Guardian's Voice", a tool that automates detailed health checks of PostgreSQL clusters, released. https://gitlab.com/postgres-ai-team/postgres-checkup/tags/1.2 == PostgreSQL Jobs for July == http://archives.postgresql.org/pgsql-jobs/2019-07/ == PostgreSQL Local == PGConf.Brazil 2019 will take place August 1-3, 2019 in São Paulo. http://pgconf.com.br The first Austrian pgDay, will take place September 6, 2019 at the Hilton Garden Inn in Wiener Neustadt. https://pgday.at/en/ PostgresOpen will be September 11th - 13th, 2019 in Orlando, Florida at the Rosen Centre Hotel. The CfP is open at https://2019.postgresopen.org/callforpapers/ https://2019.postgresopen.org/ PostgresConf South Africa 2019 will take place in Johannesburg on October 8-9, 2019 https://postgresconf.org/conferences/SouthAfrica2019 pgDay Paris 2020 will be held in Paris, France on March 26, 2020 at Espace Saint-Martin. http://2020.pgday.paris/ Nordic PGDay 2020 will be held in Helsinki, Finland at the Hilton Helsinki Strand Hotel on March 24, 2020. The CfP is open through December 31, 2019 at https://2020.nordicpgday.org/cfp/ == PostgreSQL in the News == Planet PostgreSQL: http://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 == Thomas Munro pushed: - Provide XLogRecGetFullXid(). In order to be able to work with FullTransactionId values during replay without increasing the size of the WAL, infer the epoch. In general we can't do that safely, but during replay we can because we know that nextFullXid can't advance concurrently. Prevent frontend code from seeing this new function, due to the above restriction. Perhaps in future it will be possible to extract the value entirely from independent WAL records, and then this restriction can be lifted. Author: Thomas Munro, based on earlier code from Andres Freund Discussion: https://postgr.es/m/CA%2BhUKG%2BmLmuDjMi6o1dxkKvGRL56Y2Rz%2BiXAcrZV03G9ZuFQ8Q%40mail.gmail.com https://git.postgresql.org/pg/commitdiff/67b9b3ca328392f9afc4e66fe03564f5fc87feff - Report the time taken by pgbench initialization steps. Author: Fabien Coelho Reviewed-by: Ibrar Ahmed Discussion: https://postgr.es/m/alpine.DEB.2.21.1904061810510.3678%40lancre https://git.postgresql.org/pg/commitdiff/ce8f946764005e1bde5a538542205e55f81bb6cc - Provide pgbench --show-script to dump built-in scripts. Author: Fabien Coelho Reviewed-by: Ibrar Ahmed Discussion: https://postgr.es/m/alpine.DEB.2.21.1904081737390.5867%40lancre https://git.postgresql.org/pg/commitdiff/5823677acc567d7790cc68972de12f6718913d7d - Move some md.c-specific logic from smgr.c to md.c. Potential future SMGR implementations may not want to create tablespace directories when creating an SMGR relation. Move that logic to mdcreate(). Move the initialization of md-specific data structures from smgropen() to a new callback mdopen(). Author: Thomas Munro Reviewed-by: Shawn Debnath (as part of an earlier patch set) Discussion: https://postgr.es/m/CA%2BhUKG%2BOZqOiOuDm5tC5DyQZtJ3FH4%2BFSVMqtdC4P1atpJ%2Bqhg%40mail.gmail.com https://git.postgresql.org/pg/commitdiff/dfd0121dc73aab491bcaad2d2b7a2a749389add8 Tom Lane pushed: - Represent Lists as expansible arrays, not chains of cons-cells. Originally, Postgres Lists were a more or less exact reimplementation of Lisp lists, which consist of chains of separately-allocated cons cells, each having a value and a next-cell link. We'd hacked that once before (commit d0b4399d8) to add a separate List header, but the data was still in cons cells. That makes some operations -- notably list_nth() -- O(N), and it's bulky because of the next-cell pointers and per-cell palloc overhead, and it's very cache-unfriendly if the cons cells end up scattered around rather than being adjacent. In this rewrite, we still have List headers, but the data is in a resizable array of values, with no next-cell links. Now we need at most two palloc's per List, and often only one, since we can allocate some values in the same palloc call as the List header. (Of course, extending an existing List may require repalloc's to enlarge the array. But this involves just O(log N) allocations not O(N).) Of course this is not without downsides. The key difficulty is that addition or deletion of a list entry may now cause other entries to move, which it did not before. For example, that breaks foreach() and sister macros, which historically used a pointer to the current cons-cell as loop state. We can repair those macros transparently by making their actual loop state be an integer list index; the exposed "ListCell *" pointer is no longer state carried across loop iterations, but is just a derived value. (In practice, modern compilers can optimize things back to having just one loop state value, at least for simple cases with inline loop bodies.) In principle, this is a semantics change for cases where the loop body inserts or deletes list entries ahead of the current loop index; but I found no such cases in the Postgres code. The change is not at all transparent for code that doesn't use foreach() but chases lists "by hand" using lnext(). The largest share of such code in the backend is in loops that were maintaining "prev" and "next" variables in addition to the current-cell pointer, in order to delete list cells efficiently using list_delete_cell(). However, we no longer need a previous-cell pointer to delete a list cell efficiently. Keeping a next-cell pointer doesn't work, as explained above, but we can improve matters by changing such code to use a regular foreach() loop and then using the new macro foreach_delete_current() to delete the current cell. (This macro knows how to update the associated foreach loop's state so that no cells will be missed in the traversal.) There remains a nontrivial risk of code assuming that a ListCell * pointer will remain good over an operation that could now move the list contents. To help catch such errors, list.c can be compiled with a new define symbol DEBUG_LIST_MEMORY_USAGE that forcibly moves list contents whenever that could possibly happen. This makes list operations significantly more expensive so it's not normally turned on (though it is on by default if USE_VALGRIND is on). There are two notable API differences from the previous code: * lnext() now requires the List's header pointer in addition to the current cell's address. * list_delete_cell() no longer requires a previous-cell argument. These changes are somewhat unfortunate, but on the other hand code using either function needs inspection to see if it is assuming anything it shouldn't, so it's not all bad. Programmers should be aware of these significant performance changes: * list_nth() and related functions are now O(1); so there's no major access-speed difference between a list and an array. * Inserting or deleting a list element now takes time proportional to the distance to the end of the list, due to moving the array elements. (However, it typically *doesn't* require palloc or pfree, so except in long lists it's probably still faster than before.) Notably, lcons() used to be about the same cost as lappend(), but that's no longer true if the list is long. Code that uses lcons() and list_delete_first() to maintain a stack might usefully be rewritten to push and pop at the end of the list rather than the beginning. * There are now list_insert_nth...() and list_delete_nth...() functions that add or remove a list cell identified by index. These have the data-movement penalty explained above, but there's no search penalty. * list_concat() and variants now copy the second list's data into storage belonging to the first list, so there is no longer any sharing of cells between the input lists. The second argument is now declared "const List *" to reflect that it isn't changed. This patch just does the minimum needed to get the new implementation in place and fix bugs exposed by the regression tests. As suggested by the foregoing, there's a fair amount of followup work remaining to do. Also, the ENABLE_LIST_COMPAT macros are finally removed in this commit. Code using those should have been gone a dozen years ago. Patch by me; thanks to David Rowley, Jesper Pedersen, and others for review. Discussion: https://postgr.es/m/11587.1550975080@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/1cff1b95ab6ddae32faa3efe0d95a820dbfdc164 - Remove dead code. These memory context switches are useless in the wake of commit 1cff1b95a. Noted by Jesper Pedersen. Discussion: https://postgr.es/m/f078ce63-9e04-0f3e-d200-d7ee66279abe@redhat.com https://git.postgresql.org/pg/commitdiff/4c3d05d875dd173a81a995c6e14d69496b467eec - Redesign the API for list sorting (list_qsort becomes list_sort). In the wake of commit 1cff1b95a, the obvious way to sort a List is to apply qsort() directly to the array of ListCells. list_qsort was building an intermediate array of pointers-to-ListCells, which we no longer need, but getting rid of it forces an API change: the comparator functions need to do one less level of indirection. Since we're having to touch the callers anyway, let's do two additional changes: sort the given list in-place rather than making a copy (as none of the existing callers have any use for the copying behavior), and rename list_qsort to list_sort. It was argued that the old name exposes more about the implementation than it should, which I find pretty questionable, but a better reason to rename it is to be sure we get the attention of any external callers about the need to fix their comparator functions. While we're at it, change four existing callers of qsort() to use list_sort instead; previously, they all had local reinventions of list_qsort, ie build-an-array-from-a-List-and-qsort-it. (There are some other places where changing to list_sort perhaps would be worthwhile, but they're less obviously wins.) Discussion: https://postgr.es/m/29361.1563220190@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/569ed7f48312c70ed4a79daec1d7688fda4e74ac - Clean up some ad-hoc code for sorting and de-duplicating Lists. heap.c and relcache.c contained nearly identical copies of logic to insert OIDs into an OID list while preserving the list's OID ordering (and rejecting duplicates, in one case but not the other). The comments argue that this is faster than qsort for small numbers of OIDs, which is at best unproven, and seems even less likely to be true now that lappend_cell_oid has to move data around. In any case it's ugly and hard-to-follow code, and if we do have a lot of OIDs to consider, it's O(N^2). Hence, replace with simply lappend'ing OIDs to a List, then list_sort the completed List, then remove adjacent duplicates if necessary. This is demonstrably O(N log N) and it's much simpler for the callers. It's possible that this would be somewhat inefficient if there were a very large number of duplicates, but that seems unlikely in the existing usage. This adds list_deduplicate_oid and list_oid_cmp infrastructure to list.c. I didn't bother with equivalent functionality for integer or pointer Lists, but such could always be added later if we find a use for it. Discussion: https://postgr.es/m/26193.1563228600@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/2f5b8eb5a28b4e6de9d20cc7d2c6028c6c7a8aa8 - Remove lappend_cell...() family of List functions. It seems worth getting rid of these functions because they require the caller to retain a ListCell pointer into a List that it's modifying, which is a dangerous practice with the new List implementation. (The only other List-modifying function that takes a ListCell pointer as input is list_delete_cell, which nowadays is preferentially used via the constrained API foreach_delete_current.) There was only one remaining caller of these functions after commit 2f5b8eb5a, and that was some fairly ugly GEQO code that can be much more clearly expressed using a list-index variable and list_insert_nth. Hence, rewrite that code, and remove the functions. Discussion: https://postgr.es/m/26193.1563228600@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/c245776906b065fcd59831a25c3b24ad3ddcd849 - Fix thinko in construction of old_conpfeqop list. This should lappend the OIDs, not lcons them; the existing code produced a list in reversed order. This is harmless for single-key FKs or FKs where all the key columns are of the same type, which probably explains how it went unnoticed. But if those conditions are not met, ATAddForeignKeyConstraint would make the wrong decision about whether an existing FK needs to be revalidated. I think it would almost always err in the safe direction by revalidating a constraint that didn't need it. You could imagine scenarios where the pfeqop check was fooled by swapping the types of two FK columns in one ALTER TABLE, but that case would probably be rejected by other tests, so it might be impossible to get to the worst-case scenario where an FK should be revalidated and isn't. (And even then, it's likely to be fine, unless there are weird inconsistencies in the equality behavior of the replacement types.) However, this is a performance bug at least. Noted while poking around to see whether lcons calls could be converted to lappend. This bug is old, dating to commit cb3a7c2b9, so back-patch to all supported branches. https://git.postgresql.org/pg/commitdiff/3093eb2b83645a083a47ea62769ffd89e31f3664 - Avoid using lcons and list_delete_first where it's easy to do so. Formerly, lcons was about the same speed as lappend, but with the new List implementation, that's not so; with a long List, data movement imposes an O(N) cost on lcons and list_delete_first, but not lappend. Hence, invent list_delete_last with semantics parallel to list_delete_first (but O(1) cost), and change various places to use lappend and list_delete_last where this can be done without much violence to the code logic. There are quite a few places that construct result lists using lcons not lappend. Some have semantic rationales for that; I added comments about it to a couple that didn't have them already. In many such places though, I think the coding is that way only because back in the dark ages lcons was faster than lappend. Hence, switch to lappend where this can be done without causing semantic changes. In ExecInitExprRec(), this results in aggregates and window functions that are in the same plan node being executed in a different order than before. Generally, the executions of such functions ought to be independent of each other, so this shouldn't result in visibly different query results. But if you push it, as one regression test case does, you can show that the order is different. The new order seems saner; it's closer to the order of the functions in the query text. And we never documented or promised anything about this, anyway. Also, in gistfinishsplit(), don't bother building a reverse-order list; it's easy now to iterate backwards through the original list. It'd be possible to go further towards removing uses of lcons and list_delete_first, but it'd require more extensive logic changes, and I'm not convinced it's worth it. Most of the remaining uses deal with queues that probably never get long enough to be worth sweating over. (Actually, I doubt that any of the changes in this patch will have measurable performance effects either. But better to have good examples than bad ones in the code base.) Patch by me, thanks to David Rowley and Daniel Gustafsson for review. Discussion: https://postgr.es/m/21272.1563318411@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/d97b714a219959a50f9e7b37ded674f5132f93f3 - Fix sepgsql test results for commit d97b714a2. The aggregate-order difference explained in my previous commit turns out to also affect the order of log entries emitted in the contrib/sepgsql regression test. Per buildfarm. Discussion: https://postgr.es/m/21272.1563318411@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/82c8a3c52adfd993b72289bfa8739f97216a06df - Doc: explain where to find Makefile used to build sepgsql-regtest.pp. At least on Fedora and RHEL, it's not in the same RPM that's needed for building sepgsql itself. Today is the second or third time I've had to rediscover how to install that, so let's document it this time. https://git.postgresql.org/pg/commitdiff/860c095fd548cd25586e4273e9b489082b4ffa13 - Clarify the distinction between public and private SPITupleTable fields. The fields that we consider public are "tupdesc" and "vals", which historically are in the middle of the struct. Move them to the front (this should be perfectly safe to do in HEAD) and add comments to make it quite clear which fields are public or not. Also adjust spi.sgml's documentation of the struct to match. That doc had bit-rotted somewhat, as it was missing some fields. (Arguably we should just remove all the private fields from the docs, but for now I refrained.) Daniel Gustafsson, reviewed by Fabien Coelho Discussion: https://postgr.es/m/0D19F836-B743-4340-B6A2-F148CA3DD1F0@yesql.se https://git.postgresql.org/pg/commitdiff/fec0778c8098cebec2d5cb3674ac7151d8d95638 - Sync our copy of the timezone library with IANA release tzcode2019b. A large fraction of this diff is just due to upstream's somewhat random decision to rename a bunch of internal variables and struct fields. However, there is an interesting new feature in zic: it's grown a "-b slim" option that emits zone files without 32-bit data and other backwards-compatibility hacks. We should consider whether we wish to enable that. https://git.postgresql.org/pg/commitdiff/f285322f9cd3145ea2e5b870e6ba7e0c641422ac - Update time zone data files to tzdata release 2019b. Brazil no longer observes DST. Historical corrections for Palestine, Hong Kong, and Italy. https://git.postgresql.org/pg/commitdiff/93907478e15f5762800b6acbe2eff03167843874 - Further adjust SPITupleTable to provide a public row-count field. Now that commit fec0778c8 drew a clear line between public and private fields in SPITupleTable, it seems pretty silly that the count of valid tuples isn't on the public side of that line. The reason why not was that there wasn't such a count. For reasons lost in the mists of time, spi.c preferred to keep a count of remaining free entries in the array. But that seems pretty pointless: it's unlike the way we handle similar code everywhere else, and it involves extra subtractions that surely outweigh having to do a comparison rather than test-for-zero to check for array-full. Hence, rearrange so that this code does the expansible array logic the same as everywhere else, with a count of valid entries alongside the allocated array length. And document the count as public. I looked for core-code callers where it would make sense to start relying on tuptable->numvals rather than the separate SPI_processed variable. Right now there don't seem to be places where it'd be a win to do so without more code restructuring than I care to undertake today. In principle, though, having SPITupleTables be fully self-contained should be helpful down the line. Discussion: https://postgr.es/m/16852.1563395722@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/bc8393cf27731055467a83068c680c86f9c112ea - Silence compiler warning, hopefully. Absorb commit e5e04c962a5d12eebbf867ca25905b3ccc34cbe0 from upstream IANA code, in hopes of silencing warnings from MSVC about negating a bool value. Discussion: https://postgr.es/m/20190719035347.GJ1859@paquier.xyz https://git.postgresql.org/pg/commitdiff/421466863548de58199c7c6ececaae6b5f621b2f - Remove no-longer-helpful reliance on fixed-size local array. Coverity complained about this code, apparently because it uses a local array of size FUNC_MAX_ARGS without a guard that the input argument list is no longer than that. (Not sure why it complained today, since this code's been the same for a long time; possibly it re-analyzed everything the List API change touched?) Rather than add a guard, though, let's just get rid of the local array altogether. It was only there to avoid list_nth() calls, and those are no longer expensive. https://git.postgresql.org/pg/commitdiff/330cafdfaa11ebe53e3e59688acac1577ae0cb34 Peter Geoghegan pushed: - Fix pathological nbtree split point choice issue. Specific ever-decreasing insertion patterns could cause successive unbalanced nbtree page splits. Problem cases involve a large group of duplicates to the left, and ever-decreasing insertions to the right. To fix, detect the situation by considering the newitem offset before performing a split using nbtsplitloc.c's "many duplicates" strategy. If the new item was inserted just to the right of our provisional "many duplicates" split point, infer ever-decreasing insertions and fall back on a 50:50 (space delta optimal) split. This seems to barely affect cases that already had acceptable space utilization. An alternative fix also seems possible. Instead of changing nbtsplitloc.c split choice logic, we could instead teach _bt_truncate() to generate a new value for new high keys by interpolating from the lastleft and firstright key values. That would certainly be a more elegant fix, but it isn't suitable for backpatching. Discussion: https://postgr.es/m/CAH2-WznCNvhZpxa__GqAa1fgQ9uYdVc=_apArkW2nc-K3O7_NA@mail.gmail.com Backpatch: 12-, where the nbtree page split enhancements were introduced. https://git.postgresql.org/pg/commitdiff/e3899ffd8beafdaaa037b503163a9f572e9fc729 - Correct nbtsplitloc.c comment. The logic just added by commit e3899ffd falls back on a 50:50 page split in the event of a new item that's just to the right of our provisional "many duplicates" split point. Fix a comment that incorrectly claimed that the new item had to be just to the left of our provisional split point. Backpatch: 12-, just like commit e3899ffd. https://git.postgresql.org/pg/commitdiff/bfdbac2ab3ef1a12a7de231552b128ed83ad00bb - Fix nbtree metapage cache upgrade bug. Commit 857f9c36cda, which taught nbtree VACUUM to avoid unnecessary index scans, bumped the nbtree version number from 2 to 3, while adding the ability for nbtree indexes to be upgraded on-the-fly. Various assertions that assumed that an nbtree index was always on version 2 had to be changed to accept any supported version (version 2 or 3 on Postgres 11). However, a few assertions were missed in the initial commit, all of which were in code paths that cache a local copy of the metapage metadata, where the index had been expected to be on the current version (no longer version 2) as a generic sanity check. Rather than simply update the assertions, follow-up commit 0a64b45152b intentionally made the metapage caching code update the per-backend cached metadata version without changing the on-disk version at the same time. This could even happen when the planner needed to determine the height of a B-Tree for costing purposes. The assertions only fail on Postgres v12 when upgrading from v10, because they were adjusted to use the authoritative shared memory metapage by v12's commit dd299df8. To fix, remove the cache-only upgrade mechanism entirely, and update the assertions themselves to accept any supported version (go back to using the cached version in v12). The fix is almost a full revert of commit 0a64b45152b on the v11 branch. VACUUM only considers the authoritative metapage, and never bothers with a locally cached version, whereas everywhere else isn't interested in the metapage fields that were added by commit 857f9c36cda. It seems unlikely that this bug has affected any user on v11. Reported-By: Christoph Berg Bug: #15896 Discussion: https://postgr.es/m/15896-5b25e260fdb0b081%40postgresql.org Backpatch: 11-, where VACUUM was taught to avoid unnecessary index scans. https://git.postgresql.org/pg/commitdiff/d004147eb3ece6b5981dbdd3d918ffc3f23fc505 - Don't rely on estimates for amcheck Bloom filters. Solely relying on a relation's reltuples/relpages estimate to size the Bloom filters used by amcheck verification makes verification less effective when the estimates are very stale. In extreme cases, verification options that use Bloom filters internally could be totally ineffective, without users receiving any clear indication that certain types of corruption might easily be missed. To fix, use RelationGetNumberOfBlocks() instead of relpages to size the downlink block Bloom filter. Use the same RelationGetNumberOfBlocks() value to derive a minimum size for the heapallindexed Bloom filter, rather than completely trusting reltuples. Verification will still be reasonably effective when the projected/estimated number of Bloom filter elements is at least 1/5 of the final number of elements, which is assured by the new sizing logic. Reported-By: Alexander Korotkov Discussion: https://postgr.es/m/CAH2-Wzk0ke2J42KrNYBKu0Xovjy-sU5ub7PWjgpbsKdAQcL4OA@mail.gmail.com Backpatch: 11-, where downlink/heapallindexed verification were added. https://git.postgresql.org/pg/commitdiff/894af78f185afee221a6762a1a49057043b7bbf5 Bruce Momjian pushed: - doc: mention pg_reload_conf() for reloading the config file. Reported-by: Ian Barwick Discussion: https://postgr.es/m/538950ec-b86a-1650-6078-beb7091c09c2@2ndquadrant.com Backpatch-through: 9.4 https://git.postgresql.org/pg/commitdiff/c6bce6ebb668c7da03d01244d34cac0335561103 Michaël Paquier pushed: - Fix inconsistencies and typos in the tree. This is numbered take 7, and addresses a set of issues around: - Fixes for typos and incorrect reference names. - Removal of unneeded comments. - Removal of unreferenced functions and structures. - Fixes regarding variable name consistency. Author: Alexander Lakhin Discussion: https://postgr.es/m/10bfd4ac-3e7c-40ab-2b2e-355ed15495e8@gmail.com https://git.postgresql.org/pg/commitdiff/0896ae561b6c799d45cb61d8a3b18fbb442130a7 - Simplify description of --data-checksums in documentation of initdb. The documentation mentioned that data checksums cannot be changed after initialization, which is not true as pg_checksums can do that with its --enable option introduced in v12. This simply removes the sentence telling so. Reported-by: Basil Bourque Author: Michael Paquier Reviewed-by: Daniel Gustafsson Discussion: https://postgr.es/m/15909-e9d74271f1647472@postgresql.org Backpatch-through: 12 https://git.postgresql.org/pg/commitdiff/1c1602b8b685a68796f8ba48e41f778c0c42ba43 - Fix typo in mvdistinct.c. Noticed while browsing the code. https://git.postgresql.org/pg/commitdiff/70a33b21099c046dc38f07ffb02b1e0cf2aff91d - Refactor parallelization processing code in src/bin/scripts/. The existing facility of vacuumdb to handle parallel connections into a given database with an authentication set is moved to a common file in src/bin/scripts/, named scripts_parallel.c. This introduces a set of routines to initialize, wait and terminate a set of connections, simplifying a bit the code of vacuumdb on the way. More routines related to result handling and database connection are moved to common.c. The initial plan is to use that for reindexdb, but it could be applied to other tools like clusterdb. While on it, clean up a set of variables "progname" which were defined as routine arguments for error messages. Since most of the callers have switched to pg_log_error() and such there is no need for this variable. Author: Julien Rouhaud Reviewed-by: Michael Paquier, Álvaro Herrera Discussion: https://postgr.es/m/CAOBaU_YrnH_Jqo46NhaJ7uRBiWWEcS40VNRQxgFbqYo9kApUsg@mail.gmail.com https://git.postgresql.org/pg/commitdiff/5f3840370b63fdf17f704a285623ccc233fa8d4f - Doc: clarify when table rewrites happen with column addition and DEFAULT. 16828d5 has improved ALTER TABLE so as a column addition does not require a rewrite for a non-NULL default with constant expressions, but one spot in the documentation did not get updated consistently. The documentation also now clarifies the fact that this does not apply if the expression is volatile, where a table rewrite is still required. Reported-by: Daniel Westermann Author: Ian Barwick Reviewed-by: Michael Paquier, Daniel Westermann Discussion: https://postgr.es/m/DB6PR0902MB2184C7D5645CF15D75EB7957D2CF0@DB6PR0902MB2184.eurprd09.prod.outlook.com Backpatch-through: 11 https://git.postgresql.org/pg/commitdiff/1300fa66b2f3d0dcd2eed7a5eff9e3fc22807f7c - Fix compilation warning of pg_basebackup with MinGW. Several buildfarm members have been complaining about that with gcc, like jacana. Weirdly enough, Visual Studio's compilers do not find this issue. Author: Michael Paquier Reviewed-by: Andrew Dunstan Discussion: https://postgr.es/m/20190719050830.GK1859@paquier.xyz https://git.postgresql.org/pg/commitdiff/90317ab7e64bd2d855c73a6ba579de6d04a7b25c Andres Freund pushed: - tableam: comment improvements. Author: Brad DeJong Discussion: https://postgr.es/m/CAJnrtnxDYOQFsDfWz2iri0T_fFL2ZbbzgCOE=4yaMcszgcsf4A@mail.gmail.com Backpatch: 12- https://git.postgresql.org/pg/commitdiff/21039555cdec75836d246fcbcd4b44ee63dabfad Tomáš Vondra pushed: - Remove unnecessary TYPECACHE_GT_OPR lookup. The TYPECACHE_GT_OPR is not needed (it used to be in older version of the MCV code), but the compiler failed to detect this as the result was used in a fmgr_info() call, populating a FmgrInfo entry. Backpatch to v12, where this code was introduced. Discussion: https://postgr.es/m/8736jdhbhc.fsf%40ansel.ydns.eu Backpatch-to: 12 https://git.postgresql.org/pg/commitdiff/a4303a078c661ebafe8c8c2167b2ad9bf16b32ce - Fix handling of NULLs in MCV items and constants. There were two issues in how the extended statistics handled NULL values in opclauses. Firstly, the code was oblivious to the possibility that Const may be NULL (constisnull=true) in which case the constvalue is undefined. We need to treat this as a mismatch, and not call the proc. Secondly, the MCV item itself may contain NULL values too - the code already did check that, and updated the match bitmap accordingly, but failed to ensure we won't call the operator procedure anyway. It did work for AND-clauses, because in that case false in the bitmap stops evaluation of further clauses. But for OR-clauses ir was not easy to get incorrect estimates or even trigger a crash. This fixes both issues by extending the existing check so that it looks at constisnull too, and making sure it skips calling the procedure. Discussion: https://postgr.es/m/8736jdhbhc.fsf%40ansel.ydns.eu https://git.postgresql.org/pg/commitdiff/e4deae7396f2a5576c0c8289e2bfc005ed3d6989 - Simplify bitmap updates in multivariate MCV code. When evaluating clauses on a multivariate MCV list, we build a bitmap tracking how the clauses match each item of the MCV list. When updating the bitmap we need to consider the current value (tracking how the item matches preceding clauses), match for the current clause and whether the clauses are connected by AND or OR. Until now the logic was copied on every place updating the bitmap, which was not quite readable. So just move it to a separate function and call it where needed. Backpatch to 12, where the code was introduced. While not a bugfix, this should make maintenance and future backpatches easier. Discussion: https://postgr.es/m/8736jdhbhc.fsf%40ansel.ydns.eu https://git.postgresql.org/pg/commitdiff/7d24f6a49076f975ca87926b3cde8fdea3448ecb - Fix handling of opclauses in extended statistics. We expect opclauses to have exactly one Var and one Const, but the code was checking the Const by calling is_pseudo_constant_clause() which is incorrect - we need a proper constant. Fixed by using plain IsA(x,Const) to check type of the node. We need to do these checks in two places, so move it into a separate function that can be called in both places. Reported by Andreas Seltenreich, based on crash reported by sqlsmith. Backpatch to v12, where this code was introduced. Discussion: https://postgr.es/m/8736jdhbhc.fsf%40ansel.ydns.eu Backpatch-to: 12 https://git.postgresql.org/pg/commitdiff/e8b6ae2130e3a95bb776708a9a7c9cb21fe8ac87 - Rework examine_opclause_expression to use varonleft. The examine_opclause_expression function needs to return information on which side of the operator we found the Var, but the variable was called "isgt" which is rather misleading (it assumes the operator is either less-than or greater-than, but it may be equality or something else). Other places in the planner use a variable called "varonleft" for this purpose, so just adopt the same convention here. The code also assumed we don't care about this flag for equality, as (Var = Const) and (Const = Var) should be the same thing. But that does not work for cross-type operators, in which case we need to pass the parameters to the procedure in the right order. So just use the same code for all types of expressions. This means we don't need to care about the selectivity estimation function anymore, at least not in this code. We should only get the supported cases here (thanks to statext_is_compatible_clause). Reviewed-by: Tom Lane Discussion: https://postgr.es/m/8736jdhbhc.fsf%40ansel.ydns.eu Backpatch-to: 12 https://git.postgresql.org/pg/commitdiff/e38a55ba46bbd2510baccdbaa01298cbca972b88 - Use column collation for extended statistics. The current extended statistics code was a bit confused which collation to use. When building the statistics, the collations defined as default for the data types were used (since commit 5e0928005). The MCV code was however using the column collations for MCV serialization, and then DEFAULT_COLLATION_OID when computing estimates. So overall the code was using all three possible options, inconsistently. This uses the column colation everywhere - this makes it consistent with what 5e0928005 did for regular stats. We however do not track the collations in a catalog, because we can derive them from column-level information. This may need to change in the future, e.g. after allowing statistics on expressions. Reviewed-by: Tom Lane Discussion: https://postgr.es/m/8736jdhbhc.fsf%40ansel.ydns.eu Backpatch-to: 12 https://git.postgresql.org/pg/commitdiff/a63378a03ec0a53c7c579dfdb3abff57811d8ced Jeff Davis pushed: - Fix daterange canonicalization for +/- infinity. The values 'infinity' and '-infinity' are a part of the DATE type itself, so a bound of the date 'infinity' is not the same as an unbounded/infinite range. However, it is still wrong to try to canonicalize such values, because adding or subtracting one has no effect. Fix by treating 'infinity' and '-infinity' the same as unbounded ranges for the purposes of canonicalization (but not other purposes). Backpatch to all versions because it is inconsistent with the documented behavior. Note that this could be an incompatibility for applications relying on the behavior contrary to the documentation. Author: Laurenz Albe Reviewed-by: Thomas Munro Discussion: https://postgr.es/m/77f24ea19ab802bc9bc60ddbb8977ee2d646aec1.camel%40cybertec.at Backpatch-through: 9.4 https://git.postgresql.org/pg/commitdiff/e6feef571a016c9dac52a01aebad484768eb5c68 - Fix error in commit e6feef57. I was careless passing a datum directly to DATE_NOT_FINITE without calling DatumGetDateADT() first. Backpatch-through: 9.4 https://git.postgresql.org/pg/commitdiff/b538c90b1bded5464787e2b8e4431302d24eb601 - pg_stat_statements: add missing check for pgss_enabled(). Make pgss_post_parse_analyze() more consistent with the other hooks, and avoid unnecessary overhead when pg_stat_statements.track=none. Author: Raymond Martin Reviewed-by: Fabien COELHO Discussion: https://postgr.es/m/BN8PR21MB1217B003C4F79DE230AA36B9B1580%40BN8PR21MB1217.namprd21.prod.outlook.com https://git.postgresql.org/pg/commitdiff/6f40ee4f837ec1ac59c8ddc73b67a04978a184d5 David Rowley pushed: - Speed up finding EquivalenceClasses for a given set of rels. Previously in order to determine which ECs a relation had members in, we had to loop over all ECs stored in PlannerInfo's eq_classes and check if ec_relids mentioned the relation. For the most part, this was fine, as generally, unless queries were fairly complex, the overhead of performing the lookup would have not been that significant. However, when queries contained large numbers of joins and ECs, the overhead to find the set of classes matching a given set of relations could become a significant portion of the overall planning effort. Here we allow a much more efficient method to access the ECs which match a given relation or set of relations. A new Bitmapset field in RelOptInfo now exists to store the indexes into PlannerInfo's eq_classes list which each relation is mentioned in. This allows very fast lookups to find all ECs belonging to a single relation. When we need to lookup ECs belonging to a given pair of relations, we can simply bitwise-AND the Bitmapsets from each relation and use the result to perform the lookup. We also take the opportunity to write a new implementation of generate_join_implied_equalities which makes use of the new indexes. generate_join_implied_equalities_for_ecs must remain as is as it can be given a custom list of ECs, which we can't easily determine the indexes of. This was originally intended to fix the performance penalty of looking up foreign keys matching a join condition which was introduced by 100340e2d. However, we're speeding up much more than just that here. Author: David Rowley, Tom Lane Reviewed-by: Tom Lane, Tomas Vondra Discussion: https://postgr.es/m/6970.1545327857@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/3373c7155350cf6fcd51dd090f29e1332901e329 == Pending Patches == Matheus de Oliveira sent in another revision of a patch to add support for ON UPDATE/DELETE actions on ALTER CONSTRAINT. Ian Barwick sent in a patch to improve the uuid_version() documents. Hubert Zhang sent in another revision of a patch to add hooks for disk quotas. Pavel Stěhule sent in another revision of a patch for psql to allow sorting the results of backslash commands by size. Paul Guo sent in another revision of a patch to auto-generate a recovery.conf and ensure a clean shutdown with pg_rewind. Paul Guo sent in another revision of a patch to skip copydir() if either src directory or dst directory is missing due to re-redoing create database but the tablespace is dropped. Luis Carril sent in another revision of a patch to add an option to dump foreign data in pg_dump. Andrey V. Lepikhov sent in a patch to ensure more secure initialization of required_relids field. Ian Barwick and Bruce Momjian traded patches to mention pg_reload_conf() in the pg_hba.conf documentation. John Naylor sent in a patch to fix a typo in the list of languages with new stemmers. Laurenz Albe sent in a patch to document some improvements in consistency between *n*x and Windows for syncing data. Daniel Westermann sent in a patch to clarify the tip in the documentation on adding columns with defaults. Fabien COELHO sent in another revision of a patch to share the strtoX64 functions between the front- and back-ends. Edmund Horner and David Rowley traded patches to add a new plan type, Tid Range Scan, to support range quals over CTID. Konstantin Knizhnik sent in four more revisions of a patch to add a built-in connection pooler. Nikita Glukhov sent in another revision of a patch to add information about access methods and indexes to psql's meta-commands. Peter Geoghegan sent in a patch to overwrite the lastright item with highkey in nbtsort.c. Peter Geoghegan sent in a patch to simplify _bt_getstackbuf() by making it accept a child BlockNumber argument, rather than requiring that callers store the child block number in the parent stack item's bts_btentry field. Surafel Temesgen sent in another revision of a patch to implement conflict handling in COPY ... FROM. Fabien COELHO sent in another revision of a patch which extends the initialization phase controls. Masahiko Sawada sent in a patch to add a RESUME option to VACUUM and autovacuum. Pavel Stěhule sent in another revision of a patch to add schema variables. Nikita Glukhov sent in another revision of a patch to implement SQL/JSON functions. Nikita Glukhov sent in another revision of a patch to implement SQL/JSON's JSON_TABLE. Justin Pryzby sent in three more revisions of a patch to psql to print table associated with given TOAST table, make \d pg_toast.foo show its indices, and show the children of partitioned indices. Nikita Glukhov sent in another revision of a patch to fix max size checking for ltree and lquery. Tom Lane and Sergei Kornilov traded patches to change the ereport level for QueuePartitionConstraintValidation. Jesper Pedersen and Michaël Paquier traded patches to highlight the fact that pg_receivewal doesn't apply WAL, and as such synchronous-commit needs to be remote_write or lower. Dilip Kumar sent in another revision of a patch to clean up orphaned files using undo logs. Jesper Pedersen sent in another revision of a patch to implement a separate UniqueKey node. Alexander Korotkov and Liudmila Mantrova traded patches to add support for the jsonpath .datetime() method. Michaël Paquier sent in a patch to fix a bug where pg_stat_replication lag fields return non-NULL values even with NULL LSNs. Ryo Matsumura sent in a patch to imlement CREATE AS EXECUTE in ECPG. Iwata Aya sent in another revision of a patch to implement a libpq debug log. Amit Langote sent in another revision of a patch to use the root parent's permissions when read child table's stats. Nikita Glukhov sent in another revision of a patch to improve the ltree syntax. Antonin Houska sent in another revision of a patch to implement aggregate pushdown. Ian Barwick sent in a patch to make the configuration file "include" directive handling more robust. Jeff Davis sent in two revisions of a patch to enable simplehash to use already-calculated hash values. Melanie Plageman sent in a patch to implement plan-time extraction of scan cols, execution-time extraction and comparison with the aforementioned, and stop adding returningList for invalid result_relation number. Gareth Palmer sent in a patch to implement the INSERT SET syntax. Daniel Westermann and Ian Barwick traded patches to fix the documentation for adding a column with a default value. Michaël Paquier and Julien Rouhaud traded patches to add parallelism and glibc-dependent-only options to reindexdb. Roman Zharkov sent in a patch to fix some intermittent pg_ctl failures on Windows. Anastasia Lubennikova sent in two more revisions of a patch to storage of duplicates more efficiently in B-tree indexes. Tom Lane sent in a patch to touch all the places where it seemed like an easy win to stop using lcons and/or list_delete_first. Daniel Gustafsson sent in a patch to use a counter and list_copy_tail to avoid repeated list_delete_first calls. Álvaro Herrera sent in a patch to fix a bug that manifested as getting ERROR "relation 16401 has no triggers" with partition foreign key alter. Amit Langote sent in another revision of a patch to make some cosmetic improvements to partitionwise join code, fix partitionwise join to handle FULL JOINs correctly, and add multi-relation EC child members in a separate pass. Anastasia Lubennikova sent in a patch to fix a bug that manifested as pg_upgrade failing with a non-standard ACL. Jeff Davis sent in a patch to implement memory accounting at the block level. Ashutosh Sharma sent in two revisions of a patch to implement CALL in ECPG. David Rowley sent in a patch to speed up finding EquivalenceClasses for a given set of rels, and add a special purpose generate_join_implied_equalities implementation. Amit Khandekar sent in another revision of a patch to implement minimal logical decoding on standbys. Daniel Gustafsson sent in a patch to bring xpath/numvals in line with recent developments in the SPITupleTable struct. Tom Lane sent in a patch to add dependencies for partitioning columns. Mike Palmiotto sent in a patch to make the sepgsql-regtest policy module less error-prone, and add a sandboxed cluster for the sepgsql regression tests. Amit Langote sent in a patch to use es_result_relation_info less, and rearrange the partition row movement update code. Ian Barwick sent in a patch to ensure that primary_slot_name is escaped in pg_basebackup. Peifeng Qiu sent in another revision of a patch to make it possible to compile from source using latest Microsoft Windows SDK. Tomáš Vondra sent in another revision of a patch to clean up ALTER STATISTICS. Thomas Munro sent in a PoC patch to make Datum a strong type. Tomáš Vondra and James Coleman traded patches to implement incremental sort. David Rowley sent in another revision of a patch to shrink bloated locallocktable. David Rowley sent in a patch to perform a bms_next_member() type loop then just fetch the list item with list_nth(). Tom Lane sent in a patch to rationalize list concat and copy operations.
В списке pgsql-announce по дате отправления: