== PostgreSQL Weekly News - September 25 2016 ==

== PostgreSQL Weekly News - September 25 2016 ==

== PostgreSQL Product News ==

postgresql-unit 1.0, an extension which implements a PostgreSQL datatype for SI
units and bytes, released.
https://github.com/ChristophBerg/postgresql-unit

== PostgreSQL Jobs for September ==

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

== PostgreSQL Local ==

Postgres Vision 2016 will be held October 11-13, 2016, in San Francisco.
http://postgresvision.com/

The Korean Users Group will hold its first PGDay event on October 15, 2016 in Seoul.
http://pgday.postgresql.kr/

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. Submission deadline
is September 21, 2016 by midnight CST. Details and submission form at:
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 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 ==

Robert Haas pushed:

- Update recovery_min_apply_delay docs for remote_apply mode. Bernd Helmle,
reviewed by Thomas Munro, tweaked by me.
http://git.postgresql.org/pg/commitdiff/2c8f0d6e53e5dbcf28ee127303b81a6e12942665

- MSVC: Include pg_recvlogical in client-only install. MauMau, reviewed by
Michael Paquier
http://git.postgresql.org/pg/commitdiff/8614b39bca8fc64b1128c7911424647c9a1d1da1

- Retry DSM control segment creation if Windows indicates access denied.
Otherwise, attempts to run multiple postmasters running on the same machine
may fail, because Windows sometimes returns ERROR_ACCESS_DENIED rather than
ERROR_ALREADY_EXISTS when there is an existing segment. Hitting this bug is
much more likely because of another defect not fixed by this patch, namely
that dsm_postmaster_startup() uses random() which returns the same value every
time. But that's not a reason not to fix this. Kyotaro Horiguchi and Amit
Kapila, reviewed by Michael Paquier Discussion:
<CAA4eK1JyNdMeF-dgrpHozDecpDfsRZUtpCi+1AbtuEkfG3YooQ(at)mail(dot)gmail(dot)com>
http://git.postgresql.org/pg/commitdiff/419113dfdc4c729f6c763cc30a9b02ee68a7da94

- Use PostmasterRandom(), not random(), for DSM control segment ID. Otherwise,
every startup gets the same "random" value, which is definitely not what was
intended.
http://git.postgresql.org/pg/commitdiff/470d886c32efafa1b068b5ca48afafc2198c68d4

- Add more parallel query documentation. Previously, the individual settings
were documented, but there was no overall discussion of the capabilities and
limitations of the feature. Add that. Patch by me, reviewed by Peter
Eisentraut and Álvaro Herrera.
http://git.postgresql.org/pg/commitdiff/60270e5e00850ee8cc34296e38d0000415c8b152

Heikki Linnakangas pushed:

- Remove obsolete warning from docs. Python 2.4 and Fedora 4 are both obsolete
at this point, especially unpatched debug builds. Discussion:
<85e377b2-d459-396e-59b1-115548bbc059(at)iki(dot)fi>
http://git.postgresql.org/pg/commitdiff/6cc54f38a9fe1f4103c45a9858804d1d5d4de0fd

- Fix latency calculation when there are \sleep commands in the script. We
can't use txn_scheduled to hold the sleep-until time for \sleep, because that
interferes with calculation of the latency of the transaction as whole.
Backpatch to 9.4, where this bug was introduced. Fabien COELHO Discussion:
<alpine(dot)DEB(dot)2(dot)20(dot)1608231622170(dot)7102(at)lancre>
http://git.postgresql.org/pg/commitdiff/40c3fe4980e73acb0db75a3c737a4a52e09d4cf4

- Fix outdated comments, GIST search queue is not an RBTree anymore. The GiST
search queue is implemented as a pairing heap rather than as Red-Black Tree,
since 9.5 (commit e7032610). I neglected these comments in that commit.
http://git.postgresql.org/pg/commitdiff/45310221a9afccd98e78813459472370ade9dc4c

- Fix pgbench's calculation of average latency, when -T is not used. If the
test duration was given in # of transactions (-t or no option), rather as a
duration (-T), the latency average was always printed as 0. It has been
broken ever since the display of latency average was added, in 9.4. Fabien
Coelho Discussion: <alpine(dot)DEB(dot)2(dot)20(dot)1607131015370(dot)7486(at)sto>
http://git.postgresql.org/pg/commitdiff/65c65563842cc99fb1c349211581a62dc728eee2

- Print test parameters like "foo: 123", and results like "foo = 123". The way
"latency average" was printed was differently if it was calculated from the
overall run time or was measured on a per-transaction basis. Also, the
per-script weight is a test parameter, rather than a result, so use the
"weight: %f" style for that. Backpatch to 9.6, since the inconsistency on
"latency average" was introduced there. Fabien Coelho Discussion:
<alpine(dot)DEB(dot)2(dot)20(dot)1607131015370(dot)7486(at)sto>
http://git.postgresql.org/pg/commitdiff/2a7f4f76434d82eb0d1b5f4f7051043e1dd3ee1a

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

- Improve error message on MSVC if perl*.lib is not found. John Harvey,
reviewed by Michael Paquier Discussion:
<CABcP5fjEjgOsh097cWnQrsK9yCswo4DZxp-V47DKCH-MxY9Gig(at)mail(dot)gmail(dot)com>
http://git.postgresql.org/pg/commitdiff/3c2d5d6600de388729c7d613962d524352b7a67f

Peter Eisentraut pushed:

- doc: Correct ALTER USER MAPPING example. The existing example threw an error.
From: gabrielle <gorthx(at)gmail(dot)com>
http://git.postgresql.org/pg/commitdiff/4f6494cfd26c1dfe708c4598c11eea5fce168fd1

- doc: Fix documentation to match actual make output. based on patch from
Takeshi Ideriha <iderihatakeshi(at)gmail(dot)com>
http://git.postgresql.org/pg/commitdiff/16d1adb35cf887325b7c5dbf473632d557065171

- pg_restore: Add -N option to exclude schemas. This is similar to the -N
option in pg_dump, except that it doesn't take a pattern, just like the
existing -n option in pg_restore. From: Michael Banck
<michael(dot)banck(at)credativ(dot)de>
http://git.postgresql.org/pg/commitdiff/46b55e7f853dc0ef60ae3b1042b883fa4ffac95f

- Re-add translation markers that were lost. When win32security.c was moved
from src/backend/port/win32/security.c, the message writing function was
changed from write_stderr to log_error, but nls.mk was not updated. We could
add log_error to GETTEXT_TRIGGERS, but it's also used in src/common/exec.c in
a different way and that would create some confusion or a larger patch. For
now, just put an explicit translation marker onto the strings that were
previously translated.
http://git.postgresql.org/pg/commitdiff/90c9648212f7c3f0838fdaafe006ed40556a1364

- Fix typo. From: Michael Paquier <michael(dot)paquier(at)gmail(dot)com>
http://git.postgresql.org/pg/commitdiff/c91b34bab19bdc4fca4de37cd37c8a4e79dae4d0

- pg_ctl: Add tests for promote action. Reviewed-by: Michael Paquier
<michael(dot)paquier(at)gmail(dot)com>
http://git.postgresql.org/pg/commitdiff/eb5089a05ba0852cc3eafea53c5d23e7633fca81

- Delay updating control file to "in production". Move the updating of the
control file to "in production" status until the point where WAL writes are
allowed. Before, there could be a significant gap between the control file
update and write transactions actually being allowed. This makes it more
reliable to use the control status to verify the end of a promotion. From:
Michael Paquier <michael(dot)paquier(at)gmail(dot)com>
http://git.postgresql.org/pg/commitdiff/ebdf5bf7d1c97a926e2b0cb6523344c2643623c7

- pg_ctl: Add wait option to promote action When waiting is selected for the
promote action, look into pg_control until the state changes, then use the
PQping-based waiting until the server is reachable. Reviewed-by: Michael
Paquier <michael(dot)paquier(at)gmail(dot)com>
http://git.postgresql.org/pg/commitdiff/e7010ce4794a4c12a6a8bfb0ca1de49b61046847

- Make command_like output more compact. Consistently print the test name, not
the full command, which can be quite lenghty and include temporary directory
names and other distracting details. Reviewed-by: Michael Paquier
<michael(dot)paquier(at)gmail(dot)com>
http://git.postgresql.org/pg/commitdiff/e767db2242079b4ec2a7bbd18da7649d9ee1d696

- Add tests for various connection string issues. Add tests for consistent
support of connection strings in frontend programs as well as proper handling
of unusual characters in database and user names. These tests were developed
for the issues of CVE-2016-5424. To allow testing of names with spaces,
change the pg_regress command-line options --create-role and --dbname to split
their arguments by comma only, not space or comma as before. Only commas were
actually used in existing uses. Noah Misch, Michael Paquier, Peter Eisentraut
http://git.postgresql.org/pg/commitdiff/8b845520fb0aa50fea7aae44a45cee1b6d87845d

- pg_ctl: Add promote wait option to help output. pointed out by Masahiko
Sawada <sawada(dot)mshk(at)gmail(dot)com>
http://git.postgresql.org/pg/commitdiff/6fa51c79c7a645248a63205ce17af98ed404790d

- pg_ctl: Detect current standby state from pg_control. pg_ctl used to
determine whether a server was in standby mode by looking for a recovery.conf
file. With this change, it instead looks into pg_control, which is
potentially more accurate. There are also occasional discussions about
removing recovery.conf, so this removes one dependency. Reviewed-by: Michael
Paquier <michael(dot)paquier(at)gmail(dot)com>
http://git.postgresql.org/pg/commitdiff/c1dc51d4844e2a37412b034c07c1c5a439ba0b9d

Tom Lane pushed:

- Be sure to rewind the tuplestore read pointer in non-leader CTEScan nodes.
ExecInitCteScan supposed that it didn't have to do anything to the extra
tuplestore read pointer it gets from tuplestore_alloc_read_pointer. However,
it needs this read pointer to be positioned at the start of the tuplestore,
while tuplestore_alloc_read_pointer is actually defined as cloning the current
position of read pointer 0. In normal situations that accidentally works
because we initialize the whole plan tree at once, before anything gets read.
But it fails in an EvalPlanQual recheck, as illustrated in bug #14328 from
Dima Pavlov. To fix, just forcibly rewind the pointer after
tuplestore_alloc_read_pointer. The cost of doing so is negligible unless the
tuplestore is already in TSS_READFILE state, which wouldn't happen in normal
cases. We could consider altering tuplestore's API to make that case cheaper,
but that would make for a more invasive back-patch and it doesn't seem worth
it. This has been broken probably for as long as we've had CTEs, so
back-patch to all supported branches. Discussion:
<32468(dot)1474548308(at)sss(dot)pgh(dot)pa(dot)us>
http://git.postgresql.org/pg/commitdiff/96dd77d349424f270d129f8f40e75f762ddcca7d

- Remove nearly-unused SizeOfIptrData macro. Past refactorings have removed all
but one reference to SizeOfIptrData (and that one place was in a pretty
noncritical spot). Since nobody's complained, it seems probable that there
are no supported compilers that don't think sizeof(ItemPointerData) is 6. If
there are, we're wasting MAXALIGN per heap tuple anyway, so it's rather silly
to worry about whether we can shave space in places like WAL records. Pavan
Deolasee Discussion:
<CABOikdOOawDda4hwLOT6zdA6MFfPLu3Z2YBZkX0JdayNS6JOeQ(at)mail(dot)gmail(dot)com>
http://git.postgresql.org/pg/commitdiff/8023b5827fbada6815ce269db4f3373ac77ec7c3

- Avoid using PostmasterRandom() for DSM control segment ID. Commits 470d886c3
et al intended to fix the problem that the postmaster selected the same
"random" DSM control segment ID on every start. But using PostmasterRandom()
for that destroys the intended property that the delay between
random_start_time and random_stop_time will be unpredictable. (Said delay is
probably already more predictable than we could wish, but that doesn't mean
that reducing it by a couple orders of magnitude is OK.) Revert the previous
patch and add a comment warning against misuse of PostmasterRandom. Fix the
original problem by calling srandom() early in PostmasterMain, using a
low-security seed that will later be overwritten by PostmasterRandom.
Discussion: <20789(dot)1474390434(at)sss(dot)pgh(dot)pa(dot)us>
http://git.postgresql.org/pg/commitdiff/49a91b88e6c4afb840745c78942dd99ce125a6d6

- Don't trust CreateFileMapping() to clear the error code on success. We must
test GetLastError() even when CreateFileMapping() returns a non-null handle.
If that value were left over from some previous system call, we might be
fooled into thinking the segment already existed. Experimentation on Windows
7 suggests that CreateFileMapping() clears the error code on success, but it
is not documented to do so, so let's not rely on that happening in all Windows
releases. Amit Kapila Discussion: <20811(dot)1474390987(at)sss(dot)pgh(dot)pa(dot)us>
http://git.postgresql.org/pg/commitdiff/8e6b4ee21f486e6800aaa6955ff3d98e1a521b49

- Remove useless code. Apparent copy-and-pasteo in standby_desc_invalidations()
had two entries for msg->id == SHAREDINVALRELMAP_ID. Aleksander Alekseev
Discussion: <20160923090814(dot)GB1238(at)e733>
http://git.postgresql.org/pg/commitdiff/959ea7fa76120449efa5d85ae351abd1d6e5480c

- Fix incorrect logic for excluding range constructor functions in pg_dump.
Faulty AND/OR nesting in the WHERE clause of getFuncs' SQL query led to
dumping range constructor functions if they are part of an extension and we're
in binary-upgrade mode. Actually, we don't want to dump them separately even
then, since CREATE TYPE AS RANGE will create the range's constructor functions
regardless. Per report from Andrew Dunstan. It looks like this mistake was
introduced by me, in commit b985d4877, in perhaps-overzealous refactoring to
reduce code duplication. I'm suitably embarrassed. Report:
<34854939-02d7-f591-5677-ce2994104599(at)dunslane(dot)net>
http://git.postgresql.org/pg/commitdiff/12f6eadffd075e39570a0170f6791de0b96ddfde

- Doc: fix examples of # operators so they actually work. These worked as-is
until around 7.0, but fail in newer versions because there are more operators
named "#". Besides it's a bit inconsistent that only two of the examples on
this page lack type names on their constants. Report:
<20160923081530(dot)1517(dot)75670(at)wrigleys(dot)postgresql(dot)org>
http://git.postgresql.org/pg/commitdiff/5a7bae0699657315b487896810a30bd7425f6a08

- Install TAP test infrastructure so it's available for extension testing. When
configured with --enable-tap-tests, "make install" will now install the Perl
support files for TAP testing where PGXS will find them. This allows
extensions to rely on $(prove_check) even when being built out-of-tree.
Back-patch to 9.4 where we first started to support TAP testing, to reduce the
number of cases extension makefiles need to consider. Craig Ringer
Discussion:
<CAMsr+YFXv+2qne6xJW7z_25mYBtktRX5rpkrgrb+DRgQ_FxgHQ(at)mail(dot)gmail(dot)com>
http://git.postgresql.org/pg/commitdiff/c3a0818460a8f4a5e1a9109b035ac30e84b7e06f

- Do a final round of updates on the 9.6 release notes. Set release date,
document a few recent commits, do one last pass of copy-editing.
http://git.postgresql.org/pg/commitdiff/98c2d3332b30006ff71add99bc9d619c9457e71f

Bruce Momjian pushed:

- C comment: fix function header comment. Fix for transformOnConflictClause().
Author: Tomonari Katsumata
http://git.postgresql.org/pg/commitdiff/1ff0042165cf835d1b6a2ab2c3b84c201eb6577b

== Pending Patches ==

David Fetter sent in another revision of a patch to add a contrib hook and
associated GUCs for disallowing UPDATE and/or DELETE when no WHERE clause is
present.

Vladimir Gordiychuk and Craig Ringer traded patches to permit pausing
replication.

Thomas Munro sent in a patch to check the return code for postmaster death.

Takayuki Tsunakawa sent in a patch to make large shared_buffers settings
effective on Windows.

Ashutosh Bapat sent in a patch to cache the result of code in
add_paths_to_joinrel() which computes the set of target relations that should
overlap parameterization of any proposed join path rather than re-computing it
each time it's used in a query.

Daniel Vérité sent in another revision of a patch to enable psql variable hooks
to return a boolean indicating whether a change is allowed.

Mithun Cy sent in another revision of a patch to add some tests to cover hash
indexes.

Craig Ringer and Robert Haas traded patches to add a txid_status(bigint) to get
status of an xact.

Jesper Pedersen sent in four more revisions of a patch to add hash index support
to pageinspect.

Heikki Linnakangas and Fabien COELHO traded patches to refactor the pgbench meta
commands into a state machine.

Mark Dilger sent in a patch to fix a place that shouldn't have cast away const.

Michaël Paquier sent in a patch to add suitable OOM warnings to
palloc_extended() and pg_malloc_internal().

Haribabu Kommi sent in another revision of a patch to enable tracking the number
of commands executed grouped by command tag.

Ashutosh Bapat sent in two more revisions of a patch to enable optimizations of
partition-wise join for join between (declaratively) partitioned tables.

Michaël Paquier sent in three more revisions of a patch to track wait events for
latches.

Amit Kapila sent in another revision of a patch to implement concurrent hash
indexes.

David Cramer and Pavel Stěhule traded patches to add PL/PythonU support for
multidimensional arrays.

Pavel Stěhule and Craig Ringer traded patches to add xmltable().

Anastasia Lubennikova sent in another revision of a patch to add covering &
unique indexes.

Heikki Linnakangas sent in a patch to refactor the LogicalTapeSet/LogicalTape
interface.

Ashutosh Bapat sent in a patch to refactor index creation so it plays better
with declaratively partitioned tables.

Julian Markwort sent in a patch to allow specifying a 'custom' pgpassfile within
the connection string along the lines of the existing parameters sslkey and
sslcert.

Michaël Paquier sent in another revision of a patch to move sync routines of
initdb into src/common, issue fsync more carefully in pg_receivexlog and
pg_basebackup [-X] stream, add --nosync option to pg_basebackup, and switch
pg_basebackup commands in Postgres.pm to use --no-sync.

Peter Eisentraut sent in another revision of a patch to change pg_dump by
separate table and sequence data object types and fixing pg_upgrade to use this
facility from pg_dump.

David Steele sent in another revision of a patch to exclude additional
directories in pg_basebackup.

Robert Haas sent in a WIP patch to enable asynchronous execution.

Amit Kapila sent in another revision of a patch to do SetLastError(0) before
calling CreateFileMapping.

Thomas Munro sent in a patch to add psql tab completions for LOCK TABLE ... IN
ACCESS|ROW|SHARE.

Thomas Munro sent in a patch to refactor StartupXLOG.

Masahiko Sawada sent in another revision of a patch to add quorum commit.

Amit Kapila sent in another revision of a patch to add WAL for hash indexes.