Features Download
From: David Fetter <david <at> fetter.org>
Subject: == PostgreSQL Weekly News - January 27 2013 ==
Newsgroups: gmane.comp.db.postgresql.announce
Date: Monday 28th January 2013 07:11:36 UTC (over 5 years ago)
== PostgreSQL Weekly News - January 27 2013 ==

== PostgreSQL Jobs for January ==


== PostgreSQL Local ==

The FOSDEM PGDay conference that will be held before FOSDEM in
Brussels, Belgium, on Feb 1st, 2013.

The first edition of the Australian PostgreSQL Day (PGDay.AU 2013)
will be held in Melbourne on February 4, 2013. Registration is free of
charge. Schedule and info:

PyPgDay will be held on March 13th at the Santa Clara Convention
Center, the first day of PyCon.  Info here:

PGDay NYC 2013 will be held on March 22, 2013 in New York City.

PostgreSQL Session will be held on March 28th, 2013 in Paris,
France.  The Call for Papers is open.

PGCon 2013 will be held May 23-24 2013, in Ottawa at the University of

The 6th annual "Prague PostgreSQL Developers Day" conference,
organized by CSPUG (Czech and Slovak PostgreSQL Users Group), will be
held on May 30, 2013 at Faculty of Mathematics and Physics, Charles
University (Malostranske namesti 25, Prague).  The CfP is open until
April 14, 2013 .  More information in Czech is at

== 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 [email protected], German language
to [email protected], Italian language to [email protected]  Spanish language
to [email protected]

== Applied Patches ==

Alvaro Herrera pushed:

- Refactor ALTER some-obj RENAME implementation.  Remove duplicate
  implementations of catalog munging and miscellaneous privilege
  checks.  Instead rely on already existing data in objectaddress.c to
  do the work.  Author: KaiGai Kohei, changes by me.  Reviewed by:
  Robert Haas, Álvaro Herrera, Dimitri Fontaine

- Improve concurrency of foreign key locking This patch introduces two
  additional lock modes for tuples: "SELECT FOR KEY SHARE" and "SELECT
  FOR NO KEY UPDATE".  These don't block each other, in contrast with
  already existing "SELECT FOR SHARE" and "SELECT FOR UPDATE".  UPDATE
  commands that do not modify the values stored in the columns that
  are part of the key of the tuple now grab a SELECT FOR NO KEY UPDATE
  lock on the tuple, allowing them to proceed concurrently with tuple
  locks of the FOR KEY SHARE variety.  Foreign key triggers now use
  FOR KEY SHARE instead of FOR SHARE; this means the concurrency
  improvement applies to them, which is the whole point of this patch.
  The added tuple lock semantics require some rejiggering of the
  multixact module, so that the locking level that each transaction is
  holding can be stored alongside its Xid.  Also, multixacts now need
  to persist across server restarts and crashes, because they can now
  represent not only tuple locks, but also tuple updates.  This means
  we need more careful tracking of lifetime of pg_multixact SLRU
  files; since they now persist longer, we require more infrastructure
  to figure out when they can be removed.  pg_upgrade also needs to be
  careful to copy pg_multixact files over from the old server to the
  new, or at least part of multixact.c state, depending on the
  versions of the old and new servers.  Tuple time qualification rules
  (HeapTupleSatisfies routines) need to be careful not to consider
  tuples with the "is multi" infomask bit set as being only locked;
  they might need to look up MultiXact values (i.e.  possibly do
  pg_multixact I/O) to find out the Xid that updated a tuple, whereas
  they previously were assured to only use information readily
  available from the tuple header.  This is considered acceptable,
  because the extra I/O would involve cases that would previously
  cause some commands to block waiting for concurrent transactions to
  finish.  Another important change is the fact that locking tuples
  that have previously been updated causes the future versions to be
  marked as locked, too; this is essential for correctness of foreign
  key checks.  This causes additional WAL-logging, also (there was
  previously a single WAL record for a locked tuple; now there are as
  many as updated copies of the tuple there exist.) With all this in
  place, contention related to tuples being checked by foreign key
  rules should be much reduced.  As a bonus, the old behavior that a
  subtransaction grabbing a stronger tuple lock than the parent
  (sub)transaction held on a given tuple and later aborting caused the
  weaker lock to be lost, has been fixed.  Many new spec files were
  added for isolation tester framework, to ensure overall behavior is
  sane.  There's probably room for several more tests.  There were
  several reviewers of this patch; in particular, Noah Misch and
  Andres Freund spent considerable time in it.  Original idea for the
  patch came from Simon Riggs, after a problem report by Joel
  Jacobson.  Most code is from me, with contributions from Marti
  Raudsepp, Alexander Shulgin, Noah Misch and Andres Freund.  This
  patch was discussed in several pgsql-hackers threads; the most
  important start at the following message-ids:
  [email protected]
  [email protected]
  [email protected]
  [email protected]
  [email protected]
  [email protected]
  [email protected]

- isolationtester: add a few fflush(stderr) calls.  The lack of them
  is causing failures in some BF members.  Per Andrew Dunstan.

- Make output identical to pg_resetxlog's

- Don't require oldestMultixact if server doesn't have it

- Use the catversion to distinguish old/new clusters.  This makes 9.3
  -> 9.3 upgrades work when they cross the commit that added
  persistent multixacts; early 9.3 pg_controldata did not have the
  required oldestMultiXact line, and so would fail to upgrade.  per
  Bruce Momjian

  a cluster that's later processed by pg_upgrade would have a
  different infomask bit pattern than those produced by 9.3dev; that
  bit pattern was being seen as "dead" by HEAD (because they would
  fail the "is this tuple locked" test, and so the visibility rules
  would thing they're updated, even though there's no HEAP_UPDATED
  version of them).  In other words, some rows could silently
  disappear after pg_upgrade.  With this new definition, those tuples
  become visible again.  This is breakage resulting from my commit

Robert Haas pushed:

- Add ddl_command_end support for event triggers.  Dimitri Fontaine,
  with slight changes by me

- Typo fixes.  Noted by Thom Brown.

- Fix CREATE EVENT TRIGGER syntax synopsis in documentation.  Dimitri
  Fontaine, per a report from Thom Brown

- Fix a few small bugs in yesterday's event trigger patch.  Dimitri

- Update comments and output for event_trigger regression test.

- Further documentation tweaks for event triggers.  Per discussion
  between Dimitri Fontaine, myself, and others.

- pg_isready.  New command-line utility to test whether a server is
  ready to accept connections.  Phil Sorber, reviewed by Michael
  Paquier and Peter Eisentraut

- Clarify that connection parameters aren't totally meaningless for
  PQping.  Per discussion with Phil Sorber.

- Make it easy to time out pg_isready, and make the default 3 seconds.
  Along the way, add a missing line to the help message.  Phil Sorber,
  reviewed by Fujii Masao

Tom Lane pushed:

- Add infrastructure for storing a VARIADIC ANY function's VARIADIC
  flag.  Originally we didn't bother to mark FuncExprs with any
  indication whether VARIADIC had been given in the source text,
  because there didn't seem to be any need for it at runtime.
  However, because we cannot fold a VARIADIC ANY function's arguments
  into an array (since they're not necessarily all the same type), we
  do actually need that information at runtime if VARIADIC ANY
  functions are to respond unsurprisingly to use of the VARIADIC
  keyword.  Add the missing field, and also fix ruleutils.c so that
  VARIADIC ANY function calls are dumped properly.  Extracted from a
  larger patch that also fixes concat() and format() (the only two
  extant VARIADIC ANY functions) to behave properly when VARIADIC is
  specified.  This portion seems appropriate to review and commit
  separately.  Pavel Stehule

- Fix failure to rotate postmaster log file for size reasons on
  Windows.  When we eliminated "unnecessary" wakeups of the syslogger
  process, we broke size-based logfile rotation on Windows, because on
  that platform data transfer is done in a separate thread.  While
  non-Windows platforms would recheck the output file size after every
  log message, Windows only did so when the control thread woke up for
  some other reason, which might be quite infrequent.  Per bug #7814
  from Tsunezumi.  Back-patch to 9.2 where the problem was introduced.
  Jeff Janes

- Fix SPI documentation for new handling of ExecutorRun's count
  parameter.  Since 9.0, the count parameter has only limited the
  number of tuples actually returned by the executor.  It doesn't
  affect the behavior of INSERT/UPDATE/DELETE unless RETURNING is
  specified, because without RETURNING, the ModifyTable plan node
  doesn't return control to execMain.c for each tuple.  And we only
  check the limit at the top level.  While this behavioral change was
  unintentional at the time, discussion of bug #6572 led us to the
  conclusion that we prefer the new behavior anyway, and so we should
  just adjust the docs to match rather than change the code.
  Accordingly, do that.  Back-patch as far as 9.0 so that the docs
  match the code in each branch.

- Fix concat() and format() to handle VARIADIC-labeled arguments
  correctly.  Previously, the VARIADIC labeling was effectively
  ignored, but now these functions act as though the array elements
  had all been given as separate arguments.  Pavel Stehule

- Change plan caching to honor, not resist, changes in search_path.
  In the initial implementation of plan caching, we saved the active
  search_path when a plan was first cached, then reinstalled that path
  anytime we needed to reparse or replan.  The idea of that was to try
  to reselect the same referenced objects, in somewhat the same way
  that views continue to refer to the same objects in the face of
  schema or name changes.  Of course, that analogy doesn't bear close
  inspection, since holding the search_path fixed doesn't cope with
  object drops or renames.  Moreover sticking with the old path seems
  to create more surprises than it avoids.  So instead of doing that,
  consider that the cached plan depends on search_path, and force
  reparse/replan if the active search_path is different than it was
  when we last saved the plan.  This gets us fairly close to having
  "transparency" of plan caching, in the sense that the cached
  statement acts the same as if you'd just resubmitted the original
  query text for another execution.  There are still some corner cases
  where this fails though: a new object added in the search path
  schema(s) might capture a reference in the query text, but we'd not
  realize that and force a reparse.  We might try to fix that in the
  future, but for the moment it looks too expensive and complicated.

- Fix plpython's handling of functions used as triggers on multiple
  tables.  plpython tried to use a single cache entry for a trigger
  function, but it needs a separate cache entry for each table the
  trigger is applied to, because there is table-dependent data in
  there.  This was done correctly before 9.1, but commit
  46211da1b84bc3537e799ee1126098e71c2428e8 broke it by simplifying the
  lookup key from "function OID and triggered table OID" to "function
  OID and is-trigger boolean".  Go back to using both OIDs as the
  lookup key.  Per bug report from Sandro Santilli.  Andres Freund

- Make LATERAL implicit for functions in FROM.  The SQL standard does
  not have general functions-in-FROM, but it does allow UNNEST() there
  (see the  production), and the semantics
  of that are defined to include lateral references.  So spec
  compliance requires allowing lateral references within UNNEST() even
  without an explicit LATERAL keyword.  Rather than making UNNEST() a
  special case, it seems best to extend this flexibility to any
  function-in-FROM.  We'll still allow LATERAL to be written
  explicitly for clarity's sake, but it's now a noise word in this
  context.  In theory this change could result in a change in behavior
  of existing queries, by allowing what had been an outer reference in
  a function-in-FROM to be captured by an earlier FROM-item at the
  same level.  However, all pre-9.3 PG releases have a bug that causes
  them to match variable references to earlier FROM-items in
  preference to outer references (and then throw an error).  So no
  previously-working query could contain the type of ambiguity that
  would risk a change of behavior.  Per a suggestion from Andrew
  Gierth, though I didn't use his patch.

Peter Eisentraut pushed:

- doc: Fix declared number of columns in table.  This was broken in

Bruce Momjian pushed:

- pg_upgrade:  remove --single-transaction usage.  With AtEOXact
  applied, --single-transaction makes pg_restore slower, and has the
  potential to require lock table configuration, so remove the
  argument.  Per suggestion from Tom.

- pg_upgrade:  report failed cluster name.  When pg_upgrade can't find
  required pg_controldata information, report _which_ cluster is
  failing, with this message: The %s cluster lacks some required
  control information:

- pg_upgrade:  detect stale postmaster.pid lock files.  If the
  postmaster.pid lock file exists, try starting/stopping the cluster
  to check if the lock file is valid.  Per request from Tom.

- doc:  correct sepgsql doc about permission checking of CASCADE.
  Backpatch to 9.2.  Patch from Kohei KaiGai

- doc:  add mention of ssi read anomolies to mvcc docs.  From Jeff
  Davis, modified by Kevin Grittner

- doc:  improve wording of "foreign data server" in file-fdw docs.
  Backpatch to 9.2 Shigeru HANADA

- docs:  In ecpg, clarify how username/password colon parameters are
  used.  Backpatch to 9.2.  Patch from Alan B

- doc:  split search_path index entries into separate secondaries.
  Karl O. Pinc

- doc:  adjust search_path secondary index mention.  per Tom Lane

- doc:  merge ecpg username/password example into C comment.
  Backpatch to 9.2, per Tom Lane

- doc: mention commit_delay is only honored if fsync is enabled.  per
  Tianyin Xu

- doc:  revert 80c20fcf3df17309b3c131962045825f42e45bc7 and
  0e93959a70ac6e7c7858d1d6fb00645e7540a1cc.  Revert patch that
  modified doc index mentions of search_path.  Per Peter Eisentraut.

- Issue ERROR if FREEZE mode can't be honored by COPY.  Previously
  non-honored FREEZE mode was ignored.  This also issues an
  appropriate error message based on the cause of the failure, per
  suggestion from Tom.  Additional regression test case added.

- Allow CREATE TABLE IF EXIST so succeed if the schema is nonexistent
  Previously, CREATE TABLE IF EXIST threw an error if the schema was
  nonexistent.  This was done by passing 'missing_ok' to the function
  that looks up the schema oid.

- Update LookupExplicitNamespace() comments;  commit message update.
  Also, commit 7e2322dff30c04d90c0602d2b5ae24b4881db88b affected DROP

- Update comments in new DROP IF EXISTS code; commit message update.
  DROP IF EXISTS with a missing schema in commit
  7e2322dff30c04d90c0602d2b5ae24b4881db88b applies not only to tables,
  but to DROP IF EXISTS with missing schemas for indexes, views,
  sequences, and foreign tables.  Yeah!

Heikki Linnakangas pushed:

- Fix more issues with cascading replication and timeline switches.
  When a standby server follows the master using WAL archive, and it
  chooses a new timeline (recovery_target_timeline='latest'), it only
  fetches the timeline history file for the chosen target timeline,
  not any other history files that might be missing from pg_xlog. For
  example, if the current timeline is 2, and we choose 4 as the new
  recovery target timeline, the history file for timeline 3 is not
  fetched, even if it's part of this server's history. That's enough
  for the standby itself - the history file for timeline 4 includes
  timeline 3 as well - but if a cascading standby server wants to
  recover to timeline 3, it needs the history file. To fix, when a new
  recovery target timeline is chosen, try to copy any missing history
  files from the archive to pg_xlog between the old and new target
  timeline.  A second similar issue was with the WAL files. When a
  standby recovers from archive, and it reaches a segment that
  contains a switch to a new timeline, recovery fetches only the WAL
  file labelled with the new timeline's ID. The file from the new
  timeline contains a copy of the WAL from the old timeline up to the
  point where the switch happened, and recovery recovers it from the
  new file. But in streaming replication, walsender only tries to read
  it from the old timeline's file. To fix, change walsender to read it
  from the new file, so that it behaves the same as recovery in that
  sense, and doesn't try to open the possibly nonexistent file with
  the old timeline's ID.

- Implement pg_unreachable() on MSVC.

- Also fix rotation of csvlog on Windows.  Backpatch to 9.2, like the
  previous fix.

- Add some randomness to the choice of which GiST page to insert to.
  When descending the tree for an insert, and there are multiple
  equally good pages we could insert to, make the choice in random.
  Previously, we would always choose the tuple with lowest offset
  number. That meant that when two non-leaf pages overlap - in the
  extreme case they might have exactly the same key - all but the
  first such page went unused. That wasn't optimal for space usage; if
  you deleted some tuples from the non-first pages, the space would
  never be reused.  With this patch, the other pages are sometimes
  chosen too, although there's still a heavy bias towards low-offset
  tuples, so that we don't lose cache locality when doing a lot of
  inserts with similar keys.  Original idea by Alexander Korotkov,
  although this patch version was written by me and copy-edited by Tom

- Add prosecdef to \df+ output.  Jon Erdman, reviewed by Phil Sorber
  and Stephen Frost.

Andrew Dunstan pushed:

- Gitignore vcxproj files.  Per request from Craig Ringer.

- Use correct output device for Windows prompts.  This ensures that
  mapping of non-ascii prompts to the correct code page occurs.  Bug
  report and original patch from Alexander Law, reviewed and reworked
  by Noah Misch.  Backpatch to all live branches.

Simon Riggs pushed:

- Docs shouldn't say HOT Standby.  Not an acronym.  Jeff Janes

- Fix rare missing cancellations in Hot Standby.  The machinery around
  XLOG_HEAP2_CLEANUP_INFO failed to correctly pass through the
  necessary information on latestRemovedXid, avoiding cancellations in
  some infrequent concurrent update/cleanup scenarios.  Backpatchable
  fix to 9.0 Detailed bug report and fix by Noah Misch, backpatchable
  version by me.

Magnus Hagander pushed:

- Make pg_dump exclude unlogged table data on hot standby slaves.
  Noted by Joe Van Dyk

Michael Meskes pushed:

- Made ecpglib use translated messages.  Bug reported and fixed by
  Chen Huajun chenhj AT cn DOT fujitsu DOT com.

== Rejected Patches (for now) ==

No one was disappointed this week :-)

== Pending Patches ==

Ian Lawrence Barwick sent in a patch to fix an infelicity in psql's \s
with absolute path names.

Amit Kapila sent in two more revisions of a patch to improve
performance by reducing the volume of WAL for an update operation.

Amit Kapila sent in another revision of a patch to compute max LSN of
data pages.

Jeff Davis sent in another revision of a patch to remove

Josh Kupershmidt sent in a patch to fix pg_ctl with relative paths.

David Fetter sent in a patch on behalf of Andrew (RhodiumToad) Gierth
to clean up the API in addRangeTableEntryForFunction in preparation
for adding WITH ORDINALITY to UNNEST per SQL:2011 and to other
functions for consistency.

David Fetter sent in a patch on behalf of Andrew (RhodiumToad) Gierth
to add WITH ORDINALITY to UNNEST and other functions in the FROM

Amit Kapila sent in two more revisions of a patch to enable changing
postgresql.conf via SQL.

Michael Paquier sent in two more revisions of a patch to overhaul

Heikki Linnakangas and Alexander Korotkov traded patches to enable
indexing regexes which can be expressed as deterministic finite

Etsuro Fujita sent in another revision of a patch to add input and
output filtering for COPY.

Jeevan Chalke sent in another revision of a patch to fix an infelicity
in the interaction of unlogged tables and GiST indexing.

Phil Sorber sent in a patch to add a Makefile dependency in
bin/scripts for libpgport.

Pavel Stehule sent in a patch to make --echo-hidden work for \sf in

Jeff Janes sent in a patch to fix the capitalization in bgwriter's
references to hot standby.

Craig Ringer and Noah Misch traded patches to make MS Visual Studio
2012 work to compile PostgreSQL.

Steve Singer and Andres Freund traded patches to add logical changeset

Hari Babu sent in a patch to add an option to pass a connection string
to pg_basebackup.

Heikki Linnakangas and Tom Lane traded patches to fix bloating in
GiST indexes.

Jameison Martin sent in another revision of a patch to optimize the
case of many trailing NULLs in a table.

Xi Wang sent in a patch to fix x + y < x overflow checks which icc
mis-handles with wriggle room from the C standard.

Xi Wang sent in a patch to fix x +1 < x overflow checks.

Xi Wang sent in a patch to fix overflow checks in repeat().

Simon Riggs sent in a patch to skip checkpoint in 'pg_ctl promote -m

Kevin Grittner sent in another revision of a patch to implement
materialized views.

Michael Paquier sent in another revision of a patch to implement

Alvaro Herrera sent in a patch to set the priority for auto-wraparound
tables higher than others.

Jeff Davis sent in another revision of a patch to enable page

Dhruv Ahuja sent in a patch to change the pg_ctl promote exit status.

Hannu Krosing sent in a WIP patch to solve the typeio function
recursion problem in PL/python by simply testing if the function we
are currently in is a type-io function (fn_oid ==
argTypeStruct->typoutput ... )

Andrew Dunstan sent in another revision of a patch to implement the

Marko (johto) Tiikkaja sent in another revision of a patch to allow
PL/pgsql to check for STRICT-ness in PL/pgsql SELECTs.

Satoshi Nagayasu sent in another revision of a patch to upgrade
pgbench to work better in 64-bit environments.

KaiGai Kohei sent in another revision of a patch to implement
OAT_POST_ALTER object access hooks.

Pavan Deolasee sent in another revision of a patch to set the
visibility map in VACUUM's second phase.

Marko (johto) Tiikkaja sent in two more revisions of a patch to add an
option to pg_dump to pretty-print views.

Dimitri Fontaine sent in another revision of a patch to add in-catalog
Extension Scripts and Control parameters.

Tatsuo Ishii sent in another revision of a patch to allow writing only
aggregates to the pgbench log.

Peter Geoghegan sent in another revision of a patch to make a firm
recommendation about the value of commit_delay.

Sent via pgsql-announce mailing list ([email protected])
To make changes to your subscription:
CD: 4ms