== PostgreSQL Weekly News - December 30, 2018 ==

== PostgreSQL Weekly News - December 30, 2018 ==

== PostgreSQL Product News ==

pgBadger v10.2, a PostgreSQL log analyzer and graph tool written in
Perl, released.
https://github.com/dalibo/pgbadger/releases/

== PostgreSQL Jobs for December ==

http://archives.postgresql.org/pgsql-jobs/2018-12/

== PostgreSQL Local ==

FOSDEM PGDay 2019, a one day conference held before the main FOSDEM event will
be held in Brussels, Belgium, on Feb 1st, 2019.
https://2019.fosdempgday.org/

Prague PostgreSQL Developer Day 2019 (P2D2 2019) is a two-day
conference that will be held on February 13-14, 2019 in Prague, Czech Republic.
The CfP is open until January 4, 2018 at https://p2d2.cz/callforpapers
http://www.p2d2.cz/

PGConf India 2019 will be on February 13-15, 2019 in Bengaluru, Karnataka.
http://pgconf.in/

pgDay Paris 2019 will be held in Paris, France on March 12, 2019
at 199bis rue Saint-Martin.
http://2019.pgday.paris/

PGConf APAC 2019 will be held in Singapore March 19-21, 2019.
http://2019.pgconfapac.org/

The German-speaking PostgreSQL Conference 2019 will take place on May 10, 2019
in Leipzig. The CfP is open until February 26, 2019 at http://2019.pgconf.de/cfp
http://2019.pgconf.de/

PGDay.IT 2019 will take place May 16th and May 17th in Bologna, Italy. Both the
CfP https://2019.pgday.it/en/blog/cfp and the Call for Workshops
https://2019.pgday.it/en/blog/cfw are openuntil January 15, 2019.
https://2019.pgday.it/en/

PGCon 2019 will take place in Ottawa on May 28-31, 2019. The CfP is open
through January 19, 2019 at http://www.pgcon.org/2019/papers.php
https://www.pgcon.org/2018/schedule/

Swiss PGDay 2019 will take place in Rapperswil (near Zurich) on June 28, 2019.
The CfP is open January 17, 2019 through April 18, 2019, and registration will
open January 17, 2019.
http://www.pgday.ch/2019/

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

Michaël Paquier pushed:

- Prioritize history files when archiving. At the end of recovery for the
post-promotion process, a new history file is created followed by the last
partial segment of the previous timeline. Based on the timing, the archiver
would first try to archive the last partial segment and then the history file.
This can delay the detection of a new timeline taken, particularly depending
on the time it takes to transfer the last partial segment as it delays the
moment the history file of the new timeline gets archived. This can cause
promoted standbys to use the same timeline as one already taken depending on
the circumstances if multiple instances look at archives at the same location.
This commit changes the order of archiving so as history files are archived in
priority over other file types, which reduces the likelihood of the same
timeline being taken (still not reducing the window to zero), and it makes the
archiver behave more consistently with the startup process doing its
post-promotion business. Author: David Steele Reviewed-by: Michael Paquier,
Kyotaro Horiguchi Discussion:
https://postgr.es/m/929068cf-69e1-bba2-9dc0-e05986aed471@pgmasters.net
Backpatch-through: 9.5
https://git.postgresql.org/pg/commitdiff/b981df4cc09aca978c5ce55e437a74913d09cccc

- Improve tab completion of ALTER INDEX/TABLE with SET STATISTICS in psql. This
fixes two issues with the completion of ALTER TABLE and ALTER INDEX after SET
STATISTICS is typed, trying to suggest schema objects while the grammar only
allows integers. The tab completion of ALTER INDEX is made smarter by
handling properly more patterns. COLUMN is an optional keyword, but as no
column numbers can be suggested yet as possible input simply adjust the
completion so as no incorrect queries are generated. Author: Michael Paquier
Reviewed-by: Tatsuro Yamada Discussion:
https://postgr.es/m/20181219092255.GC680@paquier.xyz
https://git.postgresql.org/pg/commitdiff/f89ae34ab8b4d9e9ce8af6bd889238b0ccff17cb

- Ignore inherited temp relations from other sessions when truncating.
Inheritance trees can include temporary tables if the parent is permanent,
which makes possible the presence of multiple temporary children from
different sessions. Trying to issue a TRUNCATE on the parent in this scenario
causes a failure, so similarly to any other queries just ignore such cases,
which makes TRUNCATE work transparently. This makes truncation behave
similarly to any other DML query working on the parent table with queries
which need to be work on the children. A set of isolation tests is added to
cover basic cases. Reported-by: Zhou Digoal Author: Amit Langote, Michael
Paquier Discussion: https://postgr.es/m/15565-ce67a48d0244436a@postgresql.org
Backpatch-through: 9.4
https://git.postgresql.org/pg/commitdiff/1e504f01da11db0181d7b28bb30cb5eeb0767184

- Clarify referential actions in docs of CREATE/ALTER TABLE. The documentation
of ON DELETE and ON UPDATE uses the term "action", which is also used on the
ALTER TABLE page for other purposes. This commit renames the term to
"referential_action", which is more consistent with the SQL specification.
The new term is now used on the documentation of both CREATE TABLE and ALTER
TABLE for consistency. Reported-by: Brigitte Blanc-Lafay Author: Lætitia
Avrot Reviewed-by: Álvaro Herrera Discussion:
https://postgr.es/m/CAB_COdiHEVVs0uB+uYCjjYUwQ4YFFekppq+Xqv6qAM8+cd42gA@mail.gmail.com
https://git.postgresql.org/pg/commitdiff/f7ea1a42337f45efed7c4d44f50f62a55e60d30c

- Improve description of DEFAULT_XLOG_SEG_SIZE in pg_config.h. This was
incorrectly referring to --walsegsize, and its description is rewritten in a
clearer way. Author: Ian Barwick, Tom Lane Reviewed-by: Álvaro Herrera,
Michael Paquier Discussion:
https://postgr.es/m/08534fc6-119a-c498-254e-d5acc4e6bf85@2ndquadrant.com
https://git.postgresql.org/pg/commitdiff/0a5a493f042b30afe350763820dc79bb3e6df91a

- Trigger stmt_beg and stmt_end for top-level statement blocks of PL/pgSQL.
PL/pgSQL provides a set of callbacks which can be used for extra
instrumentation of functions written in this language called at function
setup, begin and end, as well as statement begin and end. When calling a
routine, a trigger, or an event trigger, statement callbacks are not getting
called for the top-level statement block leading to an inconsistent handling
compared to the other statements. This inconsistency can potentially
complicate extensions doing instrumentation work on top of PL/pgSQL, so this
commit makes sure that all statement blocks, including the top-level one, go
through the correct corresponding callbacks. Author: Pavel Stehule
Reviewed-by: Michael Paquier Discussion:
https://postgr.es/m/CAFj8pRArEANsaUjo5in9_iQt0vKf9ecwDAmsdN_EBwL13ps12A@mail.gmail.com
https://git.postgresql.org/pg/commitdiff/e0ef136d5235f39f5652c209c08a5d4322560e9c

Tom Lane pushed:

- Fix portability failure introduced in commits d2b0b60e7 et al. I made a
frontend fprintf() format use %m, forgetting that that's only safe in HEAD not
the back branches; prior to 96bf88d52 and d6c55de1f, it would work on glibc
platforms but not elsewhere. Revert to using %s ... strerror(errno) as the
code did before. We could have left HEAD as-is, but for code consistency
across branches, I chose to apply this patch there too. Per Coverity and a
few buildfarm members.
https://git.postgresql.org/pg/commitdiff/e9fcfed3fb6b15e6b33bea9bb8504b14560857a8

- Fix failure to check for open() or fsync() failures. While it seems OK to not
be concerned about fsync() failure for a pre-existing signal file, it's not OK
to not even check for open() failure. This at least causes complaints from
static analyzers, and I think on some platforms passing -1 to fsync() or
close() might trigger assertion-type failures. Also add (void) casts to make
clear that we're ignoring fsync's result intentionally. Oversights in commit
2dedf4d9a, noted by Coverity.
https://git.postgresql.org/pg/commitdiff/8528e3d849a896f8711c56fb41eae56f8c986729

- Fix latent problem with pg_jrand48(). POSIX specifies that jrand48() returns
a signed 32-bit value (in the range [-2^31, 2^31)), but our code was returning
an unsigned 32-bit value (in the range [0, 2^32)). This doesn't actually
matter to any existing call site, because they all cast the "long" result to
int32 or uint32; but it will doubtless bite somebody in the future. To fix,
cast the arithmetic result to int32 explicitly before the compiler widens it
to long (if widening is needed). While at it, upgrade this file's
far-short-of-project-style comments. Had there been some peer pressure to
document pg_jrand48() properly, maybe this thinko wouldn't have gotten
committed to begin with. Backpatch to v10 where pg_jrand48() was added, just
in case somebody back-patches a fix that uses it and depends on the standard
behavior. Discussion: https://postgr.es/m/17235.1545951602@sss.pgh.pa.us
https://git.postgresql.org/pg/commitdiff/e09046641114dc5ab038530019bc35b04c2976e7

- Marginal performance hacking in erand48.c. Get rid of the multiplier and
addend variables in favor of hard-wired constants. Do the multiply-and-add
using uint64 arithmetic, rather than manually combining several narrower
multiplications and additions. Make _dorand48 return the full-width new
random value, and have its callers use that directly (after suitable masking)
rather than reconstructing what they need from the unsigned short[]
representation. On my machine, this is good for a nearly factor-of-2 speedup
of pg_erand48(), probably mostly from needing just one call of ldexp() rather
than three. The wins for the other functions are smaller but measurable.
While none of the existing call sites are really performance-critical, a cycle
saved is a cycle earned; and besides the machine code is smaller this way (at
least on x86_64). Patch by me, but the original idea to optimize this by
switching to int64 arithmetic is from Fabien Coelho. Discussion:
https://postgr.es/m/1551.1546018192@sss.pgh.pa.us
https://git.postgresql.org/pg/commitdiff/6b9bba2df8d469a13bf8f0b9eb6933c7eaaff2c1

- Use pg_strong_random() to select each server process's random seed.
Previously we just set the seed based on process ID and start timestamp. Both
those values are directly available within the session, and can be found out
or guessed by other users too, making the session's series of random(3) values
fairly predictable. Up to now, our backend-internal uses of random(3) haven't
seemed security-critical, but commit 88bdbd3f7 added one that potentially is:
when using log_statement_sample_rate, a user might be able to predict which of
his SQL statements will get logged. To improve this situation, upgrade the
per-process seed initialization method to use pg_strong_random() if available,
greatly reducing the predictability of the initial seed value. This adds a
few tens of microseconds to process start time, but since backend startup time
is at least a couple of milliseconds, that seems an acceptable price. This
means that pg_strong_random() needs to be able to run without reliance on any
backend infrastructure, since it will be invoked before any of that is up. It
was safe for that already, but adjust comments and #include commands to make
it clearer. Discussion: https://postgr.es/m/3859.1545849900@sss.pgh.pa.us
https://git.postgresql.org/pg/commitdiff/4203842a1cd06a7c30fd3eaae1c111928909e539

- Use a separate random seed for SQL random()/setseed() functions. Previously,
the SQL random() function depended on libc's random(3), and setseed() invoked
srandom(3). This results in interference between these functions and
backend-internal uses of random(3). We'd never paid too much mind to that,
but in the wake of commit 88bdbd3f7 which added log_statement_sample_rate, the
interference arguably has a security consequence: if log_statement_sample_rate
is active then an unprivileged user could probably control which if any of his
SQL commands get logged, by issuing setseed() at the right times. That seems
bad. To fix this reliably, we need random() and setseed() to use their own
private random state variable. Standard random(3) isn't amenable to such
usage, so let's switch to pg_erand48(). It's hard to say whether that's more
or less "random" than any particular platform's version of random(3), but it
does have a wider seed value and a longer period than are required by POSIX,
so we can hope that this isn't a big downgrade. Also, we should now have
uniform behavior of random() across platforms, which is worth something.
While at it, upgrade the per-process seed initialization method to use
pg_strong_random() if available, greatly reducing the predictability of the
initial seed value. (I'll separately do something similar for the internal
uses of random().) In addition to forestalling the possible security problem,
this has a benefit in the other direction, which is that we can now document
setseed() as guaranteeing a reproducible sequence of random() values.
Previously, because of the possibility of internal calls of random(3), we
could not promise any such thing. Discussion:
https://postgr.es/m/3859.1545849900@sss.pgh.pa.us
https://git.postgresql.org/pg/commitdiff/6645ad6bdd81e7d5a764e0d94ef52fae053a9e13

- Teach eval_const_expressions to constant-fold LEAST/GREATEST expressions.
Doing this requires an assumption that the invoked btree comparison function
is immutable. We could check that explicitly, but in other places such as
contain_mutable_functions we just assume that it's true, so we may as well do
likewise here. (If the comparison function's behavior isn't immutable, the
sort order in indexes built with it would be unstable, so it seems certainly
wrong for it not to be so.) Vik Fearing Discussion:
https://postgr.es/m/c6e8504c-4c43-35fa-6c8f-3c0b80a912cc@2ndquadrant.com
https://git.postgresql.org/pg/commitdiff/6f19a8c41f976236310a272bb646d3411759e18d

- Support parameterized TidPaths. Up to now we've not worried much about joins
where the join key is a relation's CTID column, reasoning that storing a
table's CTIDs in some other table would be pretty useless. However, there are
use-cases for this sort of query involving self-joins, so that argument
doesn't really hold water. This patch allows generating plans for joins on
CTID that use a nestloop with inner TidScan, similar to what we might do with
an index on the join column. This is the most efficient way to join when the
outer side of the nestloop is expected to yield relatively few rows. This
change requires upgrading tidpath.c and the generated TidPaths to work with
RestrictInfos instead of bare qual clauses, but that's long-postponed
technical debt anyway. Discussion:
https://postgr.es/m/17443.1545435266@sss.pgh.pa.us
https://git.postgresql.org/pg/commitdiff/b5415e3c2187ab304390524f5ae66b4bd2c58279

- Add a hash opclass for type "tid". Up to now we've not worried much about
joins where the join key is a relation's CTID column, reasoning that storing a
table's CTIDs in some other table would be pretty useless. However, there are
use-cases for this sort of query involving self-joins, so that argument
doesn't really hold water. With larger relations, a merge or hash join is
desirable. We had a btree opclass for type "tid", allowing merge joins on
CTID, but no hash opclass so that hash joins weren't possible. Add the
missing infrastructure. This also potentially enables hash aggregation on
"tid", though the use-cases for that aren't too clear. Discussion:
https://postgr.es/m/1853.1545453106@sss.pgh.pa.us
https://git.postgresql.org/pg/commitdiff/0a6ea4001a9dff64e9ba66f68855a59a1bf69bc9

Alexander Korotkov pushed:

- Remove entry tree root conflict checking from GIN predicate locking.
According to README we acquire predicate locks on entry tree leafs and posting
tree roots. However, when ginFindLeafPage() is going to lock leaf in
exclusive mode, then it checks root for conflicts regardless whether it's a
entry or posting tree. Assuming that we never place predicate lock on entry
tree root (excluding corner case when root is leaf), this check is redundant.
This commit removes this check. Now, root conflict checking is controlled by
separate argument of ginFindLeafPage(). Discussion:
https://postgr.es/m/CAPpHfdv7rrDyy%3DMgsaK-L9kk0AH7az0B-mdC3w3p0FSb9uoyEg%40mail.gmail.com
Author: Alexander Korotkov Backpatch-through: 11
https://git.postgresql.org/pg/commitdiff/b450abd2551ee30b5bc289c662f5728d87e13a39

- Reduce length of GIN predicate locking isolation test suite. Isolation test
suite of GIN predicate locking was criticized for being too slow, especially
under Valgrind. This commit is intended to accelerate it. Tests are
simplified in the following ways. 1) Amount of data is reduced. We're now
close to the minimal amount of data, which produces at least one posting tree
and at least two pages of entry tree. 2) Three isolation tests are merged
into one. 3) Only one tuple is queried from posting tree. So, locking of
index is the same, but tuple locks are not propagated to relation lock. Also,
it is faster. 4) Test cases itself are simplified. Now each test case run
just one INSERT and one SELECT involving GIN, which either conflict or not.
Discussion:
https://postgr.es/m/20181204000740.ok2q53nvkftwu43a%40alap3.anarazel.de
Reported-by: Andres Freund Tested-by: Andrew Dunstan Author: Alexander
Korotkov Backpatch-through: 11
https://git.postgresql.org/pg/commitdiff/0c6f4f9212cf3155b258910acf80fec89f49b767

Peter Eisentraut pushed:

- pg_dump: Add missing newline to error message.
https://git.postgresql.org/pg/commitdiff/5c828307973366f424438b848d4cca6ef98c032e

- Remove obsolete IndexIs* macros. Remove IndexIsValid(), IndexIsReady(),
IndexIsLive() in favor of accessing the index structure directly. These
macros haven't been used consistently, and the original reason of maintaining
source compatibility with PostgreSQL 9.2 is gone. Discussion:
https://www.postgresql.org/message-id/flat/d419147c-09d4-6196-5d9d-0234b230880a%402ndquadrant.com
https://git.postgresql.org/pg/commitdiff/ae4472c619341ff0517254d395d74796277622e6

- Remove redundant translation markers. psql_error() already handles that
itself.
https://git.postgresql.org/pg/commitdiff/e3299d36a938c7af386b240f318c7b9e55bdc92d

- pg_rewind: Add missing newline to error message.
https://git.postgresql.org/pg/commitdiff/1a4eba4e246480466c04feb81f76866e7dcb2827

- Change "checkpoint starting" message to use "wal". This catches up with the
recent renaming of all user-facing mentions of "xlog" to "wal". Discussion:
https://www.postgresql.org/message-id/flat/20181129084708.GA9562%40msg.credativ.de
https://git.postgresql.org/pg/commitdiff/60d99797bfb07f8042a8b82c161500f6e4a500ec

Álvaro Herrera pushed:

- Rewrite ExecPartitionCheckEmitError for clarity. The original was hard to
follow and failed to comply with DRY principle. Discussion:
https://postgr.es/m/20181206222221.g5witbsklvqthjll@alvherre.pgsql
https://git.postgresql.org/pg/commitdiff/e8b0e6b82dbdb4459ec19f9871f691dfa7d1902c

- Fix thinko in previous commit.
https://git.postgresql.org/pg/commitdiff/4ed6c071b82a80fb7d8396477a5d698de3c93bf3

== Pending Patches ==

Alexander Kuzmenkov sent in another revision of a patch to remove unneeded
self-joins.

Noah Misch sent in another revision of a patch to fix a race to build
pg_isolation_regress in "make -j check-world".

Aleksey Kondratov sent in two more revisions of a patch to pg_rewind which adds
options to use restore_command from postgresql.conf or from the command line.

Ian Barwick sent in a patch to fill in some missing doc references on array and
string functions.

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

Mitar sent in six revisions of a patch to add triggers to materialized views.

David Rowley and Tomáš Vondra traded patches to speed up foreign-key-aware join
estimation.

Michaël Paquier sent in a patch to add flag to format_type_extended to enforce
NULL-ness, refactor the format procedure and operator APIs to be more modular,
and eliminate user-visible cache lookup errors for objaddr SQL functions.

Iwata Aya sent in another revision of a patch to add a trace log for libpq.

Corey Huinker sent in a patch to refactor per-row unique key deferred constraint
triggers into per-statement triggers.

Michael Banck sent in another revision of a patch to implement progress
reporting for pg_verify_checksums.

Yoshikazu Imai and Amit Langote traded patches to speed up planning with
partitions.

Fabien COELHO sent in a patch to make pg_dumpall's output more verbose.

Mitar sent in four revisions of a patch to implement temporary materialized
views.

Michaël Paquier sent in a patch to clarify a comment in
src/backend/access/transam/xlog.c in WaitForWALToBecomeAvailable().

Tomáš Vondra sent in another revision of a patch to implement multivariate
histograms and MCV lists.

Ryohei Nagaura sent in another revision of a patch to add TCP timeout parameters
to both the backend and to libpq.

Etsuro Fujita sent in two more revisions of a patch to the postgres_fdw which
makes it perform both the UPPERREL_ORDERED and UPPERREL_FINAL steps remotely.

Thomas Munro sent in a patch to add a shared_memory_type GUC and use it to
support huge pages on AIX.

Michaël Paquier sent in a patch to fix a failure to check for open() or fsync()
failures.

Hayato Kuroda sent in another revision of a patch to add DECLARE STATEMENT to
ecpg.

Evgeniy Efimkin sent in another revision of a patch to add a filter to CREATE
SUBSCRIPTION.

Dmitry Dolgov sent in another revision of a patch to implement generic type
subscripting.

Peter Eisentraut sent in two more revisions of a patch to add value 'current'
for recovery_target_timeline and change the default of recovery_target_timeline
to 'latest'.

John Naylor sent in two more revisions of a patch to use offset-based keyword
lookup and dispatch keyword lookup on the first character.

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

Thomas Munro sent in another revision of a patch to implement a synchronous
replay mode for avoiding stale reads on hot standbys.

Tatsuro Yamada sent in another revision of a patch to implement a CLUSTER
command progress monitor.

Etsuro Fujita sent in a patch to fix the PostgreSQL FDW grouping path cost
estimation.

Peter Eisentraut sent in another revision of a patch to implement collations
with nondeterministic comparison, one use case being case-insensitive
collations.

Surafel Temesgen sent in another revision of a patch to enable pg_dump's
--inserts option to use multi-values INSERTs.

Dimitri Fontaine sent in two revisions of a patch to add Prepare Transaction
support for ON COMMIT DROP temporary tables.

Peter Eisentraut sent in a patch to use atexit() in initdb and pg_basebackup.

legrand legrand sent in a patch to add planning counters to pg_stat_statements.

Michaël Paquier sent in another revision of a patch to clean up some elog
messages and comments for do_pg_stop_backup and do_pg_start_backup.

James Coleman sent in a patch to teach Btree to provide sorting on suffix keys
with LIMIT.

Noah Misch sent in a patch to augment every test postgresql.conf.

Michaël Paquier sent in two revisions of a patch to remove
--disable-strong-random from the code.

Petr Jelínek sent in two revisions of a patch to move slot restart_lsn/catalog_xmin
more aggressively from SQL slot functions.

Peter Eisentraut sent in a patch to create a unified logging system for
command-line programs.

Heikki Linnakangas sent in a patch to create a new session in postmaster by
calling setsid().

Petr Jelínek sent in a patch to synchronize logical replication slots from
primary to standby and add an option for filtering which slots get synchronized.