== PostgreSQL Weekly News - July 28 2013 ==

== PostgreSQL Weekly News - July 28 2013 ==

The CfP for Italian PostgreSQL day has been extended until August 4, 2013.
http://2013.pgday.it/index.php/programma/call-for-papers-english/

== PostgreSQL Jobs for July ==

http://archives.postgresql.org/pgsql-jobs/2013-07/threads.php

== PostgreSQL Local ==

PostgreSQL Brazil will be held August 15-17, 2013 in Porto Velho, RO,
Brazil.
http://pgbr.postgresql.org.br/2013/chamada.en.php

Postgres Open 2013 will be in Chicago, IL, USA, September 16-18.
The list of talks has been posted on the site.
http://postgresopen.org/

The Italian PGDay (PGDay.IT) will be held on October the 25th in
Prato, Tuscany, Italy, at Monash University Center.
The International Call For Papers is open:
http://2013.pgday.it/index.php/programma/call-for-papers-english/

pgconf.EU 2013 will be held on Oct 29-Nov 1, 2013 at the Conrad Hotel
in downtown Dublin, Ireland.  The CfP is open.
http://2013.pgconf.eu/

PGConf.DE 2013 will be held November 8th, 2013, at the Rhineland
Industrial Museum in Oberhausen.  The CfP is open through September
15, 2013.
http://2013.pgconf.de/

== 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 david@fetter.org, German language
to pwn@pgug.de, Italian language to pwn@itpug.org.  Spanish language
to pwn@arpug.com.ar.

== Applied Patches ==

Robert Haas pushed:

- Add infrastructure for mapping relfilenodes to relation OIDs.
  Future patches are expected to introduce logical replication that
  works by decoding WAL.  WAL contains relfilenodes rather than
  relation OIDs, so this infrastructure will be needed to find the
  relation OID based on WAL contents.  If logical replication does not
  make it into this release, we probably should consider reverting
  this, since it will add some overhead to DDL operations that create
  new relations.  One additional index insert per pg_class row is not
  a large overhead, but it's more than zero.  Another way of meeting
  the needs of logical replication would be to the relation OID to
  WAL, but that would burden DML operations, not only DDL.  Andres
  Freund, with some changes by me.  Design review, in earlier
  versions, by Álvaro Herrera.
  http://git.postgresql.org/pg/commitdiff/f01d1ae3a104019d6d68aeff85c4816a275130b3

- Adjust HeapTupleSatisfies* routines to take a HeapTuple.
  Previously, these functions took a HeapTupleHeader, but upcoming
  patches for logical replication will introduce new a new snapshot
  type under which the tuple's TID will be used to lookup (CMIN, CMAX)
  for visibility determination purposes.  This makes that information
  available.  Code churn is minimal since HeapTupleSatisfiesVisibility
  took the HeapTuple anyway, and deferenced it before calling the
  satisfies function.  Independently of logical replication, this
  allows t_tableOid and t_self to be cross-checked via assertions in
  tqual.c.  This seems like a useful way to make sure that all callers
  are setting these values properly, which has been previously put
  forward as desirable.  Andres Freund, reviewed by Álvaro Herrera
  http://git.postgresql.org/pg/commitdiff/0518eceec3a1cc2b71da04e839f05f555fdd8567

- Remove bgw_sighup and bgw_sigterm.  Per discussion on pgsql-hackers,
  these aren't really needed.  Interim versions of the background
  worker patch had the worker starting with signals already unblocked,
  which would have made this necessary.  But the final version does
  not, so we don't really need it; and it doesn't work well with the
  new facility for starting dynamic background workers, so just rip it
  out.  Also per discussion on pgsql-hackers, back-patch this change
  to 9.3.  It's best to get the API break out of the way before we do
  an official release of this facility, to avoid more pain for
  extension authors later.
  http://git.postgresql.org/pg/commitdiff/f40a318eeaed0c66fcb2ddd442006e54bf49c634

- pgrowlocks: Use GetActiveSnapshot() rather than SnapshotNow.  Per
  discussion, it's desirable to eliminate all remaining uses of
  SnapshotNow, because it has unpleasant semantics: race conditions
  can result in seeing multiple versions of a concurrently updated
  row, or none at all.  By using GetActiveSnapshot() here, callers
  will see exactly those rows that would have been visible if the
  invoking query had scanned the table using, for example, a SELECT
  statement.  This is slightly different from the old behavior,
  because commits that happen concurrently with the scan will not
  affect the results.  In REPEATABLE READ or SERIALIZABLE modes, where
  transaction snapshots are used, commits that have happened since the
  start of the transaction will also not affect the results.  It is
  hoped that this minor incompatibility will be thought an
  improvement, or at least no worse than what we did before.
  http://git.postgresql.org/pg/commitdiff/2e44770fa39051f404f7d94fed557b359b1dba3c

- Fix cache flush hazard in ExecRefreshMatView.  Andres Freund
  http://git.postgresql.org/pg/commitdiff/21e28e4531e761e7cdf1b9a0bbf0c06f6107686a

- Additional regression tests for ALTER OPERATOR FAMILY.  Robins
  Tharakan, reviewed by Szymon Guz
  http://git.postgresql.org/pg/commitdiff/e6055061c524698918ab0b7a0c51b822c03ef1fa

- Use InvalidSnapshot, now SnapshotNow, as the default snapshot.  As
  far as I can determine, there's no code in the core distribution
  that fails to explicitly set the snapshot of a scan or executor
  state.  If there is any such code, this will probably cause it to
  seg fault; friendlier suggestions were discussed on pgsql-hackers,
  but there was no consensus that anything more than this was needed.
  This is another step towards the hoped-for complete removal of
  SnapshotNow.
  http://git.postgresql.org/pg/commitdiff/765ad89be36f699e2d65238c1f0458a1ab7e4d8b

- Don't use SnapshotNow in get_actual_variable_range.  Instead, use
  the active snapshot.  Per Tom Lane, this function is most interested
  in knowing the range of tuples our scan will actually see.  This is
  another step towards full removal of SnapshotNow.
  http://git.postgresql.org/pg/commitdiff/3483f4332de73e038db64be0614219b64fd3c971

- pgstattuple: Use SnapshotDirty, not SnapshotNow.  Tuples belonging
  to uncommitted transactions should not be counted as dead.  This is
  arguably a bug fix that should be back-patched, but as no one ever
  noticed until it came time to try to get rid of SnapshotNow, I'm
  only doing this in master for now.
  http://git.postgresql.org/pg/commitdiff/80c79ab2a8d63bc6c83269b29c1ba993c8077d3a

- pgstattuple: Doc update for previous commit.  In my previous change
  to make pgstattuple use SnapshotDirty rather than SnapshotNow, I
  failed to notice that the documenation also needed to be updated to
  match.  Fix.
  http://git.postgresql.org/pg/commitdiff/fd27b999196959bd20d777a1010c786feb1586c2

- Change currtid functions to use an MVCC snapshot, not SnapshotNow.
  This has a slight performance cost, but the only known consumers of
  these functions, known at the SQL level as currtid and currtid2, is
  pgsql-odbc; whose usage, we hope, is not sufficiently intensive to
  make this a problem.  Per discussion.
  http://git.postgresql.org/pg/commitdiff/ed93feb80891b131e9676e962256cc2b18aa5e78

Alvaro Herrera pushed:

- Silence compiler warning on an unused variable.  Also, tweak wording
  in comments (per Andres) and documentation (myself) to point out
  that it's the database's default tablespace that can be passed as 0,
  not DEFAULTTABLESPACE_OID.  Robert Haas noticed the bug in the code,
  but didn't update the accompanying prose.
  http://git.postgresql.org/pg/commitdiff/0aeb5ae2041520f02cabbc7083aec46733689bce

- Tweak FOR UPDATE/SHARE error message wording (again).  In commit
  0ac5ad5134 I changed some error messages from "FOR UPDATE/SHARE" to
  a rather long gobbledygook which nobody liked.  Then, in commit
  cb9b66d31 I changed them again, but the alternative chosen there was
  deemed suboptimal by Peter Eisentraut, who in message
  1373937980.20441.8.camel@vanquo.pezone.net proposed an alternative
  involving a dynamically-constructed string based on the actual
  locking strength specified in the SQL command.  This patch
  implements that suggestion.
  http://git.postgresql.org/pg/commitdiff/c359a1b0823f798fc419adea5da7991845c915aa

- Check for NULL result from strdup.  Per Coverity Scan
  http://git.postgresql.org/pg/commitdiff/bb686c9a865dc15a704e6a96367b3d7bfe79df6f

Tatsuo Ishii pushed:

- Add --rate option.  This controls the target transaction rate to
  certain tps, rather than maximum. Patch contributed by Fabien
  COELHO, reviewed by Greg Smith, and slight editing by me.
  http://git.postgresql.org/pg/commitdiff/fc9f4e9f8c981bbc050e5566cf558112c938da2c

Peter Eisentraut pushed:

- ecpg: Move function prototype into header file.
  PGTYPEStimestamp_defmt_scan() was declared twice inside different .c
  files, with slightly different prototypes.  Move it into a header
  file and correct the prototype.
  http://git.postgresql.org/pg/commitdiff/9feeef92fb9b73371e3f299ba444f86b4ef7e26f

- doc: Remove tab from SGML file
  http://git.postgresql.org/pg/commitdiff/2f1fa75a0c9327f1202b80c78ece1d33534bfd10

- Message style improvements
  http://git.postgresql.org/pg/commitdiff/626092a2e1784479b17810956b4654acf5b5ec11

Tom Lane pushed:

- Change post-rewriter representation of dropped columns in
  joinaliasvars.  It's possible to drop a column from an input table
  of a JOIN clause in a view, if that column is nowhere actually
  referenced in the view.  But it will still be there in the JOIN
  clause's joinaliasvars list.  We used to replace such entries with
  NULL Const nodes, which is handy for generation of RowExpr expansion
  of a whole-row reference to the view.  The trouble with that is that
  it can't be distinguished from the situation after subquery pull-up
  of a constant subquery output expression below the JOIN.  Instead,
  replace such joinaliasvars with null pointers (empty expression
  trees), which can't be confused with pulled-up expressions.
  expandRTE() still emits the old convention, though, for convenience
  of RowExpr generation and to reduce the risk of breaking extension
  code.  In HEAD and 9.3, this patch also fixes a problem with some
  new code in ruleutils.c that was failing to cope with
  implicitly-casted joinaliasvars entries, as per recent report from
  Feike Steenbergen.  That oversight was because of an inadequate
  description of the data structure in parsenodes.h, which I've now
  corrected.  There were some pre-existing oversights of the same ilk
  elsewhere, which I believe are now all fixed.
  http://git.postgresql.org/pg/commitdiff/a7cd853b75d01666135ca87353cee83b99d06b9b

- Further hacking on ruleutils' new column-alias-assignment code.
  After further thought about implicit coercions appearing in a
  joinaliasvars list, I realized that they represent an additional
  reason why we might need to reference the join output column
  directly instead of referencing an underlying column.  Consider
  SELECT x FROM t1 LEFT JOIN t2 USING (x) where t1.x is of type date
  while t2.x is of type timestamptz.  The merged output variable is of
  type timestamptz, but it won't go to null when t2 does, therefore
  neither t1.x nor t2.x is a valid substitute reference.  The code in
  get_variable() actually gets this case right, since it knows it
  shouldn't look through a coercion, but we failed to ensure that the
  unqualified output column name would be globally unique.  To fix,
  modify the code that trawls for a dangerous situation so that it
  actually scans through an unnamed join's joinaliasvars list to see
  if there are any non-simple-Var entries.
  http://git.postgresql.org/pg/commitdiff/ef655663c5069231e054c3514c3ee9b15b8a4f13

- Move strip_implicit_coercions() from optimizer to nodeFuncs.c.  Use
  of this function has spread into the parser and rewriter, so it
  seems like time to pull it out of the optimizer and put it into the
  more central nodeFuncs module.  This eliminates the need to #include
  optimizer/clauses.h in most of the calling files, demonstrating that
  this function was indeed a bit outside the normal code reference
  patterns.
  http://git.postgresql.org/pg/commitdiff/10a509d82956dee14eb2011bd266cd3c728ae188

- Fix booltestsel() for case where we have NULL stats but not MCV
  stats.  In a boolean column that contains mostly nulls, ANALYZE
  might not find enough non-null values to populate the
  most-common-values stats, but it would still create a pg_statistic
  entry with stanullfrac set.  The logic in booltestsel() for this
  situation did the wrong thing for "col IS NOT TRUE" and "col IS NOT
  FALSE" tests, forgetting that null values would satisfy these tests
  (so that the true selectivity would be close to one, not close to
  zero).  Per bug #8274.  Fix by Andrew Gierth, some comment-smithing
  by me.
  http://git.postgresql.org/pg/commitdiff/b32a25c3d5292c800c0468097b9a63e931a0af0f

- Improve ilist.h's support for deletion of slist elements during
  iteration.  Previously one had to use slist_delete(), implying an
  additional scan of the list, making this infrastructure considerably
  less efficient than traditional Lists when deletion of element(s) in
  a long list is needed.  Modify the slist_foreach_modify() macro to
  support deleting the current element in O(1) time, by keeping a
  "prev" pointer in addition to "cur" and "next".  Although this makes
  iteration with this macro a bit slower, no real harm is done, since
  in any scenario where you're not going to delete the current list
  element you might as well just use slist_foreach instead.  Improve
  the comments about when to use each macro.  Back-patch to 9.3 so
  that we'll have consistent semantics in all branches that provide
  ilist.h.  Note this is an ABI break for callers of
  slist_foreach_modify().  Andres Freund and Tom Lane
  http://git.postgresql.org/pg/commitdiff/fa2fad3c06bfde03594ff38d53acdf9a60c56bb2

- Fix configure probe for sys/ucred.h.  The configure script's test
  for <sys/ucred.h> did not work on OpenBSD, because on that platform
  <sys/param.h> has to be included first.  As a result, socket peer
  authentication was disabled on that platform.  Problem introduced in
  commit be4585b1c27ac5dbdd0d61740d18f7ad9a00e268.  Andres Freund,
  slightly simplified by me.
  http://git.postgresql.org/pg/commitdiff/1b09630fce1dfd4116eaaf154766a8a435168193

- Prevent leakage of SPI tuple tables during subtransaction abort.
  plpgsql often just remembers SPI-result tuple tables in local
  variables, and has no mechanism for freeing them if an
  ereport(ERROR) causes an escape out of the execution function whose
  local variable it is.  In the original coding, that wasn't a problem
  because the tuple table would be cleaned up when the function's SPI
  context went away during transaction abort.  However, once plpgsql
  grew the ability to trap exceptions, repeated trapping of errors
  within a function could result in significant intra-function-call
  memory leakage, as illustrated in bug #8279 from Chad Wagner.  We
  could fix this locally in plpgsql with a bunch of PG_TRY/PG_CATCH
  coding, but that would be tedious, probably slow, and prone to bugs
  of omission; moreover it would do nothing for similar risks
  elsewhere.  What seems like a better plan is to make SPI itself
  responsible for freeing tuple tables at subtransaction abort.  This
  patch attacks the problem that way, keeping a list of live tuple
  tables within each SPI function context.  Currently, such freeing is
  automatic for tuple tables made within the failed subtransaction.
  We might later add a SPI call to mark a tuple table as not to be
  freed this way, allowing callers to opt out; but until someone
  exhibits a clear use-case for such behavior, it doesn't seem worth
  bothering.  A very useful side-effect of this change is that
  SPI_freetuptable() can now defend itself against bad calls, such as
  duplicate free requests; this should make things more robust in many
  places.  (In particular, this reduces the risks involved if a
  third-party extension contains now-redundant SPI_freetuptable()
  calls in error cleanup code.) Even though the leakage problem is of
  long standing, it seems imprudent to back-patch this into stable
  branches, since it does represent an API semantics change for SPI
  users.  We'll patch this in 9.3, but live with the leakage in older
  branches.
  http://git.postgresql.org/pg/commitdiff/3d13623d75d3206c8f009353415043a191ebab39

Bruce Momjian pushed:

- pg_upgrade: fix parallel/-j crash on Windows.  This fixes the
  problem of passing the wrong function pointer when doing parallel
  copy/link operations on Windows.  Backpatched to 9.3beta.  Found and
  patch supplied by Andrew Dunstan
  http://git.postgresql.org/pg/commitdiff/d7de6a4790a8e2d61e985b7273557ee8fb7ffbc0

- pg_upgrade:  more Windows parallel/-j fixes More fixes to handle
  Windows thread parameter passing.  Backpatch to 9.3 beta.  Patch
  originally from Andrew Dunstan
  http://git.postgresql.org/pg/commitdiff/910d3a458c15c1b4cc518ba480be2f712f42f179

- pg_upgrade: fix initialization of thread argument.  Reorder
  initialization of thread argument marker to it happens before
  reap_child() is called.  Backpatch to 9.3.
  http://git.postgresql.org/pg/commitdiff/e4c6cccd8cbb96e0f64d81bde2136041492d4312

- pg_upgrade:  adjust umask() calls.  Since pg_upgrade -j on Windows
  uses threads, calling umask() before/after opening a file via
  fopen_priv() is no longer possible, so set umask() as we enter the
  thread-creating loop, and reset it on exit.  Also adjust internal
  fopen_priv() calls to just use fopen().  Backpatch to 9.3beta.
  http://git.postgresql.org/pg/commitdiff/76a7650c40efeeb35be7a60f27ce151ad4e473a2

- pg_upgrade docs: don't use cluster for binary/lib.  In a few cases,
  pg_upgrade said old/new cluster location when it meant old/new
  Postgres install location, so fix those.  Per private email report
  http://git.postgresql.org/pg/commitdiff/5691de6c95836e0cef3a2fbb3ce61ce80ddecc69

- pg_upgrade: fix -j race condition on Windows.  Pg_Upgrade cannot
  write the command string to the log file and then call system() to
  write to the same file without causing occasional file-share errors
  on Windows.  So instead, write the command string to the log file
  after system(), in those cases.  Backpatch to 9.3.
  http://git.postgresql.org/pg/commitdiff/815fcd050fbe18976c51af59116d60a6be5f3e41

Stephen Frost pushed:

- Improvements to GetErrorContextStack().  As GetErrorContextStack()
  borrowed setup and tear-down code from other places, it was less
  than clear that it must only be called as a top-level entry point
  into the error system and can't be called by an exception handler
  (unlike the rest of the error system, which is set up to be
  reentrant-safe).  Being called from an exception handler is outside
  the charter of GetErrorContextStack(), so add a bit more protection
  against it, improve the comments addressing why we have to set up an
  errordata stack for this function at all, and add a few more
  regression tests.  Lack of clarity pointed out by Tom Lane; all bugs
  are mine.
  http://git.postgresql.org/pg/commitdiff/9bd0feeba85fae411e01798d5a5d76b70333e38e

- Add GET DIAGNOSTICS ... PG_CONTEXT in PL/PgSQL.  This adds the
  ability to get the call stack as a string from within a PL/PgSQL
  function, which can be handy for logging to a table, or to include
  in a useful message to an end-user.  Pavel Stehule, reviewed by
  Rushabh Lathia and rather heavily whacked around by Stephen Frost.
  http://git.postgresql.org/pg/commitdiff/831283256796d1c20858862b568d73e505eb4a84

== Rejected Patches (for now) ==

No one was disappointed this week :-)

== Pending Patches ==

Greg Smith sent in another revision of a patch to add --throttle to
pgbench.

Karol Trzcionka sent in another revision of a patch to implement
UPDATE ... RETURNING BEFORE/AFTER.

Satoshi Nagayasu sent in a PoC patch to add a newpgstattuple function
to allow block sampling.

Alvaro Herrera sent in a patch to improve the error messages for
row-level locks.

David Fetter sent in two more revisions of a patch to add ORDINALITY
for set-returning functions.

Amit Kapila sent in another revision of a patch to implement ALTER
SYSTEM.

Antonin Houska sent in a patch to allow throttling pg_basebackup.

Vik Fearing sent in a patch to fix a bug where the reported number of
rows overflows the 32-bit integer assigned to it by making the integer
64-bit instead.

Robert Haas sent in another revision of a patch to add dynamic
background workers.

Fabrízio de Royes Mello and Abhijit Menon-Sen traded patches to add IF
NOT EXISTS to extant CREATE DDLs where it made sense and hadn't yet
been implemented.

Ronan Dunklau sent in a patch to add "make coverage" targets for the
additional supplied modules.

Robert Haas sent in another revision of a patch to remove SnapshotNow.

Andrew Dunstan sent in a patch to install libpq.dll in the bin
directory on Windows when compiling under Cygwin.

Stephen Frost sent in a patch to