== PostgreSQL Weekly News - March 10, 2019 ==
| От | David Fetter |
|---|---|
| Тема | == PostgreSQL Weekly News - March 10, 2019 == |
| Дата | |
| Msg-id | 20190310202619.GA29725@fetter.org обсуждение исходный текст |
| Список | pgsql-announce |
== PostgreSQL Weekly News - March 10, 2019 == pgDay Israel 2019 will take place on March 14, 2019 in Tel Aviv. Registration is open. http://pgday.org.il/ == PostgreSQL Product News == pgAdmin4 4.3, a web- and native GUI control center for PostgreSQL, released. https://www.pgadmin.org/docs/pgadmin4/dev/release_notes_4_3.html == PostgreSQL Jobs for March == http://archives.postgresql.org/pgsql-jobs/2019-03/ == PostgreSQL Local == PostgreSQL@SCaLE is a two day, two track event which takes place on March 7-8, 2019, at Pasadena Convention Center, as part of SCaLE 17X. https://www.socallinuxexpo.org/scale/17x/postgresscale pgDay Paris 2019 will be held in Paris, France on March 12, 2019 at 199bis rue Saint-Martin. http://2019.pgday.paris/ Nordic PGDay 2019 will be held in Copenhagen, Denmark, at the Copenhagen Marriott Hotel, on March 19, 2019. https://2019.nordicpgday.org/ PGConf APAC 2019 will be held in Singapore March 19-21, 2019. http://2019.pgconfapac.org/ The German-speaking PostgreSQL Conference 2019 will take place on May 10, 2019 in Leipzig. http://2019.pgconf.de/ PGDay.IT 2019 will take place May 16th and May 17th in Bologna, Italy. https://2019.pgday.it/en/ PGCon 2019 will take place in Ottawa on May 28-31, 2019. https://www.pgcon.org/2019 Swiss PGDay 2019 will take place in Rapperswil (near Zurich) on June 28, 2019. The CfP is open through April 18, 2019, and registration is open. http://www.pgday.ch/2019/ PostgresLondon 2019 will be July 2-3, 2019 with an optional training day on July 1. The CfP is open at https://goo.gl/forms/hsvZKAmq0c96XQ4l2 through March 15, 2019. http://postgreslondon.org PGConf.Brazil 2019 is on August 1-3 2019 in São Paulo. http://pgconf.com.br == 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 == Tom Lane pushed: - Improve performance of index-only scans with many index columns. StoreIndexTuple was a loop over index_getattr, which is O(N^2) if the index columns are variable-width, and the performance impact is already quite visible at ten columns. The obvious move is to replace that with a call to index_deform_tuple ... but that's *also* a loop over index_getattr. Improve it to be essentially a clone of heap_deform_tuple. (There are a few other places that loop over all index columns with index_getattr, and perhaps should be changed likewise, but most of them don't seem performance-critical. Anyway, the rest would mostly only be interested in the index key columns, which there aren't likely to be so many of. Wide index tuples are a new thing with INCLUDE.) Konstantin Knizhnik Discussion: https://postgr.es/m/e06b2d27-04fc-5c0e-bb8c-ecd72aa24959@postgrespro.ru https://git.postgresql.org/pg/commitdiff/80b9e9c4664a020ebd14889046bd8d22a17d1ca6 - Fix handling of targetlist SRFs when scan/join relation is known empty. When we introduced separate ProjectSetPath nodes for application of set-returning functions in v10, we inadvertently broke some cases where we're supposed to recognize that the result of a subquery is known to be empty (contain zero rows). That's because IS_DUMMY_REL was just looking for a childless AppendPath without allowing for a ProjectSetPath being possibly stuck on top. In itself, this didn't do anything much worse than produce slightly worse plans for some corner cases. Then in v11, commit 11cf92f6e rearranged things to allow the scan/join targetlist to be applied directly to partial paths before they get gathered. But it inserted a short-circuit path for dummy relations that was a little too short: it failed to insert a ProjectSetPath node at all for a targetlist containing set-returning functions, resulting in bogus "set-valued function called in context that cannot accept a set" errors, as reported in bug #15669 from Madelaine Thibaut. The best way to fix this mess seems to be to reimplement IS_DUMMY_REL so that it drills down through any ProjectSetPath nodes that might be there (and it seems like we'd better allow for ProjectionPath as well). While we're at it, make it look at rel->pathlist not cheapest_total_path, so that it gives the right answer independently of whether set_cheapest has been done lately. That dependency looks pretty shaky in the context of code like apply_scanjoin_target_to_paths, and even if it's not broken today it'd certainly bite us at some point. (Nastily, unsafe use of the old coding would almost always work; the hazard comes down to possibly looking through a dangling pointer, and only once in a blue moon would you find something there that resulted in the wrong answer.) It now looks like it was a mistake for IS_DUMMY_REL to be a macro: if there are any extensions using it, they'll continue to use the old inadequate logic until they're recompiled, after which they'll fail to load into server versions predating this fix. Hopefully there are few such extensions. Having fixed IS_DUMMY_REL, the special path for dummy rels in apply_scanjoin_target_to_paths is unnecessary as well as being wrong, so we can just drop it. Also change a few places that were testing for partitioned-ness of a planner relation but not using IS_PARTITIONED_REL for the purpose; that seems unsafe as well as inconsistent, plus it required an ugly hack in apply_scanjoin_target_to_paths. In passing, save a few cycles in apply_scanjoin_target_to_paths by skipping processing of pre-existing paths for partitioned rels, and do some cosmetic cleanup and comment adjustment in that function. I renamed IS_DUMMY_PATH to IS_DUMMY_APPEND with the intention of breaking any code that might be using it, since in almost every case that would be wrong; IS_DUMMY_REL is what to be using instead. In HEAD, also make set_dummy_rel_pathlist static (since it's no longer used from outside allpaths.c), and delete is_dummy_plan, since it's no longer used anywhere. Back-patch as appropriate into v11 and v10. Tom Lane and Julien Rouhaud Discussion: https://postgr.es/m/15669-02fb3296cca26203@postgresql.org https://git.postgresql.org/pg/commitdiff/1d338584062b3e53b738f987ecb0d2b67745232a - Minor improvements for reformat_dat_file.pl. Use Getopt::Long in preference to hand-rolled option parsing code. Also, remove "-I .../backend/catalog" switch from the Makefile invocations. That's been unnecessary for some time, and leaving it there gives the false impression it's needed in manual invocations. John Naylor (extracted from a larger but more controversial patch) Discussion: https://postgr.es/m/CACPNZCsHdcQN2jQ1=ptbi1Co2Nj3aHgRCUMk62=ThgWNabPY+Q@mail.gmail.com https://git.postgresql.org/pg/commitdiff/27aaf6eff49a6348408476652911fe4acceffc71 - Reformat catalog .dat files. Test run for my previous commit; cleans up formatting issues in some other recent commits. https://git.postgresql.org/pg/commitdiff/1b76168da7787505fbe506ef3ab74e9a14b4b7fb - Simplify release-note links to back branches. Now that https://www.postgresql.org/docs/release/ is populated, replace the stopgap text we had under "Prior Releases" with a pointer to that archive. Discussion: https://postgr.es/m/e0f09c9a-bd2b-862a-d379-601dfabc8969@postgresql.org https://git.postgresql.org/pg/commitdiff/a0b762626884b3b949c2703abb1c4b42fbbdfdc6 - Disallow NaN as a value for floating-point GUCs. None of the code that uses GUC values is really prepared for them to hold NaN, but parse_real() didn't have any defense against accepting such a value. Treat it the same as a syntax error. I haven't attempted to analyze the exact consequences of setting any of the float GUCs to NaN, but since they're quite unlikely to be good, this seems like a back-patchable bug fix. Note: we don't need an explicit test for +-Infinity because those will be rejected by existing range checks. I added a regression test for that in HEAD, but not older branches because the spelling of the value in the error message will be platform-dependent in branches where we don't always use port/snprintf.c. Discussion: https://postgr.es/m/1798.1552165479@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/ac75959cdc073a09a0d3d649fb922d90e1df4c61 Andrew Dunstan pushed: - Don't do pg_ctl logrotate test on Windows. The test crashes and burns quite badly, for some reason, but even if it didn't it wouldn't work, since Windows doesn't let you rename a file held by a running process. https://git.postgresql.org/pg/commitdiff/d611175e531b9e09f2f497d7338fff73dd8f2c49 - fix thinko in logrotate test. https://git.postgresql.org/pg/commitdiff/5bd9160f27420ab1a2fb54458d84606b1527a15c - Reorder configure tests for accept() in Windows. Currently only frogmouth in the buildfarm uses the 32bit params, and it's not able to build past release 10, so put those last, saving substantial configure time on more modern systems. Even if we get a modern 32 bit Windows system at some stage we should probably prefer the 64 bit interface here these days. https://git.postgresql.org/pg/commitdiff/6ad94e4d73c098aea8636e27212b9284c6cd9708 - Allow recovery tests to run on Windows as an admin user. This is the only test that fails when run as an admin user. The reason is that when Postgres is started via pg_ctl its admin privileges are lowered. However, this test called 'postgres -D datadir' directly, resulting in a failure. Replace that by calling pg_ctl and then checking the result for the expected failure, and the logfile for the expected error message. https://git.postgresql.org/pg/commitdiff/4eff1e9f0bf7835bee61aaaa9d6de23422a724a0 - Disable dump_connstr test on Msys2. For some reason the dump test with names with high bits set fails on Msys2 (although not Msys1). Disable the tests for now, so that other tests can run. https://git.postgresql.org/pg/commitdiff/1638623f34b716cab8e260b11720466223fe2589 - Fix pgbench TAP test failure with funky file names (redux). This test fails if the containing directory contains a funny character such as a space or some perl metacharacter. To avoid that, we check for files names using readdir and a regex, rather than using a glob pattern. Discussion: https://postgr.es/m/CAM6_UM6dGdU39PKAC24T+HD9ouy0jLN9vH6163K8QEEzr__iZw@mail.gmail.com Author: Fabien COELHO Reviewed-by: Raúl Marín Rodríguez https://git.postgresql.org/pg/commitdiff/e988878f85436dcd74e503c94f5b8e2b76a08711 - Increase the default vacuum_cost_limit from 200 to 2000. The original 200 default value was set back in f425b605f4e when the cost delay settings were first added. Hardware has improved quite a bit since then and we've also made improvements such as sorting buffers during checkpoints (9cd00c457e6) which should result in less random writes. This low default value was reportedly causing problems with badly configured servers and in the absence of a native method to remove excessive bloat from tables without incurring an AccessExclusiveLock, this often made cleaning up the damage caused by badly configured auto-vacuums difficult. It seems more likely that someone will notice that auto-vacuum is running too quickly than too slowly, so let's go all out and multiple the default value for the setting by 10. With the default vacuum_cost_page_dirty and autovacuum_vacuum_cost_delay (assuming a page size of 8192 bytes), this allows autovacuum a theoretical maximum dirty write rate of around 39MB/s instead of just 3.9MB/s. Author: David Rowley Discussion: https://postgr.es/m/CAKJS1f_YbXC2qTMPyCbmsPiKvZYwpuQNQMohiRXLj1r=8_rYvw@mail.gmail.com https://git.postgresql.org/pg/commitdiff/bd09503e633b8077822bb4daf91625b71ac16253 - Don't log incomplete startup packet if it's empty. This will stop logging cases where, for example, a monitor opens a connection and immediately closes it. If the packet contains any data an incomplete packet will still be logged. Author: Tom Lane Discussion: https://postgr.es/m/a1379a72-2958-1ed0-ef51-09a21219b155@2ndQuadrant.com https://git.postgresql.org/pg/commitdiff/342cb650e0ffc7a007a12a419be04d47da4bd8cc Michaël Paquier pushed: - Fix error handling of readdir() port implementation on first file lookup. The implementation of readdir() in src/port/ which gets used by MSVC has been added in 399a36a, and since the beginning it considers all errors on the first file lookup as ENOENT, setting errno accordingly and letting the routine caller think that the directory is empty. While this is normally enough for the case of the backend, this can confuse callers of this routine on Windows as all errors would map to the same behavior. So, for example, even permission errors would be thought as having an empty directory, while there could be contents in it. This commit changes the error handling so as readdir() gets a behavior similar to native implementations: force errno=0 when seeing ERROR_FILE_NOT_FOUND as error and consider other errors as plain failures. While looking at the patch, I noticed that MinGW does not enforce errno=0 when looking at the first file, but it gets enforced on the next file lookups. A comment related to that was incorrect in the code. Reported-by: Yuri Kurenkov Diagnosed-by: Yuri Kurenkov, Grigory Smolkin Author: Konstantin Knizhnik Reviewed-by: Andrew Dunstan, Michael Paquier Discussion: https://postgr.es/m/2cad7829-8d66-e39c-b937-ac825db5203d@postgrespro.ru Backpatch-through: 9.4 https://git.postgresql.org/pg/commitdiff/754b90f657bd54b482524b73726dae4a9165031c - Teach SKIP_LOCKED to psql tab completion of VACUUM and ANALYZE. This was missing since 803b130, which has introduced the option for the user-facing VACUUM and ANALYZE. Author: Masahiko Sawada Discussion: https://postgr.es/m/CAD21AoD2TMdTxRhZ7WSp940V82_OAyPmgHnbi25UbbArLgA92Q@mail.gmail.com https://git.postgresql.org/pg/commitdiff/ff9bff0a85b6c23d622b4913ed38f459866f0d3a - Fix compatibility of pg_basebackup -R with 11 and older versions. When 2dedf4d9 has integrated recovery.conf into postgresql.conf, it also changed pg_basebackup -R in the way recovery configuration is generated. However this implementation forgot the fact that pg_basebackup needs to keep compatibility with older server versions as well. Reported-by: Devrim Gündüz Author: Sergei Kornilov, Michael Paquier Discussion: https://postgr.es/m/3458f7cd12d74acd90180a671c8d5a081d60e162.camel@gunduz.org https://git.postgresql.org/pg/commitdiff/beeb8e2e0717065296dc7b32daba2d66f0f931dd - Fix function signatures of pageinspect in documentation. tuple_data_split() lacked the type of the first argument, and heap_page_item_attrs() has reversed the first and second argument, with the bytea argument using an incorrect name. Author: Laurenz Albe Discussion: https://postgr.es/m/8f9ab7b16daf623e87eeef5203a4ffc0dece8dfd.camel@cybertec.at Backpatch-through: 9.6 https://git.postgresql.org/pg/commitdiff/e1e0e8d58c5c70da92e36cb9d59c2f7ecf839e00 - Tighten use of OpenTransientFile and CloseTransientFile. This fixes two sets of issues related to the use of transient files in the backend: 1) OpenTransientFile() has been used in some code paths with read-write flags while read-only is sufficient, so switch those calls to be read-only where necessary. These have been reported by Joe Conway. 2) When opening transient files, it is up to the caller to close the file descriptors opened. In error code paths, CloseTransientFile() gets called to clean up things before issuing an error. However in normal exit paths, a lot of callers of CloseTransientFile() never actually reported errors, which could leave a file descriptor open without knowing about it. This is an issue I complained about a couple of times, but never had the courage to write and submit a patch, so here we go. Note that one frontend code path is impacted by this commit so as an error is issued when fetching control file data, making backend and frontend to be treated consistently. Reported-by: Joe Conway, Michael Paquier Author: Michael Paquier Reviewed-by: Álvaro Herrera, Georgios Kokolatos, Joe Conway Discussion: https://postgr.es/m/20190301023338.GD1348@paquier.xyz Discussion: https://postgr.es/m/c49b69ec-e2f7-ff33-4f17-0eaa4f2cef27@joeconway.com https://git.postgresql.org/pg/commitdiff/82a5649fb9dbef12d04cd24799be6bf298d889a6 Peter Eisentraut pushed: - Remove volatile from latch API. This was no longer useful since the latch functions use memory barriers already, which are also compiler barriers, and volatile does not help with cross-process access. Discussion: https://www.postgresql.org/message-id/flat/20190218202511.qsfpuj5sy4dbezcw%40alap3.anarazel.de#18783c27d73e9e40009c82f6e0df0974 https://git.postgresql.org/pg/commitdiff/278584b526d71a3fe86f91be5870f99f38477e27 - Remove duplicate macro. The original commit appears to have accidentally introduced a duplicate definition. Keep only one of them. https://git.postgresql.org/pg/commitdiff/9b1384dd6a03c6ae1db1c2f5a8e87875f2f299ca Álvaro Herrera pushed: - Test partition functions with legacy inheritance children, too. It's worth immortalizing this behavior, per discussion. Discussion: https://postgr.es/m/20190228193203.GA26151@alvherre.pgsql https://git.postgresql.org/pg/commitdiff/d12fbe2f8e5daf84b07a61884a8ea5f84d6c5832 - pg_partition_ancestors. Adds another introspection feature for partitioning, necessary for further psql patches. Reviewed-by: Michaël Paquier Discussion: https://postgr.es/m/20190226222757.GA31622@alvherre.pgsql https://git.postgresql.org/pg/commitdiff/b96f6b19487fb9802216311b242c01c27c1938de - pg_dump: allow multiple rows per insert. This is useful to speed up loading data in a different database engine. Authors: Surafel Temesgen and David Rowley. Lightly edited by Álvaro. Reviewed-by: Fabien Coelho Discussion: https://postgr.es/m/CALAY4q9kumSdnRBzvRJvSRf2+BH20YmSvzqOkvwpEmodD-xv6g@mail.gmail.com https://git.postgresql.org/pg/commitdiff/7e413a0f82c8f8e9abb3981839191414de3aae08 - Add missing <limits.h>. Per buildfarm https://git.postgresql.org/pg/commitdiff/cb706ec4b60b3dede957ffdd70451b017eddffb0 - Fix the BY {REF,VALUE} clause of XMLEXISTS/XMLTABLE. This clause is used to indicate the passing mode of a XML document, but we were doing it wrong: we accepted BY REF and ignored it, and rejected BY VALUE as a syntax error. The reality, however, is that documents are always passed BY VALUE, so rejecting that clause was silly. Change things so that we accept BY VALUE. BY REF continues to be accepted, and continues to be ignored. Author: Chapman Flack Reviewed-by: Pavel Stehule Discussion: https://postgr.es/m/5C297BB7.9070509@anastigmatix.net https://git.postgresql.org/pg/commitdiff/eaaa5986ad03f7871fa95878460e1132cb7e8963 - Fix broken markup. https://git.postgresql.org/pg/commitdiff/ec51727f6eed38f302a10cdb0066f4484b0b5902 - Fix minor deficiencies in XMLTABLE, xpath(), xmlexists(). Correctly process nodes of more types than previously. In some cases, nodes were being ignored (nothing was output); in other cases, trying to return them resulted in errors about unrecognized nodes. In yet other cases, necessary escaping (of XML special characters) was not being done. Fix all those (as far as the authors could find) and add regression tests cases verifying the new behavior. I (Álvaro) was of two minds about backpatching these changes. They do seem bugfixes that would benefit most users of the affected functions; but on the other hand it would change established behavior in minor releases, so it seems prudent not to. Authors: Pavel Stehule, Markus Winand, Chapman Flack Discussion: https://postgr.es/m/CAFj8pRA6J25CtAZ2TuRvxK3gat7-bBUYh0rfE2yM7Hj9GD14Dg@mail.gmail.com https://postgr.es/m/8BDB0627-2105-4564-AA76-7849F028B96E@winand.at The elephant in the room as pointed out by Chapman Flack, not fixed in this commit, is that we still have XMLTABLE operating on XPath 1.0 instead of the standard-mandated XQuery (or even its subset XPath 2.0). Fixing that is a major undertaking, however. https://git.postgresql.org/pg/commitdiff/251cf2e27bec98274e8bb002608680bdc211319e - Fix crash with old libxml2. Certain libxml2 versions (such as the 2.7.6 commonly seen in older distributions, but apparently only on x86_64) contain a bug that causes xmlCopyNode, when called on a XML_DOCUMENT_NODE, to return a node that xmlFreeNode crashes on. Arrange to call xmlFreeDoc instead of xmlFreeNode for those nodes. Per buildfarm members lapwing and grison. Author: Pavel Stehule, light editing by Álvaro. Discussion: https://postgr.es/m/20190308024436.GA2374@alvherre.pgsql https://git.postgresql.org/pg/commitdiff/2e616dee9e601d36462dc4cc48eb0b6a1ff20051 - pg_upgrade: Ignore TOAST for partitioned tables. Since partitioned tables in pg12 do not have toast tables, trying to set the toast OID confuses pg_upgrade. Have pg_dump omit those values to avoid the problem. Per Andres Freund and buildfarm members crake and snapper Discussion: https://postgr.es/m/20190306204104.yle5jfbnqkcwykni@alap3.anarazel.de https://git.postgresql.org/pg/commitdiff/203749a8a66096171f808dd8e870d08d8ad57e5e Peter Geoghegan pushed: - Correct obsolete nbtree page split WAL comment. Commit 2c03216d831, which revamped the WAL record format, failed to update a comment referencing the old API. Update the comment. https://git.postgresql.org/pg/commitdiff/72c7c4e38610297b200721a7d5201f79e7ceef7c - Note case where nbtree VACUUM finishes splits. The nbtree README claims that VACUUM can never finish interrupted page splits by design. That isn't entirely accurate, though. Note an exception to the general rule. Discussion: https://postgr.es/m/CAH2-Wz=_Xvv8byzK_LvY4ci76OgsHCQzoKF7We8yG9waO7j6rA@mail.gmail.com https://git.postgresql.org/pg/commitdiff/35bc0ec7c800b303e0629913ac336924cf979dcc Heikki Linnakangas pushed: - Scan GiST indexes in physical order during VACUUM. Scanning an index in physical order is faster than walking it in logical order, because sequential I/O is faster than random I/O. The idea and code structure is borrowed from B-tree vacuum code. Patch by Andrey Borodin, with changes by me. Based on early work by Konstantin Kuznetsov, although the patch has been rewritten multiple times since his original version. Discussion: https://www.postgresql.org/message-id/1B9FAC6F-FA19-4A24-8C1B-F4F574844892%40yandex-team.ru https://git.postgresql.org/pg/commitdiff/fe280694d0d49e3d5f2666042b5e9ff0c8305341 Robert Haas pushed: - Removed unused variable, openLogOff. Antonin Houska Discussion: http://postgr.es/m/30413.1551870730@localhost https://git.postgresql.org/pg/commitdiff/93473c6ac805994a74e74ed13828c6c9433c8faf - Allow ATTACH PARTITION with only ShareUpdateExclusiveLock. We still require AccessExclusiveLock on the partition itself, because otherwise an insert that violates the newly-imposed partition constraint could be in progress at the same time that we're changing that constraint; only the lock level on the parent relation is weakened. To make this safe, we have to cope with (at least) three separate problems. First, relevant DDL might commit while we're in the process of building a PartitionDesc. If so, find_inheritance_children() might see a new partition while the RELOID system cache still has the old partition bound cached, and even before invalidation messages have been queued. To fix that, if we see that the pg_class tuple seems to be missing or to have a null relpartbound, refetch the value directly from the table. We can't get the wrong value, because DETACH PARTITION still requires AccessExclusiveLock throughout; if we ever want to change that, this will need more thought. In testing, I found it quite difficult to hit even the null-relpartbound case; the race condition is extremely tight, but the theoretical risk is there. Second, successive calls to RelationGetPartitionDesc might not return the same answer. The query planner will get confused if lookup up the PartitionDesc for a particular relation does not return a consistent answer for the entire duration of query planning. Likewise, query execution will get confused if the same relation seems to have a different PartitionDesc at different times. Invent a new PartitionDirectory concept and use it to ensure consistency. This ensures that a single invocation of either the planner or the executor sees the same view of the PartitionDesc from beginning to end, but it does not guarantee that the planner and the executor see the same view. Since this allows pointers to old PartitionDesc entries to survive even after a relcache rebuild, also postpone removing the old PartitionDesc entry until we're certain no one is using it. For the most part, it seems to be OK for the planner and executor to have different views of the PartitionDesc, because the executor will just ignore any concurrently added partitions which were unknown at plan time; those partitions won't be part of the inheritance expansion, but invalidation messages will trigger replanning at some point. Normally, this happens by the time the very next command is executed, but if the next command acquires no locks and executes a prepared query, it can manage not to notice until a new transaction is started. We might want to tighten that up, but it's material for a separate patch. There would still be a small window where a query that started just after an ATTACH PARTITION command committed might fail to notice its results -- but only if the command starts before the commit has been acknowledged to the user. All in all, the warts here around serializability seem small enough to be worth accepting for the considerable advantage of being able to add partitions without a full table lock. Although in general the consequences of new partitions showing up between planning and execution are limited to the query not noticing the new partitions, run-time partition pruning will get confused in that case, so that's the third problem that this patch fixes. Run-time partition pruning assumes that indexes into the PartitionDesc are stable between planning and execution. So, add code so that if new partitions are added between plan time and execution time, the indexes stored in the subplan_map[] and subpart_map[] arrays within the plan's PartitionedRelPruneInfo get adjusted accordingly. There does not seem to be a simple way to generalize this scheme to cope with partitions that are removed, mostly because they could then get added back again with different bounds, but it works OK for added partitions. This code does not try to ensure that every backend participating in a parallel query sees the same view of the PartitionDesc. That currently doesn't matter, because we never pass PartitionDesc indexes between backends. Each backend will ignore the concurrently added partitions which it notices, and it doesn't matter if different backends are ignoring different sets of concurrently added partitions. If in the future that matters, for example because we allow writes in parallel query and want all participants to do tuple routing to the same set of partitions, the PartitionDirectory concept could be improved to share PartitionDescs across backends. There is a draft patch to serialize and restore PartitionDescs on the thread where this patch was discussed, which may be a useful place to start. Patch by me. Thanks to Alvaro Herrera, David Rowley, Simon Riggs, Amit Langote, and Michael Paquier for discussion, and to Alvaro Herrera for some review. Discussion: http://postgr.es/m/CA+Tgmobt2upbSocvvDej3yzokd7AkiT+PvgFH+a9-5VV1oJNSQ@mail.gmail.com Discussion: http://postgr.es/m/CA+TgmoZE0r9-cyA-aY6f8WFEROaDLLL7Vf81kZ8MtFCkxpeQSw@mail.gmail.com Discussion: http://postgr.es/m/CA+TgmoY13KQZF-=HNTrt9UYWYx3_oYOQpu9ioNT49jGgiDpUEA@mail.gmail.com https://git.postgresql.org/pg/commitdiff/898e5e3290a72d288923260143930fb32036c00c Andres Freund pushed: - tableam: introduce table AM infrastructure. This introduces the concept of table access methods, i.e. CREATE ACCESS METHOD ... TYPE TABLE and CREATE TABLE ... USING (storage-engine). No table access functionality is delegated to table AMs as of this commit, that'll be done in following commits. Subsequent commits will incrementally abstract table access functionality to be routed through table access methods. That change is too large to be reviewed & committed at once, so it'll be done incrementally. Docs will be updated at the end, as adding them incrementally would likely make them less coherent, and definitely is a lot more work, without a lot of benefit. Table access methods are specified similar to index access methods, i.e. pg_am.amhandler returns, as INTERNAL, a pointer to a struct with callbacks. In contrast to index AMs that struct needs to live as long as a backend, typically that's achieved by just returning a pointer to a constant struct. Psql's \d+ now displays a table's access method. That can be disabled with HIDE_TABLEAM=true, which is mainly useful so regression tests can be run against different AMs. It's quite possible that this behaviour still needs to be fine tuned. For now it's not allowed to set a table AM for a partitioned table, as we've not resolved how partitions would inherit that. Disallowing allows us to introduce, if we decide that's the way forward, such a behaviour without a compatibility break. Catversion bumped, to add the heap table AM and references to it. Author: Haribabu Kommi, Andres Freund, Alvaro Herrera, Dimitri Golgov and others Discussion: https://postgr.es/m/20180703070645.wchpu5muyto5n647@alap3.anarazel.de https://postgr.es/m/20160812231527.GA690404@alvherre.pgsql https://postgr.es/m/20190107235616.6lur25ph22u5u5av@alap3.anarazel.de https://postgr.es/m/20190304234700.w5tmhducs5wxgzls@alap3.anarazel.de https://git.postgresql.org/pg/commitdiff/8586bf7ed8889f39a59dd99b292014b73be85342 - Fix bug in clearing of virtual tuple slot. I broke/typoed this in 4da597edf1bae0c. Astonishingly this mostly doesn't cause breakage, except when trying to change the tuple descriptor of a slot (because TTS_FLAG_FIXED is assumed to be set). Author: Andres Freund https://git.postgresql.org/pg/commitdiff/f21776185648537a7bb82dfdf89991fb2e0b9ca5 - tableam: Add pg_dump support. This adds pg_dump support for table AMs in a similar manner to how tablespaces are handled. That is, instead of specifying the AM for every CREATE TABLE etc, emit SET default_table_access_method statements. That makes it easier to change the AM for all/most tables in a dump, and allows restore to succeed even if some AM is not available. This increases the dump archive version, as a tables/matview's AM needs to be tracked therein. Author: Dimitri Dolgov, Andres Freund Discussion: https://postgr.es/m/20180703070645.wchpu5muyto5n647@alap3.anarazel.de https://postgr.es/m/20190304234700.w5tmhducs5wxgzls@alap3.anarazel.de https://git.postgresql.org/pg/commitdiff/3b925e905de3204ffef64fa4d53dd7bbac1a143f - Fix collation dependency in test introduced in 8586bf7ed8. Per buildfarm. https://git.postgresql.org/pg/commitdiff/836f634522d2f07efdec2f41d63b582aac95e546 - Fix collation dependency in test introduced in 8586bf7ed8, take 2. Per buildfarm. This time I hopefully actually made sure to get all the cases... https://git.postgresql.org/pg/commitdiff/863aa55624d891be57b54f8d12e61cb4fa9b70ef - Fix copy/out/readfuncs for accessMethod addition in 8586bf7ed8. This includes a catversion bump, as IntoClause is theoretically speaking part of storable rules. In practice I don't think that can happen, but there's no reason to be stingy here. Per buildfarm member calliphoridae. https://git.postgresql.org/pg/commitdiff/b1723423216749be9b28f8430c3b7180dec3fa70 - Fix equalfuncs for accessMethod addition in 8586bf7ed8. In a complete brown paper bag moment, I forgot to include equalfuncs in my previous fix of copy/out/readfuncs. Thanks Tom for noticing. Discussion: https://postgr.es/m/1659.1551903210@sss.pgh.pa.us https://git.postgresql.org/pg/commitdiff/d16a74c20ce3485d43902b0b1fb8ec1c11ec84a5 - Don't reuse slots between root and partition in ON CONFLICT ... UPDATE. Until now the the slot to store the conflicting tuple, and the result of the ON CONFLICT SET, where reused between partitions. That necessitated changing slots descriptor when switching partitions. Besides the overhead of switching descriptors on a slot (which requires memory allocations and prevents JITing), that's importantly also problematic for tableam. There individual partitions might belong to different tableams, needing different kinds of slots. In passing also fix ExecOnConflictUpdate to clear the existing slot at exit. Otherwise that slot could continue to hold a pin till the query ends, which could be far too long if the input data set is large, and there's no further conflicts. While previously also problematic, it's now more important as there will be more such slots when partitioned. Author: Andres Freund Reviewed-By: Robert Haas, David Rowley Discussion: https://postgr.es/m/20180703070645.wchpu5muyto5n647@alap3.anarazel.de https://git.postgresql.org/pg/commitdiff/277cb789836b5ddf81aabb80c2058268c70e2f36 Thomas Munro pushed: - Drop the vestigial "smgr" type. Before commit 3fa2bb31 this type appeared in the catalogs to select which of several block storage mechanisms each relation used. New features under development propose to revive the concept of different block storage managers for new kinds of data accessed via bufmgr.c, but don't need to put references to them in the catalogs. So, avoid useless maintenance work on this type by dropping it. Update some regression tests that were referencing it where any type would do. Discussion: https://postgr.es/m/CA%2BhUKG%2BDE0mmiBZMtZyvwWtgv1sZCniSVhXYsXkvJ_Wo%2B83vvw%40mail.gmail.com https://git.postgresql.org/pg/commitdiff/91595f9d49cf6fd6b6524f3269b2105b3ec18d96 - Remove useless header inclusion. https://git.postgresql.org/pg/commitdiff/42210524cca3a6d3161bcef9d9e687c7c1f5f6c4 Noah Misch pushed: - Avoid some table rewrites for ALTER TABLE .. SET DATA TYPE timestamp. When the timezone is UTC, timestamptz and timestamp are binary coercible in both directions. See b8a18ad4850ea5ad7884aa6ab731fd392e73b4ad and c22ecc6562aac895f0f0529707d7bdb460fd2a49 for the previous attempt in this problem space. Skip the table rewrite; for now, continue to needlessly rewrite any index on an affected column. Reviewed by Simon Riggs and Tom Lane. Discussion: https://postgr.es/m/20190226061450.GA1665944@rfd.leadboat.com https://git.postgresql.org/pg/commitdiff/3c5926301aea476025f118159688a6a88b2738bc Magnus Hagander pushed: - Track block level checksum failures in pg_stat_database. This adds a column that counts how many checksum failures have occurred on files belonging to a specific database. Both checksum failures during normal backend processing and those created when a base backup detects a checksum failure are counted. Author: Magnus Hagander Reviewed by: Julien Rouhaud https://git.postgresql.org/pg/commitdiff/6b9e875f7286d8535bff7955e5aa3602e188e436 - Add new clientcert hba option verify-full. This allows a login to require both that the cn of the certificate matches (like authentication type cert) *and* that another authentication method (such as password or kerberos) succeeds as well. The old value of clientcert=1 maps to the new clientcert=verify-ca, clientcert=0 maps to the new clientcert=no-verify, and the new option erify-full will add the validation of the CN. Author: Julian Markwort, Marius Timmer Reviewed by: Magnus Hagander, Thomas Munro https://git.postgresql.org/pg/commitdiff/0516c61b756e39ed6eb7a6bb54311a841002211a Alexander Korotkov pushed: - Support for INCLUDE attributes in GiST indexes. Similarly to B-tree, GiST index access method gets support of INCLUDE attributes. These attributes aren't used for tree navigation and aren't present in non-leaf pages. But they are present in leaf pages and can be fetched during index-only scan. The point of having INCLUDE attributes in GiST indexes is slightly different from the point of having them in B-tree. The main point of INCLUDE attributes in B-tree is to define UNIQUE constraint over part of attributes enabled for index-only scan. In GiST the main point of INCLUDE attributes is to use index-only scan for attributes, whose data types don't have GiST opclasses. Discussion: https://postgr.es/m/73A1A452-AD5F-40D4-BD61-978622FF75C1%40yandex-team.ru Author: Andrey Borodin, with small changes by me Reviewed-by: Andreas Karlsson https://git.postgresql.org/pg/commitdiff/f2e403803fe6deb8cff59ea09dff42c6163b2110 == Pending Patches == Kyotaro HORIGUCHI sent in another revision of a patch to add a TAP test for the copy-truncation optimization, write WAL for empty nbtree index builds, add infrastructure to the WAL-logging skip feature, and fix the WAL-logging skipping feature. Andres Freund sent in another revision of a patch to add infrastructure for table access methods and use same to support pg_dump for them. Kirk Jamison sent in another revision of a patch for pgbench to add doCustom cleanup. David Rowley sent in a PoC patch to exploit a sped-up list_nth call. Álvaro Herrera sent in another revision of a patch to fix psql's display of foreign keys. Álvaro Herrera sent in another revision of a patch to report progress of CREATE INDEX operations including hash indexes. Justin Pryzby sent in another revision of a patch to avoid repetitive log of PREPARE during EXECUTE of prepared statements. Ryo Matsumura sent in another revision of a patch to fix PREPARE in ECPG. Takeshi Ideriha sent in another revision of a patch to protect syscache from bloating with negative cache entries. Tatsuro Yamada sent in three more revisions of a patch to implement a progress monitor for CLUSTER. Amit Langote and Imai Yoshikazu traded patches to speed up planning with partitions. Amit Khandekar sent in another revision of a patch to implement minimal logical decoding on standbys. Michael Banck sent in another revision of a patch to make it possible to verify page checksums online. Ibrar Ahmed and Corey Huinker traded patches to add \describe and friends to psql. Tom Lane and David Rowley traded patches to remove [Merge]Append nodes which contain a single subpath. Karl O. Pinc sent in eight revisions of a patch to document base64 encoding. Peter Geoghegan and Heikki Linnakangas traded patches to make all nbtree entries unique by having heap TIDs participate in comparisons. Heikki Linnakangas and Andrey Borodin traded patches to implement GiST VACUUM. Dmitry Dolgov sent in another revision of a patch to implement index skip scans. Nikita Glukhov sent in another revision of a patch to implement the SQL/JSON functions. Nikita Glukhov sent in another revision of a patch to implement JSON_TABLE. Filip Rembiałkowski sent in a patch to fix some bugs in contrib/ltree. Tom Lane sent in a patch to fix a few issues in a patch Paul Ramsey sent to improve operator and function support. Shawn Debnath sent in two more revisions of a patch to refactor the checkpointer's fsync request queue. Jerry Jelinek sent in two more revisions of a patch to make it possible to disable WAL recycling. Amul Sul sent in two more revisions of a patch to improve the partition matching algorithm for partition-wise join. Filip Rembiałkowski sent in two more revisions of a patch to add a FORCE option to DROP DATABASE and a corresponding --force option to dropdb. David Rowley sent in two more revisions of a patch to allow Append to be used in place of MergeAppend for some cases. Nikita Glukhov and Alexander Korotkov traded patches to add JSONPATH. Sergei Kornilov sent in two more revisions of a patch to use CHECK when possible in lieu of a table scan in ALTER TABLE ... SET NOT NULL. Robbie Harwood sent in a patch to add tests for GSSAPI/krb5 encryption. David Rowley sent in another revision of a patch to turn NOT IN into anti-JOINs during planning where possible. Michaël Paquier sent in a patch to fix a server crash in transformPartitionRangeBounds. Chris Travers sent in another revision of a patch to add src/backend/utils/misc/README.SIGNAL_HANDLING. Amit Langote sent in a patch to fix a bug in tuple-routing to foreign partitions. Nikita Glukhov sent in another revision of a patch to add kNN support to B-tree indexes. David Rowley sent in a patch to make pg_dump emit ATTACH PARTITION instead of PARTITION OF. Thomas Munro and Michaël Paquier traded patches to document few more wait events. Masahiko Sawada sent in another revision of a patch to add block-level parallel vacuum. Masahiko Sawada sent in two more revisions of a patch to add a DISABLE_INDEX_CLEANUP option to VACUUM. David Rowley and Amit Langote traded patches to better document the limitations of UPDATEs vs. partitions that happen to be FOREIGN TABLEs. Etsuro Fujita sent in two more revisions of a patch to perform the UPPERREL_ORDERED and UPPERREL_FINAL steps remotely for the PostgreSQL FDW, and refactor create_limit_path() to share cost adjustment code. Andres Freund and Haribabu Kommi traded patches to implement pluggable storage. Taylor Vesely sent in a patch to use batch insert in both the CTAS and the MatView code. Aleksey Kondratov sent in another revision of a patch for pg_rewind which adds options to use restore_command either from the command line or from the cluster configuration files (postgresql.conf, postgresql.conf.auto, e.g.). Sandro Mani sent in a patch to Mingw to fix the import library extension, and build actual static libraries. David Steele sent in a patch to add exclusive backup deprecation notes to the documentation. Artur Zakirov sent in a patch to ensure that VACUUM, only in single-user mode, drops orphan temp tables. Filip Rembiałkowski sent in two more revisions of a patch to add a COLLAPSE option to NOTIFY which de-duplicates messages. Chapman Flack sent in another revision of a patch to better document the data types in XML. Michaël Paquier sent in a patch to Fix memleaks and error handling in jsonb_plpython. Peter Eisentraut sent in another revision of a patch to add collations with nondeterministic comparison. Shaoqi Bai sent in two revisions of a patch to add a tablespace TAP test to pg_rewind. Pavel Stěhule and Álvaro Herrera traded patches to handle XML more robustly. Magnus Hagander and Julien Rouhaud traded patches to fix a bug that manifested as checksum errors in pg_stat_database. Alexander Korotkov sent in another revision of a patch to implement covering GiST indexes. Noah Misch sent in another revision of a patch to implement a weaker shmem interlock without postmaster.pid. Magnus Hagander sent in another revision of a patch to add clientcert=verify-full as an auth method. Paul Martinez sent in another revision of a patch to include all columns in the default names for foreign key constraints. Pavel Stěhule sent in two more revisions of a patch to add pragmas to PL/pgsql. Paul A Jungwirth sent in another revision of a patch to add temporal PRIMARY KEYs. David Rowley sent in two more revisions of a patch to fix a performance issue in foreign-key-aware join estimation. Fabien COELHO sent in another revision of a patch to add a TAP test for pgbench's progress reports. John Naylor sent in a patch to skip skip transfering small FSMs during pg_upgrade. John Naylor sent in another revision of a patch to add a script for renumbering OIDs.
В списке pgsql-announce по дате отправления: