== PostgreSQL Weekly News - November 27 2016 ==

== PostgreSQL Weekly News - November 27 2016 ==

== PostgreSQL Product News ==

Ora2Pg 17.6, a tool for migrating Oracle databases to PostgreSQL, released.
http://ora2pg.darold.net/

pgpool-II 3.6.0 released.
http://pgpool.net/mediawiki/index.php/Downloads

pitrery 1.12, a set of Bash scripts to manage PITR backups for PostgreSQL, released.
https://github.com/dalibo/pitrery

== PostgreSQL Jobs for November ==

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

== PostgreSQL Local ==

PGConf.ASIA 2016 will take place on December 1-3, 2016 in Akihabara, Tokyo,
Japan. Registration is open. There will be an UnConference on December 1 with
a separate registration required.
http://www.pgconf.asia/EN/registration/
http://www.pgconf.asia/EN/day-0/

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

PGConf India 2017 will be on March 2-3, 2017 in Bengaluru, Karnataka.
Proposals are due via <papers AT pgconf DOT in> by 31st December, 2016.
http://pgconf.in/

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

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

- Fix test for subplans in force-parallel mode. We mustn't force parallel mode
if the query has any subplans, since ExecSerializePlan doesn't transmit them
to workers. Testing top_plan->initPlan is inadequate because (1) there might
be initPlans attached to lower plan nodes, and (2) non-initPlan subplans don't
work either. There's certainly room for improvement in those restrictions,
but for the moment that's what we've got. Amit Kapila, per report from
Andreas Seltenreich Discussion: <8737im6pmh(dot)fsf(at)credativ(dot)de>
http://git.postgresql.org/pg/commitdiff/f24cf960d7ae3503e21fcb59dca652575619d9d4

- Fix optimization for skipping searches for parallel-query hazards. Fix thinko
in commit da1c91631: even if the original query was free of parallel hazards,
we might introduce such a hazard by adding PARAM_EXEC Param nodes. Adjust
is_parallel_safe() so that it will scan the given expression whenever any such
nodes have been created. Per report from Andreas Seltenreich. Discussion:
<878tse6yvf(dot)fsf(at)credativ(dot)de>
http://git.postgresql.org/pg/commitdiff/4324ade9a6880113b08070305482ace2e8a2617c

- Fix PGLC_localeconv() to handle errors better. The code was intentionally not
very careful about leaking strdup'd strings in case of an error. That was
forgivable probably, but it also failed to notice strdup() failures, which
could lead to subsequent null-pointer-dereference crashes, since many callers
unsurprisingly didn't check for null pointers in the struct lconv fields. An
even worse problem is that it could throw error while we were setlocale'd to a
non-C locale, causing unwanted behavior in subsequent libc calls. Rewrite to
ensure that we cannot throw elog(ERROR) until after we've restored the
previous locale settings, or at least attempted to. (I'm sorely tempted to
make restore failure be a FATAL error, but will refrain for the moment.)
Having done that, it's not much more work to ensure that we clean up strdup'd
storage on the way out, too. This code is substantially the same in all
supported branches, so back-patch all the way. Michael Paquier and Tom Lane
Discussion:
<CAB7nPqRMbGqa_mesopcn4MPyTs34eqtVEK7ELYxvvV=oqS00YA(at)mail(dot)gmail(dot)com>
http://git.postgresql.org/pg/commitdiff/a4930e7ca2aa49c13ccba089f5fd2c416d87e96a

- Fix uninitialized variable. Oversight in a734fd5d1. Michael Paquier
http://git.postgresql.org/pg/commitdiff/ae92a9a3806c025653140ee6316a82d55e24b82c

- Make contrib/test_decoding regression tests safe for CZ locale. A little
COLLATE "C" goes a long way. Pavel Stehule, per suggestion from Craig Ringer
Discussion:
<CAFj8pRA8nJZcozgxN=RMSqMmKuHVOkcGAAKPKdFeiMWGDSUDLA(at)mail(dot)gmail(dot)com>
http://git.postgresql.org/pg/commitdiff/e2a0ee69006bf31f24958b72c93455412cba035c

- Doc: add a section in Part II concerning RETURNING. There are assorted
references to RETURNING in Part II, but nothing that would qualify as an
explanation of the feature, which seems like an oversight considering how
useful it is. Add something. Noted while looking for a place to point a
cross-reference to ...
http://git.postgresql.org/pg/commitdiff/1c7861e81b4220364bef75d2445e9c0619f3f3f8

- Improve handling of "UPDATE ... SET (column_list) = row_constructor".
Previously, the right-hand side of a multiple-column assignment, if it wasn't
a sub-SELECT, had to be a simple parenthesized expression list, because gram.y
was responsible for "bursting" the construct into independent column
assignments. This had the minor defect that you couldn't write ROW (though
you should be able to, since the standard says this is a row constructor), and
the rather larger defect that unlike other uses of row constructors, we would
not expand a "foo.*" item into multiple columns. Fix that by changing the RHS
to be just "a_expr" in the grammar, leaving it to transformMultiAssignRef to
separate the elements of a RowExpr; which it will do only after performing
standard transformation of the RowExpr, so that "foo.*" behaves as expected.
The key reason we didn't do that before was the hard-wired handling of DEFAULT
tokens (SetToDefault nodes). This patch deals with that issue by allowing
DEFAULT in any a_expr and having parse analysis throw an error if SetToDefault
is found in an unexpected place. That's an improvement anyway since the error
can be more specific than just "syntax error". The SQL standard suggests that
the RHS could be any a_expr yielding a suitable row value. This patch doesn't
really move the goal posts in that respect --- you're still limited to RowExpr
or a sub-SELECT --- but it does fix the grammar restriction, so it provides
some tangible progress towards a full implementation. And the limitation is
now documented by an explicit error message rather than an unhelpful "syntax
error". Discussion: <8542(dot)1479742008(at)sss(dot)pgh(dot)pa(dot)us>
http://git.postgresql.org/pg/commitdiff/906bfcad7ba7cb3863fe0e2a7810be8e3cd84fbd

- Doc: improve documentation about composite-value usage. Create a section
specifically for the syntactic rules around whole-row variable usage, such as
expansion of "foo.*". This was previously documented only haphazardly, with
some critical info buried in unexpected places like
xfunc-sql-composite-functions. Per repeated questions in different mailing
lists. Discussion: <16288(dot)1479610770(at)sss(dot)pgh(dot)pa(dot)us>
http://git.postgresql.org/pg/commitdiff/e1320266edd7df53c60af10b4c33ab2754278b3e

- Make sure ALTER TABLE preserves index tablespaces. When rebuilding an
existing index, ALTER TABLE correctly kept the physical file in the same
tablespace, but it messed up the pg_class entry if the index had been in the
database's default tablespace and "default_tablespace" was set to some
non-default tablespace. This led to an inaccessible index. Fix by fixing
pg_get_indexdef_string() to always include a tablespace clause, whether or not
the index is in the default tablespace. The previous behavior was installed
in commit 537e92e41, and I think it just wasn't thought through very clearly;
certainly the possible effect of default_tablespace wasn't considered.
There's some risk in changing the behavior of this function, but there are no
other call sites in the core code. Even if it's being used by some third
party extension, it's fairly hard to envision a usage that is okay with a
tablespace clause being appended some of the time but can't handle it being
appended all the time. Back-patch to all supported versions. Code fix by me,
investigation and test cases by Michael Paquier. Discussion:
<1479294998857-5930602(dot)post(at)n3(dot)nabble(dot)com>
http://git.postgresql.org/pg/commitdiff/bd673e8e864a1987eca8d40c593e857ab8d0a5ba

- Avoid masking a function parameter name with a local variable name. No actual
bug here, but it might confuse readers, so change the name of the local
variable. Ashutosh Bapat
http://git.postgresql.org/pg/commitdiff/6fa391be4e83139cd134d5ccfc1499809bb8c98c

- Check that default_tablespace affects ALTER TABLE ADD UNIQUE/PRIMARY KEY.
Seems like a good thing to test, considering that we nearly broke it
yesterday. Michael Paquier
http://git.postgresql.org/pg/commitdiff/4cc6a3f1106034187be3a371e61a02915bb93c11

- Check for pending trigger events on far end when dropping an FK constraint.
When dropping a foreign key constraint with ALTER TABLE DROP CONSTRAINT, we
refuse the drop if there are any pending trigger events on the named table;
this ensures that we won't remove the pg_trigger row that will be consulted by
those events. But we should make the same check for the referenced relation,
else we might remove a due-to-be-referenced pg_trigger row for that relation
too, resulting in "could not find trigger NNN" or "relation NNN has no
triggers" errors at commit. Per bug #14431 from Benjie Gillam. Back-patch to
all supported branches. Report:
<20161124114911(dot)6530(dot)31200(at)wrigleys(dot)postgresql(dot)org>
http://git.postgresql.org/pg/commitdiff/4e026b32d4024b03856b4981b26c747b7fef7afb

- Mark a query's topmost Paths parallel-unsafe if they will have initPlans.
Andreas Seltenreich found another case where we were being too optimistic
about allowing a plan to be considered parallelizable despite it containing
initPlans. It seems like the real issue here is that if we know we are going
to tack initPlans onto the topmost Plan node for a subquery, we had better
mark that subquery's result Paths as not-parallel-safe. That fixes this
problem and allows reversion of a kluge (added in commit 7b67a0a49 and
extended in f24cf960d) to not trust the parallel_safe flag at top level.
Discussion: <874m2w4k5d(dot)fsf(at)ex(dot)ansel(dot)ydns(dot)eu>
http://git.postgresql.org/pg/commitdiff/ab77a5a4561fad847af4a101a29c922c66449870

- Bring some clarity to the defaults for the xxx_flush_after parameters.
Instead of confusingly stating platform-dependent defaults for these
parameters in the comments in postgresql.conf.sample (with the main entry
being a lie on Linux), teach initdb to install the correct platform-dependent
value in postgresql.conf, similarly to the way we handle other
platform-dependent defaults. This won't do anything for existing 9.6
installations, but since it's effectively only a documentation improvement,
that seems OK. Since this requires initdb to have access to the default
values, move the #define's for those to pg_config_manual.h; the original
placement in bufmgr.h is unworkable because that file can't be included by
frontend programs. Adjust the default value for wal_writer_flush_after so
that it is 1MB regardless of XLOG_BLCKSZ, conforming to what is stated in both
the SGML docs and postgresql.conf. (We could alternatively make it scale with
XLOG_BLCKSZ, but I'm not sure I see the point.) Copy-edit related SGML
documentation. Fabien Coelho and Tom Lane, per a gripe from Tomas Vondra.
Discussion: <30ebc6e3-8358-09cf-44a8-578252938424(at)2ndquadrant(dot)com>
http://git.postgresql.org/pg/commitdiff/dbdfd114f34443f1e4ad16ce2721f9817d3b3d80

- Fix test about ignoring extension dependencies during extension scripts.
Commit 08dd23cec introduced an exception to the rule that extension member
objects can only be dropped as part of dropping the whole extension, intending
to allow such drops while running the extension's own creation or update
scripts. However, the exception was only applied at the outermost recursion
level, because it was modeled on a pre-existing check to ignore dependencies
on objects listed in pendingObjects. Bug #14434 from Philippe Beaudoin shows
that this is inadequate: in some cases we can reach an extension member object
by recursion from another one. (The bug concerns the serial-sequence case;
I'm not sure if there are other cases, but there might well be.) To fix,
revert 08dd23cec's changes to findDependentObjects() and instead apply the
creating_extension exception regardless of stack level. Having seen this
example, I'm a bit suspicious that the pendingObjects logic is also wrong and
such cases should likewise be allowed at any recursion level. However,
changing that would interact in subtle ways with the recursion logic (at least
it would need to be moved to after the recursing-from check). Given that the
code's been like that a long time, I'll refrain from touching it without a
clear example showing it's wrong. Back-patch to all active branches. In HEAD
and 9.6, where suitable test infrastructure exists, add a regression test case
based on the bug report. Report:
<20161125151448(dot)6529(dot)33039(at)wrigleys(dot)postgresql(dot)org> Discussion:
<13224(dot)1480177514(at)sss(dot)pgh(dot)pa(dot)us>
http://git.postgresql.org/pg/commitdiff/182db070403fb33566da156a3a77cbcda16583b4

Robert Haas pushed:

- autovacuum: Drop orphan temp tables more quickly but with more caution.
Previously, we only dropped an orphan temp table when it became old enough to
threaten wraparound; instead, doing it immediately. The only value of waiting
is that someone might be able to examine the contents of the orphan temp table
for forensic purposes, but it's pretty difficult to actually do that and few
users will wish to do so. On the flip side, not performing the drop
immediately generates log spam and bloats pg_class. In addition, per a report
from Grigory Smolkin, if a temporary schema contains a very large number of
temporary tables, a backend attempting to clear the temporary schema might
fail due to lock table exhaustion. It's helpful for autovacuum to clean up
after such cases, and we don't want it to wait for wraparound to threaten
before doing so. To prevent autovacuum from failing in the same manner as a
backend trying to drop an entire temp schema, remove orphan temp tables in
batches of 50, committing after each batch, so that we don't accumulate an
unbounded number of locks. If a drop fails, retry other orphan tables that
need to be dropped up to 10 times before giving up. With this system, if a
backend does fail to clean a temporary schema due to lock table exhaustion,
autovacuum should hopefully put things right the next time it processes the
database. Discussion:
CAB7nPqSbYT6dRwsXVgiKmBdL_ARemfDZMPA+RPeC_ge0GK70hA(at)mail(dot)gmail(dot)com Michael
Paquier, with a bunch of comment changes by me.
http://git.postgresql.org/pg/commitdiff/a734fd5d1c309cc553b7c8c79fba96218af090f7

- Support condition variables. Condition variables provide a flexible way to
sleep until a cooperating process causes an arbitrary condition to become
true. In simple cases, this can be accomplished with a WaitLatch/ResetLatch
loop; the cooperating process can call SetLatch after performing work that
might cause the condition to be satisfied, and the waiting process can recheck
the condition each time. However, if the process performing the work doesn't
have an easy way to identify which processes might be waiting, this doesn't
work, because it can't identify which latches to set. Condition variables
solve that problem by internally maintaining a list of waiters; a process that
may have caused some waiter's condition to be satisfied must "signal" or
"broadcast" on the condition variable. Robert Haas and Thomas Munro
http://git.postgresql.org/pg/commitdiff/e8ac886c24776295dd9b025386a821061da8e4d1

- Code review for commit 274bb2b3857cc987cfa21d14775cae9b0dababa5. Avoid memory
leak in conninfo_uri_parse_options. Use the current host rather than the
comma-separated list of host names when the host name is needed for GSS, SSPI,
or SSL authentication. Document the way connect_timeout interacts with
multiple host specifications. Takayuki Tsunakawa
http://git.postgresql.org/pg/commitdiff/9a1d0af4ad2cbd419115b453d811c141b80d872b

- Remove barrier.h. A new thing also called a "barrier" is proposed, but
whether we decide to take that patch or not, this file seems to have outlived
its usefulness. Thomas Munro
http://git.postgresql.org/pg/commitdiff/e343dfa42bff35983c582da3916b205763aeac90

- Mark IsPostmasterEnvironment and IsBackgroundWorker as PGDLLIMPORT. Per
request from Craig Ringer.
http://git.postgresql.org/pg/commitdiff/273270593f42bdbd5422923eb70fbd0fb0f65bf0

Álvaro Herrera pushed:

- Fix commit_ts for FrozenXid and BootstrapXid. Previously, requesting commit
timestamp for transactions FrozenTransactionId and BootstrapTransactionId
resulted in an error. But since those values can validly appear in committed
tuples' Xmin, this behavior is unhelpful and error prone: each caller would
have to special-case those values before requesting timestamp data for an Xid.
We already have a perfectly good interface for returning "the Xid you
requested is too old for us to have commit TS data for it", so let's use that
instead. Backpatch to 9.5, where commit timestamps appeared. Author: Craig
Ringer. Discussion:
https://www.postgresql.org/message-id/CAMsr+YFM5Q=+ry3mKvWEqRTxrB0iU3qUSRnS28nz6FJYtBwhJg@mail.gmail.com
http://git.postgresql.org/pg/commitdiff/4aaddf2f009821e29aea3735e44332ad9ca47aaa

Magnus Hagander pushed:

- Fix typo in comment. Thomas Munro
http://git.postgresql.org/pg/commitdiff/8afb811088c2a8bfa2b68bd24c05471b8ea4a8ce

- Mention server start requirement for ssl parameters. Fix that the
documentation for three ssl related parameters did not specify that they can
only be changed at server start. Michael Paquier
http://git.postgresql.org/pg/commitdiff/c6dbc7b65175f791b8e63a1970ee6b6f68ebec09

== Pending Patches ==

Craig Ringer sent in two revisions of a patch to document that test must pass
with Perl 5.8.0 and show how to ensure that they do.

Michaël Paquier sent in another revision of a patch to fix the checkpoint skip
logic on idle systems by tracking LSN progress.

Corey Huinker sent in a patch to ensure that dblink knows about valid PostgreSQL
FDW options.

Thomas Munro sent in another revision of a patch to add delta relations in AFTER
triggers, complete with exposure to PL/pgsql and PL/PythonU.

KaiGai Kohei sent in another revision of a patch to enable passing LIMIT to FDWs
AND CUSTOMSCANS.

Daniel Vérité sent in two more revisions of a patch to improve psql hooks for
variables.

Amit Kapila sent in a patch to prevent cascading standbys from getting stuck.

Etsuro Fujita sent in three more revisions of a patch to push down more full
joins to the PostgreSQL FDW.

Craig Ringer sent in a patch to add logical decoding on standbys.

Thomas Munro sent in another revision of a patch to measure replay lag.

Matheus de Oliveira sent in a patch to extend ALTER DEFAULT PRIVILEGES to
include schemas.

Thomas Munro sent in another revision of a patch to add barriers for
synchronizing cooperating processes.

Peter Geoghegan sent in another revision of a patch to add amcheck, a B-Tree
integrity checking tool.

Mithun Cy sent in two more revisions of a patch to implement failover on the
libpq connect level.

Michaël Paquier sent in another revision of a patch to forbid the use of LF and
CR characters in database and role names.

Amit Langote sent in two more revisions of a patch to add declarative
partitioning.

Erik Rijkers sent in a patch to fix the CREATE SUBSCRIPTION documents, which
broke the oldhtml target.

Andres Freund sent in a patch to fix a performance degradation in Bitmapscan.

Amul Sul sent in another revision of a patch to add a pg_background contrib
extension.

Amit Kapila sent in another revision of a patch to make hash indexes
concurrency-safe.

Álvaro Herrera and Pavel Stěhule traded patches to add xmltable().

Thomas Munro sent in two more revisions of a patch to add dynamic shared memory
areas (DSAs).

Thomas Munro sent in two more revisions of a patch to add a DSA for the parallel
execution.

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

Rushabh Lathia sent in another revision of a patch to implement Gather Merge.

Andreas Karlsson sent in a WIP patch to make PQHost() return the value of the
host parameter rather than the hostaddr.

Amit Kapila sent in two revisions of a patch to allow safe subquery with
parallel workers.

Haribabu Kommi sent in another revision of a patch to add an 8-byte MAC address
type called macaddr8 which conforms to EUI-64.

Pavel Stěhule sent in a PoC patch to add session server side variables.

Ming Li sent in a patch to fix a pg_class refcache leak when the meta tuple in
pg_class is invalid.

Amit Kapila sent in another revision of a patch to add parallel index scans.

Michaël Paquier sent in another revision of a patch to add quorum commit for
multiple synchronous replication.

Artur Zakirov sent in a patch to fix a bug in which caused a
pg_event_trigger_ddl_commands() error with ALTER TEXT SEARCH CONFIGURATION.