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

== PostgreSQL Weekly News - January 08 2017 ==

From: David Fetter <david(at)fetter(dot)org>
To: PostgreSQL Announce <pgsql-announce(at)postgresql(dot)org>
Subject: == PostgreSQL Weekly News - January 08 2017 ==
Date: 2017-01-09 20:49:28
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-announce
== PostgreSQL Weekly News - January 08 2017 ==

== PostgreSQL Product News ==

barman 2.1, a backup and recovery manager for PostgreSQL, released.

repmgr 3.3, a replication manager for PostgreSQL, released.

pgquarrel 0.2.0, a tool which compares PostgreSQL database schemas and outputs a
set of commands to turn a database schema into another one, released.

== PostgreSQL Jobs for January ==

== PostgreSQL Local ==

PGConf India 2017 will be on March 2-3, 2017 in Bengaluru, Karnataka.

PostgreSQL(at)SCaLE will take place on March 2-3, 2017, at Pasadena Convention
Center, as part of SCaLE 15X.

PgConf.Russia 2017 will take place on 15-17 March 2017 in Moscow.

PGDay Asia 2017 will be held March 17-18 in Singapore.
The CfP closes January 16, 2017.

Nordic PGDay 2017 will be held in Stockholm, Sweden, at the Sheraton
Hotel, on March 21, 2017.  The CfP is open until January 9, 2017.

pgDay Paris 2017 will be held in Paris, France on March 23, 2017.
The CfP is open until January 9, 2017.

PGCon 2017 will take place in Ottawa on 23-26 May.  Proposals are due by
19 Jan 2017.

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

- In pgbench logging, avoid assuming that instr_times match Unix timestamps.
  For aggregated logging, pg_bench supposed that printing the integer part of
  INSTR_TIME_GET_DOUBLE() would produce a Unix timestamp.  That was already
  broken on Windows, and it's about to get broken on most other platforms as
  well.  As in commit 74baa1e3b, we can remove the entanglement at the price of
  one extra syscall per transaction; though here it seems more convenient to use
  time(NULL) instead of gettimeofday(), since we only need integral-second
  precision.  I took the time to do some wordsmithing on the documentation about
  pgbench's logging features, too.  Discussion:

- Use clock_gettime(), if available, in instr_time measurements.  The advantage
  of clock_gettime() is that the API allows the result to be precise to
  nanoseconds, not just microseconds as in gettimeofday().  Now that it's
  routinely possible to do tens of plan node executions in 1us, we really need
  more precision than gettimeofday() can offer for EXPLAIN ANALYZE to accumulate
  statistics with.  Some research shows that clock_gettime() is available on
  pretty nearly every modern Unix-ish platform, and as far as I have been able
  to test, it has about the same execution time as gettimeofday(), so there's no
  loss in switching over.  (By the same token, this doesn't do anything to fix
  the fact that we really wish clock readings were faster.  But there's enough
  win here to justify changing anyway.) A small side benefit is that on most
  platforms, we can use CLOCK_MONOTONIC instead of CLOCK_REALTIME and thereby
  render EXPLAIN impervious to concurrent resets of the system clock.  (This
  means that code must not assume that the contents of struct instr_time have
  any well-defined interpretation as timestamps, but really that was true
  before.) Some platforms offer nonstandard clock IDs that might be of interest.
  This patch knows we should use CLOCK_MONOTONIC_RAW on macOS, because it
  provides more precision and is faster to read than their CLOCK_MONOTONIC.  If
  there turn out to be many more cases where we need special rules, it might be
  appropriate to handle the selection of clock ID in configure, but for the
  moment that doesn't seem worth the trouble.  Discussion:

- Allow SSL configuration to be updated at SIGHUP.  It is no longer necessary to
  restart the server to enable, disable, or reconfigure SSL.  Instead, we just
  create a new SSL_CTX struct (by re-reading all relevant files) whenever we get
  SIGHUP.  Testing shows that this is fast enough that it shouldn't be a
  problem.  In conjunction with that, downgrade the logic that complains about
  pg_hba.conf "hostssl" lines when SSL isn't active: now that's just a warning
  condition not an error.  An issue that still needs to be addressed is what
  shall we do with passphrase-protected server keys?  As this stands, the server
  would demand the passphrase again on every SIGHUP, which is certainly
  impractical.  But the case was only barely supported before, so that does not
  seem a sufficient reason to hold up committing this patch.  Andreas Karlsson,
  reviewed by Michael Banck and Michael Paquier Discussion:

- Disable prompting for passphrase while (re)loading SSL config files.
  OpenSSL's default behavior when loading a passphrase-protected key file is to
  open /dev/tty and demand the password from there.  It was kinda sorta okay to
  allow that to happen at server start, but really that was never workable in
  standard daemon environments.  And it was a complete fail on Windows, where
  the same thing would happen at every backend launch.  Yesterday's commit
  de41869b6 put the final nail in the coffin by causing that to happen at every
  SIGHUP; even if you've still got a terminal acting as the server's TTY, having
  the postmaster freeze until you enter the passphrase again isn't acceptable.
  Hence, override the default behavior with a callback that returns an empty
  string, ensuring failure.  Change the documentation to say that you can't have
  a passphrase-protected server key, period.  If we can think of a
  production-grade way of collecting a passphrase from somewhere, we might do
  that once at server startup and use this callback to feed it to OpenSSL, but
  it's far from clear that anyone cares enough to invest that much work in the
  feature.  The lack of complaints about the existing fractionally-baked
  behavior suggests nobody's using it anyway.  Discussion:

- Re-allow SSL passphrase prompt at server start, but not thereafter.  Leave
  OpenSSL's default passphrase collection callback in place during the first
  call of secure_initialize() in server startup.  Although that doesn't work
  terribly well in daemon contexts, some people feel we should not break it for
  anyone who was successfully using it before.  We still block passphrase
  demands during SIGHUP, meaning that you can't adjust SSL configuration
  on-the-fly if you used a passphrase, but this is no worse than what it was
  before commit de41869b6.  And we block passphrase demands during EXEC_BACKEND
  reloads; that behavior wasn't useful either, but at least now it's documented.
  Tweak some related log messages for more readability, and avoid issuing
  essentially duplicate messages about reload failure caused by a passphrase.

- Prefer int-wide pg_atomic_flag over char-wide when using gcc intrinsics.
  configure can only probe the existence of gcc intrinsics, not how well they're
  implemented, and unfortunately the answer is sometimes "badly".  In particular
  we've found that multiple compilers fail to implement char-width
  __sync_lock_test_and_set() correctly on PPC; and even a correct implementation
  would necessarily be pretty inefficient, since that hardware has only a
  word-wide primitive to work with.  Given the knowledge we've accumulated in
  s_lock.h, it appears that it's best to rely on int-width TAS operations on
  most non-Intel architectures.  Hence, pick int not char when both are
  nominally available to us in generic-gcc.h (note that that code is not used
  for x86[_64]).  Back-patch to fix regression test failures on FreeBSD/PPC.
  Ordinarily back-patching a change like this would be verboten because of ABI
  breakage.  But since pg_atomic_flag is not yet used in any Postgres data
  structure, there's no ABI to break.  It seems safer to back-patch to avoid
  possible gotchas, if someday we do back-patch something that uses
  pg_atomic_flag.  Discussion:

- Handle OID column inheritance correctly in ALTER TABLE ... INHERIT.
  Inheritance operations must treat the OID column, if any, much like regular
  user columns.  But MergeAttributesIntoExisting() neglected to do that, leading
  to weird results after a table with OIDs is associated to a parent with OIDs
  via ALTER TABLE ... INHERIT.  Report and patch by Amit Langote, reviewed by
  Ashutosh Bapat, some adjustments by me.  It's been broken all along, so
  back-patch to all supported branches.  Discussion:

- Fix handling of empty arrays in array_fill().  array_fill(..., array[0])
  produced an empty array, which is probably what users expect, but it was a
  one-dimensional zero-length array which is not our standard representation of
  empty arrays.  Also, for no very good reason, it rejected empty input arrays;
  that case should be allowed and produce an empty output array.  In passing,
  remove the restriction that the input array(s) have lower bound 1.  That seems
  rather pointless, and it would have needed extra complexity to make the check
  deal with empty input arrays.  Per bug #14487 from Andrew Gierth.  It's been
  broken all along, so back-patch to all supported branches.  Discussion:

- Invalidate cached plans on FDW option changes.  This fixes problems where a
  plan must change but fails to do so, as seen in a bug report from Rajkumar
  Raghuwanshi.  For ALTER FOREIGN TABLE OPTIONS, do this through the standard
  method of forcing a relcache flush on the table.  For ALTER FOREIGN DATA
  WRAPPER and ALTER SERVER, just flush the whole plan cache on any change in
  pg_foreign_data_wrapper or pg_foreign_server.  That matches the way we handle
  some other low-probability cases such as opclass changes, and it's unclear
  that the case arises often enough to be worth working harder.  Besides, that
  gives a patch that is simple enough to back-patch with confidence.  Back-patch
  to 9.3.  In principle we could apply the code change to 9.2 as well, but (a)
  we lack postgres_fdw to test it with, (b) it's doubtful that anyone is doing
  anything exciting enough with FDWs that far back to need this desperately, and
  (c) the patch doesn't apply cleanly.  Patch originally by Amit Langote,
  reviewed by Etsuro Fujita and Ashutosh Bapat, who each contributed substantial
  changes as well.  Discussion:

- Merge two copies of tuple-building code in pltcl.c.  Make
  pltcl_trigger_handler() construct modified tuples using
  pltcl_build_tuple_result(), rather than its own copy of essentially the same
  logic.  This results in slightly different message wording for the error
  cases, and in one case a different SQLSTATE, but it seems unlikely that any
  existing applications are depending on any of those details.  While at it, fix
  a typo in commit 26abb50c4: pltcl_build_tuple_result was applying encoding
  conversion in the wrong direction.  That would be a back-patchable bug fix,
  except the code hasn't shipped yet.  Jim Nasby, reviewed by me Discussion:

- Get rid of ParseState.p_value_substitute; use a columnref hook instead.  I
  noticed that p_value_substitute, which is a single-purpose kluge I added in
  2002 (commit b0422b215), could be replaced by having domainAddConstraint
  install a parser hook that looks for the name "value".  The parser hook code
  only dates back to 2009, so it's not surprising that we had to kluge this in
  2002, but we can do it more cleanly now.

- Improve documentation of struct ParseState.  I got annoyed about how some
  fields of ParseState were documented in the struct's block comment and some
  weren't; not all of the latter are trivial.  Fix that.  Also reorder a couple
  of fields that seem to have been placed rather randomly, or maybe with an idea
  of avoiding padding space; but there are never so many ParseStates in
  existence at one time that we ought to value pad space over readability.

Heikki Linnakangas pushed:

- Remove bogus notice that older clients might not work with MD5 passwords.
  That was written when we still had "crypt" authentication, and it was
  referring to the fact that an older client might support "crypt"
  authentication but not "md5". But we haven't supported "crypt" for years.  (As
  soon as we add a new authentication mechanism that doesn't work with MD5
  hashes, we'll need a similar notice again. But this text as it's worded now is
  just wrong.) Backpatch to all supported versions.  Discussion:

Peter Eisentraut pushed:

- Fix hot standby tests for sequence catalog change.  From: Kuntal Ghosh

- Better fix for sequence access in hot standby test.  The purpose of the test
  was to check access to the sequence relation on a hot standby, so change the
  test to read a different column from the sequence, instead of just reading the
  catalog.  From: Andreas Karlsson <andreas(at)proxel(dot)se>

- Use 'use strict' in all Perl programs

Bruce Momjian pushed:

- Update copyright for 2017.  Backpatch-through: certain files through 9.2

- Update manual set of copyright files for 2017

Magnus Hagander pushed:

- Make wal streaming the default mode for pg_basebackup.  Since streaming is now
  supported for all output formats, make this the default as this is what most
  people want.  To get the old behavior, the parameter -X none can be specified
  to turn it off.  This also removes the parameter -x for fetch, now requiring
  -X fetch to be specified to use that.  Reviewed by Vladimir Rusinov, Michael
  Paquier and Simon Riggs

- Attempt to handle pending-delete files on Windows.  These files are deleted
  but not yet gone from the filesystem. Operations on them will return
  ERROR_DELETE_PENDING.  With this we start treating that as ENOENT, meaning
  files does not exist (which is the state it will soon reach). This should be
  safe in every case except when we try to recreate a file with exactly the same
  name. This is an operation that PostgreSQL does very seldom, so hopefully that
  won't happen much -- and even if it does, this treatment should be no worse
  than treating it as an unhandled error.  We've been un able to reproduce the
  bug reliably, so pushing this to master to get buildfarm coverage and other
  testing. Once it's proven to be stable, it should be considered for
  backpatching.  Discussion: Patch
  by me and Michael Paquier

Simon Riggs pushed:

- Allow tests to wait for catchup.  Add methods to the core test
  framework to allow us to test that standby nodes have caught
  up with the master, as well as basic LSN handling.  Used in tests
  recovery/t/ and recovery/t/ Craig
  Ringer, reviewed by Aleksander Alekseev and Simon Riggs

- Add 18 new recovery TAP tests.  Add new tests for physical repl slots and hot
  standby feedback.  Craig Ringer, reviewed by Aleksander Alekseev and Simon

- Add pg_recvlogical —-endpos=LSN.  Allow pg_recvlogical to specify an ending
  LSN, complementing the existing -—startpos=LSN option.  Craig Ringer, reviewed
  by Euler Taveira and Naoki Okano

- Add new TAP tests for pg_recvlogical.  Craig Ringer, reviewed by Euler Taveira
  and Naoki Okano

- Fix format for TAP test docs.  Small number of fixes to perl docs for TAP
  tests.  Plus two comments that use "xlog" rather than WAL Michael Paquier

Robert Haas pushed:

- Update obsolete comments in lwlock.h.  The typical size of an LWLock is now 16
  bytes even on 64-bit platforms, and the size of slock_t is now irrelevant.
  But pg_atomic_uint32 can (perhaps surprisingly) still be larger than 4 bytes,
  so there's still some marginal point to allowing LWLOCK_MINIMAL_SIZE == 64.
  Commit 008608b9d51061b1f598c197477b3dc7be9c4a64 made the changes that led to
  the need for these updates.

- Move partition_tuple_slot out of EState.  Commit
  2ac3ef7a01df859c62d0a02333b646d65eaec5ff added a TupleTapleSlot for partition
  tuple slot to EState (es_partition_tuple_slot) but it's more logical to have
  it as part of ModifyTableState (mt_partition_tuple_slot) and CopyState
  (partition_tuple_slot).  Discussion: Amit
  Langote, per a gripe from me

- Fix reporting of constraint violations for table partitioning.  After a tuple
  is routed to a partition, it has been converted from the root table's row type
  to the partition's row type.  ExecConstraints needs to report the failure
  using the original tuple and the parent's tuple descriptor rather than the
  ones for the selected partition.  Amit Langote

- Remove unnecessary arguments from partitioning functions.
  RelationGetPartitionQual() and generate_partition_qual() are always called
  with recurse = true, so we don't need an argument for that.  Extracted by me
  from a larger patch by Amit Langote.

- Assorted code improvements for table partitioning.  Michael Paquier, per

- Improve documentation of timestamp internal representation.  Be more clear
  that we represent timestamps in microseconds when integer timestamps are used,
  and in fractional seconds when floating-point timestamps are used.
  Discussion: Report
  by Alexander Alekseev.  Wording by me with a suggestion from Tom Lane.

- Fix possible crash reading pg_stat_activity.  With the old code, a backend
  that read pg_stat_activity without ever having executed a parallel query might
  see a backend in the midst of executing one waiting on a DSA LWLock, resulting
  in a crash.  The solution is for backends to register the tranche at startup
  time, not the first time a parallel query is executed.  Report by Andreas
  Seltenreich.  Patch by me, reviewed by Thomas Munro.

- Fix possible leak of semaphore count.  Commit
  4aec49899e5782247e134f94ce1c6ee926f88e1c reorganized the order of operations
  here so that we no longer increment the number of "extra waits" before locking
  the semaphore, but it did not change the starting value of extraWaits from 0
  to -1 to compensate.  In the worst case, this could leak a semaphore count,
  but that seems to be unlikely in practice.  Discussion:
  Amit Kapila, per an off-list report by Dilip Kumar.  Reviewed by me.

- Repair commit b81b5a96f424531b97cdd1dba97d9d1b9c9d372e.  This commit purported
  to use a variable hash seed for Partial HashAggregate, but actually did the
  opposite - it made us use a variable seed for any HashAggregate that is NOT
  partial.  Woops.

Stephen Frost pushed:

- Protect against NULL-dereference in pg_dump.  findTableByOid() is allowed to
  return NULL and we should therefore be checking for that case.  getOwnedSeqs()
  and dumpSequence() shouldn't ever actually see this happen, but given odd
  circumstances it might and commit f9e439b1 probably shouldn't have removed
  that check.  Pointed out by Coverity.  Initial patch from Michael Paquier.
  Back-patch to 9.6, where that commit had removed the check.

- Add basic pg_dumpall/pg_restore TAP tests.  For reasons unknown, pg_dumpall
  and pg_restore managed to escape the basic set of TAP tests that were added
  for pg_dump in 6bd356c3, so let's get them added now.  A few minor adjustments
  are also made to the dump/restore tests to improve code coverage for

== Pending Patches ==

Thomas Munro sent in a patch to add an isolation test for SERIALIZABLE READ ONLY

Ashutosh Bapat sent in another revision of a patch to support partition-wise
join between multi-level partitioned tables.

Thomas Munro sent in another revision of a patch to implement causal reads.

Pavan Deolasee sent in two more revisions of a patch to implement WARM.

Ashutosh Bapat sent in a patch to cast nodes more safely.

Thomas Munro sent in another revision of a patch to help measure replay lag.

KaiGai Kohei sent in another revision of a patch to add PassDownLimitBound for

Mithun Cy sent in two more revisions of a patch to cache hash index meta page.

Amit Kapila sent in another revision of a patch to parallelize queries
containing subplans.

Peter Geoghegan sent in another revision of a patch to implement parallel

Tom Lane sent in a patch to expand the existing API to allow the AM to return
either a heap or index tuple.

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

Heikki Linnakangas sent in a patch to replace isMD5() with a more future-proof
way to check if password is encrypted and use "plain:" prefix for plaintext
passwords stored in pg_authid.

Dilip Kumar sent in another revision of a patch to implement parallel bitmap
heap scan.

Craig Ringer sent in two more revisions of a patch to implement logical decoding
on standbys.

Peter Eisentraut sent in two revisions of a patch to fix some infelicities in
the logical replication protocol and output plugin.

Peter Eisentraut sent in another revision of a patch to add logical replication

Peter Eisentraut and Petr Jelínek traded patches to add PUBLICATION catalogs and

Álvaro Herrera sent in three revisions of a patch to fix and error in ALTER

Haribabu Kommi sent in another revision of a patch to implement a
pg_hba_file_settings view and add TAP tests for same.

David Fetter sent in two more revisions of a patch to disable simple UPDATEs and
DELETEs which lack any WHERE clause.

Peter Eisentraut sent in another revision of a patch to fix some infelicities
between pg_basebackups and slots.

Dilip Kumar sent in another revision of a patch to implement parallel merge

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

Craig Ringer sent in another revision of a patch to add PostgresNode methods to
wait for node catchup and expand streaming replication tests to cover hot
standby feedback and physical replication slots.

Michaël Paquier sent in another revision of a patch to bring the TAP test docs for
PostgresNode up to date.

Peter Eisentraut sent in another revision of a patch to generate fmgr prototypes

Masahiko Sawada sent in a patch to add a GUC for cleanup indexes threshold.

Ashutosh Bapat sent in a patch to add an option to EXPLAIN: SUMMARY option which
behaves as: SUMMARY when ON prints planning time, and prints planning time in

Takayuki Tsunakawa sent in two more revisions of a patch to support huge pages
on Windows.

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

Ashutosh Sharma sent in two more revisions of a patch to add microvacuum support
for hash indexes.

Jesper Pedersen sent in another revision of a patch to add support for hash
index in pageinspect contrib module.

Tatsuo Ishii sent in a patch to fix a mistaken tag in the documentation for
user name maps.

Vitaly Burovoy sent in another revision of a patch to add check for overflow to
'interval' functions.

Andrew Gierth sent in a patch to add hash support for grouping sets.

Jim Nasby sent in another revision of a patch to implement faster methods for
getting SPI results in PL/PythonU.

Vladimir Rusinov sent in another revision of a patch to rename things xlog to
things wal.

Jonathon Nelson sent in a patch to guc-ify the formerly hard-coded MAX_SEND_SIZE
to max_wal_send.

Takeshi Ideriha sent in another revision of a patch to add DECLARE STATEMENT
setting up a connection in ECPG.

Amit Langote sent in a patch to fix up some infelicities in declarative

Ashutosh Sharma sent in another revision of a patch to add pgstathashindex() to
get hash index table statistics.

Thomas Munro sent in another revision of a patch to implement barriers.

Thomas Munro sent in another revision of a patch to add parallel shared hash.

Peter Geoghegan sent in a patch to fix a subtle bug in "Simplify tape block
format" commit.

Masahiko Sawada sent in another revision of a patch to enable block level
parallel vacuum.

Michaël Paquier sent in another revision of a patch to add compression levels to

Amul Sul sent in two more revisions of a patch to add a pg_background contrib

Pavel Stěhule sent in a patch to add pragmas to PL/pgsql.

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

Tom Lane sent in a patch to disallow output columns of type UNKNOWN.

Ryan Murphy sent in a patch to show type name and constraints for errors in
inherited tables.

Jim Nasby sent in two more revisions of a patch to add more test coverage for

Victor Wagner sent in two revisions of a patch to add explicit subtransactions
to PL/Tcl.

Haribabu Kommi sent in a patch to fix a bug in the patch to add a
pg_hba_settings view.

pgsql-announce by date

Next:From: Christoph BergDate: 2017-01-10 14:42:21
Subject: postgresql-unit 2.0 released
Previous:From: Umair ShahidDate: 2017-01-06 08:33:30
Subject: Announcing The Release Of repmgr 3.3

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