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

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

PostgreSQL security releases 11.4, 10.9, 9.6.14, 9.5.18, and 9.4.23 released.
Upgrade as soon as possible.
https://www.postgresql.org/about/news/1949/

== PostgreSQL Jobs for June ==

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

== PostgreSQL Local ==

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

Tom Lane pushed:

- Doc: remove description of commit 23bd3cec6 from v12 release notes. Now that
we've back-patched that, it shouldn't be mentioned in v12 anymore.
https://git.postgresql.org/pg/commitdiff/16c4e76f1b0a0145572551b7e919b967e406a0b1

- Stamp 12beta2.
https://git.postgresql.org/pg/commitdiff/0ab7110bcbcce5ff58afb32e7871c54e87502139

- Fix spinlock assembly code for MIPS so it works on MIPS r6. Original MIPS-I
processors didn't have the LL/SC instructions (nor any other userland
synchronization primitive). If the build toolchain targets that ISA variant
by default, as an astonishingly large fraction of MIPS platforms still do, the
assembler won't take LL/SC without coercion in the form of a ".set mips2"
instruction. But we issued that unconditionally, making it an ISA downgrade
for chips later than MIPS2. That breaks things for the latest MIPS r6 ISA,
which encodes these instructions differently. Adjust the code so we don't
change ISA level if it's >= 2. Note that this patch doesn't change what
happens on an actual MIPS-I processor: either the kernel will emulate these
instructions transparently, or you'll get a SIGILL failure. That tradeoff
seemed fine in 2002 when this code was added (cf 3cbe6b247), and it's even
more so today when MIPS-I is basically extinct. But let's add a comment about
that. YunQiang Su (with cosmetic adjustments by me). Back-patch to all
supported branches. Discussion:
https://postgr.es/m/15844-8f62fe7e163939b3@postgresql.org
https://git.postgresql.org/pg/commitdiff/1323bfce55109dd54ee164828aab7983d3020a25

Álvaro Herrera pushed:

- Revert "Avoid spurious deadlocks when upgrading a tuple lock". This reverts
commits 3da73d6839dc and de87a084c0a5. This code has some tricky corner cases
that I'm not sure are correct and not properly tested anyway, so I'm reverting
the whole thing for next week's releases (reintroducing the deadlock bug that
we set to fix). I'll try again afterwards. Discussion:
https://postgr.es/m/E1hbXKQ-0003g1-0C@gemulon.postgresql.org
https://git.postgresql.org/pg/commitdiff/9d20b0ec8f2af43041b1a65e5fcd91acc47e9ace

- Avoid spurious deadlocks when upgrading a tuple lock. This puts back reverted
commit de87a084c0a5, with some bug fixes. 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 T1 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 T1 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 T1 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 T1 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
Discussion: https://postgr.es/m/2815.1560521451@sss.pgh.pa.us
https://git.postgresql.org/pg/commitdiff/8b21b416ed621501db3be38817c298c57470524f

Michaël Paquier pushed:

- Fix more typos and inconsistencies in the tree. Author: Alexander Lakhin
Discussion: https://postgr.es/m/0a5419ea-1452-a4e6-72ff-545b1a5a8076@gmail.com
https://git.postgresql.org/pg/commitdiff/3412030205211079f9b0510e2244083e4ee7b15a

- Fix buffer overflow when parsing SCRAM verifiers in backend. Any authenticated
user can overflow a stack-based buffer by changing the user's own password to
a purpose-crafted value. This often suffices to execute arbitrary code as the
PostgreSQL operating system account. This fix is contributed by multiple
folks, based on an initial analysis from Tom Lane. This issue has been
introduced by 68e61ee, so it was possible to make use of it at authentication
time. It became more easily to trigger after ccae190 which has made the SCRAM
parsing more strict when changing a password, in the case where the client
passes down a verifier already hashed using SCRAM. Back-patch to v10 where
SCRAM has been introduced. Reported-by: Alexander Lakhin Author: Jonathan
Katz, Heikki Linnakangas, Michael Paquier Security: CVE-2019-10164
Backpatch-through: 10
https://git.postgresql.org/pg/commitdiff/09ec55b933091cb5b0af99978718cb3d289c71b6

- Fix buffer overflow when processing SCRAM final message in libpq. When a
client connects to a rogue server sending specifically-crafted messages, this
can suffice to execute arbitrary code as the operating system account used by
the client. While on it, fix one error handling when decoding an incorrect
salt included in the first message received from server. Author: Michael
Paquier Reviewed-by: Jonathan Katz, Heikki Linnakangas Security:
CVE-2019-10164 Backpatch-through: 10
https://git.postgresql.org/pg/commitdiff/b67421178880f9df337dc19b8601b54b99efbc78

- Fix description of WAL record XLOG_BTREE_META_CLEANUP. This record uses one
metadata buffer and registers some data associated to the buffer, but when
parsing the record for its description a direct access to the record data was
done, but there is none. This leads usually to an incorrect description, but
can also cause crashes like in pg_waldump. Instead, fix things so as the
parsing uses the data associated to the metadata block. This is an oversight
from 3d92796, so backpatch down to 11. Author: Michael Paquier Description:
https://postgr.es/m/20190617013059.GA3153@paquier.xyz Backpatch-through: 11
https://git.postgresql.org/pg/commitdiff/3c28fd2281223580b88473e5145b752cc7cbadf9

- Remove last references to WAL segment size in MSVC scripts. fc49e24 has
removed the last use of this compile-time variable as WAL segment size is
something that can now be set at initdb time, still this commit has forgotten
some references to it. Discussion:
https://postgr.es/m/20190617073228.GE18917@paquier.xyz
https://git.postgresql.org/pg/commitdiff/414cca40d506dd3f17b49ae3139853139192c2ba

- Fix description of pg_class.relam for table access methods. Author: Ian
Barwick Discussion:
https://postgr.es/m/087a6961-1aaf-e36c-b712-bd5a644da20a@2ndquadrant.com
https://git.postgresql.org/pg/commitdiff/252f9a2580b013fe45ba28bb6d7a4994abc93bb5

- Rework some error strings for REINDEX CONCURRENTLY with system catalogs. This
makes the whole user experience more consistent when bumping into failures,
and more in line with the rewording done via 508300e. Author: Michael Paquier
Reviewed-by: Álvaro Herrera Discussion:
https://postgr.es/m/20190514153252.GA22168@alvherre.pgsql
https://git.postgresql.org/pg/commitdiff/20e1cc898da6f82b5680973c107e8a96d46caf01

Peter Eisentraut pushed:

- Translation updates. Source-Git-URL:
https://git.postgresql.org/git/pgtranslation/messages.git Source-Git-Hash:
1a710c413ce4c4cd081843e563cde256bb95f490
https://git.postgresql.org/pg/commitdiff/91acff7a538e6e6a8175450a38c7fa1d9a290011

- Add XSL stylesheet to fix up SVG files. The SVG output produced by external
tools needs some postprocessing. This is implemented by this new XSL
stylesheet. Issues are: - SVG produced by Ditaa does not add a viewBox
attribute to the svg element, needed to make the image scalable. - SVG
produced by Graphviz uses a stroke="transparent" attribute, which is not
valid SVG. It appears to mostly work, but FOP complains. Other tweaks can
be added over time. This reverts 7dc78d8ef3e62f7e06d7767c63dcede048377b9a and
29046c44f36099f4c979b1491fcf27db2f9184f9, which applied these fixes manually.
https://git.postgresql.org/pg/commitdiff/ac5bb8f275dd9b2746fe6d433f1c08c1c3bd3d61

- Update list of combining characters. The list of combining characters to
ignore for calculating the display width of a string (used for example by
psql) was wildly outdated and incorrect. Discussion:
https://www.postgresql.org/message-id/flat/bbb19114-af1e-513b-08a9-61272794bd5c%402ndquadrant.com
https://git.postgresql.org/pg/commitdiff/d8594d123c155aeecd47fc2450f62f5100b2fbf0

- pg_upgrade: Improve invalid option handling. Currently, calling pg_upgrade
with an invalid command-line option aborts pg_upgrade but leaves a
pg_upgrade_internal.log file lying around. Reorder things a bit so that that
file is not created until all the options have been parsed. Discussion:
https://www.postgresql.org/message-id/24c8bd05-aed1-6301-919d-8acbabdb8c24@2ndquadrant.com
https://git.postgresql.org/pg/commitdiff/aba78ab4a9509e7ed92acefd707a99425568f49f

- pg_upgrade: Improve error messages. Make wording more accurate and add
strerror() information. Discussion:
https://www.postgresql.org/message-id/24c8bd05-aed1-6301-919d-8acbabdb8c24@2ndquadrant.com
https://git.postgresql.org/pg/commitdiff/cd917ffb9a43e9e4699c2a89d495ee02a8701034

Thomas Munro pushed:

- Prevent Parallel Hash Join for JOIN_UNIQUE_INNER. WHERE EXISTS (...) queries
cannot be executed by Parallel Hash Join with jointype JOIN_UNIQUE_INNER,
because there is no way to make a partial plan totally unique. The
consequence of allowing such plans was duplicate results from some EXISTS
queries. Back-patch to 11. Bug #15857. Author: Thomas Munro Reviewed-by:
Tom Lane Reported-by: Vladimir Kriukov Discussion:
https://postgr.es/m/15857-d1ba2a64bce0795e%40postgresql.org
https://git.postgresql.org/pg/commitdiff/aca127c105aae551620d607e88d76930e6b9a2cf

- Remove obsolete comments about sempahores from proc.c. Commit 6753333f
switched from a semaphore-based wait to a latch-based wait for
ProcSleep()/ProcWakeup(), but left behind some stray references to semaphores.
Back-patch to 9.5. Reviewed-by: Daniel Gustafsson, Michael Paquier
Discussion:
https://postgr.es/m/CA+hUKGLs5H6zhmgTijZ1OaJvC1sG0=AFXc1aHuce32tKiQrdEA@mail.gmail.com
https://git.postgresql.org/pg/commitdiff/25b93a2967a4ebfb90168ca087026cc697fc4b77

- Remove unnecessary comment. Author: Vik Fearing Discussion:
https://postgr.es/m/150d3e9f-c7ec-3fb3-4fdb-def47c4144af%402ndquadrant.com
https://git.postgresql.org/pg/commitdiff/89ff7c08eee355195eba6f544d28584e61200665

Andres Freund pushed:

- Fix memory corruption/crash in ANALYZE. This fixes an embarrassing oversight I
(Andres) made in 737a292b, namely missing two place where liverows/deadrows
were used when converting those variables to pointers, leading to incrementing
the pointer, rather than the value. It's not that actually that easy to
trigger a crash: One needs tuples deleted by the current transaction, followed
by a tuple deleted in another session, all in one page. Which is presumably
why this hasn't been noticed before. Reported-By: Steve Singer Author: Steve
Singer Discussion:
https://postgr.es/m/c7988239-d42c-ddc4-41db-171b23b35e4f@ssinger.info
https://git.postgresql.org/pg/commitdiff/23224563d97913aa824d04c498d59ad4d85fda38

Magnus Hagander pushed:

- Replace an occurrence of slave with standby. Commit
a1ef920e27ba6ab3602aaf6d6751d8628fac1af8 replaced the use of slave with
standby, but overlooked this comment. Author: Daniel Gustafsson
https://git.postgresql.org/pg/commitdiff/992fe54e77f66ed67b6480fcf7dd208b2cd28aac

- Fix typo. Author: Daniel Gustafsson
https://git.postgresql.org/pg/commitdiff/66013fe73095f851c151a17ccc7553abe6246891

Alexander Korotkov pushed:

- Support 'q' flag in jsonpath 'like_regex' predicate. SQL/JSON standard defines
that jsonpath 'like_regex' predicate should support the same set of flags as
XQuery/XPath. It appears that implementation of 'q' flag was missed. This
commit fixes that. Discussion:
https://postgr.es/m/CAPpHfdtyfPsxLYiTjp5Ov8T5xGsB5t3CwE5%2B3PS%3DLLwA%2BxTJog%40mail.gmail.com
Author: Nikita Glukhov, Alexander Korotkov
https://git.postgresql.org/pg/commitdiff/261a5c1928566f938cbda0f62dc16f6a2d1e871d

- Improve documentation for jsonpath like_regex predicate. Reference posix regex
documentation section and list supported flags. Discussion:
https://postgr.es/m/CAKPRHz%2BxOuQSSvkuB1mCQjedd%2BB2B1Vnkrq0E-pLmoXyTO%2Bz9Q%40mail.gmail.com
Author: Kyotaro Horiguchi, Alexander Korotkov
https://git.postgresql.org/pg/commitdiff/1ff8dc9f19ddf00e3ad69d7141f78341769cd8a3

- Fix description for $varname jsonpath variable. The description is ended part
way and PASSING clause is not implemented yet. But the variables might be
passed as parameters to several jsonpath functions. So, complete the
description based on the current implementation, leaving description of
PASSING clause in TODO. Discussion:
https://postgr.es/m/CAKPRHz%2BxOuQSSvkuB1mCQjedd%2BB2B1Vnkrq0E-pLmoXyTO%2Bz9Q%40mail.gmail.com
Author: Kyotaro Horiguchi, Alexander Korotkov
https://git.postgresql.org/pg/commitdiff/dfd79e2d0e8f399785ca2f7c659b45fd620cba17

Noah Misch pushed:

- Consolidate methods for translating a Perl path to a Windows path. This fixes
some TAP suites when using msys Perl and a builddir located in an msys mount
point other than "/". For example, builddir=/c/pg exhibited the problem,
since /c/pg falls in mount point "/c". Back-patch to 9.6, where tests first
started to perform such translations. In back branches, offer both new and
old APIs. Reviewed by Andrew Dunstan. Discussion:
https://postgr.es/m/20190610045838.GA238501@rfd.leadboat.com
https://git.postgresql.org/pg/commitdiff/660a2b19038b2f6b9f6bcb2c3297a47d5e3557a8

== Pending Patches ==

Paul A Jungwirth sent in another revision of a patch to implement range_agg.

Pavel Stěhule sent in another revision of a patch to implement anycompatible and
friends.

Paul A Jungwirth sent in another revision of a patch to implement temporal
primary and foreign keys.

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

Kyotaro HORIGUCHI sent in a patch to fix error message for timeline history
mismatch.

David Cramer sent in another revision of a patch to document the new binary
option for CREATE SUBSCRIPTION and the new binary column of pg_subscription.

Peter Eisentraut sent in a patch to allow an alias to be attached directly to a
JOIN ... USING.

Richard Guo sent in a patch to fix up grouping sets reorder.

Paul Guo sent in two more revisions of a patch to use heap batch insert for CTAS
and MatView.

Michaël Paquier sent in a patch to fix an inconsistency between nbtdesc.c and
nbtpage.c in XLOG_BTREE_META_CLEANUP.

Pavel Stěhule and Adrien Nayrat traded patches to add a lower limit for sampling
when log_statement_sample_rate is set.

Michaël Paquier sent in a patch to fix some race conditions with TAP test for
syncrep.

Alexander Korotkov and Liudmila Mantrova traded patches to support a 'q' flag in
the jsonpath 'like_regex' predicate, improve the documentation for same, fix
the description for the $varname jsonpath variable, and clarify the jsonpath
docs.

Andres Freund sent in a WIP patch to fix hash key computations in hash joins.

Zheng Li sent in another revision of a patch to transform NOT IN to ANTI JOIN in
cases where this is possible.

Peter Eisentraut sent in a patch to initdb to change the authentication defaults.

Peter Eisentraut sent in a patch to use better error messages for short
reads/writes in SLRU and use consistent style for checking return from system
calls.

Daniel Gustafsson sent in a patch to the EDH SKIP primes.

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

Melanie Plageman sent in another revision of a patch to add a hashloop fallback
which "chunks" the inner file into arbitrary partitions of work_mem size, and
implements outer tuple batch rewinding per chunk of inner batch.

Paul Guo sent in another revision of a patch to skip copydir() if either src
directory or dst directory is missing due to re-redoing create database but the
tablespace is dropped.

Michaël Paquier sent in a patch to ensure that all reloptions are initialized
when loaded.

Binguo Bao sent in a patch to provide the ability to de-TOAST a fully TOAST'd
and compressed field using an iterator and then update the appropriate parts of
the code to use the iterator where possible instead of de-TOAST'ing and
de-compressing the entire value.

Dagfinn Ilmari Mannsåker sent in a patch to remove one last occurrence of
"replication slave" in comments.

Peter Eisentraut sent in a patch to update src/include/common/unicode_norm_table.h
to the latest Unicode tables.

Thomas Munro sent in a patch to tweak the DSM and DSA memory limits.

Dmitry Dolgov, Jesper Pedersen, and Peter Geoghegan traded patches to implement
index skip scans.

Peter Eisentraut sent in a patch to implement unlogged sequences.

John Naylor sent in a patch to remove scanner fail rules.

RekGRpth sent in a patch to disconnect from SPI manager on error.

Yugo Nagata sent in a patch to implement incremental view maintenance.

Amit Khandekar sent in another revision of a patch to implement minimal logical
decoding on standbys.

Michaël Paquier sent in another revision of a patch to add index_cleanup to
VACUUM.

Tomáš Vondra sent in a patch to fix an O(N^2) coding when building multi-column
MCV lists.

Peter Eisentraut sent in a patch to use explicit_bzero.

Álvaro Herrera sent in a patch to add a progress report for ANALYZE.

Peter Eisentraut sent in a patch to change allow_system_table_mods to SUSET, add
tests for allow_system_table_mods, and disable some dead code.

Tomáš Vondra sent in a patch to fix an MCV performance issue, and pick MCV items
by relative error.

Michaël Paquier sent in a patch to base64 encoding and decoding into a safer
interface.

Justin Pryzby sent in another revision of a patch to print the table associated
with given TOAST table, make \d pg_toast.foo show its indices, and show the
children of partitioned indices.

Peter Eisentraut sent in a patch to remove explicit error handling for obsolete
date/time values and not call data type input functions in GUC check hooks.