== PostgreSQL Weekly News - October 23 2016 ==

== PostgreSQL Weekly News - October 23 2016 ==

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

== PostgreSQL Product News ==

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

PostgresDAC 3.2.0, a direct access component suite for PostgreSQL, released.
http://microolap.com/products/connectivity/postgresdac/news/detail.php?ID=1781

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

- Replace PostmasterRandom() with a stronger way of generating randomness. This
adds a new routine, pg_strong_random() for generating random bytes, for use in
both frontend and backend. At the moment, it's only used in the backend, but
the upcoming SCRAM authentication patches need strong random numbers in libpq
as well. pg_strong_random() is based on, and replaces, the existing
implementation in pgcrypto. It can acquire strong random numbers from a number
of sources, depending on what's available: - OpenSSL RAND_bytes(), if built
with OpenSSL - On Windows, the native cryptographic functions are used -
/dev/urandom - /dev/random Original patch by Magnus Hagander, with further
work by Michael Paquier and me. Discussion:
<CAB7nPqRy3krN8quR9XujMVVHYtXJ0_60nqgVc6oUk8ygyVkZsA(at)mail(dot)gmail(dot)com>
http://git.postgresql.org/pg/commitdiff/9e083fd4683294f41544e6d0d72f6e258ff3a77c

- Fix use-after-free around DISTINCT transition function calls. Have
tuplesort_gettupleslot() copy the contents of its current table slot as
needed. This is based on an approach taken by tuplestore_gettupleslot(). In
the future, tuplesort_gettupleslot() may also be taught to avoid copying the
tuple where caller can determine that that is safe (the
tuplestore_gettupleslot() interface already offers this option to callers).
Patch by Peter Geoghegan. Fixes bug #14344, reported by Regina Obe. Report:
<20160929035538(dot)20224(dot)39628(at)wrigleys(dot)postgresql(dot)org> Backpatch-through: 9.6
http://git.postgresql.org/pg/commitdiff/d8589946ddd5c43e1ebd01c5e92d0e177cbfc198

- Revert "Replace PostmasterRandom() with a stronger way of generating
randomness." This reverts commit 9e083fd4683294f41544e6d0d72f6e258ff3a77c.
That was a few bricks shy of a load: * Query cancel stopped working *
Buildfarm member pademelon stopped working, because the box doesn't have
/dev/urandom nor /dev/random. This clearly needs some more discussion, and a
quite different patch, so revert for now.
http://git.postgresql.org/pg/commitdiff/faae1c918e8aaae034eaf3ea103fcb6ba9adc5ab

- Fix WAL-logging of FSM and VM truncation. When a relation is truncated, it is
important that the FSM is truncated as well. Otherwise, after recovery, the
FSM can return a page that has been truncated away, leading to errors like:
ERROR: could not read block 28991 in file "base/16390/572026": read only 0 of
8192 bytes We were using MarkBufferDirtyHint() to dirty the buffer holding the
last remaining page of the FSM, but during recovery, that might in fact not
dirty the page, and the FSM update might be lost. To fix, use the stronger
MarkBufferDirty() function. MarkBufferDirty() requires us to do WAL-logging
ourselves, to protect from a torn page, if checksumming is enabled. Also fix
an oversight in visibilitymap_truncate: it also needs to WAL-log when
checksumming is enabled. Analysis by Pavan Deolasee. Discussion:
<CABOikdNr5vKucqyZH9s1Mh0XebLs_jRhKv6eJfNnD2wxTn=_9A(at)mail(dot)gmail(dot)com>
http://git.postgresql.org/pg/commitdiff/917dc7d2393ce680dea7a59418be9ff341df3c14

- Use OpenSSL EVP API for symmetric encryption in pgcrypto. The old "low-level"
API is deprecated, and doesn't support hardware acceleration. And this makes
the code simpler, too. Discussion: <561274F1(dot)1030000(at)iki(dot)fi>
http://git.postgresql.org/pg/commitdiff/5ff4a67f63fd6d3eb01ff9707d4674ed54a89f3b

Robert Haas pushed:

- By default, set log_line_prefix = '%m [%p] '. This value might not be to
everyone's taste; in particular, some people might prefer %t to %m, and others
may want %u, %d, or other fields. However, it's a vast improvement on the old
default of ''. Christoph Berg
http://git.postgresql.org/pg/commitdiff/7d3235ba42f8d5fc70c58e242702cc5e2e3549a6

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

- Improve regression test coverage for hash indexes. On my system, this
improves coverage for src/backend/access/hash from 61.3% of lines to 88.2% of
lines, and from 83.5% of functions to 97.5% of functions, which is pretty good
for 36 lines of tests. Mithun Cy, reviewing by Amit Kapila and Álvaro Herrera
http://git.postgresql.org/pg/commitdiff/b801e120080de836b834c1b756c4c4d81ce841b5

- Remove a comment which is now incorrect. Before
5d305d86bd917723f09ab4f15c075d90586a210a, this comment was correct, but now it
says we do something which we don't actually do. Accordingly, remove the
comment.
http://git.postgresql.org/pg/commitdiff/ec7db2b483e0ff247ed41612cdb5716022401fe6

- ename "pg_xlog" directory to "pg_wal". "xlog" is not a particularly clear
abbreviation for "write-ahead log", and it sometimes confuses users into
believe that the contents of the "pg_xlog" directory are not critical data,
leading to unpleasant consequences. So, rename the directory to "pg_wal".
This patch modifies pg_upgrade and pg_basebackup to understand both the old
and new directory layouts; the former is necessary given the purpose of the
tool, while the latter merely avoids an unnecessary backward-compatibility
break. We may wish to consider renaming other programs, switches, and
functions which still use the old "xlog" naming to also refer to "wal".
However, that's still under discussion, so let's do just this much for now.
Discussion: CAB7nPqTeC-8+zux8_-4ZD46V7YPwooeFxgndfsq5Rg8ibLVm1A(at)mail(dot)gmail(dot)com
Michael Paquier
http://git.postgresql.org/pg/commitdiff/f82ec32ac30ae7e3ec7c84067192535b2ff8ec0e

- postgres_fdw: Push down aggregates to remote servers. Now that the upper
planner uses paths, and now that we have proper hooks to inject paths into the
upper planning process, it's possible for foreign data wrappers to arrange to
push aggregates to the remote side instead of fetching all of the rows and
aggregating them locally. This figures to be a massive win for performance,
so teach postgres_fdw to do it. Jeevan Chalke and Ashutosh Bapat. Reviewed
by Ashutosh Bapat with additional testing by Prabhat Sahu. Various mostly
cosmetic changes by me.
http://git.postgresql.org/pg/commitdiff/7012b132d07c2b4ea15b0b3cb1ea9f3278801d98

- Fix comment formatting.
http://git.postgresql.org/pg/commitdiff/919c811ca1e2a545cb1db243af93d55270d84469

- postgres_fdw: Attempt to stabilize regression results. Set enable_hashagg to
false for tests involving least_agg(), so that we get the same plan regardless
of local costing variances. Also, remove a test involving sqrt(); it's there
to test deparsing of HAVING clauses containing expressions, but that's tested
elsewhere anyway, and sqrt(2) deparses with different amounts of precision on
different machines. Per buildfarm.
http://git.postgresql.org/pg/commitdiff/ad13a09d762f0c903a52ed0ec668a0ba51a61047

Tom Lane pushed:

- Fix cidin() to handle values above 2^31 platform-independently. CommandId is
declared as uint32, and values up to 4G are indeed legal. cidout() handles
them properly by treating the value as unsigned int. But cidin() was just
using atoi(), which has platform-dependent behavior for values outside the
range of signed int, as reported by Bart Lengkeek in bug #14379. Use
strtoul() instead, as xidin() does. In passing, make some purely cosmetic
changes to make xidin/xidout look more like cidin/cidout; the former didn't
have a monopoly on best practice IMO. Neither xidin nor cidin make any
attempt to throw error for invalid input. I didn't change that here, and am
not sure it's worth worrying about since neither is really a user-facing type.
The point is just to ensure that indubitably-valid inputs work as expected.
It's been like this for a long time, so back-patch to all supported branches.
Report: <20161018152550(dot)1413(dot)6439(at)wrigleys(dot)postgresql(dot)org>
http://git.postgresql.org/pg/commitdiff/6f13a682c86801cfb9ae4f3126888b42f3cb5c46

- Update time zone data files to tzdata release 2016g. DST law changes in
Turkey. Historical corrections for America/Los_Angeles, Europe/Kirov,
Europe/Moscow, Europe/Samara, and Europe/Ulyanovsk. Rename Asia/Rangoon to
Asia/Yangon, with a backward compatibility link. The IANA crew continue their
campaign to replace invented time zone abbrevations with numeric GMT offsets.
This update changes numerous zones in Antarctica and the former Soviet Union,
for instance Antarctica/Casey now reports "+08" not "AWST" in the
pg_timezone_names view. I kept these abbreviations in the tznames/ data
files, however, so that we will still accept them for input. (We may want to
start trimming those files someday, but today is not that day.) An exception
is that since IANA no longer claims that "AMT" is in use in Armenia for GMT+4,
I replaced it in the Default file with GMT-4, corresponding to Amazon Time
which is in use in South America. It may be that that meaning is also
invented and IANA will drop it in a future update; but for now, it seems silly
to give pride of place to a meaning not traceable to IANA over one that is.
http://git.postgresql.org/pg/commitdiff/ecbac3e6e038e990f24a2e0eacdcd6738292105f

- Suppress "Factory" zone in pg_timezone_names view for tzdata >= 2016g. IANA
got rid of the really silly "abbreviation" and replaced it with one that's
only moderately silly. But it's still pointless, so keep on not showing it.
http://git.postgresql.org/pg/commitdiff/a3215431ab7c667bf581728f10c80a36abbe1d5a

- Windows portability fix. Per buildfarm.
http://git.postgresql.org/pg/commitdiff/ad90ac4d671d320ade3c127f215e97cd49c307fb

- Sync our copy of the timezone library with IANA release tzcode2016g. This is
mostly to absorb some corner-case fixes in zic for year-2037 timestamps. The
other changes that have been made are unlikely to affect our usage, but
nonetheless we may as well take 'em.
http://git.postgresql.org/pg/commitdiff/f3094920a567cde6c86adf36a1a033d7431b11ff

- Another portability fix for tzcode2016g update. clang points out that
SIZE_MAX wouldn't fit into an int, which means this comparison is pretty
useless. Per report from Thomas Munro.
http://git.postgresql.org/pg/commitdiff/23ed2ba8121178474f8c51774c6c258cb165a562

- Update time zone data files to tzdata release 2016h. (Didn't I just do this?
Oh well.) DST law changes in Palestine. Historical corrections for Turkey.
Switch to numeric abbreviations for Asia/Colombo.
http://git.postgresql.org/pg/commitdiff/d8fc45bd0f62fcebac80c63840b753f8e3b737ff

- Sync our copy of the timezone library with IANA release tzcode2016h. This
absorbs a fix for a symlink-manipulation bug in zic that was introduced in
2016g. It probably isn't interesting for our use-case, but I'm not quite
sure, so let's update while we're at it.
http://git.postgresql.org/pg/commitdiff/5e21b6811148fdc1fce9dcdcdc777418cc901fe4

- Fix EXPLAIN so that it doesn't emit invalid XML in corner cases. With
track_io_timing = on, EXPLAIN (ANALYZE, BUFFERS) will emit fields named like
"I/O Read Time". The slash makes that invalid as an XML element name, so that
adding FORMAT XML would produce invalid XML. We already have code in there to
translate spaces to dashes, so let's generalize that to convert anything that
isn't a valid XML name character, viz letters, digits, hyphens, underscores,
and periods. We could just reject slashes, which would run a bit faster. But
the fact that this went unnoticed for so long doesn't give me a warm feeling
that we'd notice the next creative violation, so let's make it a permanent
fix. Reported by Markus Winand, though this isn't his initial patch proposal.
Back-patch to 9.2 where track_io_timing was added. The problem is only latent
in 9.1, so I don't feel a need to fix it there. Discussion:
<E0BF6A45-68E8-45E6-918F-741FB332C6BB(at)winand(dot)at>
http://git.postgresql.org/pg/commitdiff/709e461befa8a4999c4ccdbfc7260ef8092e805c

- Doc: wording tweak for PERL, PYTHON, TCLSH configuration variables. Replace
"Full path to ..." with "Full path name of ...". At least one user has
misinterpreted the existing wording as meaning "Directory containing ...".
http://git.postgresql.org/pg/commitdiff/7aa2c10ac6785a2de683609b98da607e588a6d02

- First-draft release notes for 9.6.1. As usual, the release notes for other
branches will be made by cutting these down, but put them up for community
review first.
http://git.postgresql.org/pg/commitdiff/eacaf6e29fd2a3047aff9738a35a8e9b05e55375

- Improve documentation about use of Linux huge pages. Show how to get the
system's huge page size, rather than misleadingly referring to PAGE_SIZE
(which is usually understood to be the regular page size). Show how to
confirm whether huge pages have been allocated. Minor wordsmithing.
Back-patch to 9.4 where this section appeared.
http://git.postgresql.org/pg/commitdiff/1885c88459698251eca64f095d9942c540ba0fa8

- Avoid testing tuple visibility without buffer lock in RI_FKey_check().
Despite the argumentation I wrote in commit 7a2fe85b0, it's unsafe to do this,
because in corner cases it's possible for HeapTupleSatisfiesSelf to try to set
hint bits on the target tuple; and at least since 8.2 we have required the
buffer content lock to be held while setting hint bits. The added regression
test exercises one such corner case. Unpatched, it causes an assertion
failure in assert-enabled builds, or otherwise would cause a hint bit change
in a buffer we don't hold lock on, which given the right race condition could
result in checksum failures or other data consistency problems. The odds of a
problem in the field are probably pretty small, but nonetheless back-patch to
all supported branches. Report: <19391(dot)1477244876(at)sss(dot)pgh(dot)pa(dot)us>
http://git.postgresql.org/pg/commitdiff/6292c2339186bac215bab5a1f01370f9735582c1

Andres Freund pushed:

- Fix a few typos in simplehash.h. Author: Erik Rijkers Discussion:
<274e4c8ac545d6622735f97c1f6c354b(at)xs4all(dot)nl>
http://git.postgresql.org/pg/commitdiff/90d3da11c9417218ebd4f86b2003c98421824712

Peter Eisentraut pushed:

- doc: Consistently use = sign in long options synopses. This was already the
predominant form in man pages and help output.
http://git.postgresql.org/pg/commitdiff/c709c6074083a8cc5f1ba431c741ff04e3a8a906

- initdb pg_basebackup: Rename --noxxx options to --no-xxx. --noclean and
--nosync were the only options spelled without a hyphen, so change this for
consistency with other options. The options in pg_basebackup have not been in
a release, so we just rename them. For initdb, we retain the old variants.
Vik Fearing and me
http://git.postgresql.org/pg/commitdiff/5d58c07a441414ae29a8e315d2f9868d3d8e20be

- pg_ctl: Add long option for -o Now all normally used options are covered by
long options as well.
http://git.postgresql.org/pg/commitdiff/caf936b09fc7c74844575332b07c667a178cb078

- pg_ctl: Add long options for -w and -W. From: Vik Fearing
<vik(at)2ndquadrant(dot)fr>
http://git.postgresql.org/pg/commitdiff/0be22457d730da8971f761b9c948f742a12b50b2

- Use pg_ctl promote -w in TAP tests. Switch TAP tests to use the new wait mode
of pg_ctl promote. This allows avoiding extra logic with poll_query_until()
to be sure that a promoted standby is ready for read-write queries. From:
Michael Paquier <michael(dot)paquier(at)gmail(dot)com>
http://git.postgresql.org/pg/commitdiff/e5a9bcb529c474a07d1aa077665c5fade4c83cfc

- Make getrusage() output a little more readable. Reviewed-by: Robert Haas
<robertmhaas(at)gmail(dot)com>. Reviewed-by: Peter Geoghegan <pg(at)heroku(dot)com>.
http://git.postgresql.org/pg/commitdiff/9ffe4a8b4cbb713bf8137f8414f02d97b6b2eb08

Magnus Hagander pushed:

- Remove extra comma at end of enum list. C99-specific feature, and wasn't
intentional in the first place. Per buildfarm member mylodon
http://git.postgresql.org/pg/commitdiff/d97a59a4c5597af5f53869a5a1c753893752c66b

- Fix walmethods.c build without libz. Per numerous buildfarm manuals.
http://git.postgresql.org/pg/commitdiff/9ae6713cdf32122fa5a0bf15ddcd85f78f6f4631

- Fix obviously too quickly applied fix to zlib issue.
http://git.postgresql.org/pg/commitdiff/a5c17c1dcef4a656559152d3f6a5a27ae4957843

- Allow pg_basebackup to stream transaction log in tar mode. This will write
the received transaction log into a file called pg_wal.tar(.gz) next to the
other tarfiles instead of writing it to base.tar. When using fetch mode, the
transaction log is still written to base.tar like before, and when used
against a pre-10 server, the file is named pg_xlog.tar. To do this, implement
a new concept of a "walmethod", which is responsible for writing the WAL. Two
implementations exist, one that writes to a plain directory (which is also
used by pg_receivexlog) and one that writes to a tar file with optional
compression. Reviewed by Michael Paquier
http://git.postgresql.org/pg/commitdiff/56c7d8d4552180fd66fe48423bb2a9bb767c2d87

- Rename walmethod fsync method to sync. Using the name fsync clashed with the
#define we have on Windows that redefines it to _commit. Naming it sync should
remove that conflict. Per all the Windows buildfarm members
http://git.postgresql.org/pg/commitdiff/eade082b122889eaf92eb806b8b6799160a25256

== Pending Patches ==

Haribabu Kommi and Vinayak Pokale traded patches to add a pg_stat_sql system
view.

Dilip Kumar sent in two more revisions of a patch to add parallel bitmap heap
scan.

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

Kyotaro HORIGUCHI sent in another revision of a patch to implement asynchronous
execution.

Laurenz Albe sent in a patch to add PGDLLEXPORT to a sample C function.

Ashutosh Bapat and Etsuro Fujita traded patches to ensure that altering a
foreign table invalidates plans involving same.

Aleksander Alekseev sent in two revisions of a patch to enable logging the
contents of COPY statements.

Heikki Linnakangas and Michaël Paquier traded patches to fix an FSM corruption
leading to errors.

Gilles Darold sent in another revision of a patch to implement
pg_current_logfile().

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

Michaël Paquier sent in two more revisions of a patch to implement SCRAM auth.

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

Thom Brown sent in another revision of a patch to implement failover on the
libpq connect level.

Jeevan Chalke and Ashutosh Bapat traded patches to implement aggregate pushdown.

Peter Geoghegan sent in a patch to fix ON CONFLICT bugs at higher isolation levels.

Oleksandr Shulgin sent in a patch to prevent psql from sending commands after a
connection reset.

Vinayak Pokale sent in a patch to fix a typo in pgstat.h.

Vinayak Pokale sent in a patch to fix a typo in pgstat.c.

Constantin S. Pan and Michaël Paquier traded patches to fix the fact that there
can be a lot of orphan temp tables.

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

Kyotaro HORIGUCHI sent in another revision of a patch to implement a radix tree
for character conversion.

Masahiko Sawada sent in a patch to allow specifying the log file name of pgbench
-l option.

Julien Rouhaud sent in a patch to fix the fact that when track_commit_timestamp
is enabled, the oldestCommitTsXid and newestCommitTsXid don't persist after a
server restart.

Bruce Momjian sent in a patch to mention pg_reload_conf() as a way to reload
configurations in the docs.

Ashutosh Bapat sent in another revision of a patch to allow pushing down more
FULL JOINs to the the PostgreSQL FDW.

Michaël Paquier sent in another revision of a patch to rename pg_clog to pg_xact
and pg_subtrans to pg_subxact.