== PostgreSQL Weekly News - October 23 2016 ==

== PostgreSQL Weekly News - October 23 2016 ==

PostgreSQL@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@fetter.org, German language
to pwn@pgug.de, Italian language to pwn@itpug.org.

== 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@mail.gmail.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.20224.39628@wrigleys.postgresql.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@mail.gmail.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.1030000@iki.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@mail.gmail.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.1413.6439@wrigleys.postgresql.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@winand.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.1477244876@sss.pgh.pa.us>
  http://git.postgresql.org/pg/commitdiff/6292c2339186bac215bab5a1f01370f9735582c1

Andres Freund pushed:

- Fix a few typos in simplehash.h.  Author: Erik Rijkers Discussion:
  <274e4c8ac545d6622735f97c1f6c354b@xs4all.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@2ndquadrant.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.paquier@gmail.com>
  http://git.postgresql.org/pg/commitdiff/e5a9bcb529c474a07d1aa077665c5fade4c83cfc

- Make getrusage() output a little more readable.  Reviewed-by: Robert Haas
  <robertmhaas@gmail.com>.  Reviewed-by: Peter Geoghegan <pg@heroku.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.