== PostgreSQL Weekly News - September 18 2016 ==

== PostgreSQL Weekly News - September 18 2016 ==

The 10th annual Prague PostgreSQL Developer Day 2017 (P2D2 2017) is a two-day
conference that will be held on February 15-16 2017 in Prague, Czech Republic.
Czech language web site below:
http://www.p2d2.cz/

The PGConf US 2017 CfP will be open until November 15th, 2016, 11:59PM EST.
Notifications December 2. Schedule published January 2, 2017.
http://www.pgconf.us/2017/submit/

== PostgreSQL Product News ==

brimbox 2.1, a web database interface and framework built atop PostgreSQL, released.
http://www.brimbox.com/release/brimbox-release-v2-1/

MJSQLView Version 7.08, a Java-based UI which supports PostgreSQL, released.
http://myjsqlview.com/

== PostgreSQL Jobs for September ==

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

== PostgreSQL Local ==

PostgreSQL Session will be held on September 22th, 2016, in Lyon,
France.
http://blog.dalibo.com/2016/05/13/CFP-pgsession-lyon.html

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

Kevin Grittner pushed:

- psql tab completion for CREATE DATABASE ... TEMPLATE ... Sehrope Sarkuni,
reviewed by Merlin Moncure & Vitaly Burovoy with some editing by me
http://git.postgresql.org/pg/commitdiff/52803098ab26051c0c9802f3c19edffc90de8843

- Fix recent commit for tab-completion of database template. The details of
commit 52803098ab26051c0c9802f3c19edffc90de8843 were based on a
misunderstanding of the role inheritance allowing use of a database for a
template. While the CREATEDB privilege is not inherited, the database
ownership is privileges are. Pointed out by Vitaly Burovoy and Tom Lane. Fix
provided by Tom Lane, reviewed by Vitaly Burovoy.
http://git.postgresql.org/pg/commitdiff/63c1a871940c7c4798788e98fdb1a24408a49d05

Simon Riggs pushed:

- Raise max setting of checkpoint_timeout to 1d. Previously checkpoint_timeout
was capped at 3600s New max setting is 86400s = 24h = 1d Discussion:
32558(dot)1454471895(at)sss(dot)pgh(dot)pa(dot)us
http://git.postgresql.org/pg/commitdiff/c3c0d7bd701dae4737c974a59ffa9b366110f9c1

- Identify walsenders in pg_stat_activity. Following 8299471c37fff0b walsender
procs are now visible in pg_stat_activity. Set query to ‘walsender’ for
walsender procs to allow them to be identified.
Discussion:CAB7nPqS8c76KPSufK_HSDeYrbtg+zZ7D0EEkjeM6txSEuCB_jA(at)mail(dot)gmail(dot)com
Michael Paquier, issue raised by Fujii Masao, reviewed by Tom Lane
http://git.postgresql.org/pg/commitdiff/fc3d4a44e9375f79709f470cb3c83d4ca28fb370

- Fix copy/pasto in file identification Daniel Gustafsson
http://git.postgresql.org/pg/commitdiff/4068eb9918cbbeaba8042fa6fe0c1f5382f2f05f

Peter Eisentraut pushed:

- pg_basebackup: Clean created directories on failure. Like initdb, clean up
created data and xlog directories, unless the new -n/--noclean option is
specified. Tablespace directories are not cleaned up, but a message is
written about that. Reviewed-by: Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com>
http://git.postgresql.org/pg/commitdiff/9083353b15c3cf8e7bbac104a81ad42281178cdf

- Add overflow checks to money type input function. The money type input
function did not have any overflow checks at all. There were some regression
tests that purported to check for overflow, but they actually checked for the
overflow behavior of the int8 type before casting to money. Remove those
unnecessary checks and add some that actually check the money input function.
Reviewed-by: Fabien COELHO <coelho(at)cri(dot)ensmp(dot)fr>
http://git.postgresql.org/pg/commitdiff/656df624c0d7b50e1714f2a3a14e143e63799a80

Tom Lane pushed:

- Docs: assorted minor cleanups. Standardize on "user_name" for a field name in
related examples in ddl.sgml; before we had variously "user_name", "username",
and "user". The last is flat wrong because it conflicts with a reserved word.
Be consistent about entry capitalization in a table in func.sgml. Fix a typo
in pgtrgm.sgml. Back-patch to 9.6 and 9.5 as relevant. Alexander Law
http://git.postgresql.org/pg/commitdiff/42fd984c0b7b53d1bc961c9ed6bb84fe28eae52b

- Fix executor/README to reflect disallowing SRFs in UPDATE. The parenthetical
comment here is obsoleted by commit a4c35ea1c. Noted by Andres Freund.
http://git.postgresql.org/pg/commitdiff/fdc79e1909dc3866a385ffb74bdd6ce6a082a300

- Improve parser's and planner's handling of set-returning functions. Teach the
parser to reject misplaced set-returning functions during parse analysis using
p_expr_kind, in much the same way as we do for aggregates and window functions
(cf commit eaccfded9). While this isn't complete (it misses nesting-based
restrictions), it's much better than the previous error reporting for such
cases, and it allows elimination of assorted ad-hoc expression_returns_set()
error checks. We could add nesting checks later if it seems important to
catch all cases at parse time. There is one case the parser will now throw
error for although previous versions allowed it, which is SRFs in the tlist of
an UPDATE. That never behaved sensibly (since it's ill-defined which
generated row should be used to perform the update) and it's hard to see why
it should not be treated as an error. It's a release-note-worthy change
though. Also, add a new Query field hasTargetSRFs reporting whether there are
any SRFs in the targetlist (including GROUP BY/ORDER BY expressions). The
parser can now set that basically for free during parse analysis, and we can
use it in a number of places to avoid expression_returns_set searches. (There
will be more such checks soon.) In some places, this allows decontorting the
logic since it's no longer expensive to check for SRFs in the tlist --- so I
made the checks parallel to the handling of hasAggs/hasWindowFuncs wherever it
seemed appropriate. catversion bump because adding a Query field changes
stored rules. Andres Freund and Tom Lane Discussion:
<24639(dot)1473782855(at)sss(dot)pgh(dot)pa(dot)us>
http://git.postgresql.org/pg/commitdiff/a4c35ea1c2f05dd5b56739fcd0dc36a4870ea0c0

- Be pickier about converting between Name and Datum. We were misapplying
NameGetDatum() to plain C strings in some places. This worked, because it was
just a pointer cast anyway, but it's a type cheat in some sense. Use
CStringGetDatum instead, and modify the NameGetDatum macro so it won't compile
if applied to something that's not a pointer to NameData. This should result
in no changes to generated code, but it is logically cleaner. Mark Dilger,
tweaked a bit by me Discussion:
<EFD8AC94-4C1F-40C1-A5EA-304080089C1B(at)gmail(dot)com>
http://git.postgresql.org/pg/commitdiff/55c3391d1e6a201b5b891781d21fe682a8c64fe6

- Tweak targetlist-SRF tests. Add a test case showing that we don't support
SRFs in window-function arguments. Remove a duplicate test case for SRFs in
aggregate arguments.
http://git.postgresql.org/pg/commitdiff/a163c006ca3e6026546ee0f6c487a0dcfc66f82b

- Tweak targetlist-SRF tests some more. Seems like it would be good to have a
test case documenting the existing behavior for non-top-level SRFs.
http://git.postgresql.org/pg/commitdiff/0dac5b5174bde3d6fb4b444a2aa4ca1f0091e258

- Make min_parallel_relation_size's default value platform-independent. The
documentation states that the default value is 8MB, but this was only true at
BLCKSZ = 8kB, because the default was hard-coded as 1024. Make the code match
the docs by computing the default as 8MB/BLCKSZ. Oversight in commit
75be66464, noted pursuant to a gripe from Peter E. Discussion:
<90634e20-097a-e4fd-67d5-fb2c42f0dd71(at)2ndquadrant(dot)com>
http://git.postgresql.org/pg/commitdiff/5472ed3e9bc23eff0b4e457fe564ac667cb69441

- Add debugging aid "bmsToString(Bitmapset *bms)". This function has no direct
callers at present, but it's convenient for manual use in a debugger, rather
than having to inspect memory and do bit-counting in your head. In passing,
get rid of useless outBitmapset() wrapper around _outBitmapset(); let's just
export the function that does the work. Likewise for outToken(). Ashutosh
Bapat, tweaked a bit by me Discussion:
<CAFjFpRdiht8e1HTVirbubr4YzaON5iZTzFJjq909y4sU8M_6eA(at)mail(dot)gmail(dot)com>
http://git.postgresql.org/pg/commitdiff/d8c61c9765339351409f06bbf964dcb8c1929e8b

Andres Freund pushed:

- Add more tests for targetlist SRFs. We're considering changing the
implementation of targetlist SRFs considerably, and a lot of the current
behaviour isn't tested in our regression tests. Thus it seems useful to
increase coverage to avoid accidental behaviour changes. It's quite possible
that some of the plans here will require adjustments to avoid falling afoul of
ordering differences (e.g. hashed group bys). The buildfarm will tell us.
Reviewed-By: Tom Lane Discussion:
<20160827214829(dot)zo2dfb5jaikii5nw(at)alap3(dot)anarazel(dot)de>
http://git.postgresql.org/pg/commitdiff/bfe16d1a5dec0d23c917c37de646256c71e07ee8

- Address portability issues in bfe16d1a5 test output.
http://git.postgresql.org/pg/commitdiff/9f478b4f19d8e26300ae19e42c26343f5791e32a

- Remove user_relns() SRF from regression tests. The output of the function
changes whenever previous (or, as in this case, concurrent) tests leave a
table in place. That causes unneeded churn. This should fix failures due to
the tests added bfe16d1a5, like on lapwing, caused by the tsrf test running
concurrently with misc. Those could also have been addressed by using temp
tables, but that test has annoyed me before. Discussion:
<27626(dot)1473729905(at)sss(dot)pgh(dot)pa(dot)us>
http://git.postgresql.org/pg/commitdiff/0dba54f1666ead71c54ce100b39efda67596d297

Robert Haas pushed:

- Have heapam.h include lockdefs.h rather than lock.h. lockdefs.h was only
split from lock.h relatively recently, and represents a minimal subset of the
old lock.h. heapam.h only needs that smaller subset, so adjust it to include
only that. This requires some corresponding adjustments elsewhere. Peter
Geoghegan
http://git.postgresql.org/pg/commitdiff/445a38aba26cb80a4506af2248e3b425f795a099

- Improve code comment for GatherPath's single_copy flag. Discussion:
5934(dot)1472642782(at)sss(dot)pgh(dot)pa(dot)us
http://git.postgresql.org/pg/commitdiff/6415ba502bdc540e21f122d4c6c87d4a35f8ec27

- pg_buffercache: Allow huge allocations. Otherwise, users who have configured
shared_buffers >= 256GB won't be able to use this module. There probably
aren't many of those, but it doesn't hurt anything to fix it so that it works.
Backpatch to 9.4, where MemoryContextAllocHuge was introduced. The same
problem exists in older branches, but there's no easy way to fix it there.
KaiGai Kohei
http://git.postgresql.org/pg/commitdiff/8a503526e455b981718c2d24bd1630fd5b4cf7ac

- Fix typo in comment. Amit Langote
http://git.postgresql.org/pg/commitdiff/ffccee473682ed18a27d667b7a4f45d802dd61c4

- Clarify policy on marking inherited constraints as valid. Amit Langote and
Robert Haas
http://git.postgresql.org/pg/commitdiff/5225c66336a1e4b46925e9f169086fc70f49736f

Heikki Linnakangas pushed:

- Fix and clarify comments on replacement selection. These were modified by the
patch to only use replacement selection for the first run in an external sort.
http://git.postgresql.org/pg/commitdiff/c99dd5bfed23d9787dcf7d00197c1ed42bcfdb02

- Support OpenSSL 1.1.0. Changes needed to build at all: - Check for SSL_new in
configure, now that SSL_library_init is a macro. - Do not access struct
members directly. This includes some new code in pgcrypto, to use the resource
owner mechanism to ensure that we don't leak OpenSSL handles, now that we
can't embed them in other structs anymore. - RAND_SSLeay() -> RAND_OpenSSL()
Changes that were needed to silence deprecation warnings, but were not
strictly necessary: - RAND_pseudo_bytes() -> RAND_bytes(). -
SSL_library_init() and OpenSSL_config() -> OPENSSL_init_ssl() -
ASN1_STRING_data() -> ASN1_STRING_get0_data() - DH_generate_parameters() ->
DH_generate_parameters() - Locking callbacks are not needed with OpenSSL 1.1.0
anymore. (Good riddance!) Also change references to SSLEAY_VERSION_NUMBER with
OPENSSL_VERSION_NUMBER, for the sake of consistency. OPENSSL_VERSION_NUMBER
has existed since time immemorial. Fix SSL test suite to work with OpenSSL
1.1.0. CA certificates must have the "CA:true" basic constraint extension now,
or OpenSSL will refuse them. Regenerate the test certificates with that. The
"openssl" binary, used to generate the certificates, is also now more picky,
and throws an error if an X509 extension is specified in "req_extensions", but
that section is empty. Backpatch to all supported branches, per popular
demand. In back-branches, we still support OpenSSL 0.9.7 and above. OpenSSL
0.9.6 should still work too, but I didn't test it. In master, we only support
0.9.8 and above. Patch by Andreas Karlsson, with additional changes by me.
Discussion: <20160627151604(dot)GD1051(at)msg(dot)df7cb(dot)de>
http://git.postgresql.org/pg/commitdiff/593d4e47db7af1a3a5dd6b6b1971f181b5566dbd

- Fix building with LibreSSL. LibreSSL defines OPENSSL_VERSION_NUMBER to claim
that it is version 2.0.0, but it doesn't have the functions added in OpenSSL
1.1.0. Add autoconf checks for the individual functions we need, and stop
relying on OPENSSL_VERSION_NUMBER. Backport to 9.5 and 9.6, like the patch
that broke this. In the back-branches, there are still a few
OPENSSL_VERSION_NUMBER checks left, to check for OpenSSL 0.9.8 or 0.9.7. I
left them as they were - LibreSSL has all those functions, so they work as
intended. Per buildfarm member curculio. Discussion:
<2442(dot)1473957669(at)sss(dot)pgh(dot)pa(dot)us>
http://git.postgresql.org/pg/commitdiff/5c6df67e0c961f68e73e7c1e6312211ed59da00a

- Fix ecpg -? option on Windows, add -V alias for --version. This makes the -?
and -V options work consistently with other binaries. --help and --version
are now only recognized as the first option, i.e. "ecpg --foobar --help" no
longer prints the help, but that's consistent with most of our other binaries,
too. Backpatch to all supported versions. Haribabu Kommi Discussion:
<CAJrrPGfnRXvmCzxq6Dy=stAWebfNHxiL+Y_z7uqksZUCkW_waQ(at)mail(dot)gmail(dot)com>
http://git.postgresql.org/pg/commitdiff/3fcc98c990ede7d3b415cc06eb6664aac6e7bbbc

== Pending Patches ==

Michaël Paquier sent in a patch to add a pgstat_report_activity() call to
walsender.

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

Dagfinn Ilmari Mannsåker sent in a patch to add psql tab completion for the
recently-added ALTER TYPE … RENAME VALUE.

Corey Huinker sent in two more revisions of a patch to let file_fdw access COPY
FROM PROGRAM.

Craig Ringer sent in two revisions of a patch to install the Perl TAP tests, add
install rules for the isolation tester, and note that src/test/Makefile is not
called from src/Makefile.

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

Etsuro Fujita sent in another revision of a patch to push more FULL JOINs to the
PostgreSQL FDW.

Heikki Linnakangas sent in two revisions of a patch to change the way
pre-reading in external sort's merge phase works.

Alexander Korotkov sent in another revision of a patch to implement partial
sorting.

Mithun Cy sent in another revision of a patch to cache hash index meta page.

Haribabu Kommi sent in a patch to replace most of the getimeofday function
calls, except timeofday(user callable) and GetCurrentTimestamp functions with
clock_gettime.

Andrew Borodin sent in two more revisions of a patch to implement penalty
functions for GiST in the cube contrib extension.

Michaël Paquier sent in a patch to make PostgresNode.pm the new --noclean option
in its call.

KaiGai Kohei sent in another revision of a patch to implement PassDownLimitBound
for ForeignScan/CustomScan.

Amit Kapila sent in two more revisions of a patch to implement WAL for hash
indexes.

Amit Kapila sent in two more revisions of a patch to implement concurrent hash
indexes.

Fabien COELHO sent in another revision of a patch to enable pgbench to store
results into variables.

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

Andrew Borodin sent in a patch to change the interpretation of NaN returned from
the GiST penalty function from "best fit" to "worst fit."

Tom Lane sent in a PoC patch to put srfs in separate result nodes.

Pavan Deolasee and Claudio Freire traded patches to allow VACUUM to use over 1GB
of work_mem.

Thomas Munro sent in another revision of a patch to use kqueue on platforms
where it helps.

Craig Ringer sent in another revision of a patch to install the Perl TAP tests,
add install rules for isolation tester, and add txid_status(bigint).

Kuntal Ghosh sent in another revision of a patch to add a WAL consistency check
facility.

Marco Pfatschbacher sent in a patch to keep one postmaster monitoring pipe per
process.

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

Rajkumar Raghuwanshi sent in another revision of a patch to enable piecewise
joins of partitioned tables.

Daniel Vérité sent in a patch to create hooks into psql variables to return a
boolean indicating whether a change is allowed.

Jeevan Chalke sent in another revision of a patch to enable pushing aggregates
to a foreign server.

Masahiko Sawada sent in another revision of a patch to enable quorum commit for
multiple synchronous replication.

Stas Kelvich sent in another revision of a patch to speed up 2PC transactions.

Julien Rouhaud sent in another revision of a patch to rename the
max-worker-processes GUC to max-parallel-workers.

Kyotaro HORIGUCHI sent in another revision of a patch to refactor psql's tab
completion and use that refactoring to implement IF (NOT) EXISTS completion.

MauMau sent in a patch to fix the omission of pg_recvlogical from the Windows
installation.

Yury Zhuravlev sent in another revision of a patch to CMake-ify PostgreSQL.