Skip site navigation (1) Skip section navigation (2)

== PostgreSQL Weekly News - October 30 2016 ==

From: David Fetter <david(at)fetter(dot)org>
To: PostgreSQL Announce <pgsql-announce(at)postgresql(dot)org>
Subject: == PostgreSQL Weekly News - October 30 2016 ==
Date: 2016-10-31 02:37:27
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-announce
== PostgreSQL Weekly News - October 30 2016 ==

Bug fix releases 9.6.1, 9.5.5, 9.4.10, 9.3.15, 9.2.19, and 9.1.24 are out.
Upgrade ASAP.  9.1.24 is the final release of the 9.1 series.

== PostgreSQL Jobs for October ==

== PostgreSQL Local ==

PostgreSQL Conference Europe will take place in Tallin, Estonia, on
November 1-4, 2016.  The schedule has been published.

PGDay Austin 2016, will take place on November 12, 2016.

PgConf Silicon Valley 2016 will be held on November 14-16, 2016.

CHAR(16) will take place in New York, December 6, 2016.

PGDay.IT 2016 will take place in Prato on December the 13th 2016.

PostgreSQL(at)SCaLE will take place on March 2-3, 2017, at Pasadena Convention
Center, as part of SCaLE 15X.  The CfP is open until 15 November, 2016.

== PostgreSQL in the News ==

Planet PostgreSQL:

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:

- Don't throw serialization errors for self-conflicts in INSERT ON CONFLICT.  A
  transaction that conflicts against itself, for example INSERT INTO t(pk)
  VALUES (1),(1) ON CONFLICT DO NOTHING; should behave the same regardless of
  isolation level.  It certainly shouldn't throw a serialization error, as
  retrying will not help.  We got this wrong due to the ON CONFLICT logic not
  considering the case, as reported by Jason Dusek.  Core of this patch is by
  Peter Geoghegan (based on an earlier patch by Thomas Munro), though I didn't
  take his proposed code refactoring for fear that it might have unexpected
  side-effects.  Test cases by Thomas Munro and myself.  Report:
  Related-Discussion: <57EE93C8(dot)8080504(at)postgrespro(dot)ru>

- Avoid testing tuple visibility without buffer lock.  INSERT ... ON CONFLICT
  (specifically ExecCheckHeapTupleVisible) contains another example of this
  unsafe coding practice.  It is much harder to get a failure out of it than the
  case fixed in commit 6292c2339, because in most scenarios any hint bits that
  could be set would have already been set earlier in the command.  However,
  Konstantin Knizhnik reported a failure with a custom transaction manager, and
  it's clearly possible to get a failure via a race condition in async-commit
  mode.  For lack of a reproducible example, no regression test case in this
  commit.  I did some testing with Asserts added to tqual.c's functions, and can
  say that running "make check-world" exposed these two bugs and no others.  The
  Asserts are messy enough that I've not added them to the code for now.
  Report: <57EE93C8(dot)8080504(at)postgrespro(dot)ru> Related-Discussion:

- Release notes for 9.6.1, 9.5.5, 9.4.10, 9.3.15, 9.2.19, 9.1.24.

- Update release notes for last-minute commit timestamp fix.

- Suppress unused-variable warning in non-assert builds.  Introduced in commit
  7012b132d.  Kyotaro Horiguchi

- Doc: improve documentation about inheritance.  Clarify documentation about
  inheritance of check constraints, in particular mentioning the NO INHERIT
  option, which didn't exist when this text was written.  Document that in an
  inherited query, the applicable row security policies are those of the
  explicitly-named table, not its children.  This is the intended behavior (per
  off-list discussion with Stephen Frost), and there are regression tests for
  it, but it wasn't documented anywhere user-facing as far as I could find.  Do
  a bit of wordsmithing on the description of inherited access-privilege checks.
  Back-patch to 9.5 where RLS was added.

- Fix not-HAVE_SYMLINK code in zic.c.  I broke this in commit f3094920a.
  Apparently it's dead code anyway, at least as far as our buildfarm is
  concerned (and the upstream IANA code doesn't worry at all about symlink() not
  being present).  But as long as the rest of our code is willing to guard
  against not having symlink(), this should too.  Noted while investigating a
  tangentially-related complaint from Sandeep Thakkar.  Back-patch to keep
  branches in sync.

- Fix incorrect trigger-property updating in ALTER CONSTRAINT.  The code to
  change the deferrability properties of a foreign-key constraint updated all
  the associated triggers to match; but a moment's examination of the code that
  creates those triggers in the first place shows that only some of them should
  track the constraint's deferrability properties.  This leads to odd failures
  in subsequent exercise of the foreign key, as the triggers are fired at the
  wrong times.  Fix that, and add a regression test comparing the trigger
  properties produced by ALTER CONSTRAINT with those you get by creating the
  constraint as-intended to begin with.  Per report from James Parks.
  Back-patch to 9.4 where this ALTER functionality was introduced.  Report:

- Improve speed of aggregates that use array_append as transition function.  In
  the previous coding, if an aggregate's transition function returned an
  expanded array, nodeAgg.c and nodeWindowAgg.c would always copy it and thus
  force it into the flat representation.  This led to ping-ponging between flat
  and expanded formats, which costs a lot.  For an aggregate using array_append
  as transition function, I measured about a 15X slowdown compared to the
  pre-9.5 code, when working on simple int[] arrays.  Of course, the old code
  was already O(N^2) in this usage due to copying flat arrays all the time, but
  it wasn't quite this inefficient.  To fix, teach nodeAgg.c and nodeWindowAgg.c
  to allow expanded transition values without copying, so long as the transition
  function takes care to return the transition value already properly parented
  under the aggcontext.  That puts a bit of extra responsibility on the
  transition function, but doing it this way allows us to not need any extra
  logic in the fast path of advance_transition_function (ie, with a
  pass-by-value transition value, or with a modified-in-place pass-by-reference
  value).  We already know that that's a hot spot so I'm loath to add any cycles
  at all there.  Also, while only array_append currently knows how to follow
  this convention, this solution allows other transition functions to opt-in
  without needing to have a whitelist in the core aggregation code.  (The reason
  we would need a whitelist is that currently, if you pass a R/W expanded-object
  pointer to an arbitrary function, it's allowed to do anything with it
  including deleting it; that breaks the core agg code's assumption that it
  should free discarded values.  Returning a value under aggcontext is the
  transition function's signal that it knows it is an aggregate transition
  function and will play nice.  Possibly the API rules for expanded objects
  should be refined, but that would not be a back-patchable change.) With this
  fix, an aggregate using array_append is no longer O(N^2), so it's much faster
  than pre-9.5 code rather than much slower.  It's still a bit slower than the
  bespoke infrastructure for array_agg, but the differential seems to be only
  about 10%-20% rather than orders of magnitude.  Discussion:

- Fix bogus tree-flattening logic in QTNTernary().  QTNTernary() contains logic
  to flatten, eg, '(a & b) & c' into 'a & b & c', which is all well and good,
  but it tries to do that to NOT nodes as well, so that '!!a' gets changed to
  '!a'.  Explicitly restrict the conversion to be done only on AND and OR nodes,
  and add a test case illustrating the bug.  In passing, provide some comments
  for the sadly naked functions in tsquery_util.c, and simplify some baroque
  logic in QTNFree(), which I think may have been leaking some items it intended
  to free.  Noted while investigating a complaint from Andreas Seltenreich.
  Back-patch to all supported versions.

- Fix nasty performance problem in tsquery_rewrite().  tsquery_rewrite() tries
  to find matches to subsets of AND/OR conditions; for example, in the query 'a
  | b | c' the substitution subquery 'a | c' should match and lead to
  replacement of the first and third items.  That's fine, but the matching
  algorithm apparently takes about O(2^N) for an N-clause query (I say
  "apparently" because the code is also both unintelligible and uncommented).
  We could probably do better than that even without any extra assumptions ---
  but actually, we know that the subclauses are sorted, indeed are depending on
  that elsewhere in this very same function.  So we can just scan the two lists
  a single time to detect matches, as though we were doing a merge join.  Also
  do a re-flattening call (QTNTernary()) in tsquery_rewrite_query, just to make
  sure that the tree fits the expectations of the next search cycle.  I didn't
  try to devise a test case for this, but I'm pretty sure that the oversight
  could have led to failure to match in some cases where a match would be
  expected.  Improve comments, and also stick a CHECK_FOR_INTERRUPTS into
  dofindsubquery, just in case it's still too slow for somebody.  Per report
  from Andreas Seltenreich.  Back-patch to all supported branches.  Discussion:

Álvaro Herrera pushed:

- Preserve commit timestamps across clean restart.  An oversight in setting the
  boundaries of known commit timestamps during startup caused old commit
  timestamps to become inaccessible after a server restart.  Author and
  reporter: Julien Rouhaud Review, test code: Craig Ringer

Magnus Hagander pushed:

- Use ssize_t where signed results can happen.  Noted by Alexander Korotkov

- Free walmethods before exiting.  Not strictly necessary since we quite after,
  but could become important in the future if we do restarts etc.  Michael
  Paquier with nitpicking from me

- Don't fsync() files when --no-sync is specified.  Michael Paquier

- Fix memory leak in tar file padding.  Spotted by Coverity, patch by Michael

Robert Haas pushed:

- postgres_fdw: Try again to stabilize aggregate pushdown regression tests.  A
  query that only aggregates one row isn't a great argument for pushdown, and
  buildfarm member brolga decides against it.  Adjust the query a bit in the
  hopes of getting remote aggregation to win consistently.  Jeevan Chalke, per
  suggestion from Tom Lane

- Fix possible pg_basebackup failure on standby with "include WAL".  If a
  restartpoint flushed no dirty buffers, it could fail to update the minimum
  recovery point, leading to a minimum recovery point prior to the starting REDO
  location.  perform_base_backup() would interpret that as meaning that no WAL
  files at all needed to be included in the backup, failing an internal sanity
  check.  To fix, have restartpoints always update the minimum recovery point to
  just after the checkpoint record itself, so that the file (or files)
  containing the checkpoint record will always be included in the backup.  Code
  by Amit Kapila, per a design suggestion by me, with some additional work on
  the code comment by me.  Test case by Michael Paquier.  Report by Kyotaro

- If the stats collector dies during Hot Standby, restart it.  This bug exists
  as far back as 9.0, when Hot Standby was introduced, so back-patch to all
  supported branches.  Report and patch by Takayuki Tsunakawa, reviewed by
  Michael Paquier and Kuntal Ghosh.

- Fix leftover reference to background writer performing checkpoints.  This was
  changed in PostgreSQL 9.2, but somehow this comment never got updated.

- pgstattuple: Don't take heavyweight locks when examining a hash index.  It's
  currently necessary to take a heavyweight lock when scanning a hash bucket,
  but pgstattuple only examines individual pages, so it doesn't need to do this.
  If, for some hypothetical reason, it did need to do any heavyweight locking
  here, this logic would probably still be incorrect, because most of the locks
  that it is taking are meaningless.  Only a heavyweight lock on a primary
  bucket page has any meaning, but this takes heavyweight locks on all pages
  regardless of function - and in particular overflow pages, where you might
  imagine that we'd want to lock the primary bucket page if we needed to lock
  anything at all.  This is arguably a bug that has existed since this code was
  added in commit dab42382f483c3070bdce14a4d93c5d0cf61e82b, but I'm not going to
  bother back-patching it because in most cases the only consequence is that
  running pgstattuple() on a hash index is a little slower than it otherwise
  might be, which is no big deal.  Extracted from a vastly larger patch by Amit
  Kapila which heavyweight locking for hash indexes entirely; analysis of why
  this can be done independently of the rest by me.

Bruce Momjian pushed:

- Consistently mention 'SELECT pg_reload_conf()' in config files.  Previously we
  only mentioned SIGHUP and 'pg_ctl reload' in postgresql.conf and pg_hba.conf.

- Properly indent postgresql.conf comments to align.  A few comments were

Peter Eisentraut pushed:

- pg_dump: Simplify internal archive version handling.  The ArchiveHandle
  structure contained the archive format version number twice, once as a single
  field and once split into components.  Simplify that by just keeping the
  single field and adding some macros to extract the components.  Introduce some
  macros for composing version numbers, to eliminate the repeated use of magic
  formulas.  Drop the unused trailing zero byte from the run-time composite
  version representation.  reviewed by Tom Lane

- Avoid using a C++ keyword in header file, per cpluspluscheck.

- Add function name to PyArg_ParseTuple().  This causes the supplied function
  name to appear in any error message, making the error message friendlier and
  relieving us from having to provide our own in some cases.

- Format PL/Python module contents test vertically.  It makes it readable again
  and makes merges more manageable.

- Remove invitation to report a bug about unknown encoding.  The error message
  when we couldn't determine the encoding from a locale said to report a bug
  about that.  That might have been appropriate when this code was first added,
  but by now this works pretty solidly and any encodings we don't recognize we
  probably just don't support.  We still print the warning, but no longer invite
  the bug report.

- doc: Small style improvements

Heikki Linnakangas pushed:

- Support multi-dimensional arrays in PL/python.  Multi-dimensional arrays can
  now be used as arguments to a PL/python function (used to throw an error), and
  they can be returned as nested Python lists.  This makes a
  backwards-incompatible change to the handling of composite types in arrays.
  Previously, you could return an array of composite types as "[[col1, col2],
  [col1, col2]]", but now that is interpreted as a two- dimensional array.
  Composite types in arrays must now be returned as Python tuples, not lists, to
  resolve the ambiguity. I.e. "[(col1, col2), (col1, col2)]".  To avoid breaking
  backwards-compatibility, when not necessary, () is still accepted for arrays
  at the top-level, but it is always treated as a single-dimensional array.
  Likewise, [] is still accepted for composite types, when they are not in an
  array. Update the documentation to recommend using [] for arrays, and () for
  composite types, with a mention that those other things are also accepted in
  some contexts.  This needs to be mentioned in the release notes.  Alexey
  Grishchenko, Dave Cramer and me. Reviewed by Pavel Stehule.  Discussion:

- Give a hint, when [] is incorrectly used for a composite type in array.  That
  used to be accepted, so let's try to give a hint to users on why their
  PL/python functions no longer work.  Reviewed by Pavel Stehule.  Discussion:

- Fix typos in comments.  Vinayak Pokale

- Fix typo in comment.  Daniel Gustafsson

- Fix regression test to also work with Python 2.  Per buildfarm.

- Only treat Python Lists as array dimensions.  Instead of treating all python
  sequence types as array dimensions, except for tuples and various kinds of
  strings, only treat Python lists as dimensions. The PyBytes_Check() function
  used previously is only available on Python 2.6 and newer, and it was a bit
  fiddly anyway. The list of exceptions would require adjustment if Python got a
  new kind of a sequence similar to bytes/unicodes/strings, so only checking for
  Lists seems more future-proof. The documentation only mentioned using Lists,
  so this is closer to what was documented, anyway.  This should fix the
  buildfarm failures on systems building with Python 2.5, although I don't have
  Python 2.5 installed myself to test with.

- Avoid using platform-dependent floats in test case.  The number of decimals
  printed for floats varied in this test case, as noted by several buildfarm
  members. There's nothing special about floats and arrays in the code being
  tested, so replace the floats with numerics to make the output

- Remove platform-dependent PL/python test case.  Turns out that the output
  format of Python Decimal isn't totally platform- independent either. There are
  other tests for multi-dimensional arrays, so rather than try to fix this test
  case, just remove it.  Per buildfarm member prairiedog.

Tatsuo Ishii pushed:

- Fix typo in sources.sgml.  Per Shinichi Matsuda.

== Pending Patches ==

Samuel D. Leslie sent in a patch to add a radiustimeout parameter for RADIUS

Tsutomu Yamada and Ashutosh Bapat traded patches to change a returned NULL to
NIL in create_foreignscan_path(), and a similar issue in a
create_foreignscan_path() call in add_foreign_grouping_paths().

Petr Jelinek sent in another revision of a patch to implement logical

Petr Jelinek sent in another revision of a patch to follow timeline switches in
logical decoding and add a test harness for same.

Laurenz Albe sent in two more revisions of a patch to add PGDLLEXPORT to
PG_FUNCTION_INFO_V1 function declarations.

Haribabu Kommi sent in another revision of a patch to add macaddr 64 bit
(EUI-64) datatype support.

Robert Haas and Victor Wagner traded patches to implement failover on the libpq
connect level.

Peter Geoghegan sent in another revision of a patch to cap the number of tapes
used by external sorts, add parallel B-tree index build sorting, and add a
force_btree_randomaccess GUC for testing.

Amit Kapila sent in two more revisions of a patch to extend the bufmgr api to
include hash index.

Ashutosh Sharma sent in a patch to add microvacuum support for hash indexes.

Amit Kapila sent in another revision of a patch to add group update clog.

Kyotaro HORIGUCHI sent in three more revisions of a patch to implement
asynchronous execution.

Noah Misch sent in a patch to firmly recommend PG_GETARG_TEXT_PP() over

Tomas Vondra sent in another revision of a patch to add two slab-like memory

Venkata B Nagothi sent in a patch to add a recovery_target_incomplete GUC.

Julian Markwort sent in another revision of a patch to allow specifying a
password file location for libpq-based applications.

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

Heikki Linnakangas and Kyotaro HORIGUCHI traded patches to implement a radix
tree for character conversion.

Thomas Munro sent in another revision of a patch to add a replay_lag column to
the pg_stat_replication view.

Claudio Freire sent in another revision of a patch to refetch buffers on
backward scan and allow using more than 1GB work mem in VACUUM.

Peter Eisentraut sent in a patch to add NLS to the remaining bin programs which
were moved from contrib a while ago.

Rushabh Lathia sent in another revision of a patch to implement gather merge.

Etsuro Fujita sent in another revision of a patch to push down more full joins
to the postgres FDW.

Ashutosh Bapat sent in another revision of a patch to pgstat to avoid writing on

Peter Eisentraut sent in a patch to add a user-callable function to import
operation system collations.

Michaël Paquier sent in a patch to apply XLR_INFO_MASK correctly when looking at
WAL record information.

Peter Eisentraut sent in a patch to add rules to download raw UNICODE mapping

Peter Eisentraut sent in a patch to split up psql's \d modifiers column into
more legible pieces.

Haribabu Kommi sent in another revision of a patch to add a pg_hba_file_settings

Michaël Paquier sent in another revision of a patch to add a wal_consistency GUC
with associated machinery.

Mithun Cy sent in two revisions of a patch to add auto_prewarm.

Peter Eisentraut sent in a patch to save redundant code for pseudotype I/O

Ashutosh Bapat sent in a patch to free unused paths in the case of
partition-wise join for join between (declaratively) partitioned tables.

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

Julien Rouhaud sent in a patch to remove pq_putmessage_hook and pq_flush_hook.

Magnus Hagander and Michaël Paquier traded patches to fix an issue where
symlinks in the wrong place could cause streaming backups to fail.

Tomas Vondra sent in another revision of a patch to implement multivariate

Michaël Paquier sent in another revision of a patch to mention column name in
error messages.

Kevin Grittner sent in another revision of a patch to construct and enable the
use of delta relations in AFTER triggers.

Masahiko Sawada sent in another revision of a patch to support 2PC across FDWs.

Kyotaro HORIGUCHI sent in another revision of a patch to refactor tab completion
in psql to use a more principled approach to grammar.

pgsql-announce by date

Next:From: PostgreSQL - Hans-Jürgen SchönigDate: 2016-10-31 10:59:46
Subject: pg_squeeze is out: Fighting table bloat
Previous:From: Dave PageDate: 2016-10-27 13:42:06
Subject: PostgreSQL 2016-10-27 Cumulative Update Release

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group