== PostgreSQL Weekly News - October 30 2016 ==

== PostgreSQL Weekly News - October 30 2016 ==

Bug fix releases 9.6.1, 9.5.5, 9.4.10, 9.3.15, 9.2.19, and 9.1.24 are out.
Upgrade ASAP. 9.1.24 is the final release of the 9.1 series.
https://www.postgresql.org/about/news/1712/

== PostgreSQL Jobs for October ==

http://archives.postgresql.org/pgsql-jobs/2016-10/threads.php

== PostgreSQL Local ==

PostgreSQL Conference Europe will take place in Tallin, Estonia, on
November 1-4, 2016. The schedule has been published.
http://2016.pgconf.eu/registration/

PGDay Austin 2016, will take place on November 12, 2016.
https://www.postgresql.us/events/2016/austin

PgConf Silicon Valley 2016 will be held on November 14-16, 2016.
http://www.pgconfsv.com/

CHAR(16) will take place in New York, December 6, 2016.
http://charconference.org/

PGDay.IT 2016 will take place in Prato on December the 13th 2016.
http://pgday.it

PostgreSQL(at)SCaLE will take place on March 2-3, 2017, at Pasadena Convention
Center, as part of SCaLE 15X. The CfP is open until 15 November, 2016.
http://www.socallinuxexpo.org/scale/15x/cfp

== 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 Pacific time.
Please send English language ones to david(at)fetter(dot)org, German language
to pwn(at)pgug(dot)de, Italian language to pwn(at)itpug(dot)org(dot)

== Applied Patches ==

Tom Lane pushed:

- Don't throw serialization errors for self-conflicts in INSERT ON CONFLICT. A
transaction that conflicts against itself, for example INSERT INTO t(pk)
VALUES (1),(1) ON CONFLICT DO NOTHING; should behave the same regardless of
isolation level. It certainly shouldn't throw a serialization error, as
retrying will not help. We got this wrong due to the ON CONFLICT logic not
considering the case, as reported by Jason Dusek. Core of this patch is by
Peter Geoghegan (based on an earlier patch by Thomas Munro), though I didn't
take his proposed code refactoring for fear that it might have unexpected
side-effects. Test cases by Thomas Munro and myself. Report:
<CAO3NbwOycQjt2Oqy2VW-eLTq2M5uGMyHnGm=RNga4mjqcYD7gQ(at)mail(dot)gmail(dot)com>
Related-Discussion: <57EE93C8(dot)8080504(at)postgrespro(dot)ru>
http://git.postgresql.org/pg/commitdiff/a6c0a5b6e8a9498540c6a7bb1b6d68958acc9bc6

- Avoid testing tuple visibility without buffer lock. INSERT ... ON CONFLICT
(specifically ExecCheckHeapTupleVisible) contains another example of this
unsafe coding practice. It is much harder to get a failure out of it than the
case fixed in commit 6292c2339, because in most scenarios any hint bits that
could be set would have already been set earlier in the command. However,
Konstantin Knizhnik reported a failure with a custom transaction manager, and
it's clearly possible to get a failure via a race condition in async-commit
mode. For lack of a reproducible example, no regression test case in this
commit. I did some testing with Asserts added to tqual.c's functions, and can
say that running "make check-world" exposed these two bugs and no others. The
Asserts are messy enough that I've not added them to the code for now.
Report: <57EE93C8(dot)8080504(at)postgrespro(dot)ru> Related-Discussion:
<CAO3NbwOycQjt2Oqy2VW-eLTq2M5uGMyHnGm=RNga4mjqcYD7gQ(at)mail(dot)gmail(dot)com>
http://git.postgresql.org/pg/commitdiff/8f1fb7d621b0e6bd2eb0ba2ac9634c5b5a03564b

- Release notes for 9.6.1, 9.5.5, 9.4.10, 9.3.15, 9.2.19, 9.1.24.
http://git.postgresql.org/pg/commitdiff/7d80417d3dfc88b0c03b5c08a18b29f9d430e217

- Update release notes for last-minute commit timestamp fix.
http://git.postgresql.org/pg/commitdiff/30a6f98ed8a2223ef876197b5dea0d94e5807b51

- Suppress unused-variable warning in non-assert builds. Introduced in commit
7012b132d. Kyotaro Horiguchi
http://git.postgresql.org/pg/commitdiff/8529686ccbb9f1c2fe350920ad324c223135a957

- Doc: improve documentation about inheritance. Clarify documentation about
inheritance of check constraints, in particular mentioning the NO INHERIT
option, which didn't exist when this text was written. Document that in an
inherited query, the applicable row security policies are those of the
explicitly-named table, not its children. This is the intended behavior (per
off-list discussion with Stephen Frost), and there are regression tests for
it, but it wasn't documented anywhere user-facing as far as I could find. Do
a bit of wordsmithing on the description of inherited access-privilege checks.
Back-patch to 9.5 where RLS was added.
http://git.postgresql.org/pg/commitdiff/162477a63d3c0fd1c31197717140a88077a8d0aa

- Fix not-HAVE_SYMLINK code in zic.c. I broke this in commit f3094920a.
Apparently it's dead code anyway, at least as far as our buildfarm is
concerned (and the upstream IANA code doesn't worry at all about symlink() not
being present). But as long as the rest of our code is willing to guard
against not having symlink(), this should too. Noted while investigating a
tangentially-related complaint from Sandeep Thakkar. Back-patch to keep
branches in sync.
http://git.postgresql.org/pg/commitdiff/19b2094d96807e43d29687b3860e8fffb9da61b4

- Fix incorrect trigger-property updating in ALTER CONSTRAINT. The code to
change the deferrability properties of a foreign-key constraint updated all
the associated triggers to match; but a moment's examination of the code that
creates those triggers in the first place shows that only some of them should
track the constraint's deferrability properties. This leads to odd failures
in subsequent exercise of the foreign key, as the triggers are fired at the
wrong times. Fix that, and add a regression test comparing the trigger
properties produced by ALTER CONSTRAINT with those you get by creating the
constraint as-intended to begin with. Per report from James Parks.
Back-patch to 9.4 where this ALTER functionality was introduced. Report:
<CAJ3Xv+jzJ8iNNUcp4RKW8b6Qp1xVAxHwSXVpjBNygjKxcVuE9w(at)mail(dot)gmail(dot)com>
http://git.postgresql.org/pg/commitdiff/a522fc3d806ca6ebe3f66e55b3f8cecb85116711

- Improve speed of aggregates that use array_append as transition function. In
the previous coding, if an aggregate's transition function returned an
expanded array, nodeAgg.c and nodeWindowAgg.c would always copy it and thus
force it into the flat representation. This led to ping-ponging between flat
and expanded formats, which costs a lot. For an aggregate using array_append
as transition function, I measured about a 15X slowdown compared to the
pre-9.5 code, when working on simple int[] arrays. Of course, the old code
was already O(N^2) in this usage due to copying flat arrays all the time, but
it wasn't quite this inefficient. To fix, teach nodeAgg.c and nodeWindowAgg.c
to allow expanded transition values without copying, so long as the transition
function takes care to return the transition value already properly parented
under the aggcontext. That puts a bit of extra responsibility on the
transition function, but doing it this way allows us to not need any extra
logic in the fast path of advance_transition_function (ie, with a
pass-by-value transition value, or with a modified-in-place pass-by-reference
value). We already know that that's a hot spot so I'm loath to add any cycles
at all there. Also, while only array_append currently knows how to follow
this convention, this solution allows other transition functions to opt-in
without needing to have a whitelist in the core aggregation code. (The reason
we would need a whitelist is that currently, if you pass a R/W expanded-object
pointer to an arbitrary function, it's allowed to do anything with it
including deleting it; that breaks the core agg code's assumption that it
should free discarded values. Returning a value under aggcontext is the
transition function's signal that it knows it is an aggregate transition
function and will play nice. Possibly the API rules for expanded objects
should be refined, but that would not be a back-patchable change.) With this
fix, an aggregate using array_append is no longer O(N^2), so it's much faster
than pre-9.5 code rather than much slower. It's still a bit slower than the
bespoke infrastructure for array_agg, but the differential seems to be only
about 10%-20% rather than orders of magnitude. Discussion:
<6315(dot)1477677885(at)sss(dot)pgh(dot)pa(dot)us>
http://git.postgresql.org/pg/commitdiff/9a00f03e479c2d4911eed5b4bd1994315d409938

- Fix bogus tree-flattening logic in QTNTernary(). QTNTernary() contains logic
to flatten, eg, '(a & b) & c' into 'a & b & c', which is all well and good,
but it tries to do that to NOT nodes as well, so that '!!a' gets changed to
'!a'. Explicitly restrict the conversion to be done only on AND and OR nodes,
and add a test case illustrating the bug. In passing, provide some comments
for the sadly naked functions in tsquery_util.c, and simplify some baroque
logic in QTNFree(), which I think may have been leaking some items it intended
to free. Noted while investigating a complaint from Andreas Seltenreich.
Back-patch to all supported versions.
http://git.postgresql.org/pg/commitdiff/24ebc444c61306f50777f674544e8559e765ad81

- Fix nasty performance problem in tsquery_rewrite(). tsquery_rewrite() tries
to find matches to subsets of AND/OR conditions; for example, in the query 'a
| b | c' the substitution subquery 'a | c' should match and lead to
replacement of the first and third items. That's fine, but the matching
algorithm apparently takes about O(2^N) for an N-clause query (I say
"apparently" because the code is also both unintelligible and uncommented).
We could probably do better than that even without any extra assumptions ---
but actually, we know that the subclauses are sorted, indeed are depending on
that elsewhere in this very same function. So we can just scan the two lists
a single time to detect matches, as though we were doing a merge join. Also
do a re-flattening call (QTNTernary()) in tsquery_rewrite_query, just to make
sure that the tree fits the expectations of the next search cycle. I didn't
try to devise a test case for this, but I'm pretty sure that the oversight
could have led to failure to match in some cases where a match would be
expected. Improve comments, and also stick a CHECK_FOR_INTERRUPTS into
dofindsubquery, just in case it's still too slow for somebody. Per report
from Andreas Seltenreich. Back-patch to all supported branches. Discussion:
<8760oasf2y(dot)fsf(at)credativ(dot)de>
http://git.postgresql.org/pg/commitdiff/5ec81aceec8cc5a0edcc4609ee8edbc43a47e878

Álvaro Herrera pushed:

- Preserve commit timestamps across clean restart. An oversight in setting the
boundaries of known commit timestamps during startup caused old commit
timestamps to become inaccessible after a server restart. Author and
reporter: Julien Rouhaud Review, test code: Craig Ringer
http://git.postgresql.org/pg/commitdiff/00f15338b234e5fd7cda2d7bf0ef8d9f29f6bf5f

Magnus Hagander pushed:

- Use ssize_t where signed results can happen. Noted by Alexander Korotkov
http://git.postgresql.org/pg/commitdiff/2dde01ccbfb4c53c7edd28a6836ba47303fea0ba

- Free walmethods before exiting. Not strictly necessary since we quite after,
but could become important in the future if we do restarts etc. Michael
Paquier with nitpicking from me
http://git.postgresql.org/pg/commitdiff/78d109150bf133c38bcdc6d8c5bd9ff546ed1171

- Don't fsync() files when --no-sync is specified. Michael Paquier
http://git.postgresql.org/pg/commitdiff/8c46f0c9ce4695db7d68188e08e5e6be1c329645

- Fix memory leak in tar file padding. Spotted by Coverity, patch by Michael
Paquier
http://git.postgresql.org/pg/commitdiff/a775406ec439bc585a17f8edffd47641390d6e5a

Robert Haas pushed:

- postgres_fdw: Try again to stabilize aggregate pushdown regression tests. A
query that only aggregates one row isn't a great argument for pushdown, and
buildfarm member brolga decides against it. Adjust the query a bit in the
hopes of getting remote aggregation to win consistently. Jeevan Chalke, per
suggestion from Tom Lane
http://git.postgresql.org/pg/commitdiff/f5d6bce63ceb3c59a964814bb0df5a0648e750e5

- Fix possible pg_basebackup failure on standby with "include WAL". If a
restartpoint flushed no dirty buffers, it could fail to update the minimum
recovery point, leading to a minimum recovery point prior to the starting REDO
location. perform_base_backup() would interpret that as meaning that no WAL
files at all needed to be included in the backup, failing an internal sanity
check. To fix, have restartpoints always update the minimum recovery point to
just after the checkpoint record itself, so that the file (or files)
containing the checkpoint record will always be included in the backup. Code
by Amit Kapila, per a design suggestion by me, with some additional work on
the code comment by me. Test case by Michael Paquier. Report by Kyotaro
Horiguchi.
http://git.postgresql.org/pg/commitdiff/f267c1c2447bb8da6e4a6b2fcbb612762c3579a8

- If the stats collector dies during Hot Standby, restart it. This bug exists
as far back as 9.0, when Hot Standby was introduced, so back-patch to all
supported branches. Report and patch by Takayuki Tsunakawa, reviewed by
Michael Paquier and Kuntal Ghosh.
http://git.postgresql.org/pg/commitdiff/4f714b2fd2a580d909607de889ac822956eb8299

- Fix leftover reference to background writer performing checkpoints. This was
changed in PostgreSQL 9.2, but somehow this comment never got updated.
http://git.postgresql.org/pg/commitdiff/33839b5ffbd7be56681f31d107ec8238c4a0494a

- pgstattuple: Don't take heavyweight locks when examining a hash index. It's
currently necessary to take a heavyweight lock when scanning a hash bucket,
but pgstattuple only examines individual pages, so it doesn't need to do this.
If, for some hypothetical reason, it did need to do any heavyweight locking
here, this logic would probably still be incorrect, because most of the locks
that it is taking are meaningless. Only a heavyweight lock on a primary
bucket page has any meaning, but this takes heavyweight locks on all pages
regardless of function - and in particular overflow pages, where you might
imagine that we'd want to lock the primary bucket page if we needed to lock
anything at all. This is arguably a bug that has existed since this code was
added in commit dab42382f483c3070bdce14a4d93c5d0cf61e82b, but I'm not going to
bother back-patching it because in most cases the only consequence is that
running pgstattuple() on a hash index is a little slower than it otherwise
might be, which is no big deal. Extracted from a vastly larger patch by Amit
Kapila which heavyweight locking for hash indexes entirely; analysis of why
this can be done independently of the rest by me.
http://git.postgresql.org/pg/commitdiff/d4b5d4caddb73f954d9ef86641201dc99677719d

Bruce Momjian pushed:

- Consistently mention 'SELECT pg_reload_conf()' in config files. Previously we
only mentioned SIGHUP and 'pg_ctl reload' in postgresql.conf and pg_hba.conf.
http://git.postgresql.org/pg/commitdiff/10c064ce4dad088ba2d8b978bff6009b9f22dc3a

- Properly indent postgresql.conf comments to align. A few comments were
misaligned.
http://git.postgresql.org/pg/commitdiff/586a46c22cea1526995195283fee0521fc6674b8

Peter Eisentraut pushed:

- pg_dump: Simplify internal archive version handling. The ArchiveHandle
structure contained the archive format version number twice, once as a single
field and once split into components. Simplify that by just keeping the
single field and adding some macros to extract the components. Introduce some
macros for composing version numbers, to eliminate the repeated use of magic
formulas. Drop the unused trailing zero byte from the run-time composite
version representation. reviewed by Tom Lane
http://git.postgresql.org/pg/commitdiff/8c035e55c4807dc5844f563dcbbc44ffe2fdd4d6

- Avoid using a C++ keyword in header file, per cpluspluscheck.
http://git.postgresql.org/pg/commitdiff/c32fe432afd4bc428acf4a237f911271746f689f

- Add function name to PyArg_ParseTuple(). This causes the supplied function
name to appear in any error message, making the error message friendlier and
relieving us from having to provide our own in some cases.
http://git.postgresql.org/pg/commitdiff/eaed88ce120746b3004225252f52d8c79fea2f58

- Format PL/Python module contents test vertically. It makes it readable again
and makes merges more manageable.
http://git.postgresql.org/pg/commitdiff/84d457edaf4b3a1e10fd9e100e8ca18c042ad30c

- Remove invitation to report a bug about unknown encoding. The error message
when we couldn't determine the encoding from a locale said to report a bug
about that. That might have been appropriate when this code was first added,
but by now this works pretty solidly and any encodings we don't recognize we
probably just don't support. We still print the warning, but no longer invite
the bug report.
http://git.postgresql.org/pg/commitdiff/ce4dc970560a25d4f7091a6e9ce8c6e1f25d55c9

- doc: Small style improvements
http://git.postgresql.org/pg/commitdiff/a94b70356bcc3b2f5fc107d0ddfa936c32535a30

Heikki Linnakangas pushed:

- Support multi-dimensional arrays in PL/python. Multi-dimensional arrays can
now be used as arguments to a PL/python function (used to throw an error), and
they can be returned as nested Python lists. This makes a
backwards-incompatible change to the handling of composite types in arrays.
Previously, you could return an array of composite types as "[[col1, col2],
[col1, col2]]", but now that is interpreted as a two- dimensional array.
Composite types in arrays must now be returned as Python tuples, not lists, to
resolve the ambiguity. I.e. "[(col1, col2), (col1, col2)]". To avoid breaking
backwards-compatibility, when not necessary, () is still accepted for arrays
at the top-level, but it is always treated as a single-dimensional array.
Likewise, [] is still accepted for composite types, when they are not in an
array. Update the documentation to recommend using [] for arrays, and () for
composite types, with a mention that those other things are also accepted in
some contexts. This needs to be mentioned in the release notes. Alexey
Grishchenko, Dave Cramer and me. Reviewed by Pavel Stehule. Discussion:
<CAH38_tmbqwaUyKs9yagyRra=SMaT45FPBxk1pmTYcM0TyXGG7Q(at)mail(dot)gmail(dot)com>
http://git.postgresql.org/pg/commitdiff/94aceed317730953476bec490ce0148b2af3c383

- Give a hint, when [] is incorrectly used for a composite type in array. That
used to be accepted, so let's try to give a hint to users on why their
PL/python functions no longer work. Reviewed by Pavel Stehule. Discussion:
<CAH38_tmbqwaUyKs9yagyRra=SMaT45FPBxk1pmTYcM0TyXGG7Q(at)mail(dot)gmail(dot)com>
http://git.postgresql.org/pg/commitdiff/510e1b8ecf2a6f0d91d50f41f6b7fd75242273a0

- Fix typos in comments. Vinayak Pokale
http://git.postgresql.org/pg/commitdiff/56f39009c53e752493ca4478449a1311865dd51a

- Fix typo in comment. Daniel Gustafsson
http://git.postgresql.org/pg/commitdiff/8a2f08fbeaf75f67da122b49f05f96257df3faed

- Fix regression test to also work with Python 2. Per buildfarm.
http://git.postgresql.org/pg/commitdiff/e131ba4fe58123ce5726c1405486913b429c068c

- Only treat Python Lists as array dimensions. Instead of treating all python
sequence types as array dimensions, except for tuples and various kinds of
strings, only treat Python lists as dimensions. The PyBytes_Check() function
used previously is only available on Python 2.6 and newer, and it was a bit
fiddly anyway. The list of exceptions would require adjustment if Python got a
new kind of a sequence similar to bytes/unicodes/strings, so only checking for
Lists seems more future-proof. The documentation only mentioned using Lists,
so this is closer to what was documented, anyway. This should fix the
buildfarm failures on systems building with Python 2.5, although I don't have
Python 2.5 installed myself to test with.
http://git.postgresql.org/pg/commitdiff/cfd9c87a54b0d414a59a912710c6633313174e51

- Avoid using platform-dependent floats in test case. The number of decimals
printed for floats varied in this test case, as noted by several buildfarm
members. There's nothing special about floats and arrays in the code being
tested, so replace the floats with numerics to make the output
platform-independent.
http://git.postgresql.org/pg/commitdiff/73c8e8506cd1933ccf5c5d61088ca171a5f414c0

- Remove platform-dependent PL/python test case. Turns out that the output
format of Python Decimal isn't totally platform- independent either. There are
other tests for multi-dimensional arrays, so rather than try to fix this test
case, just remove it. Per buildfarm member prairiedog.
http://git.postgresql.org/pg/commitdiff/8eb6337f9f53c85e707f60157e42fcacfe927668

Tatsuo Ishii pushed:

- Fix typo in sources.sgml. Per Shinichi Matsuda.
http://git.postgresql.org/pg/commitdiff/36d154ecb2286328c7c59b99d5abb71318e056eb

== Pending Patches ==

Samuel D. Leslie sent in a patch to add a radiustimeout parameter for RADIUS
HBA.

Tsutomu Yamada and Ashutosh Bapat traded patches to change a returned NULL to
NIL in create_foreignscan_path(), and a similar issue in a
create_foreignscan_path() call in add_foreign_grouping_paths().

Petr Jelinek sent in another revision of a patch to implement logical
replication.

Petr Jelinek sent in another revision of a patch to follow timeline switches in
logical decoding and add a test harness for same.

Laurenz Albe sent in two more revisions of a patch to add PGDLLEXPORT to
PG_FUNCTION_INFO_V1 function declarations.

Haribabu Kommi sent in another revision of a patch to add macaddr 64 bit
(EUI-64) datatype support.

Robert Haas and Victor Wagner traded patches to implement failover on the libpq
connect level.

Peter Geoghegan sent in another revision of a patch to cap the number of tapes
used by external sorts, add parallel B-tree index build sorting, and add a
force_btree_randomaccess GUC for testing.

Amit Kapila sent in two more revisions of a patch to extend the bufmgr api to
include hash index.

Ashutosh Sharma sent in a patch to add microvacuum support for hash indexes.

Amit Kapila sent in another revision of a patch to add group update clog.

Kyotaro HORIGUCHI sent in three more revisions of a patch to implement
asynchronous execution.

Noah Misch sent in a patch to firmly recommend PG_GETARG_TEXT_PP() over
PG_GETARG_TEXT_P().

Tomas Vondra sent in another revision of a patch to add two slab-like memory
allocators.

Venkata B Nagothi sent in a patch to add a recovery_target_incomplete GUC.

Julian Markwort sent in another revision of a patch to allow specifying a
password file location for libpq-based applications.

Karl O. Pinc and Gilles Darold traded patches to implement a
pg_current_logfile() function.

Heikki Linnakangas and Kyotaro HORIGUCHI traded patches to implement a radix
tree for character conversion.

Thomas Munro sent in another revision of a patch to add a replay_lag column to
the pg_stat_replication view.

Claudio Freire sent in another revision of a patch to refetch buffers on
backward scan and allow using more than 1GB work mem in VACUUM.

Peter Eisentraut sent in a patch to add NLS to the remaining bin programs which
were moved from contrib a while ago.

Rushabh Lathia sent in another revision of a patch to implement gather merge.

Etsuro Fujita sent in another revision of a patch to push down more full joins
to the postgres FDW.

Ashutosh Bapat sent in another revision of a patch to pgstat to avoid writing on
SIGQUIT.

Peter Eisentraut sent in a patch to add a user-callable function to import
operation system collations.

Michaël Paquier sent in a patch to apply XLR_INFO_MASK correctly when looking at
WAL record information.

Peter Eisentraut sent in a patch to add rules to download raw UNICODE mapping
files.

Peter Eisentraut sent in a patch to split up psql's \d modifiers column into
more legible pieces.

Haribabu Kommi sent in another revision of a patch to add a pg_hba_file_settings
view.

Michaël Paquier sent in another revision of a patch to add a wal_consistency GUC
with associated machinery.

Mithun Cy sent in two revisions of a patch to add auto_prewarm.

Peter Eisentraut sent in a patch to save redundant code for pseudotype I/O
functions.

Ashutosh Bapat sent in a patch to free unused paths in the case of
partition-wise join for join between (declaratively) partitioned tables.

Amit Langote sent in another revision of a patch to implement declarative
partitioning for tables.

Julien Rouhaud sent in a patch to remove pq_putmessage_hook and pq_flush_hook.

Magnus Hagander and Michaël Paquier traded patches to fix an issue where
symlinks in the wrong place could cause streaming backups to fail.

Tomas Vondra sent in another revision of a patch to implement multivariate
statistics.

Michaël Paquier sent in another revision of a patch to mention column name in
error messages.

Kevin Grittner sent in another revision of a patch to construct and enable the
use of delta relations in AFTER triggers.

Masahiko Sawada sent in another revision of a patch to support 2PC across FDWs.

Kyotaro HORIGUCHI sent in another revision of a patch to refactor tab completion
in psql to use a more principled approach to grammar.