== PostgreSQL Weekly News - October 26 2014 ==

== PostgreSQL Weekly News - October 26 2014 ==

== PostgreSQL Product News ==

walbouncer 0.9, a WAL-based partial replication tool, released.
http://www.cybertec.at/en/products/walbouncer-enterprise-grade-partial-replication/

== PostgreSQL Jobs for October ==

http://archives.postgresql.org/pgsql-jobs/2014-10/threads.php

== PostgreSQL Local ==

PGDay.IT 2014 will take place in Prato on November the 7th 2014.
http://2014.pgday.it/call-for-papers-en/

PG-Cuba will be November 25 and 26, 2014
http://postgresql.uci.cu/

The CfP for PGConf US 2015 is open through December 17th, 2014
Notifications will go out on January 10, 2014.  The event takes place
March 25-27, 2015 in NYC.
http://nyc.pgconf.us/2015/

== 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.  Spanish language
to pwn@arpug.com.ar.

== Applied Patches ==

Robert Haas pushed:

- Fix typos.  Etsuro Fujita
  http://git.postgresql.org/pg/commitdiff/0f565c074311d202f4524d1fd104b28c5a6adaf2

- Fix typos.  David Rowley
  http://git.postgresql.org/pg/commitdiff/bc279c92f0c213b7bc9a1aef9773aa9a3a9cd0e3

- Add a function to get the authenticated user ID.  Previously, this
  was not exposed outside of miscinit.c.  It is needed for the pending
  pg_background patch, and will also be needed for parallelism.
  Without it, there's no way for a background worker to re-create the
  exact authentication environment that was present in the process
  that started it, which could lead to security exposures.
  http://git.postgresql.org/pg/commitdiff/5ac372fc1a7cc673cc7d4cf26ba651d52495b27a

- Perform less setup work for AFTER triggers at transaction start.
  Testing reveals that the memory allocation we do at transaction
  start has small but measurable overhead on simple transactions.  To
  cut down on that overhead, defer some of that work to the point when
  AFTER triggers are first used, thus avoiding it altogether if they
  never are.  Patch by me.  Review by Andres Freund.
  http://git.postgresql.org/pg/commitdiff/2781b4bea7db357be59f9a5fd73ca1eb12ff5a79

- Fix off-by-one error in 2781b4bea7db357be59f9a5fd73ca1eb12ff5a79.
  Spotted by Tom Lane.
  http://git.postgresql.org/pg/commitdiff/85bb81de530aed08888f4fc3dec85c5cfbd3befd

Tom Lane pushed:

- Fix file-identification comment in
  contrib/pgcrypto/pgcrypto--1.2.sql.  Cosmetic oversight in commit
  32984d8fc3dbb90a3fafb69fece0134f1ea790f9.  Marko Tiikkaja
  http://git.postgresql.org/pg/commitdiff/488a7c9ccff54f947d5a51f00efe873f4bae909a

- Fix mishandling of FieldSelect-on-whole-row-Var in nested lateral
  queries.  If an inline-able SQL function taking a composite argument
  is used in a LATERAL subselect, and the composite argument is a
  lateral reference, the planner could fail with "variable not found
  in subplan target list", as seen in bug #11703 from Karl Bartel.
  (The outer function call used in the bug report and in the committed
  regression test is not really necessary to provoke the bug --- you
  can get it if you manually expand the outer function into "LATERAL
  (SELECT inner_function(outer_relation))", too.) The cause of this is
  that we generate the reltargetlist for the referenced relation
  before doing eval_const_expressions() on the lateral sub-select's
  expressions (cf find_lateral_references()), so what's scheduled to
  be emitted by the referenced relation is a whole-row Var, not the
  simplified single-column Var produced by optimizing the function's
  FieldSelect on the whole-row Var.  Then setrefs.c fails to match up
  that lateral reference to what's available from the outer scan.
  Preserving the FieldSelect optimization in such cases would require
  either major planner restructuring (to recursively do expression
  simplification on sub-selects much earlier) or some amazingly ugly
  kluge to change the reltargetlist of a possibly-already-planned
  relation.  It seems better just to skip the optimization when the
  Var is from an upper query level; the case is not so common that
  it's likely anyone will notice a few wasted cycles.  AFAICT this
  problem only occurs for uplevel LATERAL references, so back-patch to
  9.3 where LATERAL was added.
  http://git.postgresql.org/pg/commitdiff/f330a6d14066277e78586cb4ceb8692ca3533046

- Update expected/sequence_1.out.  The last three updates to the
  sequence regression test have all forgotten to touch the alternate
  expected-output file.  Sigh.  Michael Paquier
  http://git.postgresql.org/pg/commitdiff/31dd7fcd03609dbc3c3be85e645ccc7c715db719

- Ensure libpq reports a suitable error message on unexpected socket
  EOF.  The EOF-detection logic in pqReadData was a bit confused about
  who should set up the error message in case the kernel gives us
  read-ready-but-no-data rather than ECONNRESET or some other explicit
  error condition.  Since the whole point of this situation is that
  the lower-level functions don't know there's anything wrong,
  pqReadData itself must set up the message.  But keep the assumption
  that if an errno was reported, a message was set up at lower levels.
  Per bug #11712 from Marko Tiikkaja.  It's been like this for a very
  long time, so back-patch to all supported branches.
  http://git.postgresql.org/pg/commitdiff/69fed5b26fa34fc825c7ed0a8d97221ff448e9c5

- Improve ispell dictionary's defenses against bad affix files.  Don't
  crash if an ispell dictionary definition contains flags but not any
  compound affixes.  (This isn't a security issue since only
  superusers can install affix files, but still it's a bad thing.)
  Also, be more careful about detecting whether an affix-file FLAG
  command is old-format (ispell) or new-format (myspell/hunspell).
  And change the error message about mixed old-format and new-format
  commands into something intelligible.  Per bug #11770 from Emre
  Hasegeli.  Back-patch to all supported branches.
  http://git.postgresql.org/pg/commitdiff/b34d6f03dbb34027ee0ee6f1c1887ae30ec7b07d

- In type_sanity, check I/O functions of built-in types are not
  volatile.  We have a project policy that I/O functions must not be
  volatile, as per commit aab353a60b95aadc00f81da0c6d99bde696c4b75,
  but we weren't doing anything to enforce that.  In most usage the
  marking of the function doesn't matter as long as its behavior is
  sane --- but I/O casts can expose the marking as user-visible
  behavior, as per today's complaint from Joe Van Dyk about
  contrib/ltree.  This test as such will only protect us against
  future errors in built-in data types.  To catch the same error in
  contrib or third-party types, perhaps we should make CREATE TYPE
  complain?  But that's a separate issue from enforcing the policy for
  built-in types.
  http://git.postgresql.org/pg/commitdiff/43ac12c6e6e397fd9142ed908447eba32d3785b2

- Improve planning of btree index scans using ScalarArrayOpExpr quals.
  Since we taught btree to handle ScalarArrayOpExpr quals natively
  (commit 9e8da0f75731aaa7605cf4656c21ea09e84d2eb1), the planner has
  always included ScalarArrayOpExpr quals in index conditions if
  possible.  However, if the qual is for a non-first index column,
  this could result in an inferior plan because we can no longer take
  advantage of index ordering (cf. commit
  807a40c551dd30c8dd5a0b3bd82f5bbb1e7fd285).  It can be better to omit
  the ScalarArrayOpExpr qual from the index condition and let it be
  done as a filter, so that the output doesn't need to get sorted.
  Indeed, this is true for the query introduced as a test case by the
  latter commit.  To fix, restructure get_index_paths and
  build_index_paths so that we consider paths both with and without
  ScalarArrayOpExpr quals in non-first index columns.  Redesign the
  API of build_index_paths so that it reports what it found, saving
  useless second or third calls.  Report and patch by Andrew Gierth
  (though rather heavily modified by me).  Back-patch to 9.2 where
  this code was introduced, since the issue can result in significant
  performance regressions compared to plans produced by 9.1 and
  earlier.
  http://git.postgresql.org/pg/commitdiff/a4523c5aa5349510ddb74abddc416add2536ec3d

- Fix undersized result buffer in pset_quoted_string().  The malloc
  request was 1 byte too small for the worst-case output.  This seems
  relatively unlikely to cause any problems in practice, as the worst
  case only occurs if the input string contains no characters other
  than single-quote or newline, and even then malloc alignment padding
  would probably save the day.  But it's definitely a bug.  David
  Rowley
  http://git.postgresql.org/pg/commitdiff/9711fa06081da230e62fa52147c49ccf7b9ccbe2

- Avoid unportable strftime() behavior in pg_dump/pg_dumpall.  Commit
  ad5d46a4494b0b480a3af246bb4227d9bdadca37 thought that we could get
  around the known portability issues of strftime's %Z specifier by
  using %z instead.  However, that idea seems to have been innocent of
  any actual research, as it certainly missed the facts that (1) %z is
  not portable to pre-C99 systems, and (2) %z doesn't actually act
  differently from %Z on Windows anyway.  Per failures on buildfarm
  member hamerkop.  While at it, centralize the code defining what
  strftime format we want to use in pg_dump; three copies of that
  string seems a bit much.
  http://git.postgresql.org/pg/commitdiff/f455fcfdb8ca3b67373223a4e15648c35e2592a9

Andrew Dunstan pushed:

- Correct volatility markings of a few json functions.  json_agg and
  json_object_agg and their associated transition functions should
  have been marked as stable rather than immutable, as they call IO
  functions indirectly. Changing this probably isn't going to make
  much difference, as you can't use an aggregate function in an index
  expression, but we should be correct nevertheless.  json_object, on
  the other hand, should be marked immutable rather than stable, as it
  does not call IO functions.  As discussed on -hackers, this change
  is being made without bumping the catalog version, as we don't want
  to do that at this stage of the cycle, and  the changes are very
  unlikely to affect anyone.
  http://git.postgresql.org/pg/commitdiff/af2b8fd057213f4b1918b9581c63e0b00427573c

Peter Eisentraut pushed:

- pg_test_fsync: Update output format.  Apparently, computers are now
  a bit faster than when this was first added, so we need to make room
  for a digit or two in the ops/sec format.  While we're at it, adjust
  some of the other output for a more consistent line length.
  http://git.postgresql.org/pg/commitdiff/83dc5908c207bc0ff4256eb862f6a211ad5596ec

- doc: Update Red Hat documentation tools information.  The old text
  was written in ancient times when RPM packages could be shared more
  or less freely across a plethora of RPM-based Linux distributions.
  This isn't really the case anymore, so just make this information
  more concrete for the Red Hat family.
  http://git.postgresql.org/pg/commitdiff/f43afbca701113fb6538e38e1f95666fb1910e2f

- doc: Update FreeBSD documentation tools information.  based on patch
  from Dag-Erling Smørgrav <des@des.no>
  http://git.postgresql.org/pg/commitdiff/f048e50037b1aa5cda857ce5f6cc17a1ea410987

- doc: Check DocBook XML validity during the build.  Building the
  documentation with XSLT does not check the DTD, like a DSSSL build
  would.  One can often get away with having invalid XML, but the
  stylesheets might then create incorrect output, as they are not
  designed to handle that.  Therefore, check the validity of the XML
  against the DTD, using xmllint, during the build.  Add xmllint
  detection to configure, and add some documentation.  xmllint comes
  with libxml2, which is already in use, but it might be in a separate
  package, such as libxml2-utils on Debian.  Reviewed-by: Fabien
  COELHO <coelho@cri.ensmp.fr>
  http://git.postgresql.org/pg/commitdiff/5d93ce2d0c619ba1b408eb749715e7223e23f6ae

- Allow input format xxxx-xxxx-xxxx for macaddr type.  Author: Herwin
  Weststrate <herwin@quarantainenet.nl> Reviewed-by: Ali Akbar
  <the.apaan@gmail.com>
  http://git.postgresql.org/pg/commitdiff/6f04368cfc05c46140f124415d752c3628c73f28

- Minimize calls of pg_class_aclcheck to minimum necessary.  In a
  couple of code paths, pg_class_aclcheck is called in succession with
  multiple different modes set.  This patch combines those modes to
  have a single call of this function and reduce a bit process
  overhead for permission checking.  Author: Michael Paquier
  <michael@otacoo.com> Reviewed-by: Fabrízio de Royes Mello
  <fabriziomello@gmail.com>
  http://git.postgresql.org/pg/commitdiff/e64d3c5635cef9ee2e9d67904f1d3e88f4d8b2e7

- Add tests for sequence privileges
  http://git.postgresql.org/pg/commitdiff/a5f7d581943e70235264098c35313d16e932ee0c

- Fix TAP tests with Perl 5.8.  The prove program included in Perl 5.8
  does not support the --ext option, so don't use that and use
  wildcards on the command line instead.  Note that the tests will
  still all be skipped, because, for instance, the version of
  Test::More is too old, but at least the regular mechanisms for
  handling that will apply, instead of failing to call prove
  altogether.
  http://git.postgresql.org/pg/commitdiff/5c3d830e4401a1825207eb18640395112e6ccd6d

- Fix TAP tests with Perl 5.12.  Perl 5.12 ships with a somewhat
  broken version of Test::Simple, so skip the tests if that is found.
  The relevant fix is 0.98  Wed, 23 Feb 2011 14:38:02 +1100 Bug Fixes:
  subtest() should not fail if $? is non-zero. (Aaron Crane)
  http://git.postgresql.org/pg/commitdiff/17009fb9ebe6873d98ed0fe216eeec08e0603823

Andres Freund pushed:

- Flush unlogged table's buffers when copying or moving databases.
  CREATE DATABASE and ALTER DATABASE .. SET TABLESPACE copy the source
  database directory on the filesystem level. To ensure the on disk
  state is consistent they block out users of the affected database
  and force a checkpoint to flush out all data to disk. Unfortunately,
  up to now, that checkpoint didn't flush out dirty buffers from
  unlogged relations.  That bug means there could be leftover dirty
  buffers in either the template database, or the database in its old
  location. Leading to problems when accessing relations in an
  inconsistent state; and to possible problems during shutdown in the
  SET TABLESPACE case because buffers belonging files that don't exist
  anymore are flushed.  This was reported in bug #10675 by Maxim
  Boguk.  Fix by Pavan Deolasee, modified somewhat by me. Reviewed by
  MauMau and Fujii Masao.  Backpatch to 9.1 where unlogged tables were
  introduced.
  http://git.postgresql.org/pg/commitdiff/7dbb60693820c20b562c12f58040c9871d6ab787

- Renumber CHECKPOINT_* flags.  Commit 7dbb6069382 added a new
  CHECKPOINT_FLUSH_ALL flag. As that commit needed to be backpatched I
  didn't change the numeric values of the existing flags as that could
  lead to nastly problems if any external code issued checkpoints.
  That's not a concern on master, so renumber them there.  Also add a
  comment about CHECKPOINT_FLUSH_ALL above CreateCheckPoint().
  http://git.postgresql.org/pg/commitdiff/11abd6c90f1094eaeee32e1c57d250c9db5102cb

- Don't duplicate log_checkpoint messages for both of restart and
  checkpoints.  The duplication originated in cdd46c765, where
  restartpoints were introduced.  In LogCheckpointStart's case the
  duplication actually lead to the compiler's format string checking
  not to be effective because the format string wasn't constant.
  Arguably these messages shouldn't be elog(), but ereport() style
  messages. That'd even allow to translate the messages... But as
  there's more mistakes of that kind in surrounding code, it seems
  better to change that separately.
  http://git.postgresql.org/pg/commitdiff/5e5b65f359b7a12c05708585a8e6cd4d80ca2652

- Add native compiler and memory barriers for solaris studio.
  Discussion: 20140925133459.GB9633@alap3.anarazel.de Author: Oskari
  Saarenmaa
  http://git.postgresql.org/pg/commitdiff/4a54b99e9c3989017fa5717f343cceab09aa5542

Noah Misch pushed:

- MinGW: Link with shell32.dll instead of shfolder.dll.  This improves
  consistency with the MSVC build.  On buildfarm member narwhal, since
  commit 846e91e0223cf9f2821c3ad4dfffffbb929cb027,
  shfolder.dll:SHGetFolderPath() crashes when dblink calls it by way
  of pqGetHomeDirectory().  Back-patch to 9.4, where that commit first
  appeared.  How it caused this regression remains a mystery.  This is
  a partial revert of commit 889f03812916b146ae504c0fad5afdc7bf2e8a2a,
  which adopted shfolder.dll for Windows NT 4.0 compatibility.
  PostgreSQL 8.2 dropped support for that operating system.
  http://git.postgresql.org/pg/commitdiff/53566fc0940cf557416b13252df57350a4511ce4

- MinGW: Use -static-libgcc when linking a DLL.  When commit
  846e91e0223cf9f2821c3ad4dfffffbb929cb027 switched the linker driver
  from dlltool/dllwrap to gcc, it became possible for linking to
  choose shared libgcc.  Backends having loaded a module dynamically
  linked to libgcc can exit abnormally, which the postmaster treats
  like a crash.  Resume use of static libgcc exclusively, like 9.3 and
  earlier.  Back-patch to 9.4.
  http://git.postgresql.org/pg/commitdiff/284590e416b72f541c5fe54f36a95e38d80835c3

Heikki Linnakangas pushed:

- Update comment.  The _bt_tuplecompare() function mentioned in
  comment hasn't existed for a long time.  Peter Geoghegan
  http://git.postgresql.org/pg/commitdiff/98b37437796bb5fedecf0d0c73cef4aa2ce9d192

- Complain if too many options are passed to pg_controldata or
  pg_resetxlog.
  http://git.postgresql.org/pg/commitdiff/2d53003432f8560b9c3adf569118747c8ac8447d

- Oops, the commit accept pg_controldata -D datadir missed code
  changes.  I updated the docs and usage blurp, but forgot to commit
  the code changes required.  Spotted by Michael Paquier.
  http://git.postgresql.org/pg/commitdiff/22b743b2ca18a692ba8d91bc18e7c42a8b7941ad

- Forgot #include "pg_getopt.h", now that pg_controldata uses getopt.
  Needed at least on Windows.
  http://git.postgresql.org/pg/commitdiff/c0c1f6fc97088100a710e7e9c2e74b775985083d

- Work around Windows locale name with non-ASCII character.  Windows
  has one a locale whose name contains a non-ASCII character:
  "Norwegian (Bokmål)" (that's an 'a' with a ring on top). That causes
  trouble; when passing it setlocale(), it's not clear what encoding
  the argument should be in. Another problem is that the locale name
  is stored in pg_database catalog table, and the encoding used there
  depends on what server encoding happens to be in use when the
  database is created. For example, if you issue the CREATE DATABASE
  when connected to a UTF-8 database, the locale name is stored in
  pg_database in UTF-8. As long as all locale names are pure ASCII,
  that's not a problem.  To work around that, map the troublesome
  locale name to a pure-ASCII alias of the same locale,
  "norwegian-bokmal".  Now, this doesn't change the existing values
  that are already in pg_database and in postgresql.conf. Old clusters
  will need to be fixed manually. Instructions for that need to be put
  in the release notes.  This fixes bug #11431 reported by Alon
  Siman-Tov. Backpatch to 9.2; backpatching further would require more
  work than seems worth it.
  http://git.postgresql.org/pg/commitdiff/db29620d4d16e08241f965ccd70d0f65883ff0de

Michael Meskes pushed:

- Small code cleanup.  Declare static variable as static and external
  as extern.
  http://git.postgresql.org/pg/commitdiff/2ae7811db87bf54e8a70aae3cfd9f7cc7eff0bc4

Fujii Masao pushed:

- Prevent the already-archived WAL file from being archived again.
  Previously the archive recovery always created .ready file for the
  last WAL file of the old timeline at the end of recovery even when
  it's restored from the archive and has .done file. That is, there
  was the case where the WAL file had both .ready and .done files.
  This caused the already-archived WAL file to be archived again.
  This commit prevents the archive recovery from creating .ready file
  for the last WAL file if it has .done file, in order to prevent it
  from being archived again.  This bug was added when cascading
  replication feature was introduced, i.e., the commit
  5286105800c7d5902f98f32e11b209c471c0c69c.  So, back-patch to 9.2,
  where cascading replication was added.  Reviewed by Michael Paquier
  http://git.postgresql.org/pg/commitdiff/c7371c4a607872c4d799e0dac0c9367574067cdc

- Remove the unused argument of PSQLexec().  This commit simply
  removes the second argument of PSQLexec that was set to the same
  value everywhere. Comments and code blocks related to this parameter
  are removed.  Noticed by Heikki Linnakangas, reviewed by Michael
  Paquier
  http://git.postgresql.org/pg/commitdiff/efbbbbc8b51bacd32e96c3f747dd6aa4c0e444cb

Álvaro Herrera pushed:

- Update README.tuplock.  This file was documenting an older version
  of patch 0ac5ad5134; update it to match what was really committed
  Author: Florian Pflug
  http://git.postgresql.org/pg/commitdiff/b01a4f68386f8bbc62c308138f23e436cb274629

- psql: complain if pg_dump custom-format is detected.  Apparently,
  this is a very common mistake for users to make; it is better to
  have it fail reasonably rather than throw potentially a large number
  of errors.  Since we have a magic string at the start of the file,
  we can detect the case easily and there's no other possible useful
  behavior anyway.  Author: Craig Ringer
  http://git.postgresql.org/pg/commitdiff/3c2aa0c6f27fc921c881b3bf0b6df03fdf557304

== Rejected Patches (for now) ==

No one was disappointed this week :-)

== Pending Patches ==

Marko (johto) Tiikkaja sent in another revision of a patch to
implement PGP signatures in the pgcrypto contrib extension.

David Wheeler sent in two revisions of a patch to add launchd support
for OSX.

Adam Brightwell sent in another revision of a patch to add directory
and file access permissions for COPY and generic file access
functions.

Jim Nasby sent in a patch to remove a spurious set in
heap_prune_chain().

Jim Nasby sent in a patch to optimize CacheInvalidateHeapTuple.

Tom Lane sent in a patch to get rid of "accept incoming network
connections" prompts on OS X.

Teodor Sigaev sent in a patch to speed up tidbitmap by caching a page.

Teodor Sigaev sent in a patch to speed up tidbitmap by hashing
BlockNumber.

Teodor Sigaev sent in a patch to add a compress method for SP-GiST.

David Rowley and Craig Ringer traded patches to use a higher-precision
timer API on Windows when available.

Teodor Sigaev sent in a patch to add GIN support contains operator for
ranges over scalar column.

Robert Haas sent in another revision of a patch to implement
pg_background.

Andreas Karlsson sent in a patch to reduce the lock strength needed
for adding foreign keys.

Etsuro Fujita sent in a patch to fix an incorrect comment in
tablecmds.c.

Pavel Stehule sent in a patch to allow AS label inside a row
constructor.

Fujii Masao sent in another revision of a patch to implement REINDEX
SCHEMA.

Florian Pflug sent in a patch to update README.tuplock to reflect
current realities.

Etsuro Fujita sent in another revision of a patch to allow foreign
tables to be part of table inheritance hierarchies.

Michael Paquier sent in two revisions of a patch to fix some typos in
the recvlogical documentation.

Peter Geoghegan sent in another revision of a patch to implement
INSERT ... ON CONFLICT {UPDATE | IGNORE}.

Kyotaro HORIGUCHI sent in another revision of a patch to add ALTER
ROLE ... CURRENT USER.

Jim Nasby sent in a patch to add some detail to a comment in
bgworker.c.

Ali Akbar and Pavel Stehule traded patches to implement
array_agg(array).

Andreas Karlsson sent in a WIP patch to use 128-bit integers for sum,
avg and statistics aggregates where available.

Michael Paquier sent in another revision of a patch to make
pg_controldata accept "-D dirname".

Andrew Dunstan sent in two more revisions of a patch to add strip
nulls functions for json and jsonb.

Marti Raudsepp sent in another revision of a patch to simplify EXISTS
subqueries containing LIMIT.

David Rowley sent in a patch to fix an issue where pset_quoted_string
was broken in psql.