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

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

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/

== PostgreSQL Product News ==

pgAdmin4 4.8, a web- and native GUI control center for PostgreSQL, released.
https://www.pgadmin.org/docs/pgadmin4/dev/release_notes_4_8.html

pgFormatter 4.0, a formatter/beautifier for SQL code, released.
http://sqlformat.darold.net/

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

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:

- Make cpluspluscheck more portable. Teach it to scrape -I and -D switches from
CPPFLAGS in Makefile.global. This is useful for testing on, eg, FreeBSD, where
you won't get far without "-I/usr/local/include". Also, expand the set of
blacklisted-for-unportability atomics headers, based on noting that arch-x86.h
fails to compile on an ARM box. The other ones I'd omitted seem to compile
all right on architectures they don't belong to, but that's surely too shaky
to rely on. Let's do like we did for the src/include/port/ headers, and
ignore all except the variant that's pulled in by the arch-independent header.
https://git.postgresql.org/pg/commitdiff/f4755a2c01486519cfce5b21ab04529ad26f5ed1

- Fix unsafe memory management in CloneRowTriggersToPartition(). It's not really
supported to call systable_getnext() in a different memory context than
systable_beginscan() was called in, and it's *definitely* not safe to do so
and then reset that context between calls. I'm not very clear on how this
code survived CLOBBER_CACHE_ALWAYS testing ... but Alexander Lakhin found a
case that would crash it pretty reliably. Per bug #15828. Fix, and backpatch
to v11 where this code came in. Discussion:
https://postgr.es/m/15828-f6ddd7df4852f473@postgresql.org
https://git.postgresql.org/pg/commitdiff/eaf0292c3ba7acac2735f99a8988bd49a26112f5

- Fix contrib/auto_explain to not cause problems in parallel workers. A parallel
worker process should not be making any decisions of its own about whether to
auto-explain. If the parent session process passed down flags asking for
instrumentation data, do that, otherwise not. Trying to enable
instrumentation anyway leads to bugs like the "could not find key N in shm
TOC" failure reported in bug #15821 from Christian Hofstaedtler. We can
implement this cheaply by piggybacking on the existing logic for not doing
anything when we've chosen not to sample a statement. While at it, clean up
some tin-eared coding related to the sampling feature, including an off-by-one
error that meant that asking for 1.0 sampling rate didn't actually result in
sampling every statement. Although the specific case reported here only
manifested in >= v11, I believe that related misbehaviors can be demonstrated
in any version that has parallel query; and the off-by-one error is certainly
there back to 9.6 where that feature was added. So back-patch to 9.6.
Discussion: https://postgr.es/m/15821-5eb422e980594075@postgresql.org
https://git.postgresql.org/pg/commitdiff/2cd4e835701e15f5e32dd8bde488f8669ddf7ae8

Michaël Paquier pushed:

- Fix some issues and improve psql completion for access methods. The following
issues have been spotted: - CREATE INDEX .. USING suggests both index and
table AMs, but it should consider only index AMs. - CREATE TABLE .. USING has
no completion support. USING was not being included in the completion list
where it should, and follow-up suggestions for table AMs have been missing as
well. - CREATE ACCESS METHOD .. TYPE suggests only INDEX, with TABLE missing.
Author: Michael Paquier Discussion:
https://postgr.es/m/20190601191007.GC1905@paquier.xyz
https://git.postgresql.org/pg/commitdiff/0240a00fbd4fd14f577edf8d36a032237fd0b9cb

- Fix typos in various places. Author: Andrea Gelmini Reviewed-by: Michael
Paquier, Justin Pryzby Discussion:
https://postgr.es/m/20190528181718.GA39034@glet
https://git.postgresql.org/pg/commitdiff/1fb6f62a84041b668fd9e85a2f33bb3a0fd28fdc

- Fix some typos and inconsistencies in tableam.h. The defined callback
definitions have been using references to heap for a couple of variables and
comments. This makes the whole interface more consistent by using "table"
which is more generic. A variable storing index information was misspelled as
well. Author: Michael Paquier Discussion:
https://postgr.es/m/20190601190946.GB1905@paquier.xyz
https://git.postgresql.org/pg/commitdiff/041a2642e51eb008351435709dcb054760a43bb9

- Rework code using list_delete_cell() in MergeAttributes. When merging two
attributes, we are sure that at least one remains. However, when deleting one
element in the attribute list we may finish with an empty list returned as NIL
by list_delete_cell(), but the code failed to track that, which is not
project-like. Adjust the call so as we check for an empty list, and make use
of it in an assertion. This has been introduced by e7b3349, when adding
support for CREATE TABLE OF. Author: Mark Dilger Reviewed-by: Álvaro Herrera,
Michael Paquier Discussion:
https://postgr.es/m/CAE-h2TpPDqSWgOvfvSziOaMngMPwW+QZcmPpY8hQ_KOJ2+3hXQ@mail.gmail.com
https://git.postgresql.org/pg/commitdiff/f7e954ad1cf99a65b1785d999058898a6d56e014

- Add forgotten translatable string in pg_checksums.c. Oversight in commit
280e5f1. Author: Kyotaro Horiguchi Discussion:
https://postgr.es/m/20190606.200612.110313249.horikyota.ntt@gmail.com
https://git.postgresql.org/pg/commitdiff/438e51987dcc21ca7ce28c6d1a4e58b0249eb690

- Improve some comments in pg_checksums about the needed clean shutdown. It was
not clear from the code why it is necessary. And we need a clean shutdown to
avoid random checksum failures caused by torn pages. Author: Masahiko Sawada
Discussion:
https://postgr.es/m/CAD21AoDum5MbAb7F=pa9dOD1W2tukuDMPzWT7NjZceNoWB_6Qw@mail.gmail.com
https://git.postgresql.org/pg/commitdiff/84d4de97e8d14469974eb488730a13f942aa787a

- Move be-gssapi-common.h into src/include/libpq/. The file has been introduced
in src/backend/libpq/ as of b0b39f72, but all backend-side headers of libpq
are located in src/include/libpq/. Note that the identification path on top of
the file referred to src/include/libpq/ from the start. Author: Michael
Paquier Reviewed-by: Stephen Frost Discussion:
https://postgr.es/m/20190607043415.GE1736@paquier.xyz
https://git.postgresql.org/pg/commitdiff/35b2d4bc0eb5d61a2a294ccb6b2e4abdad307604

- Fix some typos. Author: Guillaume Lelarge Discussion:
https://postgr.es/m/CAECtzeWPz4JikzUqZdMjqPTe8dAP3nZxPD-58Y-Hhvirg0fF+A@mail.gmail.com
https://git.postgresql.org/pg/commitdiff/b880e22de4b2543f1c1db758bca4b2135525e87a

- Switch position of some declarations in libpq.h. This makes the header more
consistent with the surroundings, with declarations associated to a given file
grouped together. Author: Daniel Gustafsson Discussion:
https://postgr.es/m/20190608012439.GB7228@paquier.xyz
https://git.postgresql.org/pg/commitdiff/cf4263cc6c3a2310de4f1540745990e62fa2312c

Peter Eisentraut pushed:

- Update SQL conformance information about JSON path. Reviewed-by: Oleg Bartunov
<obartunov(at)postgrespro(dot)ru>
https://git.postgresql.org/pg/commitdiff/05d36b68ed011d112ee287b1fedd981c73a48f85

- Add command column to pg_stat_progress_create_index. This allows determining
which command is running, similar to pg_stat_progress_cluster. Discussion:
https://www.postgresql.org/message-id/flat/f0e56b3b-74b7-6cbc-e207-a5ed6bee18dc%402ndquadrant.com
https://git.postgresql.org/pg/commitdiff/c880096dc1e14b62610aa34bc98db226fa134260

- doc: Fix whitespace. Verbatim environment tags should not be indented.
https://git.postgresql.org/pg/commitdiff/ece9dc40d6d77a8607e72931510e30cfdfb78fd4

- doc: Add links to tables. Formal tables should generally have an xref in the
text that points to them. Add them here.
https://git.postgresql.org/pg/commitdiff/b250898c76f3a04421656f1bd8c5c520de02046d

- pg_waldump: Fix invalid option handling. Previously, running pg_waldump with
an invalid option (pg_waldump --foo) would print the help output and exit
successfully. This was because it tried to process the option letter '?' as a
normal option, but that letter is used by getopt() to report an invalid
option. To fix, process help and version options separately, like we do
everywhere else. Also add a basic test suite for pg_waldump and run the basic
option handling tests, which would have caught this.
https://git.postgresql.org/pg/commitdiff/f65eced25118aab29804debc4e0b9a67f4d043ad

Álvaro Herrera pushed:

- Document piecemeal construction of partitioned indexes. Continuous operation
cannot be achieved without applying this technique, so it needs to be properly
described. Author: Álvaro Herrera Reported-by: Tom Lane Discussion:
https://postgr.es/m/8756.1556302759@sss.pgh.pa.us
https://git.postgresql.org/pg/commitdiff/5efd604ec0a3bdde98fe19d8cada69ab4ef80db3

- Fix message style. Mark one message not for translation, and prefer "cannot"
over "may not", per commentary from Robert Haas. Discussion:
https://postgr.es/m/20190430145813.GA29872@alvherre.pgsql
https://git.postgresql.org/pg/commitdiff/e8bdea58f960eac669ebac01dc3865606dd7b453

- Fix default_tablespace usage for partitioned tables. In commit 87259588d0ab I
(Álvaro) tried to rationalize the determination of tablespace to use for
partitioned tables, but failed to handle the default_tablespace case. Repair
and add proper tests. Author: Amit Langote, Rushabh Lathia Reported-by:
Rushabh Lathia Reviewed-by: Amit Langote, Álvaro Herrera Discussion:
https://postgr.es/m/CAGPqQf0cYjm1=rjxk_6gU0SjUS70=yFUAdCJLwWzh9bhNJnyVg@mail.gmail.com
https://git.postgresql.org/pg/commitdiff/a36c84c3e4a9bee6baa740848f67a5db3fa279b0

David Rowley pushed:

- Fix incorrect index behavior in COPY FROM with partitioned tables. 86b85044e
rewrote how COPY FROM works to allow multiple tuple buffers to exist to once
thus allowing multi-inserts to be used in more cases with partitioned tables.
That commit neglected to update the estate's es_result_relation_info when
flushing the insert buffer to the partition making it possible for the index
tuples to be added into an index on the wrong partition. Fix this and also
add an Assert in ExecInsertIndexTuples to help ensure that we never make this
mistake again. Reported-by: Haruka Takatsuka Author: Ashutosh Sharma
Discussion: https://postgr.es/m/15832-b1bf336a4ee246b5@postgresql.org
https://git.postgresql.org/pg/commitdiff/56b3b3838284f53c83556592e60688522155f57f

- Fix confusing NOTICE text in REINDEX CONCURRENTLY. When performing REINDEX
TABLE CONCURRENTLY, if all of the table's indexes could not be reindexed, a
NOTICE message claimed that the table had no indexes. This was confusing, so
let's change the NOTICE text to something less confusing. In passing, also
mention in the comment before ReindexRelationConcurrently that materialized
views are supported too and also explain what the return value of the function
means. Author: Ashwin Agrawal Reviewed-by: Michael Paquier Discussion:
https://postgr.es/m/CALfoeithHvi13p_VyR8kt9o6Pa7Z=Smi6Nfc2anHnQx5Lj8bTQ@mail.gmail.com
https://git.postgresql.org/pg/commitdiff/e24a815c1c8550fcba5cc5aeb0d130db46570872

- Docs: concurrent builds of partitioned indexes are not supported. Document
that CREATE INDEX CONCURRENTLY is not currently supported for indexes on
partitioned tables. Discussion:
https://postgr.es/m/CAKJS1f_CErd2z9L21Q8OGLD4TgH7yw1z9MAtHTSO13sXVG-yow@mail.gmail.com
Backpatch-through: 11
https://git.postgresql.org/pg/commitdiff/6c0c28383749c0ade89ac70ba6dbce9af140c0a9

Heikki Linnakangas pushed:

- Fix confusion on different kinds of slots in IndexOnlyScans. We used the same
slot to store a tuple from the index, and to store a tuple from the table.
That's not OK. It worked with the heap, because heapam_getnextslot() stores a
HeapTuple to the slot, and doesn't care how large the tts_values/nulls arrays
are. But when I played with a toy table AM implementation that used a virtual
tuple, it caused memory overruns. In the passing, tidy up comments on the
ioss_PscanLen fields.
https://git.postgresql.org/pg/commitdiff/cd96389d713787b025c84869ba417c6f2f008a86

- Fix copy-pasto in freeing memory on error in vacuumlo. It's harmless to call
PQfreemem() with a NULL argument, so the only consequence was that if
allocating 'schema' failed, but allocating 'table' or 'field' succeeded, we
would leak a bit of memory. That's highly unlikely to happen, so this is just
academical, but let's get it right. Per bug #15838 from Timur Birsh.
Backpatch back to 9.5, where the PQfreemem() calls were introduced.
Discussion:
https://www.postgresql.org/message-id/15838-3221652c72c5e69d@postgresql.org
https://git.postgresql.org/pg/commitdiff/77b68e3a4f854d95350a3366ecfc8b3b506ebaa7

Amit Kapila pushed:

- Fix inconsistency in comments atop ExecParallelEstimate. When this code was
initially introduced in commit d1b7c1ff, the structure used was
SharedPlanStateInstrumentation, but later when it got changed to
Instrumentation structure in commit b287df70, we forgot to update the comment.
Reported-by: Wu Fei Author: Wu Fei Reviewed-by: Amit Kapila Backpatch-through:
9.6 Discussion:
https://postgr.es/m/52E6E0843B9D774C8C73D6CF64402F0562215EB2@G08CNEXMBPEKD02.g08.fujitsu.local
https://git.postgresql.org/pg/commitdiff/d8261595bc6f5189896cdce6861915ac23830e7c

- Fix assorted inconsistencies. There were a number of issues in the recent
commits which include typos, code and comments mismatch, leftover function
declarations. Fix them. Reported-by: Alexander Lakhin Author: Alexander
Lakhin, Amit Kapila and Amit Langote Reviewed-by: Amit Kapila Discussion:
https://postgr.es/m/ef0c0232-0c1d-3a35-63d4-0ebd06e31387@gmail.com
https://git.postgresql.org/pg/commitdiff/92c4abc73693690ce9ff13ede160f0dcb0a2de7b

Noah Misch pushed:

- Stop using spelling "nonexistant". The documentation used "nonexistent"
exclusively, and the source tree used it three times as often as
"nonexistant".
https://git.postgresql.org/pg/commitdiff/f2c71cb71fe186dcf8ebec967f98d1c7c4777943

- Update stale comments, and fix comment typos.
https://git.postgresql.org/pg/commitdiff/31d250e049b4e3d51a635edaa2221c11815e40e7

- Update documentation about SIGKILL of postmaster. Removing shared memory and
semaphores in response to server start failure often masks the real problem, a
live process associated with the data directory; see commit
5a907404b52753c4d6c6a7c21765aeaa42fd6a3b. Since 9.6, it's rarely necessary to
kill subprocesses manually. (When it is necessary, that commit's HINT will
say as much, in all supported versions.)
https://git.postgresql.org/pg/commitdiff/a806b14a1139d3443a7c2c263abb9ff8e4a65694

== Pending Patches ==

Haribabu Kommi sent in another revision of a patch to add support to libpq to
connect to a standby server as priority.

Philip Dubé sent in a patch to ruleutils to fix subqueries with shadowed
aliases.

Dmitry Dolgov sent in another revision of a patch to implement index skip scan.

Peter Eisentraut sent in a patch to fix runtime errors from -fsanitize-undefined.

Melanie Plageman sent in two revisions of a patch to add sort support for
macaddr8.

didier sent in a patch to fix a JIT deform failure.

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

Ashwin Agrawal sent in two more revisions of a patch to fix a confusing error
message for REINDEX TABLE CONCURRENTLY.

Martijn van Oosterhout sent in two revisions of a patch to improve the
performance of NOTIFY over many databases.

Robert Haas and Amit Langote traded patches to fix a memory leak in DDL.

Peter Eisentraut sent in a patch to add a LOCALE option to CREATE DATABASE.

Vignesh C sent in a patch to fix a pg_basebackup failure after setting
default_table_access_method option.

Melanie Plageman sent in another revision of a patch to add a test for
the abort case of speculative insert.

Etsuro Fujita sent in another revision of a patch to postgres_fdw: Fix costing
of pre-sorted foreign paths with local stats.

Peter Eisentraut sent in a patch to unwind some workarounds for lack of portable
int64 format specifier and remove unnecessary casts from size_t to int.

Dmitry Dolgov sent in another revision of a patch to implement generic type
subscripting and use same in arrays and JSONB.

Tomáš Vondra sent in another revision of a patch to split pg_statistic_ext into
definition + data, update the docs to reflect those changes, and define a
pg_stats_ext view similar to pg_stats.

Robert Haas sent in three revisions of a patch to tableam which abstract out the
relation sizing code.

Ian Barwick sent in two revisions of a patch to document
pg_trgm.strict_word_similarity_threshold.

David Rowley sent in five more revisions of a patch to warn about using too many
partitions.

Masahiko Sawada sent in another revision of a patch to implement block-level
parallel vacuum.

Kyotaro HORIGUCHI sent in a patch to fix some inconsistent wording in the
descriptions of GUCs.

Masahiko Sawada sent in a patch to document the fact that ALTER TABLE .. ADD
COLUMN ... can now take a GENERATED specification.

Adrien Nayrat sent in a patch to document the new snowball stemmer dictionaries.

Robert Haas sent in two revisions of a patch to tableam to fix some
inconsistencies in parameter names in index_build_range_scan,
table_scan_analyze_next_tuple, and index_build_range_scan.

Amit Kapila sent in another revision of a patch to fix assorted inconsistencies.

Kuntal Ghosh sent in a patch to make pg_index tuple updates transactional.