== PostgreSQL Weekly News - December 15 2013 ==

От: David Fetter
Тема: == PostgreSQL Weekly News - December 15 2013 ==
Дата: ,
Msg-id: 20131216063951.GD15585@fetter.org
(см: обсуждение, исходный текст)
Список: pgsql-announce

== PostgreSQL Weekly News - December 15 2013 ==

The CfP for PGCon 2014 is open through January 19, 2014.

The first meeting of PUGS, the PostgreSQL Users' Group of Singapore,
will take place 19th December, 2013.

== PostgreSQL Product News ==

devart dotConnect 7.2 for PostgreSQL, an ADO.NET provider, released.

Npgsql2 2.0.14, a .NET connector for PostgreSQL, released.

== PostgreSQL Jobs for December ==


== PostgreSQL Local ==

FOSDEM PGDay, a one day conference held before FOSDEM in Brussels,
Belgium, will be on Jan 31st, 2014.  Details:

The 7th annual "Prague PostgreSQL Developers Day" (P2D2) conference
organized by CSPUG (Czech and Slovak PostgreSQL Users Group), will be
held on February 6, 2014 at Faculty of Mathematics and Physics,
Charles University (Malostranske namesti 25, Prague).  Czech language
info below.  The CfP will be open through 2014/01/03.

== 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 , German language
to , Italian language to .  Spanish language
to .

== Applied Patches ==

Robert Haas pushed:

- Fixups for dsm.c's file descriptor handling.  Per complaint from Tom

- Add new wal_level, logical, sufficient for logical decoding.  When
  wal_level=logical, we'll log columns from the old tuple as
  configured by the REPLICA IDENTITY facility added in commit
  07cacba983ef79be4a84fcd0e0ca3b5fcb85dd65.  This makes it possible a
  properly-configured logical replication solution to correctly follow
  table updates even if they change the chosen key columns, or, with
  REPLICA IDENTITY FULL, even if the table has no key at all.  Note
  that updates which do not modify the replica identity column won't
  log anything extra, making the choice of a good key (i.e. one that
  will rarely be changed) important to performance when
  wal_level=logical is configured.  Each insert, update, or delete to
  a catalog table will also log the CMIN and/or CMAX values of stamped
  by the current transaction.  This is necessary because logical
  decoding will require access to historical snapshots of the catalog
  in order to decode some data types, and the CMIN/CMAX values that we
  may need in order to judge row visibility may have been overwritten
  by the time we need them.  Andres Freund, reviewed in various
  versions by myself, Heikki Linnakangas, KONDO Mitsumasa, and many

- Add a new reloption, user_catalog_table.  When this reloption is set
  and wal_level=logical is configured, we'll record the CIDs stamped
  by inserts, updates, and deletes to the table just as we would for
  an actual catalog table.  This will allow logical decoding to use
  historical MVCC snapshots to access such tables just as they access
  ordinary catalog tables.  Replication solutions built around the
  logical decoding machinery will likely need to set this operation
  for their configuration tables; it might also be needed by
  extensions which perform table access in their output functions.
  Andres Freund, reviewed by myself and others.

- Add a new option, -g, to createuser, to add membership in a role.
  Chistopher Browne, reviewed by Sameer Thakur, Amit Kapila, and Peter

- Under wal_level=logical, when saving old tuples, always save OID.
  There's no real point in not doing this.  It doesn't cost anything
  in performance or space.  So let's go wild.  Andres Freund, with
  substantial editing as to style by me.

Bruce Momjian pushed:

- pgcrypto docs:  update encryption timings and add relative times.
  Miles Elam

- pgcrypto docs: update cpu type used in duration testing

Noah Misch pushed:

- Rename TABLE() to ROWS FROM().  SQL-standard TABLE() is a subset of
  UNNEST(); they deal with arrays and other collection types.  This
  feature, however, deals with set-returning functions.  Use a
  different syntax for this feature to keep open the possibility of
  implementing the standard TABLE().

Tom Lane pushed:

- Fix possible crash with nested SubLinks.  An expression such as
  WHERE (... x IN (SELECT ...) ...) IN (SELECT ...) could produce an
  invalid plan that results in a crash at execution time, if the
  planner attempts to flatten the outer IN into a semi-join.  This
  happens because convert_testexpr() was not expecting any nested
  SubLinks and would wrongly replace any PARAM_SUBLINK Params
  belonging to the inner SubLink.  (I think the comment denying that
  this case could happen was wrong when written; it's certainly been
  wrong for quite a long time, since very early versions of the
  semijoin flattening logic.) Per report from Teodor Sigaev.
  Back-patch to all supported branches.

- Tweak placement of explicit ANALYZE commands in the regression
  tests.  Make the COPY test, which loads most of the large static
  tables used in the tests, also explicitly ANALYZE those tables.
  This allows us to get rid of various ad-hoc, and rather redundant,
  ANALYZE commands that had gotten stuck into various test scripts
  over time to ensure we got consistent plan choices.  (We could have
  done a database-wide ANALYZE, but that would cause stats to get
  attached to the small static tables too, which results in plan
  changes compared to the historical behavior.  I'm not sure that's a
  good idea, so not going that far for now.) Back-patch to 9.0, since
  9.0 and 9.1 are currently sometimes failing regression tests for
  lack of an "ANALYZE tenk1" in the subselect test.  There's no need
  for this in 8.4 since we didn't print any plans back then.

- Add a regression test case for plpython function returning setof
  RECORD.  We had coverage for functions returning setof a named
  composite type, but not for anonymous records, which is a somewhat
  different code path.  In view of recent crash report from Sergey
  Konoplev, this seems worth testing, though I doubt there's any
  deterministic bug here today.

- Remove bogus executable permissions on xlog.c.  Apparently
  fat-fingered in 1a3d104475ce01326fc00601ed66ac4d658e37e5.  Noted by
  Peter Geoghegan.

- Improve EXPLAIN to print the grouping columns in Agg and Group
  nodes.  Per request from Kevin Grittner.

- Fix ancient docs/comments thinko: XID comparison is mod 2^32, not
  2^31.  Pointed out by Gianni Ciolli.

- Don't let timeout interrupts happen unless ImmediateInterruptOK is
  set.  Serious oversight in commit
  16e1b7a1b7f7ffd8a18713e83c8cd72c9ce48e07: we should not allow an
  interrupt to take control away from mainline code except when
  ImmediateInterruptOK is set.  Just to be safe, let's adopt the same
  save-clear-restore dance that's been used for many years in
  HandleCatchupInterrupt and HandleNotifyInterrupt, so that nothing
  bad happens if a timeout handler invokes code that tests or even
  manipulates ImmediateInterruptOK.  Per report of "stuck spinlock"
  failures from Christophe Pettus, though many other symptoms are
  possible.  Diagnosis by Andres Freund.

  HandleCatchupInterrupt/HandleNotifyInterrupt.  This prevents a
  possible longjmp out of the signal handler if a timeout or SIGINT
  occurs while something within the handler has transiently set
  ImmediateInterruptOK.  For safety we must hold off the timeout or
  cancel error until we're back in mainline, or at least till we reach
  the end of the signal handler when ImmediateInterruptOK was true at
  entry.  This syncs these functions with the logic now present in
  handle_sig_alarm.  AFAICT there is no live bug here in 9.0 and up,
  because I don't think we currently can wait for any heavyweight lock
  inside these functions, and there is no other code (except
  read-from-client) that will turn on ImmediateInterruptOK.  However,
  that was not true pre-9.0: in older branches ProcessIncomingNotify
  might block trying to lock pg_listener, and then a SIGINT could lead
  to undesirable control flow.  It might be all right anyway given the
  relatively narrow code ranges in which NOTIFY interrupts are
  enabled, but for safety's sake I'm back-patching this.

- Fix inherited UPDATE/DELETE with UNION ALL subqueries.  Fix an
  oversight in commit b3aaf9081a1a95c245fd605dcf02c91b3a5c3a29: we do
  indeed need to process the planner's append_rel_list when copying
  RTE subqueries, because if any of them were flattenable UNION ALL
  subqueries, the append_rel_list shows which subquery RTEs were
  pulled up out of which other ones.  Without this, UNION ALL
  subqueries aren't correctly inserted into the update plans for
  inheritance child tables after the first one, typically resulting in
  no update happening for those child table(s).  Per report from
  Victor Yegorov.  Experimentation with this case also exposed a fault
  in commit a7b965382cf0cb30aeacb112572718045e6d4be7: if an inherited
  UPDATE/DELETE was proven totally dummy by constraint exclusion, we
  might arrive at add_rtes_to_flat_rtable with root->simple_rel_array
  being NULL.  This should be interpreted as not having any
  RelOptInfos.  I chose to code the guard as a check against
  simple_rel_array_size, so as to also provide some protection against
  indexing off the end of the array.  Back-patch to 9.2 where the
  faulty code was added.

- Allow empty target list in SELECT.  This fixes a problem noted as a
  followup to bug #8648: if a query has a semantically-empty target
  list, e.g. SELECT * FROM zero_column_table, ruleutils.c will dump it
  as a syntactically-empty target list, which was not allowed.  There
  doesn't seem to be any reliable way to fix this by hacking ruleutils
  (note in particular that the originally zero-column table might
  since have had columns added to it); and even if we had such a fix,
  it would do nothing for existing dump files that might contain bad
  syntax.  The best bet seems to be to relax the syntactic
  restriction.  Also, add parse-analysis errors for SELECT DISTINCT
  with no columns (after *-expansion) and RETURNING with no columns.
  These cases previously produced unexpected behavior because the
  parsed Query looked like it had no DISTINCT or RETURNING clause,
  respectively.  If anyone ever offers a plausible use-case for this,
  we could work a bit harder on making the situation distinguishable.
  Arguably this is a bug fix that should be back-patched, but I'm
  worried that there may be client apps or PLs that expect "SELECT ;"
  to throw a syntax error.  The issue doesn't seem important enough to
  risk changing behavior in minor releases.

Peter Eisentraut pushed:

- doc: Fix DocBook table column count declaration.  This was broken in

- PL/Perl: Add event trigger support.  From: Dimitri Fontaine

- configure: Allow adding a custom string to PG_VERSION.  This can be
  used to mark custom built binaries with an extra version string such
  as a git describe identifier or distribution package release
  version.  From: Oskari Saarenmaa <>

Kevin Grittner pushed:

- Add table name to VACUUM statement in matview.c.  The test only
  needs the one table to be vacuumed.  Vacuuming the database may
  affect other tests.  Per gripe from Tom Lane.  Back-patch to 9.3,
  where the test was was added.

Simon Riggs pushed:

- Regression tests for ALTER TABLESPACE RENAME,OWNER.  Hari Babu Kommi
  reviewed by David Rowley

- Regression tests for SCHEMA commands.  Hari Babu Kommi reviewed by
  David Rowley

- Allow time delayed standbys and recovery.  Set
  min_recovery_apply_delay to force a delay in recovery apply for
  commit and restore point WAL records. Other records are replayed
  immediately.  Delay is measured between WAL record time and local
  standby time.  Robert Haas, Fabrízio de Royes Mello and Simon Riggs
  Detailed review by Mitsumasa Kondo

- New autovacuum_work_mem parameter.  If autovacuum_work_mem is set,
  autovacuum workers now use this parameter in preference to
  maintenance_work_mem.  Peter Geoghegan

Heikki Linnakangas pushed:

- Display old and new values in pg_resetxlog -n output.  For extra
  clarity.  Rajeev Rastogi, reviewed by Amit Kapila

- Fix WAL-logging of setting the visibility map bit.  The operation
  that removes the remaining dead tuples from the page must be
  WAL-logged before the setting of the VM bit. Otherwise, if you
  replay the WAL to between those two records, you end up with the VM
  bit set, but the dead tuples are still there.  Backpatch to 9.3,
  where this bug was introduced.

- Add GUC to enable WAL-logging of hint bits, even with checksums
  disabled.  WAL records of hint bit updates is useful to tools that
  want to examine which pages have been modified. In particular, this
  is required to make the pg_rewind tool safe (without checksums).
  This can also be used to test how much extra WAL-logging would occur
  if you enabled checksums, without actually enabling them (which you
  can't currently do without re-initdb'ing).  Sawada Masahiko, docs by
  Samrat Revagade. Reviewed by Dilip Kumar, with further changes by

- Fix more instances of "the the" in comments.  Plus one instance of
  "to to" in the docs.

Tatsuo Ishii pushed:

- Fix progress logging when scale factor is large.  Integer overflow
  showed minus percent and minus remaining time something like this.
  239300000 of 3800000000 tuples (-48%) done (elapsed 226.86 s,
  remaining -696.10 s).

- Add "SHIFT_JIS" as an accepted encoding name for locale checking.
  When locale is "ja_JP.SJIS", nl_langinfo(CODESET) returns
  "SHIFT_JIS" on some platforms, at least on RedHat Linux. So the
  encoding/locale match table (encoding_match_list) needs the entry.
  Otherwise client encoding is set to SQL_ASCII.  Back patch to all
  supported branches.

Magnus Hagander pushed:

- Fix double "the" in the documentation.  Erik Rijkers

Alvaro Herrera pushed:

- Rework MultiXactId cache code.  The original performs too poorly; in
  some scenarios it shows way too high while profiling.  Try to make
  it a bit smarter to avoid excessive cosst.  In particular, make it
  have a maximum size, and have entries be sorted in LRU order; once
  the max size is reached, evict the oldest entry to avoid it from
  growing too large.  Per complaint from Andres Freund in connection
  with new tuple freezing code.

- Fix typo

== Rejected Patches (for now) ==

No one was disappointed this week :-)

== Pending Patches ==

MauMau sent in a patch to enable recovery to a backup point.

Peter Geoghegan sent in a patch to store pg_stat_statements query
texts externally and move pg_stat_statements into core.

Euler Taveira de Oliveira sent in a patch to implement a json output
plugin for logical decoding of the WAL stream.

Heikki Linnakangas and Alexander Korotkov traded patches adding
information to GIN indexes.

Heikki Linnakangas sent in a patch to use posix_fadvise in ANALYZE.

Antonin Houska sent in another revision of a patch to implement backup

Peter Eisentraut sent in another revision of a patch to add

Fujii Masao sent in a patch to fix a bug in libpq's PQhost().

Mitsumasa KONDO sent in another revision of a patch to optimize kernel
readahead using buffer access strategy.

Haribabu Kommi sent in another revision of a patch to allow specifying
a separate xlog directory in pg_basebackup.

Andres Freund sent in a patch to implement replication node
identifiers including an SQL API.

Sergey Muraviov sent in another revision of a patch to improve the
look of psql in the case of wide output.

Kyotaro HORIGUCHI sent in a patch to fix an issue where recovery can
fail in 9.3+.

Haribabu Kommi sent in another revision of a patch to fix a race
condition between high-volume writing and VACUUM.

Tatsuo Ishii sent in two revisions of a patch to allow pgbench to take
large scale factors.

Andres Freund sent in a patch always to include oids in WAL-logged
replica identities.

Amit Kapila sent in another revision of a patch to reduce the WAL for
update operations.

KaiGai Kohei sent in another revision of a patch to implement custom
scans and use same to implement custom TID scans as a contrib module,
and add join pushdowns in the PostgreSQL FDW.

Rajeev Rastogi sent in another revision of a patch to ensure that COPY
... FROM STDIN shows a count tag.

Peter Geoghegan sent in another revision of a patch to implement

Pavel Stehule sent in two more revisions of a patch to create
make_timestamp functions.

Marko Kreen sent in another revision of a patch to have SSL choose
from an updated list of cypher suites.

MauMau sent in another revision of a patch to fix a bug in psql on
Windows where pg_ctl always uses the same event source.

David Rowley sent in a patch to quiet a compiler warning in relcache.

Etsuro Fujita sent in a patch to Show lossy heap block info in EXPLAIN
ANALYZE for bitmap heap scan.

Etsuro Fujita sent in a patch to fix a comment in

Pavel Stehule sent in another revision of a patch to enable an
--if-exists switch in pg_dump.

MauMau sent in a patch to fix an issue where multibyte messages are
displayed incorrectly on the client during startup.

Craig Ringer sent in another revision of a patch to implement
updateable security barrier views.

Greg Smith sent in another revision of a patch to implement row-based
access control on the read side.

Andres Freund sent in a patch to fix some issues pointed out to by

Alexander Korotkov sent in a PoC patch to implement partial sorting.

David Rowley sent in two revisions of a patch to allow aggregates with
negative (or reverse, or inverse) transition functions.

В списке pgsql-announce по дате сообщения:

От: David Fetter
Сообщение: == PostgreSQL Weekly News - December 15 2013 ==
От: Hiroshi Saito
Сообщение: psqlODBC 09.03.0100 Released