== PostgreSQL Weekly News - June 16, 2019 ==

== PostgreSQL Weekly News - June 16, 2019 ==

Security releases 11.4, 10.9, 9.6.14, 9.5.18, 9.4.23 coming soon. Get ready to
upgrade!
https://www.postgresql.org/message-id/98EFF6DC-805B-4309-AD89-6F3223CBDA7F%40postgresql.org

== PostgreSQL Product News ==

psycopg2 2.8.3, a Python connector for PostgreSQL, released.
http://initd.org/psycopg/articles/2019/06/14/psycopg-283-released/

== PostgreSQL Jobs for June ==

http://archives.postgresql.org/pgsql-jobs/2019-06/

== PostgreSQL Local ==

pgibz will be held in Ibiza, Spain on June 19-23, 2019. The CfP is open.
https://pgibz.io/

Swiss PGDay 2019 will take place in Rapperswil (near Zurich) on June 28, 2019.
Registration is open.
http://www.pgday.ch/2019/

PostgresLondon 2019 will be July 2-3, 2019 with an optional training day on
July 1.
http://postgreslondon.org

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
The Call for Papers is open through June 30, 2019.
https://postgresconf.org/conferences/SouthAfrica2019

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

Noah Misch pushed:

- Reconcile nodes/*funcs.c with PostgreSQL 12 work. One would have needed
out-of-tree code to observe the defects. Remove unreferenced fields instead
of completing their support functions. Since in-tree code can't reach
_readIntoClause(), no catversion bump.
https://git.postgresql.org/pg/commitdiff/44982e7d09cf9b1a08fded7fb0ca9a60dae869a5

- MSVC: Reconcile clean.bat with PostgreSQL 12 work.
https://git.postgresql.org/pg/commitdiff/ae78a9456c8986a8869ec470b40e177b5617a2f1

Alexander Korotkov pushed:

- Fix docs indentation in pgtrgm.sgml. 5871b884 introduced
pg_trgm.word_similarity_threshold GUC, but its documentation contains wrong
indentation. This commit fixes that. Backpatch for easier backpatching of
other documentation fixes. Discussion:
https://postgr.es/m/4c735d30-ab59-fc0e-45d8-f90eb5ed3855%402ndquadrant.com
Author: Ian Barwick Backpatch-through: 9.6
https://git.postgresql.org/pg/commitdiff/852ddfbbe2121c905f2ecd750b1ec4cbfbea9f76

- Add docs of missing GUC to pgtrgm.sgml. be8a7a68 introduced
pg_trgm.strict_word_similarity_threshold GUC, but missed docs for that. This
commit fixes that. Discussion:
https://postgr.es/m/fc907f70-448e-fda3-3aa4-209a59597af0%402ndquadrant.com
Author: Ian Barwick Reviewed-by: Masahiko Sawada, Michael Paquier
Backpatch-through: 9.6
https://git.postgresql.org/pg/commitdiff/ba3783e0244e3502d1b08896ffc72b3a88dbdb83

- Fix operator naming in pg_trgm GUC option descriptions. Descriptions of
pg_trgm GUC options have % replaced with %% like it was a printf-like format.
But that's not needed since they are just plain strings. This commit fixed
that. Backpatch to last supported version since this error present from the
beginning. Reported-by: Masahiko Sawada Discussion:
https://postgr.es/m/CAD21AoAgPKODUsu9gqUFiNqEOAqedStxJ-a0sapsJXWWAVp%3Dxg%40mail.gmail.com
Backpatch-through: 9.4
https://git.postgresql.org/pg/commitdiff/b6987a885ba9216e1f4319441303ee115556922c

Álvaro Herrera pushed:

- Make pg_dump emit ATTACH PARTITION instead of PARTITION OF (reprise). Using
PARTITION OF can result in column ordering being changed from the database
being dumped, if the partition uses a column layout different from the
parent's. It's not pg_dump's job to editorialize on table definitions, so
this is not acceptable; back-patch all the way back to pg10, where partitioned
tables where introduced. This change also ensures that partitions end up in
the correct tablespace, if different from the parent's; this is an oversight
in ca4103025dfe (in pg12 only). Partitioned indexes (in pg11) don't have this
problem, because they're already created as independent indexes and attached
to their parents afterwards. This change also has the advantage that the
partition is restorable from the dump (as a standalone table) even if its
parent table isn't restored. The original commits (3b23552ad8bb in branch
master) failed to cover subsidiary column elements correctly, such as NOT NULL
constraint and CHECK constraints, as reported by Rushabh Lathia (initially as
a failure to restore serial columns). They were reverted. This
recapitulation commit fixes those problems. Add some pg_dump tests to verify
these things more exhaustively, including constraints with legacy-inheritance
tables, which were not tested originally. In branches 10 and 11, add a local
constraint to the pg_dump test partition that was added by commit 2d7eeb1b1492
to master. Author: Álvaro Herrera, David Rowley Reviewed-by: Álvaro Herrera
Discussion:
https://postgr.es/m/CAKJS1f_1c260nOt_vBJ067AZ3JXptXVRohDVMLEBmudX1YEx-A@mail.gmail.com
Discussion: https://postgr.es/m/20190423185007.GA27954@alvherre.pgsql
Discussion:
https://postgr.es/m/CAGPqQf0iQV=PPOv2Btog9J9AwOQp6HmuVd6SbGTR_v3Zp2XT1w@mail.gmail.com
https://git.postgresql.org/pg/commitdiff/33a53130a89447e171a8268ae0b221bb48af6468

- Fix order of steps in DISCARD ALL documentation. The docs have always been
slightly inaccurate, but got particularly so in a874fe7b4c89, which made
DISCARD ALL occur before everything else; reorder. Author: Jan Chochol
Discussion:
https://postgr.es/m/CAEASf_3TzBbnXm64HpnD5zCZEh8An9jN8ubMR=De-vOXHMHGeA@mail.gmail.com
https://git.postgresql.org/pg/commitdiff/9f05c44ba4a4a6a857b957734bb369a2bb4dd62b

- Fix double-word typos. Discussion:
https://postgr.es/m/20190612184527.GA24266@alvherre.pgsql Reviewed-by: Michaël
Paquier
https://git.postgresql.org/pg/commitdiff/b9768458154de6c001af23efc02514f120c6094f

- Avoid spurious deadlocks when upgrading a tuple lock. When two (or more)
transactions are waiting for transaction T1 to release a tuple-level lock, and
transaction T1 upgrades its lock to a higher level, a spurious deadlock can be
reported among the waiting transactions when T1 finishes. The simplest
example case seems to be: T1: select id from job where name = 'a' for key
share; Y: select id from job where name = 'a' for update; -- starts waiting
for X Z: select id from job where name = 'a' for key share; T1: update job set
name = 'b' where id = 1; Z: update job set name = 'c' where id = 1; -- starts
waiting for X T1: rollback; At this point, transaction Y is rolled back on
account of a deadlock: Y holds the heavyweight tuple lock and is waiting for
the Xmax to be released, while Z holds part of the multixact and tries to
acquire the heavyweight lock (per protocol) and goes to sleep; once X releases
its part of the multixact, Z is awakened only to be put back to sleep on the
heavyweight lock that Y is holding while sleeping. Kaboom. This can be
avoided by having Z skip the heavyweight lock acquisition. As far as I can
see, the biggest downside is that if there are multiple Z transactions, the
order in which they resume after X finishes is not guaranteed. Backpatch to
9.6. The patch applies cleanly on 9.5, but the new tests don't work there
(because isolationtester is not smart enough), so I'm not going to risk it.
Author: Oleksii Kliukin Discussion:
https://postgr.es/m/B9C9D7CD-EB94-4635-91B6-E558ACEC0EC3@hintbits.com
https://git.postgresql.org/pg/commitdiff/de87a084c0a5ac927017cd0834b33a932651cfc9

- Silence compiler warning. Introduced in de87a084c0a5.
https://git.postgresql.org/pg/commitdiff/3da73d6839dc47f1f47ca57974bf28e5abd9b572

- Tweak libpq's PQhost, PQhostaddr, and psql's \connect. Fixes some problems
introduced by 6e5f8d489acc: * When reusing conninfo data from the previous
connection in \connect, the host address should only be reused if it was
specified as hostaddr; if it wasn't, then 'host' is resolved afresh. We
were reusing the same IP address, which ignores a possible DNS change as
well as any other addresses that the name resolves to than the one that was
used in the original connection. * PQhost, PQhostaddr: Don't present
user-specified hostaddr when we have an inet_net_ntop-produced equivalent
address. The latter has been put in canonical format, which is cleaner (so
it produces "127.0.0.1" when given "host=2130706433", for example). *
Document the hostaddr-reusing aspect of \connect. * Fix some code comments
Author: Fabien Coelho Reported-by: Noah Misch Discussion:
https://postgr.es/m/20190527203713.GA58392@gust.leadboat.com
https://git.postgresql.org/pg/commitdiff/313f56ce2d1b9dfd3483e4f39611baa27852835a

- Add pg_dumpall --rows-per-insert. Commit 7e413a0f82c8 added that option to
pg_dump, but neglected to teach pg_dumpall how to pass it along. Repair.
Author: Fabien Coelho Reported-by: Peter Eisentraut Reviewed-by: David Rowley
Discussion:
https://postgr.es/m/45f50c59-ddbb-8cf2-eedb-81003f603528@2ndquadrant.com
https://git.postgresql.org/pg/commitdiff/a193cbec1199e4e529c46645b7aecbf38956048b

Robert Haas pushed:

- tableam: Fix index_build_range_scan parameter name. All of the other code
thinks that the 8th parameter is the number of blocks, but this declaration
thinks that it's the ending block number. Repair this inconsistency. Patch by
me, reviewed by Andres Freund. Discussion:
http://postgr.es/m/CA+TgmoY49ManQWnJtiwkuytXBkmyTuDFqb74Pr4Zn2Nq9TuNBQ@mail.gmail.com
https://git.postgresql.org/pg/commitdiff/132a1c101a205ee52ec7d731abcb8593a6354097

Michaël Paquier pushed:

- Fix documentation of ALTER TABLE for stored values. Author: Masahiko Sawada
Discussion:
https://postgr.es/m/CAD21AoAA_gvZ002U6kovOHu0FsM7ieoCzdSqWBd7_KaQL0UMKg@mail.gmail.com
https://git.postgresql.org/pg/commitdiff/727e45c8a457ab7378494ff33bf3b50607d303a8

- Fix handling of COMMENT for domain constraints. For a non-superuser, changing
a comment on a domain constraint was leading to a cache lookup failure as the
code tried to perform the ownership lookup on the constraint OID itself,
thinking that it was a type, but this check needs to happen on the type the
domain constraint relies on. As the type a domain constraint relies on can be
guessed directly based on the constraint OID, first fetch its type OID and
perform the ownership on it. This is broken since 7eca575, which has split
the handling of comments for table constraints and domain constraints, so
back-patch down to 9.5. Reported-by: Clemens Ladisch Author: Daniel
Gustafsson, Michael Paquier Reviewed-by: Álvaro Herrera Discussion:
https://postgr.es/m/15833-808e11904835d26f@postgresql.org Backpatch-through:
9.5
https://git.postgresql.org/pg/commitdiff/ceac4505d3428a8414b4f3d6708ea81506811a5f

- Use OpenSSL-specific ifdefs in sha2.h. In order to separate OpenSSL's SHA
symbols, this header has been using USE_SSL, which is equivalent to
USE_OPENSSL. There is now only one SSL implementation included in the tree,
so this works fine, but when adding a new SSL implementation this would run
into failures. Author: Daniel Gustafsson Discussion:
https://postgr.es/m/0DF29010-CE26-4F51-85A6-9C8ABF5536F9@yesql.se
https://git.postgresql.org/pg/commitdiff/96719e52b1abd3a8c712d06a809b7e6c97c2e3a0

- Fix typos and inconsistencies in code comments. Author: Alexander Lakhin
Discussion: https://postgr.es/m/dec6aae8-2d63-639f-4d50-20e229fb83e3@gmail.com
https://git.postgresql.org/pg/commitdiff/f43608bda2111a1fda514d1bed4df313ee2bbec3

Etsuro Fujita pushed:

- postgres_fdw: Reorder C includes. Reorder header files in postgres_fdw.c and
connection.c in alphabetical order. Author: Etsuro Fujita Reviewed-by: Alvaro
Herrera Discussion:
https://postgr.es/m/CAPmGK17ZmNb-EELqu8LmMh2t2uFdbfWNVDEfDO5-bpejHPONMQ@mail.gmail.com
https://git.postgresql.org/pg/commitdiff/92a88644d2b7208e98d9e913c3a574c5c2fe7b78

- postgres_fdw: Account for triggers in non-direct remote UPDATE planning.
Previously, in postgresPlanForeignModify, we planned an UPDATE operation on a
foreign table so that we transmit only columns that were explicitly targets of
the UPDATE, so as to avoid unnecessary data transmission, but if there were
BEFORE ROW UPDATE triggers on the foreign table, those triggers might change
values for non-target columns, in which case we would miss sending changed
values for those columns. Prevent optimizing away transmitting all columns if
there are BEFORE ROW UPDATE triggers on the foreign table. This is an
oversight in commit 7cbe57c34 which added triggers on foreign tables, so apply
the patch all the way back to 9.4 where that came in. Author: Shohei
Mochizuki Reviewed-by: Amit Langote Discussion:
https://postgr.es/m/201905270152.x4R1q3qi014550@toshiba.co.jp
https://git.postgresql.org/pg/commitdiff/8b6da83d162cb0ac9f6d21082727bbd45c972c53

- postgres_fdw: Fix costing of pre-sorted foreign paths with local stats. Commit
aa09cd242 modified estimate_path_cost_size() so that it reuses cached costs of
a basic foreign path for a given foreign-base/join relation when costing
pre-sorted foreign paths for that relation, but it incorrectly re-computed
retrieved_rows, an estimated number of rows fetched from the remote side,
which is needed for costing both the basic and pre-sorted foreign paths. To
fix, handle retrieved_rows the same way as the cached costs: store in that
relation's fpinfo the retrieved_rows estimate computed for costing the basic
foreign path, and reuse it when costing the pre-sorted foreign paths. Also,
reuse the rows/width estimates stored in that relation's fpinfo when costing
the pre-sorted foreign paths, to make the code consistent. In commit
ffab494a4, to extend the costing mentioned above to the foreign-grouping case,
I made a change to add_foreign_grouping_paths() to store in a given
foreign-grouped relation's RelOptInfo the rows estimate for that relation for
reuse, but this patch makes that change unnecessary since we already store the
row estimate in that relation's fpinfo, which this patch reuses when costing a
foreign path for that relation with the sortClause ordering; remove that
change. In passing, fix thinko in commit 7012b132d: in
estimate_path_cost_size(), the width estimate for a given foreign-grouped
relation to be stored in that relation's fpinfo was reset incorrectly when
costing a basic foreign path for that relation with local stats. Apply the
patch to HEAD only to avoid destabilizing existing plan choices. Author:
Etsuro Fujita Discussion:
https://postgr.es/m/CAPmGK17jaJLPDEkgnP2VmkOg=5wT8YQ1CqssU8JRpZ_NSE+dqQ@mail.gmail.com
https://git.postgresql.org/pg/commitdiff/08d2d58a2a1c8ef8d39e8132d39ee14a1d029500

Andres Freund pushed:

- Don't access catalogs to validate GUCs when not connected to a DB. Vignesh
found this bug in the check function for default_table_access_method's check
hook, but that was just copied from older GUCs. Investigation by Michael and
me then found the bug in further places. When not connected to a database
(e.g. in a walsender connection), we cannot perform (most) GUC checks that
need database access. Even when only shared tables are needed, unless they're
nailed (c.f. RelationCacheInitializePhase2()), they cannot be accessed without
pg_class etc. being present. Fix by extending the existing
IsTransactionState() checks to also check for MyDatabaseOid. Reported-By:
Vignesh C, Michael Paquier, Andres Freund Author: Vignesh C, Andres Freund
Discussion:
https://postgr.es/m/CALDaNm1KXK9gbZfY-p_peRFm_XrBh1OwQO1Kk6Gig0c0fVZ2uw%40mail.gmail.com
Backpatch: 9.4-
https://git.postgresql.org/pg/commitdiff/fff2a7d7bd09db38e1bafc1303c29b10a9805dc0

Tom Lane pushed:

- Fix conversion of JSON strings to JSON output columns in json_to_record().
json_to_record(), when an output column is declared as type json or jsonb,
should emit the corresponding field of the input JSON object. But it got this
slightly wrong when the field is just a string literal: it failed to escape
the contents of the string. That typically resulted in syntax errors if the
string contained any double quotes or backslashes. jsonb_to_record() handles
such cases correctly, but I added corresponding test cases for it too, to
prevent future backsliding. Improve the documentation, as it provided only a
very hand-wavy description of the conversion rules used by these functions.
Per bug report from Robert Vollmert. Back-patch to v10 where the error was
introduced (by commit cf35346e8). Note that PG 9.4 - 9.6 also get this case
wrong, but differently so: they feed the de-escaped contents of the string
literal to json[b]_in. That behavior is less obviously wrong, so possibly it's
being depended on in the field, so I won't risk trying to make the older
branches behave like the newer ones. Discussion:
https://postgr.es/m/D6921B37-BD8E-4664-8D5F-DB3525765DCD@vllmrt.net
https://git.postgresql.org/pg/commitdiff/6f34fcbbd5ad5a6546710e7b90e6331cacfd36dc

- Fix ALTER COLUMN TYPE failure with a partial exclusion constraint.
ATExecAlterColumnType failed to consider the possibility that an index that
needs to be rebuilt might be a child of a constraint that needs to be rebuilt.
We missed this so far because usually a constraint index doesn't have a direct
dependency on its table, just on the constraint object. But if there's a WHERE
clause, then dependency analysis of the WHERE clause results in direct
dependencies on the column(s) mentioned in WHERE. This led to trying to drop
and rebuild both the constraint and its underlying index. In v11/HEAD, we
successfully drop both the index and the constraint, and then try to rebuild
both, and of course the second rebuild hits a duplicate-index-name problem.
Before v11, it fails with obscure messages about a missing relation OID, due
to trying to drop the index twice. This is essentially the same kind of
problem noted in commit 20bef2c31: the possible dependency linkages are
broader than what ATExecAlterColumnType was designed for. It was probably OK
when written, but it's certainly been broken since the introduction of partial
exclusion constraints. Fix by adding an explicit check for whether any of the
indexes-to-be-rebuilt belong to any of the constraints-to-be-rebuilt, and
ignoring any that do. In passing, fix a latent bug introduced by commit
8b08f7d48: in get_constraint_index() we must "continue" not "break" when
rejecting a relation of a wrong relkind. This is harmless today because we
don't expect that code path to be taken anyway; but if there ever were any
relations to be ignored, the existing coding would have an extremely
undesirable dependency on the order of pg_depend entries. Also adjust a
couple of obsolete comments. Per bug #15835 from Yaroslav Schekin.
Back-patch to all supported branches. Discussion:
https://postgr.es/m/15835-32d9b7a76c06a7a9@postgresql.org
https://git.postgresql.org/pg/commitdiff/e76de886157b7f974d4d247908b242607cfbf043

- Fix incorrect printing of queries with duplicated join names. Given a query in
which multiple JOIN nodes used the same alias (which'd necessarily be in
different sub-SELECTs), ruleutils.c would assign the JOIN nodes distinct
aliases for clarity ... but then it forgot to print the modified aliases when
dumping the JOIN nodes themselves. This results in a dump/reload hazard for
views, because the emitted query is flat-out incorrect: Vars will be printed
with table names that have no referent. This has been wrong for a long time,
so back-patch to all supported branches. Philip Dubé Discussion:
https://postgr.es/m/CY4PR2101MB080246F2955FF58A6ED1FEAC98140@CY4PR2101MB0802.namprd21.prod.outlook.com
https://git.postgresql.org/pg/commitdiff/3d99a81397abb8bb7b95aee794d6644e174e174f

- Doc: improve description of allowed spellings for Boolean input. datatype.sgml
failed to explain that boolin() accepts any unique prefix of the basic input
strings. Indeed it was actively misleading because it called out a few
minimal prefixes without mentioning that there were more valid inputs. I also
felt that it wasn't doing anybody any favors by conflating SQL key words,
valid Boolean input, and string literals containing valid Boolean input.
Rewrite in hopes of reducing the confusion. Per bug #15836 from Yuming Wang,
as diagnosed by David Johnston. Back-patch to supported branches. Discussion:
https://postgr.es/m/15836-656fab055735f511@postgresql.org
https://git.postgresql.org/pg/commitdiff/9729c9360886bee7feddc6a1124b0742de4b9f3d

- Doc: fix bogus example. This wasn't incorrect SQL, but it was doing cm-to-inch
conversion backward, so it might confuse readers. Per bug #15849 from
TAKATSUKA Haruka. Discussion:
https://postgr.es/m/15849-37ad0c561a836107@postgresql.org
https://git.postgresql.org/pg/commitdiff/7dc6ae37def50b5344c157eee5e029a09359f8ee

- Mark ReplicationSlotCtl as PGDLLIMPORT. Also MyReplicationSlot, in branches
where it wasn't already. This was discussed in the thread that resulted in
c572599c6, but for some reason nobody pulled the trigger. Now that we have
another request for the same thing, we should just do it. Craig Ringer
Discussion:
https://postgr.es/m/CAMsr+YFTsq-86MnsNng=mPvjjh5EAbzfMK0ptJPvzyvpFARuRg@mail.gmail.com
Discussion: https://postgr.es/m/345138875.20190611151943@cybertec.at
https://git.postgresql.org/pg/commitdiff/3c8f8f6ebefb3c2debd1cad1ba445a729290dac7

- Avoid combinatorial explosion in add_child_rel_equivalences(). If an
EquivalenceClass member expression includes variables from multiple
appendrels, then instead of producing one substituted expression per child
relation as intended, we'd create additional child expressions for
combinations of children of different appendrels. This happened because the
child expressions generated while considering the first appendrel were taken
as sources during substitution of the second appendrel, and so on. The extra
expressions are useless, and are harmless unless there are too many of them
--- but if you have several appendrels with a thousand or so members each, it
gets bad fast. To fix, consider only original (non-em_is_child) EC members as
candidates to be expanded. This requires the ability to substitute directly
from a top parent relation's Vars to those of an indirect descendant relation,
but we already have that in adjust_appendrel_attrs_multilevel(). Per bug
#15847 from Feike Steenbergen. This is a longstanding misbehavior, but it's
only worth worrying about when there are more appendrel children than we've
historically considered wise to use. So I'm not going to take the risk of
back-patching this. Discussion:
https://postgr.es/m/15847-ea3734094bf8ae61@postgresql.org
https://git.postgresql.org/pg/commitdiff/d25ea0127598dd0ad27a029215172f396fbd1009

- Further fix privileges on pg_statistic_ext[_data]. We don't need to restrict
column privileges on pg_statistic_ext; all of that data is OK to read
publicly. What we *do* need to do, which was overlooked by 6cbfb784c, is
revoke public read access on pg_statistic_ext_data; otherwise we still have
the same security hole we started with. Catversion bump to ensure that
installations calling themselves beta2 will have this fix.
Diagnosis/correction by Dean Rasheed and Tomas Vondra, but I'm going to go
ahead and push this fix ASAP so we get more buildfarm cycles on it.
Discussion: https://postgr.es/m/8833.1560647898@sss.pgh.pa.us
https://git.postgresql.org/pg/commitdiff/6973b058bc8d32e104bed99c134a4fab4b5dfe13

David Rowley pushed:

- doc: Add best practises section to partitioning docs. A few questionable
partitioning designs have been cropping up lately around the mailing lists.
Generally, these cases have been partitioning using too many partitions which
have caused performance or OOM problems for the users. Since we have very
little else to guide users into good design, here we add a new section to the
partitioning documentation with some best practise guidelines for good design.
Reviewed-by: Justin Pryzby, Amit Langote, Alvaro Herrera Discussion:
https://postgr.es/m/CAKJS1f-2rx+E9mG3xrCVHupefMjAp1+tpczQa9SEOZWyU7fjEA@mail.gmail.com
Backpatch-through: 10
https://git.postgresql.org/pg/commitdiff/e788e849addd56007a0e75f3b5514f294a0f3bca

- doc: Fix grammatical error in partitioning docs. Reported-by: Amit Langote
Discussion:
https://postgr.es/m/CA+HiwqGZFkKi0TkBGYpr2_5qrRAbHZoP47AP1BRLUOUkfQdy_A@mail.gmail.com
Backpatch-through: 10
https://git.postgresql.org/pg/commitdiff/ddc053dc503a773267a4d035418e0c81ce3b4851

Bruce Momjian pushed:

- doc: PG 12 relnotes, merge new SQL partition function items. Reported-by:
Andres Freund Discussion:
https://postgr.es/m/20190528155823.3e4cezblxjxotq2q@alap3.anarazel.de
https://git.postgresql.org/pg/commitdiff/6a631a454664c145b7da88c9ef3976f8ac3ab0b1

- doc: PG 12 relnotes, list added snowball/FTS languages. Reported-by: Adrien
Nayrat Discussion:
https://postgr.es/m/9d6a7515-bcd8-05be-d2a5-e81dc11023cd@anayrat.info
https://git.postgresql.org/pg/commitdiff/968072837173a0c0fa426114b17177f997b85473

- docs: PG 12 relnotes, update btree items. Reported-by: Peter Geoghegan
Discussion:
https://postgr.es/m/CAH2-Wzn-aH4ToZEWR05ELSSp7bO_JMn=sMPfUhNruwVmCXKh-w@mail.gmail.com
https://git.postgresql.org/pg/commitdiff/4bfb79ff6b1fd400b3dbc761921472173675a968

- doc: PG 12 relnotes: update wording on truncate/vacuum item. This item
prevents unauthorized locking of relations, and the previous wording was
unclear. Reported-by: Michael Paquier Discussion:
https://postgr.es/m/20190522072651.GC1278@paquier.xyz
https://git.postgresql.org/pg/commitdiff/4f41a7227511a3f96148b7f3d4ae6f0c350bc7b2

- docs: PG 12 relnotes, move vacuumdb to the client app section. It was
previously incorrectly placed in the server application section. Reported-by:
Tatsuo Ishii Discussion:
https://postgr.es/m/20190526.215341.1023150119477784132.t-ishii@sraoss.co.jp
https://git.postgresql.org/pg/commitdiff/18ef7a34fe616dc63f7de672a5ab7dd88137dc5c

- docs: PG 12 relnotes, update self-join item to mention ctid. Reported-by:
Robert Haas Discussion:
https://postgr.es/m/CA+TgmoY==TZwk-4cM3Usebq1f=j7Hpm1brFbk1v48h45wsWzzg@mail.gmail.com
https://git.postgresql.org/pg/commitdiff/be2e024bd6527b050da4ad02c921869fed84bb5d

- doc: PG 12 relnotes, add mention of single-child optimization. Add mention of
single-child optimization for partitions and UNION ALL. Reported-by: David
Rowley Discussion:
https://postgr.es/m/CAKJS1f8R8riwBXw==7ijV=UZNuhP+3qXgDBKSiM+=_cTf4mXXw@mail.gmail.com
https://git.postgresql.org/pg/commitdiff/b9a0724cf7a3ecf19aaaa098ffd26e52bb800c5c

Andrew Gierth pushed:

- Prefer timezone name "UTC" over alternative spellings. tzdb 2019a made "UCT" a
link to the "UTC" zone rather than a separate zone with its own abbreviation.
Unfortunately, our code for choosing a timezone in initdb has an arbitrary
preference for names earlier in the alphabet, and so it would choose the
spelling "UCT" over "UTC" when the system is running on a UTC zone. Commit
23bd3cec6 was backpatched in order to address this issue, but that code helps
only when /etc/localtime exists as a symlink, and does nothing to help on
systems where /etc/localtime is a copy of a zone file (as is the standard
setup on FreeBSD and probably some other platforms too) or when /etc/localtime
is simply absent (giving UTC as the default). Accordingly, add a preference
for the spelling "UTC", such that if multiple zone names have equally good
content matches, we prefer that name before applying the existing arbitrary
rules. Also add a slightly lower preference for "Etc/UTC"; lower because that
preserves the previous behaviour of choosing the shorter name, but letting us
still choose "Etc/UTC" over "Etc/UCT" when both exist but "UTC" does not (not
common, but I've seen it happen). Backpatch all the way, because the tzdb
change that sparked this issue is in those branches too.
https://git.postgresql.org/pg/commitdiff/e3846a00c2f87402dcedf7f07950ab2d89cf5827

Tomáš Vondra pushed:

- Rework the pg_statistic_ext catalog. Since extended statistic got introduced
in PostgreSQL 10, there was a single catalog pg_statistic_ext storing both the
definitions and built statistic. That's however problematic when a user is
supposed to have access only to the definitions, but not to user data.
Consider for example pg_dump on a database with RLS enabled - if the
pg_statistic_ext catalog respects RLS (which it should, if it contains user
data), pg_dump would not see any records and the result would not define any
extended statistics. That would be a surprising behavior. Until now this was
not a pressing issue, because the existing types of extended statistic
(functional dependencies and ndistinct coefficients) do not include any user
data directly. This changed with introduction of MCV lists, which do include
most common combinations of values. The easiest way to fix this is to split
the pg_statistic_ext catalog into two - one for definitions, one for the built
statistic values. The new catalog is called pg_statistic_ext_data, and we're
maintaining a 1:1 relationship with the old catalog - either there are
matching records in both catalogs, or neither of them. Bumped CATVERSION due
to changing system catalog definitions. Author: Dean Rasheed, with
improvements by me Reviewed-by: Dean Rasheed, John Naylor Discussion:
https://postgr.es/m/CAEZATCUhT9rt7Ui%3DVdx4N%3D%3DVV5XOK5dsXfnGgVOz_JhAicB%3DZA%40mail.gmail.com
https://git.postgresql.org/pg/commitdiff/6cbfb784c3c91146148a76d50cda6f69ae6a79fb

- Add pg_stats_ext view for extended statistics. Regular per-column statistics
are stored in pg_statistics catalog, which is however rather difficult to
read, so we also have pg_stats view with a human-reablable version of the
data. For extended statistic the catalog was fairly easy to read, so we did
not have such human-readable view so far. Commit 9b6babfa2d however did split
the catalog into two, which makes querying harder. Furthermore, we want to
show the multi-column MCV list in a way similar to per-column stats (and not
as a bytea value). This commit introduces pg_stats_ext view, joining the two
catalogs and massaging the data to produce human-readable output similar to
pg_stats. It also considers RLS and access privileges - the data is shown only
when the user has access to all columns the extended statistic is defined on.
Bumped CATVERSION due to adding new system view. Author: Dean Rasheed, with
improvements by me Reviewed-by: Dean Rasheed, John Naylor Discussion:
https://postgr.es/m/CAEZATCUhT9rt7Ui%3DVdx4N%3D%3DVV5XOK5dsXfnGgVOz_JhAicB%3DZA%40mail.gmail.com
https://git.postgresql.org/pg/commitdiff/aa087ec64f703a52f3c48c70117bb02e578f1802

- Fix incorrect CREATE STATISTICS example in docs. The example was incorrectly
using parantheses around the list of columns, so just drop them. Reported-By:
Robert Haas Discussion:
https://postgr.es/m/CA%2BTgmoZZEMAqWMAfvLHZnK57SoxOutgvE-ALO94WsRA7zZ7wyQ%40mail.gmail.com
https://git.postgresql.org/pg/commitdiff/7f44efa10b6ea3e5fd125e2800f357f38ab1068c

- Fix privileges on pg_statistic_ext.tableoid. The GRANT in system_views allowed
SELECT privileges on various columns in the pg_statistic_ext catalog, but
tableoid was not included in the list. That made pg_dump fail because it's
accessing this column when building the list of extended statistics to dump.
Discussion: https://postgr.es/m/8833.1560647898%40sss.pgh.pa.us
https://git.postgresql.org/pg/commitdiff/fc8cf3df478e054b892f6698b6d336e72f3a7328

== Pending Patches ==

Ian Barwick sent in a patch to clarify "pg_signal_backend" default role.

Jie Zhang sent in a patch to fix potential memoryleak in guc.c.

Noah Misch sent in a patch to fix testing on msys when builddir is under /c
mount point.

Jie Zhang sent in a patch to fix a memory leak in ecpglib.

Kyotaro HORIGUCHI sent in a patch to change the format of prep status in
pg_upgrade.

Robert Haas sent in another revision of a patch to tableam to provide helper
functions for relation sizing.

Konstantin Knizhnik sent in a patch to do adaptive query optimization.

Etsuro Fujita sent in another revision of a patch to fix
estimate_path_cost_size.

Andres Freund sent in a patch to reimplement background workers.

Peter Eisentraut sent in a patch to add a gen_random_uuid() function.

Robert Haas sent in another revision of a patch to split tuptoaster.c into three
separate files, create an API for inserting and deleting rows in TOAST tables,
and allow TOAST tables to be implemented using table AMs other than heap.

Tomáš Vondra sent in another revision of a patch to implemenent BRIN multi-range
indexes.

Tomáš Vondra sent in another revision of a patch to add opclass parameters.

Daniel Gustafsson sent in another revision of a patch to use heap_multi_insert()
for pg_attribute/depend insertions.

Peter Eisentraut and Sergei Kornilov traded patches to remove explicit error
handling for obsolete date/time values and avoid calling data type input
functions in GUC check hooks.

Kirk Jamison sent in a patch to speed up truncates of relation forks.

David Cramer sent in two revisions of a patch to add binary support for the
pgoutput plugin.

Kyotaro HORIGUCHI sent in a patch to refactor pg_upgrade_controldata errors.

Peter Eisentraut sent in a patch to simplify the catalog files.

Peter Eisentraut sent in a patch to remove support for non-ELF FreeBSD systems.

Etsuro Fujita sent in another revision of a patch to fix BEFORE UPDATE TRIGGER
behavior for the PostgreSQL foreign data wrapper.

Álvaro Herrera sent in a patch to remove some instances of "the the".

Richard Guo sent in two revisions of a patch to implement parallel grouping
sets.

Timur Birsh sent in two revisions of a patch to vacuumlo to print the number of
large objects to be removed.

Amit Khandekar sent in two more revisions of a patch to implement minimal
logical decoding on standbys.

Fabien COELHO sent in two more revisions of a patch to fix psql \conninfo &
\connect when using hostaddr.

Iwata Aya sent in another revision of a patch to implement a trace output for
libpq.

Michaël Paquier sent in another revision of a patch to stabilize the synchronous
replication tests.

Peter Eisentraut sent in another revision of a patch to pg_upgrade to improve
invalid option handling.

Jesper Pedersen sent in another revision of a patch to implement index skip
scan.

Thomas Munro sent in another revision of a patch to remove obsolete comments
about sempahores from proc.c.

Peter Eisentraut sent in another revision of a patch to update the list of
combining characters.

Thomas Munro sent in another revision of a patch to clean up orphaned files
using undo logs.

Zhang Wenjie sent in a patch to check the number of potential synchronous
standbys.

Heikki Linnakangas sent in a patch to to make it possible for table AMs to put
information in relcache.

Kyotaro HORIGUCHI and Alexander Korotkov traded patches to fix some SQL/JSON
path issues.

Zheng Li sent in another revision of a patch to change NOT IN to anti-JOIN where
possible.

Ian Barwick sent in a patch to prevent ALTER SYSTEM from making bad assumptions.

David Rowley sent in a patch to fix an issue where custom table AMs needed to
include heapam.h because of BulkInsertState.

David Rowley sent in another revision of a patch to shrink bloated
locallocktables.

David Rowley sent in another revision of a patch to fix a performance issue in
foreign-key-aware join estimation.