== PostgreSQL Weekly News - January 5, 2020 ==

== PostgreSQL Weekly News - January 5, 2020 ==

== PostgreSQL Product News ==

Crunchy PostgreSQL Operator 4.2.0, a system for deploying and managing of open
source PostgreSQL clusters on Kubernetes, released.
https://access.crunchydata.com/documentation/postgres-operator/latest/quickstart/

wal2json 2.0, a JSON output plugin for changeset extraction, released.
https://github.com/eulerto/wal2json/releases

== PostgreSQL Local ==

Postgres(at)CERN will be on January 17, 2020.
https://indico.cern.ch/e/PGCERN2020

PGDay SF will take place on January 21, 2020 at the Swedish American Hall in San
Francisco.
https://2020.pgdaysf.org/

pgDay Israel 2020 will take place on March 19, 2020 in Tel Aviv.
The CfP is open through January 15, 2020.
http://pgday.org.il/

pgDay Paris 2020 will be held in Paris, France on March 26, 2020 at Espace
Saint-Martin.
https://2020.pgday.paris/

Nordic PGDay 2020 will be held in Helsinki, Finland at the Hilton Helsinki
Strand Hotel on March 24, 2020.

PGConf India 2020 will be on February 26-28, 2020 in Bengaluru, Karnataka.
http://pgconf.in/

PostgreSQL(at)SCaLE is a two day, two track event which takes place on
March 5-6, 2020, at Pasadena Convention Center, as part of SCaLE 18X.
https://www.socallinuxexpo.org/scale/18x/postgresscale

The German-speaking PostgreSQL Conference 2020 will take place on May 15, 2019
in Stuttgart.

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

Peter Eisentraut pushed:

- Modernize Python exception syntax in documentation. Change the exception
syntax used in the documentation to use the more current except Exception
as ex: rather than the old except Exception, ex: We keep the old syntax
in the test code since Python <2.6 is still supported there, but the
documentation might as well use the modern syntax.
https://git.postgresql.org/pg/commitdiff/b55413d77f96b9fa2dfae4ddec43412b90ebf588

- Fix comment in test. The comment was apparently copy-and-pasted and did not
reflect the actual test outcome.
https://git.postgresql.org/pg/commitdiff/198c7153dccb11950e3030dec564fdc6e59b4451

- pgbench: Improve test description. Author: Fabien COELHO <coelho(at)cri(dot)ensmp(dot)fr>
https://git.postgresql.org/pg/commitdiff/04334fde69132f335d9d70cfefe419bd1276b232

- Make better use of ParseState in ProcessUtility. Pass ParseState into the
functions called from standard_ProcessUtility() instead passing the query
string and query environment separately. No functionality change, but it
makes the notation consistent. We had already started moving things into that
direction piece by piece, and this completes it. Reviewed-by: Pavel Stehule
<pavel(dot)stehule(at)gmail(dot)com> Discussion:
https://www.postgresql.org/message-id/flat/6e7aa4a1-be6a-1a75-b1f9-83a678e5184a(at)2ndquadrant(dot)com
https://git.postgresql.org/pg/commitdiff/3fd40b628c7db4c4bcf03b548f9a55f85e327e25

Bruce Momjian pushed:

- Update copyrights for 2020. Backpatch-through: update all files in master,
backpatch legal files through 9.4
https://git.postgresql.org/pg/commitdiff/7559d8ebfa11d98728e816f6b655582ce41150f3

Peter Geoghegan pushed:

- Update btree_xlog_delete() comments. Commit fe97c61c updated LP_DEAD item
deletion comments, but missed a minor discrepancy on the REDO side. Fix it
now. In passing, don't talk about the btree_xlog_vacuum() behavior within
btree_xlog_delete(). The reliance on XLOG_HEAP2_CLEANUP_INFO records for
recovery conflicts is already discussed within btvacuumpage() and mentioned
again in passing above btree_xlog_vacuum(), which seems sufficient.
https://git.postgresql.org/pg/commitdiff/c5f3b53b0ef2e8ae78e7488148c12bfe5939ca17

- Revise BTP_HAS_GARBAGE nbtree VACUUM comments. _bt_delitems_vacuum() comments
claimed that it isn't worth another scan of the page to avoid falsely
unsetting the BTP_HAS_GARBAGE page flag hint (this happens to be the same
wording that was removed from _bt_delitems_delete() by my recent commit
fe97c61c). The comments made little sense, though. The issue can't have much
to do with performing a second scan of the target leaf page, since an LP_DEAD
test could easily be performed in the first scan of the page anyway (the scan
that takes place in btvacuumpage() caller). Revise the explanation. It makes
much more sense to frame this as an issue about recovery conflicts.
_bt_delitems_vacuum() cannot easily generate an XID cutoff in the same way
that _bt_delitems_delete() is designed to. Falsely unsetting the page flag is
not ideal, and is likely to happen more often than was supposed by the
original comments. Explain why it usually isn't a problem in practice. There
may be an argument for _bt_delitems_vacuum() not clearing the BTP_HAS_GARBAGE
bit, removing the question of it being falsely unset by VACUUM (there may even
be an argument for not using a page level hint at all). This can be revisited
later.
https://git.postgresql.org/pg/commitdiff/4b25f5d0ba0197af80e3af0de7441ca9c88c1e24

- Reorder two nbtree.h function prototypes. Make the function prototype order
consistent with the definition order in nbtpage.c.
https://git.postgresql.org/pg/commitdiff/a412f469880ede9f52336a7b383905129c2b03a0

- Correct _bt_delitems_vacuum() lock comments. The expectation within
_bt_delitems_vacuum() is that caller has a super-exclusive/cleanup buffer lock
(not just a pin and a write lock).
https://git.postgresql.org/pg/commitdiff/44e44bd258a71162444d41a1044c795f2c6dd3d1

- Clear up btree_xlog_split() alignment comment. Adjust a comment that describes
how alignment of the new left page high key works in btree_xlog_split(), the
nbtree page split REDO routine. The wording used before commit 2c03216d831 is
much clearer, so go back to that.
https://git.postgresql.org/pg/commitdiff/0c41c83d8ff44ed8f9753885e2c11b3277babcce

- Add xl_btree_delete optimization. Commit 558a9165e08 taught
_bt_delitems_delete() to produce its own XID horizon on the primary. Standbys
no longer needed to generate their own latestRemovedXid, since they could just
use the explicitly logged value from the primary instead. The deleted offset
numbers array from the xl_btree_delete WAL record was no longer used by the
REDO routine for anything other than deleting the items. This enables a minor
optimization: We now treat the array as buffer state, not generic WAL data,
following _bt_delitems_vacuum()'s example. This should be a minor win, since
it allows us to avoid including the deleted items array in cases where
XLogInsert() stores the whole buffer anyway. The primary goal here is to make
the code more maintainable, though. Removing inessential differences between
the two functions highlights the fundamental differences that remain. Also
change xl_btree_delete to use uint32 for the size of the array of item offsets
being deleted. This brings xl_btree_delete closer to xl_btree_vacuum.
Furthermore, it seems like a good idea to use an explicit-width integer type
(the field was previously an "int"). Bump XLOG_PAGE_MAGIC because
xl_btree_delete changed. Discussion:
https://postgr.es/m/CAH2-Wzkz4TjmezzfAbaV1zYrh=fr0bCpzuJTvBe5iUQ3aUPsCQ@mail.gmail.com
https://git.postgresql.org/pg/commitdiff/d2e5e20e57111cca9e14f6e5a99a186d4c66a5b7

Tom Lane pushed:

- Test GROUP BY matching of join columns that are type-coerced by USING. If we
have, say, an int column that is left-joined to a bigint column with USING,
the merged column is the int column promoted to bigint. GROUP BY's tests for
whether grouping on the merged column allows a reference to the underlying
column, or vice versa, should know about that relationship --- and they do.
But I nearly broke this case with an ill-advised optimization, so the lack of
any test coverage for it seems like a bad idea.
https://git.postgresql.org/pg/commitdiff/823e739d4a7257cf0ca58fc6eff3c4cec308fccf

- Make parser rely more heavily on the ParseNamespaceItem data structure. When I
added the ParseNamespaceItem data structure (in commit 5ebaaa494), it wasn't
very tightly integrated into the parser's APIs. In the wake of adding
p_rtindex to that struct (commit b541e9acc), there is a good reason to make
more use of it: by passing around ParseNamespaceItem pointers instead of bare
RTE pointers, we can get rid of various messy methods for passing back or
deducing the rangetable index of an RTE during parsing. Hence, refactor the
addRangeTableEntryXXX functions to build and return a ParseNamespaceItem
struct, not just the RTE proper; and replace addRTEtoQuery with
addNSItemToQuery, which is passed a ParseNamespaceItem rather than building
one internally. Also, add per-column data (a ParseNamespaceColumn array) to
each ParseNamespaceItem. These arrays are built during addRangeTableEntryXXX,
where we have column type data at hand so that it's nearly free to fill the
data structure. Later, when we need to build Vars referencing RTEs, we can
use the ParseNamespaceColumn info to avoid the rather expensive operations
done in get_rte_attribute_type() or expandRTE(). get_rte_attribute_type() is
indeed dead code now, so I've removed it. This makes for a useful improvement
in parse analysis speed, around 20% in one moderately-complex test query. The
ParseNamespaceColumn structs also include Var identity information
(varno/varattno). That info isn't actually being used in this patch, except
that p_varno == 0 is a handy test for a dropped column. A follow-on patch will
make more use of it. Discussion:
https://postgr.es/m/2461.1577764221@sss.pgh.pa.us
https://git.postgresql.org/pg/commitdiff/5815696bc66b3092f6361f53e0394909647042c8

- Fix collation exposed for scalar function in FROM. One code path in
addRangeTableEntryForFunction() neglected to assign a collation to the tupdesc
entry it constructs (which is a bit odd considering the other path did do so).
This didn't matter before commit 5815696bc, because nothing would look at the
type data in this tupdesc; but now it does. While at it, make sure we assign
the correct typmod as well. Most function expressions don't have a
determinate typmod, but some do. Per buildfarm, which showed failures in
non-C collations, a case I'd not thought to test for this patch :-(
https://git.postgresql.org/pg/commitdiff/4d02eb017e3c1268762fd1a10ec3c569515c047d

- Fix typmod exposed for scalar function in FROM, too. On further reflection
about commit 4d02eb017, it occurs to me that expandRTE() had better agree with
what addRangeTableEntryForFunction() is doing. So teach that about functions
possibly having typmods, too.
https://git.postgresql.org/pg/commitdiff/915c04f091f13dbbc0fde833e612dc90b70103ce

- Add basic TAP tests for psql's tab-completion logic. Up to now, psql's
tab-complete.c has had exactly no regression test coverage. This patch is an
experimental attempt to add some. This needs Perl's IO::Pty module, which
isn't installed everywhere, so the test script just skips all tests if that's
not present. There may be other portability gotchas too, so I await buildfarm
results with interest. So far this just covers a few very basic
keyword-completion and query-driven-completion scenarios, which should be
enough to let us get a feel for whether this is practical at all from a
portability standpoint. If it is, there's lots more that can be done.
Discussion: https://postgr.es/m/10967.1577562752@sss.pgh.pa.us
https://git.postgresql.org/pg/commitdiff/7c015045b9141cc30272930ea88cfa5df47240b7

- Minor portability fixes for new TAP script. Satisfy perlcritic, mostly. Per
buildfarm.
https://git.postgresql.org/pg/commitdiff/90d7f6604b6ed2dcedee2884c3b01541600515cb

- Add an ugly workaround for a bug in some recent libedit versions. Debian
unstable is shipping a broken version of libedit: it de-escapes words before
passing them to the application's tab completion function, preventing us from
recognizing backslash commands. Fortunately, we have enough information
available to dig the original text out of rl_line_buffer, so ignore the string
argument and do that. I view this as a temporary workaround to get the
affected buildfarm members back to green in the wake of 7c015045b. I hope we
can get rid of it once somebody fixes Debian's libedit; hence, no back-patch,
at least for now. Discussion:
https://postgr.es/m/20200103110128.GA28967@msg.df7cb.de
https://git.postgresql.org/pg/commitdiff/ddd87d564508bb1c80aac0a4439cfe74a3c203a9

- Further fixes for tab-completion TAP tests. Escape non-printable characters in
failure reports, by using Data::Dumper in Useqq mode. Also, bump
$Test::Builder::Level so the diagnostic references the calling line, and use
diag() instad of note(), so it shows even in non-verbose mode (per request
from Christoph Berg). Also, give up on trying to test for the specific way
that readline chooses to overwrite existing text in the \DRD -> \drds test.
There are too many variants, it seems, at least on the libedit side of things.
Dagfinn Ilmari Mannsåker and Tom Lane Discussion:
https://postgr.es/m/20200103110128.GA28967@msg.df7cb.de
https://git.postgresql.org/pg/commitdiff/56a3921a2f5102f804bd0ff741e144a0e6f1c0b6

- Make tab-completion tests more robust. Depending on
as-yet-incompletely-explained factors, readline/libedit might choose to emit
screen-control escape sequences as part of repainting the display. I'd tried
to make the test patterns avoid matching parts of the output that are likely
to contain such, but it seems that there's really no way around matching them
explicitly in some places, unless we want to just give up testing some
behaviors such as display of alternatives. Per report from Peter Geoghegan.
Discussion:
https://postgr.es/m/CAH2-WznPzfWHu8PQwv1Qjpf4wQVPaaWpoO5NunFz9zsYKB4uJA@mail.gmail.com
https://git.postgresql.org/pg/commitdiff/fac1c04feca6d01f2d324088c5899485f55b6217

- In tab-completion test, print out the value of TERM before changing it. I'm
curious to see what values are prevailing in the buildfarm. Discussion:
https://postgr.es/m/23181.1578167938@sss.pgh.pa.us
https://git.postgresql.org/pg/commitdiff/60ab7c80b4de2c3591e50dfb5ed0fd4002f0f2f2

- Don't try to force TERM to a fixed value in tab-completion test. Right at the
moment, this is making things worse not better in the buildfarm. I'm not
happy with anything about the current state, but let's at least try to have a
green buildfarm report while further investigation continues. Discussion:
https://postgr.es/m/23181.1578167938@sss.pgh.pa.us
https://git.postgresql.org/pg/commitdiff/7e42478186aa8b41a8706f3f53b44e25a427bde9

- Avoid reading ~/.inputrc in tab-completion test, and revert other changes. The
true explanation for Peter Geoghegan's trouble report turns out to be that he
has a ~/.inputrc that affects readline's behavior enough to break this test.
Prevent readline from reading that file. Also, the best way to prevent TERM
from affecting the results seems to be to unset it altogether, not to set it
to "xterm". The latter choice licenses readline to emit xterm escape
sequences, and there's a lot of variation in exactly what it will emit.
Revert changes that attempted to account exactly for xterm escape sequences.
We shouldn't need that with TERM unset, and it was not looking like a
maintainable solution anyway. Discussion:
https://postgr.es/m/23181.1578167938@sss.pgh.pa.us
https://git.postgresql.org/pg/commitdiff/48e03583cd373ce67827f4d8a99dcef8242364b0

- Minor style improvements for tab-completion test. Use qr// syntax for regex
values. Include the regex that failed to match in diagnostic reports. Dagfinn
Ilmari Mannsåker Discussion:
https://postgr.es/m/87k16610xk.fsf@wibble.ilmari.org
https://git.postgresql.org/pg/commitdiff/8c081a2f4e8eec11747e709c1ea4c3485ef129dd

Amit Kapila pushed:

- Fix running out of file descriptors for spill files. Currently while decoding
changes, if the number of changes exceeds a certain threshold, we spill those
to disk.  And this happens for each (sub)transaction.  Now, while reading all
these files, we don't close them until we read all the files.  While reading
these files, if the number of such files exceeds the maximum number of file
descriptors, the operation errors out. Use PathNameOpenFile interface to open
these files as that internally has the mechanism to release kernel FDs as
needed to get us under the max_safe_fds limit. Reported-by: Amit Khandekar
Author: Amit Khandekar Reviewed-by: Amit Kapila Backpatch-through: 9.4
Discussion:
https://postgr.es/m/CAJ3gD9c-sECEn79zXw4yBnBdOttacoE-6gAyP0oy60nfs_sabQ@mail.gmail.com
https://git.postgresql.org/pg/commitdiff/d207038053837ae9365df2776371632387f6f655

- Fix typos in parallel query docs. Reported-by: Jon Jensen Author: Jon Jensen
Reviewed-by: Amit Kapila and Robert Haas Backpatch-through: 10 Discussion:
https://postgr.es/m/nycvar.YSQ.7.76.1912301807510.9899@ybpnyubfg
https://git.postgresql.org/pg/commitdiff/d5b6b6515b35b11c82ce620fa84c989f246068aa

Álvaro Herrera pushed:

- Fix cloning of row triggers to sub-partitions. When row triggers exist in
partitioned partitions that are not either part of FKs or deferred unique
constraints, they are not correctly cloned to their partitions. That's
because they are marked "internal", and those are purposefully skipped when
doing the clone triggers dance. Fix by relaxing the condition on which
internal triggers are skipped. Amit Langote initially diagnosed the problem
and proposed a fix, but I used a different approach. Reported-by: Petr
Fedorov Discussion:
https://postgr.es/m/6b3f0646-ba8c-b3a9-c62d-1c6651a1920f@phystech.edu
https://git.postgresql.org/pg/commitdiff/1fa846f1c9afe6bb185d4bb60bed8102a8eacb8f

Noah Misch pushed:

- Skip memcpy(x, x) in qunique(). It has undefined behavior. Follow the
precedent of commit 9a9473f3cce1a21c25d6cc7569710e832d2b180b. No back-patch,
since the master branch alone has this function. Discussion:
https://postgr.es/m/20191229070221.GA13873@gust.leadboat.com
https://git.postgresql.org/pg/commitdiff/5b630501e9fa58c5069b36247d63fc460d912c7f

Tatsuo Ishii pushed:

- Docs: use more standard terminology "round-to-nearest-even" instead of
"round-to-even". Per suggestion from Tom Lane. Discussion:
https://postgr.es/m/flat/20191230.093451.1762483750956466101.t-ishii%40sraoss.co.jp
https://git.postgresql.org/pg/commitdiff/955f1213012361e32d88976c3ce03ae62153c355

== Pending Patches ==

Melanie Plageman sent in another revision of a patch to implement parallel
hashloop fallback.

Andrey Borodin sent in another revision of a patch to add sort support for point
gist_point_sortsupport, and implement GiST build using same.

Pavel Stěhule sent in another revision of a patch to implement schema variables.

Justin Pryzby sent in a patch to correctly handle infinite timestamps in
ineq_histogram_selectivity.

Vik Fearing sent in another revision of a patch to recognize superuser in
pg_hba.conf.

Peter Eisentraut sent in another revision of a patch to allow an alias to be
attached to a JOIN ... USING.

Dilip Kumar sent in another revision of a patch to fix an infelicity between
logical_work_mem and logical streaming of large in-progress transactions.

John Naylor sent in a patch to remove separate files for the initial contents of
pg_(sh)description.

Vigneshwaran C sent in a patch to fix a reorder buffer crash while aborting old
transactions.

Maxim Orlov sent in a patch to implement lazy relation delete.

Peter Eisentraut sent in a patch to turn on color by default.

Masahiko Sawada sent in another revision of a patch to implement a key
management service.

Vigneshwaran C sent in a patch to fix a pg_restore crash when there is a failure
before all child process is created.

KaiGai Kohei sent in another revision of a patch to implement TRUNCATE on
foreign tables.

Thomas Munro sent in a patch to implement WAL prefetch.

Fabien COELHO sent in two more revisions of a patch to make pgbench use the
standard logging capabilities.

Surafel Temesgen sent in two more revisions of a patch to implement
system-versioned temporal tables.

Noah Misch sent in a patch to fix the fact that mdclose() does not cope w/
FileClose() failure.

Justin Pryzby sent in another revision of a patch to rename buf to avoid
shadowing buf of another type, arrange dedup2: skip_blocks, fix typos in
comments for vacuumlazy, arrange vacuum errcontext to show the block being
processed, and add an add errcontext callback in lazy_vacuum_heap.

Justin Pryzby sent in a patch to avoid some calls to memset with array
initializer.

Tom Lane sent in a patch to redefine how parse generates varno and varattno.

Melanie Plageman sent in another revision of a patch to implement plan-time
extraction of scan cols.

Fabien COELHO sent in a patch to clarify the help output for pgbench.

Jehan-Guillaume de Rorthais sent in a patch to always expose available stats
from wal_receiver.

Justin Pryzby sent in a patch to explain HashAggregate to report bucket and
memory stats.

Suraj Kharage sent in another revision of a patch to implement backup manifests.

Michaël Paquier sent in another revision of a patch to remove support for
OpenSSL 0.9.8 and 1.0.0.

Vik Fearing sent in four more revisions of a patch to implement gcd() and lcm().

Peter Geoghegan sent in another revision of a patch to add deduplication to
nbtree.

Paul A Jungwirth sent in another revision of a patch to implement multirange
and friends.

Mahendra Singh sent in another revision of a patch to implement block-level
parallel vacuum.

Aleksey Kondratov sent in another revision of a patch to allow CLUSTER, VACUUM
FULL and REINDEX to change tablespace on the fly.

Justin Pryzby sent in a patch to allow disabling indexscans but not bitmap
scans.

Tom Lane, Peter Geoghegan, and Dagfinn Ilmari Mannsåker traded patches to add
basic TAP tests for psql's tab-completion logic.

Julien Rouhaud sent in a patch to add planning counters to pg_stat_statements.

Tom Lane sent in another revision of a patch to rethink opclass member checks
and dependency strength.