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

== PostgreSQL Weekly News - February 19 2017 ==

From: David Fetter <david(at)fetter(dot)org>
To: PostgreSQL Announce <pgsql-announce(at)postgresql(dot)org>
Subject: == PostgreSQL Weekly News - February 19 2017 ==
Date: 2017-02-19 23:51:35
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-announce
== PostgreSQL Weekly News - February 19 2017 ==

== PostgreSQL Product News ==

Ora2Pg 18.1, a tool for migrating Oracle databases to PostgreSQL, released.

psycopg2 2.7 beta2, a Python connector for PostgreSQL, released.

== PostgreSQL Jobs for February ==

== PostgreSQL Local ==

The first pgDay Israel 2017 will take place on March 2, 2017.  Registration is

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.

Nordic PGDay 2017 will be held in Stockholm, Sweden, at the Sheraton
Hotel, on March 21, 2017.

pgDay Paris 2017 will be held in Paris, France on March 23, 2017.

PGConf US 2017 CfP will be on March 28-31 in Jersey City, New Jersey.

PGCon 2017 will take place in Ottawa on 23-26 May.

Postgres Vision will take place in Boston, June 26 - 28, 2017.
the CfP is open until February 24, 2017 at CFP AT PostgresVision DOT com.

Swiss PGDay in Rapperswil will take place June 30, 2017.
The CfP is open through April 14, 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 ==

Peter Eisentraut pushed:

- doc: Remove accidental extra table cell.

- Add CREATE COLLATION IF NOT EXISTS clause.  The core of the functionality was
  already implemented when pg_import_system_collations was added.  This just
  exposes it as an option in the SQL command.

- doc: Add advice about systemd RemoveIPC.  Reviewed-by: Magnus Hagander

- Fix typo on comment.

- Remove redundant coverage target.  This was probably forgotten to be removed
  when general recursion support was added.

- pg_dump: Fix typo in query.  This could lead to incorrect dumping of language
  privileges in some cases, which is probably a rare situation.

- pg_dump: Message style improvements.

- doc: Fix typos.  From: Thom Brown <thom(at)linux(dot)com>

- Optimize query for information_schema.constraint_column_usage.  The way the
  old query was written prevented some join optimizations because the join
  conditions were hidden inside a CASE expression.  With a large number of
  constraints, the query became unreasonably slow.  The new query performs much
  better.  From: Alexey Bashtanov <bashtanov(at)imap(dot)cc> Reviewed-by: Ashutosh
  Bapat <ashutosh(dot)bapat(at)enterprisedb(dot)com>

Robert Haas pushed:

- Remove contrib/tsearch2.  This module was intended to ease migrations of
  applications that used the pre-8.3 version of text search to the in-core
  version introduced in that release.  However, since all pre-8.3 releases of
  the database have been out of support for more than 5 years at this point, we
  expect that few people are depending on it at this point.  If some people
  still need it, nothing prevents it from being maintained as a separate
  extension, outside of core.  Discussion:

- Corrections and improvements to generic parallel query documentation.  David
  Rowley, reviewed by Brad DeJong, Amit Kapila, and me.  Discussion:

- Don't disallow dropping NOT NULL for a list partition key.  Range partitioning
  doesn't support nulls in the partitioning columns, but list partitioning does.
  Amit Langote, per a complaint from Amul Sul

- Minor fixes for WAL consistency checking.  Michael Paquier, reviewed and
  slightly revised by me.  Discussion:

- Split index xlog headers from other private index headers.  The xlog-specific
  headers need to be included in both frontend code - specifically, pg_waldump -
  and the backend, but the remainder of the private headers for each index are
  only needed by the backend.  By splitting the xlog stuff out into separate
  headers, pg_waldump pulls in fewer backend headers, which is a good thing.
  Patch by me, reviewed by Michael Paquier and Andres Freund, per a complaint
  from Dilip Kumar.  Discussion:

- Allow parallel workers to execute subplans.  This doesn't do anything to make
  Param nodes anything other than parallel-restricted, so this only helps with
  uncorrelated subplans, and it's not necessarily very cheap because each worker
  will run the subplan separately (just as a Hash Join will build a separate
  copy of the hash table in each participating process), but it's a first step
  toward supporting cases that are more likely to help in practice, and is
  occasionally useful on its own.  Amit Kapila, reviewed and tested by Rafia
  Sabih, Dilip Kumar, and me.  Discussion:

- Fix typo in comment.  Higuchi Daisuke

- btree: Support parallel index scans.  This isn't exposed to the optimizer or
  the executor yet; we'll add support for those things in a separate patch.  But
  this puts the basic mechanism in place: several processes can attach to a
  parallel btree index scan, and each one will get a subset of the tuples that
  would have been produced by a non-parallel scan.  Each index page becomes the
  responsibility of a single worker, which then returns all of the TIDs on that
  page.  Rahila Syed, Amit Kapila, Robert Haas, reviewed and tested by Anastasia
  Lubennikova, Tushar Ahuja, and Haribabu Kommi.

- Fix some nonstandard capitalization.  Ashutosh Bapat

- pg_upgrade: Fix problems caused by renaming pg_resetxlog.  Commit
  85c11324cabaddcfaf3347df78555b30d27c5b5a renamed pg_resetxlog to pg_resetwal,
  but didn't make pg_upgrade smart enough to cope with the situation.  Michael
  Paquier, per a complaint from Jeff Janes

- libpq: Make target_session_attrs=read-write consume empty result.  Otherwise,
  the leftover empty result can cause problems in some situations.  Michael
  Paquier and Ashutosh Bapat, per a report from Higuchi Daisuke

- Document new libpq connection statuses for target_session_attrs.  I didn't
  realize these would ever be visible to clients, but Michael figured out that
  it can happen when using asynchronous interfaces such as PQconnectPoll.
  Michael Paquier

- Fix wrong articles in pg_proc descriptions.  This technically should involve a
  catversion bump, but that seems pedantic, so I skipped it.  Report and patch
  by David Christensen.

- Replace min_parallel_relation_size with two new GUCs.  When
  min_parallel_relation_size was added, the only supported type of parallel scan
  was a parallel sequential scan, but there are pending patches for parallel
  index scan, parallel index-only scan, and parallel bitmap heap scan.  Those
  patches introduce two new types of complications: first, what's relevant is
  not really the total size of the relation but the portion of it that we will
  scan; and second, index pages and heap pages shouldn't necessarily be treated
  in exactly the same way.  Typically, the number of index pages will be quite
  small, but that doesn't necessarily mean that a parallel index scan can't pay
  off.  Therefore, we introduce min_parallel_table_scan_size, which works out a
  degree of parallelism for scans based on the number of table pages that will
  be scanned (and which is therefore equivalent to min_parallel_relation_size
  for parallel sequential scans) and also min_parallel_index_scan_size which can
  be used to work out a degree of parallelism based on the number of index pages
  that will be scanned.  Amit Kapila and Robert Haas Discussion:

- Add optimizer and executor support for parallel index scans.  In combination
  with 569174f1be92be93f5366212cc46960d28a5c5cd, which taught the btree AM how
  to perform parallel index scans, this allows parallel index scan plans on
  btree indexes.  This infrastructure should be general enough to support
  parallel index scans for other index AMs as well, if someone updates them to
  support parallel scans.  Amit Kapila, reviewed and tested by Anastasia
  Lubennikova, Tushar Ahuja, and Haribabu Kommi, and me.

- Avoid crash in ALTER TABLE not_partitioned DETACH PARTITION.  Amit Langote,
  reviewed and slightly changed by me.

- Add new function dsa_allocate0.  This does the same thing as dsa_allocate,
  except that the memory is guaranteed to be zero-filled on return.  Dilip
  Kumar, adjusted by me.

- Make dsa_allocate interface more like MemoryContextAlloc.  A new function
  dsa_allocate_extended now takes flags which indicate that huge allocations
  should be permitted, that out-of-memory conditions should not throw an error,
  and/or that the returned memory should be zero-filled, just like
  MemoryContextAllocateExtended.  Commit
  9acb85597f1223ac26a5b19a9345849c43d0ff54, which added dsa_allocate0, was
  broken because it failed to account for the possibility that dsa_allocate()
  might return InvalidDsaPointer.  This fixes that problem along the way.
  Thomas Munro, with some comment changes by me.  Discussion:

- Add optimizer and executor support for parallel index-only scans.  Commit
  5262f7a4fc44f651241d2ff1fa688dd664a34874 added similar support for parallel
  index scans; this extends that work to index-only scans.  As with parallel
  index scans, this requires support from the index AM, so currently parallel
  index-only scans will only be possible for btree indexes.  Rafia Sabih,
  reviewed and tested by Rahila Syed, Tushar Ahuja, and Amit Kapila Discussion:

- Make partitions automatically inherit OIDs.  Previously, if the parent was
  specified as WITH OIDS, each child also had to be explicitly specified as WITH
  OIDS.  Amit Langote, per a report from Simon Riggs.  Some additional work on
  the documentation changes by me.  Discussion:

Fujii Masao pushed:

- Replace references to "xlog" with "wal" in docs.  Commit
  f82ec32ac30ae7e3ec7c84067192535b2ff8ec0e renamed the pg_xlog directory to
  pg_wal. To make things consistent, we decided to eliminate "xlog" from
  user-visible docs.

- Replace reference to "xlog-method" with "wal-method" in error message.  Commit
  62e8b38 renamed "--xlog-method" option for pg_basebackup to "--wal-method",
  but forgot to update the error message mentioning that option.

Tom Lane pushed:

- Remove duplicate code in planner.c.  I noticed while hacking on join UNION
  transforms that planner.c's function get_base_rel_indexes() just duplicates
  the functionality of get_relids_in_jointree().  It doesn't even have the
  excuse of being older code :-(.  Drop it and use the latter function instead.

- Fix YA unwanted behavioral difference with operator_precedence_warning.  Jeff
  Janes noted that the error cursor position shown for some errors would vary
  when operator_precedence_warning is turned on.  We'd prefer that option to
  have no undocumented effects, so this isn't desirable.  To fix, make sure that
  an AEXPR_PAREN node has the same exprLocation as its child node.  (Note: it
  would be a little cheaper to use @2 here instead of an exprLocation call, but
  there are cases where that wouldn't produce the identical answer, so don't do
  it like that.) Back-patch to 9.5 where this feature was introduced.

- Fix tab completion for "ALTER SYSTEM SET variable ...".  It wouldn't complete
  "TO" after the variable name, which is certainly minor enough.  But since we
  do complete "TO" after "SET variable ...", and since this case used to work
  pre-9.6, I think this is a bug.  Also, fix the query used to collect the
  variable names; whoever last touched it evidently didn't understand how the
  pieces are supposed to fit together.  It accidentally worked anyway, because
  readline ignores irrelevant completions, but it was randomly unlike the ones
  around it, and could be a source of actual bugs if someone copied it as a
  prototype for another query.

- Doc: fix syntax synopsis for INSERT ... ON CONFLICT DO UPDATE.  Commit
  906bfcad7 adjusted the syntax synopsis for UPDATE, but missed the fact that
  the INSERT synopsis now contains a duplicate of that.  In passing, improve
  wording and markup about using a table alias to dodge the conflict with use of
  "excluded" as a special table name.

- Make sure that hash join's bulk-tuple-transfer loops are interruptible.  The
  loops in ExecHashJoinNewBatch(), ExecHashIncreaseNumBatches(), and
  ExecHashRemoveNextSkewBucket() are all capable of iterating over many tuples
  without ever doing a CHECK_FOR_INTERRUPTS, so that the backend might fail to
  respond to SIGINT or SIGTERM for an unreasonably long time.  Fix that.  In the
  case of ExecHashJoinNewBatch(), it seems useful to put the added
  CHECK_FOR_INTERRUPTS into ExecHashJoinGetSavedTuple() rather than directly in
  the loop, because that will also ensure that both principal code paths through
  ExecHashJoinOuterGetTuple() will do a CHECK_FOR_INTERRUPTS, which seems like a
  good idea to avoid surprises.  Back-patch to all supported branches.  Tom Lane
  and Thomas Munro Discussion:

- Doc: fix typo in logicaldecoding.sgml.  There's no such field as
  OutputPluginOptions.output_mode; it's actually output_type.  Noted by T.
  Katsumata.  Discussion:

- Formatting and docs corrections for logical decoding output plugins.  Make the
  typedefs for output plugins consistent with project style; they were
  previously not even consistent with each other as to layout or inclusion of
  parameter names.  Make the documentation look the same, and fix errors therein
  (missing and misdescribed parameters).  Back-patch because of the
  documentation bugs.

- Doc: remove duplicate index entry.  This causes a warning with the old
  html-docs toolchain, though not with the new.  I had originally supposed that
  we needed both <indexterm> entries to get both a primary index entry and a
  see-also link; but evidently not, as pointed out by Fabien Coelho.

- Document usage of COPT environment variable for adjusting configure flags.
  Also add to the existing rather half-baked description of PROFILE, which does
  exactly the same thing, but I think people use it differently.  Discussion:

- Suppress "unused variable" warnings with older versions of flex.  Versions of
  flex before 2.5.36 might generate code that results in an "unused variable"
  warning, when using %option reentrant.  Historically we've worked around that
  by specifying -Wno-error, but that's an unsatisfying solution.  The official
  "fix" for this was just to insert a dummy reference to the variable, so write
  a small perl script that edits the generated C code similarly.  The MSVC side
  of this is untested, but the buildfarm should soon reveal if I broke that.

- Adjust PL/Tcl regression test to dodge a possible bug or zone dependency.  One
  case in the PL/Tcl tests is observed to fail on RHEL5 with a Turkish time zone
  setting.  It's not clear if this is an old Tcl bug or something odd about the
  zone data, but in any case that test is meant to see if the Tcl [clock]
  command works at all, not what its corner-case behaviors are.  Therefore we
  have no need to test exactly which week a Sunday midnight is considered to
  fall into.  Probe the following Tuesday instead.  Discussion:

- Dept of second thoughts: rename new perl script.  It didn't take long at all
  for me to become irritated that the original choice of name for this script
  resulted in "warning" showing up in several places in build logs, because I
  tend to grep for that.  Change the script name to avoid that.

- Make src/interfaces/libpq/test clean up after itself.  It failed to remove a
  .o file during "make clean", and it lacked a .gitignore file entirely.

Magnus Hagander pushed:

- Fix help message for pg_basebackup -R.  The recovery.conf file that's
  generated is specifically for replication, and not needed (or wanted) for
  regular backup restore, so indicate that in the message.

== Pending Patches ==

Michaël Paquier sent in another revision of a patch to fix an issue where 2PC
files could be lost that stemmed from the fact that unlink() is not guaranteed
to be durable.

Corey Huinker sent in two more revisions of a patch to add \if and friends to

Ashutosh Sharma sent in a patch to scan has indexes a page at a time, where the
previous scans did tuple-at-a-time.

Amit Langote sent in a patch to add relkind checks to certain contrib modules.

Seki Eiji sent in two revisions of a patch to add a GetOldestXmin option which
ignores arbitrary vacuum flags.

Thomas Munro sent in another revision of a patch to implement [[Parallel]
Shared] Hash.

Amit Khandekar sent in a patch to enable UPDATEs on partitioned tables that
would cause a tuple to move from one partition to another.

Vaishnavi Prabakaran and Aya Iwata traded patches to add batch/pipelining
support for libpq.

Amit Kapila sent in three more revisions of a patch to add WAL support for hash

Alexander Korotkov sent in another revision of a patch to cacheline-align

Kyotaro HORIGUCHI and Peter Eisentraut traded patches to make it possible for
logical encoding to do the right thing when the encodings of the origin and
replica don't match.

Pavel Stěhule sent in another revision of a patch to make it possible to set a
template database for pg_regress.

Dilip Kumar sent in three more revisions of a patch to implement parallel bitmap
heap scan.

Michaël Paquier sent in a patch to fix some issues with the WAL consistency
check facility.

Amit Langote and Ashutosh Bapat traded patches to improve the documentation for

Erik Rijkers sent in a patch to fix the docs for CREATE SUBSCRIPTION.

Kyotaro HORIGUCHI sent in another revision of a patch to refactor tab completion
in psql.

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

Tom Lane sent in another revision of a patch to improve OR conditions on joined

Haribabu Kommi sent in another revision of a patch to add a pg_stat_wal_write
statistics view.

Masahiko Sawada sent in another revision of a patch to implement transactions
involving multiple postgres foreign servers.

Jeff Janes and Neha Khatri traded patches to fix an infelicity between
bytea_output and make installcheck.

Michaël Paquier sent in another revision of a patch to fix an issue in
PQsendQuery where an error occurs when target_session_attrs is set to

Masahiko Sawada sent in two more revisions of a patch to fix an infelicity

Robert Haas and Amit Kapila traded patches to implement parallel index scans.

Thomas Munro sent in two more revisions of a patch to help measure replay lag.

Kuntal Ghosh sent in a patch to add infrastructure required to expose
non-backend processes in pg_stat_activity, use same to expose stats for
auxiliary processes in pg_stat_get_activity, expose stats for autovacuum
launcher and bgworker, and add a proc_type column in pg_stat_get_activity.

Peter Eisentraut sent in a patch to add a max_worker_processes_per_user setting.

Peter Eisentraut sent in a patch to add errcontext to background worker
registration, and hange failures in RegisterBackgroundWorker() to hard errors.

David Christensen sent in a patch to fix some DESCR() grammar mistakes
introduced by the xlog -> wal changes.

Anastasia Lubennikova sent in another revision of a patch to add an IF NOT

Pavel Stěhule sent in another revision of a patch to implement xmltable().

Thomas Munro sent in another revision of a patch to make it possible to run
parallel queries in SERIALIZABLE isolation mode.

Peter Eisentraut sent in another revision of a patch to implement ICU

Haribabu Kommi sent in a patch to allow parallel writers by separating concerns
so that the backend does the write operations, while the workers produce the

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

David Christensen sent in a patch to teach how many attributes there
should be per DATA() line.

Heikki Linnakangas sent in another revision of a patch to implement SCRAM auth.

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

Anastasia Lubennikova sent in another revision of a patch to implement covering
and unique indexes.

Amit Kapila sent in two revisions of a patch to ensure that ON CONFLICT DO
NOTHING work with partitioned tables.

Rafia Sabih sent in three more revisions of a patch to implement parallel
index-only scan.

Tom Lane sent in two revisions of a patch to avoiding OOM in a hash join with
many duplicate inner keys.

Rushabh Lathia sent in two more revisions of a patch to implement gather merge.

Rafia Sabih sent in another revision of a patch to allow query string to

Surafel Temsgen sent in another revision of a patch to implement CORRESPONDING.

David Christensen sent in two revisions of a patch to add pg_disable_checksums()
and supporting infrastructure.

KaiGai Kohei sent in another revision of a patch to implement a
ParallelFinish-hook for FDW/CSP.

Amit Langote sent in a patch to fix the fact that pg_dump would emit ALTER TABLE
ONLY for partitioned tables.

Mithun Cy sent in a PoC patch to use a better way to expand hash indexes.

Peter Eisentraut sent in a patch to implement DCL for logical replication.

Amit Khandekar sent in another revision of a patch to implement parallel append.

Michaël Paquier sent in a patch to implement SASLprep aka NFKC for SCRAM

Tom Lane sent in a patch to add an InterTimestamp struct to fix an issue with
floating point timestamps in logical replication.

Alexander Korotkov sent in another revision of a patch to implement incremental

Jim Nasby sent in a patch to deal with the fact that pg_get_object_address()
doesn't support composites.

Robert Haas sent in a patch to fix an instability in the select_parallel
regression test by ensuring that workers get forgotten faster.

Robins Tharakan sent in a patch to add a --no-pgauthid to pg_dumpall, which will
make it at least not fail immediately on Amazon RDS Postgres and similar systems.

Erik Rijkers sent in a patch to fix some comments in origin.c and snapbuild.c

Neha Khatri sent in a patch to fix a typo in varlena.c.

pgsql-announce by date

Next:From: Christoph BergDate: 2017-02-23 11:01:41
Subject: Development snapshot packages for postgresql-10 available on
Previous:From: Gilles DaroldDate: 2017-02-17 14:30:49
Subject: Ora2Pg v18.1 has been released

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