== PostgreSQL Weekly News - March 10, 2019 ==

== 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(at)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(at)fetter(dot)org(dot)

== 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.