== PostgreSQL Weekly News - February 12 2012 ==

== PostgreSQL Weekly News - February 12 2012 ==

== PostgreSQL Product News ==

pgreplay 1.1.0, a log-file-based statement replayer for PostgreSQL, released.
http://pgreplay.projects.postgresql.org/

Postgres Plus Advanced Server v9.1.2 is now Generally Available.
http://www.enterprisedb.com/download-advanced-server

Scalr 2.5 supports PostgreSQL.
http://scalr.net/features/databases/postgres/

Version 1.23.0 of tail_n_mail, a Postgres log watcher program, released:
http://bucardo.org/wiki/Tail_n_mail

== PostgreSQL Jobs for February ==

http://archives.postgresql.org/pgsql-jobs/2012-02/threads.php

== PostgreSQL Local ==

PGDay NYC will be held April 2, 2012 at Lighthouse International in
New York City.
http://pgday.nycpug.org

PGCon 2012 will be held 17-18 May 2012, in Ottawa at the University of
Ottawa.  It will be preceded by two days of tutorials on 15-16 May 2012.
http://www.pgcon.org/2012/

PGDay France will be in Lyon on June 7, 2012.
http://www.pgday.fr



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

== Reviews ==

== Applied Patches ==

Alvaro Herrera pushed:

- fe-misc.c depends on pg_config_paths.h.  Declare this in Makefile to
  avoid failures in parallel compiles.  Author: Lionel Elie Mamane
  http://git.postgresql.org/pg/commitdiff/0c88086df3f79ff48d09440d96be17f3307017ed

Robert Haas pushed:

- Remove dead declaration.
  http://git.postgresql.org/pg/commitdiff/96abd81744a90511b7cae9299e589412ce1897c9

- pg_dump: Reduce dependencies on global variables.  Change various
  places in the code that are referencing the global Archive object
  g_fout to instead reference the Archive object fout which is already
  being passed as a parameter.  For parallel pg_dump to work, we're
  going to need multiple Archive(Handle) objects, so the real solution
  here is to pass down the Archive object to everywhere that it needs
  to go, but we might as well pick the low-hanging fruit first.
  http://git.postgresql.org/pg/commitdiff/622f862868992e3b7ded31d79403a4f63281f44a

- pg_dump: Remove global Archive pointer.  Instead, everything that
  needs the Archive object now gets it as a parameter.  This is
  necessary infrastructure for parallel pg_dump, but is also amply
  justified by the ugliness of the current code (though a lot more
  than this is needed to fix that problem).
  http://git.postgresql.org/pg/commitdiff/3b157cf21dc2c1cd678580a178d05e0c1fbafd9d

- pg_dump: Further reduce reliance on global variables.  This is
  another round of refactoring to make things simpler for parallel
  pg_dump.  pg_dump.c now issues SQL queries through the relevant
  Archive object, rather than relying on the global variable g_conn.
  This commit isn't quite enough to get rid of g_conn entirely, but it
  makes a big dent in its utilization and, along the way, manages to
  be slightly less code than before.
  http://git.postgresql.org/pg/commitdiff/1631598ea204a3b05104f25d008b510ff5a5c94a

- Add TIMING option to EXPLAIN, to allow eliminating of timing
  overhead.  Sometimes it may be useful to get actual row counts out
  of EXPLAIN (ANALYZE) without paying the cost of timing every node
  entry/exit.  With this patch, you can say EXPLAIN (ANALYZE, TIMING
  OFF) to get that.  Tomas Vondra, reviewed by Eric Theise, with minor
  doc changes by me.
  http://git.postgresql.org/pg/commitdiff/af7914c6627bcf0b0ca614e9ce95d3f8056602bf

- Add a transform function for numeric typmod coercisions.  This
  enables ALTER TABLE to skip table and index rebuilds when a column
  is changed to an unconstrained numeric, or when the scale is
  unchanged and the precision does not decrease.  Noah Misch, with a
  few stylistic changes and a fix for an OID collision by me.
  http://git.postgresql.org/pg/commitdiff/3cc0800829a6dda5347497337b0cf43848da4acf

- Add a transform function for varbit typmod coercisions.  This
  enables ALTER TABLE to skip table and index rebuilds when the new
  type is unconstraint varbit, or when the allowable number of bits is
  not decreasing.  Noah Misch, with review and a fix for an OID
  collision by me.
  http://git.postgresql.org/pg/commitdiff/f7d7dade8afe19847510efe44be191c35e1ce26c

- Support fls().  The immediate impetus for this is that Noah Misch's
  patch to elide unnecessary table and index rebuilds when changing
  typmod for temporal types uses it; and this is extracted from that
  patch, with some further commentary by me.  But it seems logically
  separate from the remainder of the patch, so I'm committing it
  separately; this is not the first time someone has wanted fls() in
  the backend and probably won't be the last.  If we end up using this
  in more performance-critical spots it may be worthwhile to add some
  architecture-specific optimizations to our src/port version of fls()
  - e.g. any x86 platform can implement this using the assembly
  instruction BSRL.  But performance won't matter a bit for assessing
  typmod changes, so I'm not worried about that right now.
  http://git.postgresql.org/pg/commitdiff/4f658dc851a73fc309a61be2503c29ed78a1592e

- Fix typos pointed out by Noah Misch.
  http://git.postgresql.org/pg/commitdiff/af7dd696b00fe666fec3642e103acafe33002765

- Add transform functions for various temporal typmod coercisions.
  This enables ALTER TABLE to skip table and index rebuilds in some
  cases.  Noah Misch, with trivial changes by me.
  http://git.postgresql.org/pg/commitdiff/c13897983a0006e658fb7c6410d72ca59fb87136

- Add a comment to AdjustIntervalForTypmod to reduce chance of future
  bugs.  It's not entirely evident how the logic here relates to the
  interval_transform function, so let's clue people in that they need
  to check that if the rules change.
  http://git.postgresql.org/pg/commitdiff/d429ebe347a5f1404cc3c50234e1c93f8db2bb53

- Improve interval_transform function to detect a few more cases.
  Noah Misch, per a review comment from me.
  http://git.postgresql.org/pg/commitdiff/6656588575abd13016989be1a276b1b60be49b69

- Attempt to fix MSVC builds and other fls-related breakage.  Thanks
  to Andrew Dunstan for bringing this to my attention.
  http://git.postgresql.org/pg/commitdiff/dd7c84185c160bb0d95bd265182f24f1f6c21924

Tom Lane pushed:

- Add locking around WAL-replay modification of shared-memory
  variables.  Originally, most of this code assumed that no Postgres
  backends could be running concurrently with it, and so no locking
  could be needed.  That assumption fails in Hot Standby.  While it's
  still true that Hot Standby backends should never change values like
  nextXid, they can examine them, and consistency is important in some
  cases such as when computing a snapshot.  Therefore, prudence
  requires that WAL replay code obtain the relevant locks when
  modifying such variables, even though it can examine them without
  taking a lock.  We were following that coding rule in some places
  but not all.  This commit applies the coding rule uniformly to all
  updates of ShmemVariableCache and MultiXactState fields; a search of
  the replay routines did not find any other cases that seemed to be
  at risk.  In addition, this commit fixes a longstanding thinko in
  replay of NEXTOID and checkpoint records: we tried to advance
  nextOid only if it was behind the value in the WAL record, but the
  comparison would draw the wrong conclusion if OID wraparound had
  occurred since the previous value.  Better to just unconditionally
  assign the new value, since OID assignment shouldn't be happening
  during replay anyway.  The additional locking seems to be more in
  the nature of future-proofing than fixing any live bug, so I am not
  going to back-patch it.  The NEXTOID fix will be back-patched
  separately.
  http://git.postgresql.org/pg/commitdiff/c6d76d7c82ebebb7210029f7382c0ebe2c558bca

- Avoid problems with OID wraparound during WAL replay.  Fix a
  longstanding thinko in replay of NEXTOID and checkpoint records: we
  tried to advance nextOid only if it was behind the value in the WAL
  record, but the comparison would draw the wrong conclusion if OID
  wraparound had occurred since the previous value.  Better to just
  unconditionally assign the new value, since OID assignment shouldn't
  be happening during replay anyway.  The consequences of a failure to
  update nextOid would be pretty minimal, since we have long had the
  code set up to obtain another OID and try again if the generated
  value is already in use.  But in the worst case there could be
  significant performance glitches while such loops iterate through
  many already-used OIDs before finding a free one.  The odds of a
  wraparound happening during WAL replay would be small in a
  crash-recovery scenario, and the length of any ensuing
  OID-assignment stall quite limited anyway.  But neither of these
  statements hold true for a replication slave that follows a WAL
  stream for a long period; its behavior upon going live could be
  almost unboundedly bad.  Hence it seems worth back-patching this fix
  into all supported branches.  Already fixed in HEAD in commit
  c6d76d7c82ebebb7210029f7382c0ebe2c558bca.
  http://git.postgresql.org/pg/commitdiff/f1b8a84dec30b44e6a0b306f95961f5426cb8368

- Avoid throwing ERROR during WAL replay of DROP TABLESPACE.  Although
  we will not even issue an XLOG_TBLSPC_DROP WAL record unless removal
  of the tablespace's directories succeeds, that does not guarantee
  that the same operation will succeed during WAL replay.  Foreseeable
  reasons for it to fail include temp files created in the tablespace
  by Hot Standby backends, wrong directory permissions on a standby
  server, etc etc.  The original coding threw ERROR if replay failed
  to remove the directories, but that is a serious overreaction.
  Throwing an error aborts recovery, and worse means that manual
  intervention will be needed to get the database to start again,
  since otherwise the same error will recur on subsequent attempts to
  replay the same WAL record.  And the consequence of failing to
  remove the directories is only that some probably-small amount of
  disk space is wasted, so it hardly seems justified to throw an
  error.  Accordingly, arrange to report such failures as LOG messages
  and keep going when a failure occurs during replay.  Back-patch to
  9.0 where Hot Standby was introduced.  In principle such problems
  can occur in earlier releases, but Hot Standby increases the odds of
  trouble significantly.  Given the lack of field reports of such
  issues, I'm satisfied with patching back as far as the patch applies
  easily.
  http://git.postgresql.org/pg/commitdiff/5fc78efcec01fd5e857278556ad4312ae94ecc58

- Fix postmaster to attempt restart after a hot-standby crash.  The
  postmaster was coded to treat any unexpected exit of the startup
  process (i.e., the WAL replay process) as a catastrophic crash, and
  not try to restart it. This was OK so long as the startup process
  could not have any sibling postmaster children.  However, if a
  hot-standby backend crashes, we SIGQUIT the startup process along
  with everything else, and the resulting exit is hardly "unexpected".
  Treating it as such meant we failed to restart a standby server
  after any child crash at all, not only a crash of the WAL replay
  process as intended.  Adjust that.  Back-patch to 9.0 where hot
  standby was introduced.
  http://git.postgresql.org/pg/commitdiff/442231d7f71764b8c628044e7ce2225f9aa43b67

- Mark some more I/O-conversion-invoking functions as stable not
  volatile.  When written, textanycat, anytextcat, quote_literal, and
  quote_nullable were marked volatile, because they could invoke
  arbitrary type-specific output functions as part of casting their
  anyelement arguments to text.  Since then, we have defined a project
  policy that I/O functions must not be volatile, as per commit
  aab353a60b95aadc00f81da0c6d99bde696c4b75.  So these functions can
  safely be downgraded to stable.  Most of the time this makes no
  difference since they'll get inlined anyway, but as noted by Andrew
  Dunstan, there are cases where the volatile marking prevents
  optimizations that the planner does before function inlining.  (I
  think I might have overlooked these functions in the earlier commit
  on the grounds that inlining would make it moot, but not so --- tgl)
  This change results in a change in the expected output of the json
  regression tests, because the planner can now flatten a sub-select
  that it failed to before.  The old output is preferable, but getting
  that back will require some as-yet-unfinished work on RowExpr
  handling.  Marti Raudsepp
  http://git.postgresql.org/pg/commitdiff/3db6524fe63f0598dcb2b307bb422bc126f2b15d

- Support min/max index optimizations on boolean columns.  Since
  bool_and() is equivalent to min(), and bool_or() to max(), we might
  as well let them be index-optimized in the same way.  The practical
  value of this is debatable at best, but it seems nearly cost-free to
  enable it.  Code-wise, we need only adjust the entries in
  pg_aggregate.  There is a measurable planning speed penalty for a
  query involving one of these aggregates, but it is only a few
  percent in simple cases, so that seems acceptable.  Marti Raudsepp,
  reviewed by Abhijit Menon-Sen
  http://git.postgresql.org/pg/commitdiff/cbba55d6d792b55f6b448a31fc14aef84510967c

- Check misplaced window functions before checking aggregate/group by
  sanity.  If somebody puts a window function in WHERE, we should
  complain about that in so many words.  The previous coding tended to
  complain about the window function's arguments instead, which is
  likely to be misleading to users who are unclear on the semantics of
  window functions; as seen for example in bug #6440 from Matyas
  Novak.  Just another example of how "add new code at the end" is
  frequently a bad heuristic.
  http://git.postgresql.org/pg/commitdiff/cb7c84fae8a6780d836687aa2c9655eb936ebd25

- Fix up dumping conditions for extension configuration tables.
  Various filters that were meant to prevent dumping of table data
  were not being applied to extension config tables, notably
  --exclude-table-data and --no-unlogged-table-data.  We also would
  bogusly try to dump data from views, sequences, or foreign tables,
  should an extension try to claim they were config tables.  Fix all
  that, and refactor/redocument to try to make this a bit less
  fragile.  This reverts the implementation, though not the feature,
  of commit 7b070e896ca835318c90b02c830a5c4844413b64, which had broken
  config-table dumping altogether :-(.  It is still the case that the
  code will dump config-table data even if --schema is specified.
  That behavior was intentional, as per the comments in
  getExtensionMembership, so I think it requires some more discussion
  before we change it.
  http://git.postgresql.org/pg/commitdiff/d77354eaec53ed469a6f2444813ff3a4fd9d7a48

- Throw error sooner for unlogged GiST indexes.  Throwing an error
  only after we've built the main index fork is pretty unfriendly when
  the table already contains data.  Per gripe from Jay Levitt.
  http://git.postgresql.org/pg/commitdiff/331bf6712c71a1c110bc52423eede8b4bac221a1

- Add ORDER BY to a query to prevent occasional regression test
  failures.  Per buildfarm, we sometimes get row-ordering variations
  in the output.  This also makes this query look more like numerous
  other ones in the same test file.
  http://git.postgresql.org/pg/commitdiff/d06e2d200562837afa18058937f20460a3ea526a

- Fix pg_dump for better handling of inherited columns.  Revise
  pg_dump's handling of inherited columns, which was last looked at
  seriously in 2001, to eliminate several misbehaviors associated with
  inherited default expressions and NOT NULL flags.  In particular
  make sure that a column is printed in a child table's CREATE TABLE
  command if and only if it has attislocal = true; the former behavior
  would sometimes cause a column to become marked attislocal when it
  was not so marked in the source database.  Also, stop relying on
  textual comparison of default expressions to decide if they're
  inherited; instead, don't use default-expression inheritance at all,
  but just install the default explicitly at each level of the
  hierarchy.  This fixes the search-path-related misbehavior recently
  exhibited by Chester Young, and also removes some dubious
  assumptions about the order in which ALTER TABLE SET DEFAULT
  commands would be executed.  Back-patch to all supported branches.
  http://git.postgresql.org/pg/commitdiff/00bc96bd2b6646c73a073aa91dc68ed4718cf5f3

- Fix brain fade in previous pg_dump patch.  In pre-7.3 databases,
  pg_attribute.attislocal doesn't exist.  The easiest way to make sure
  the new inheritance logic behaves sanely is to assume it's TRUE, not
  FALSE.  This will result in printing child columns even when they're
  not really needed.  We could work harder at trying to reconstruct a
  value for attislocal, but there is little evidence that anyone still
  cares about dumping from such old versions, so just do the minimum
  necessary to have a valid dump.  I had this correct in the original
  draft of the patch, but for some unaccountable reason decided it
  wasn't necessary to change the value.  Testing against an old server
  shows otherwise...
  http://git.postgresql.org/pg/commitdiff/97dc3c8a147c01da38570e4be7b4979af918dca2

- Fix oversight in pg_dump's handling of extension configuration
  tables.  If an extension has not been selected to be dumped (perhaps
  because of a --schema or --table switch), the contents of its
  configuration tables surely should not get dumped either.  Per gripe
  from Hubert Depesz Lubaczewski.
  http://git.postgresql.org/pg/commitdiff/59de132f9a578ae5d2909228484a61309df986e0

- Fix I/O-conversion-related memory leaks in plpgsql.  Datatype I/O
  functions are allowed to leak memory in CurrentMemoryContext, since
  they are generally called in short-lived contexts.  However, plpgsql
  calls such functions for purposes of type conversion, and was
  calling them in its procedure context.  Therefore, any leaked memory
  would not be recovered until the end of the plpgsql function.  If
  such a conversion was done within a loop, quite a bit of memory
  could get consumed.  Fix by calling such functions in the transient
  "eval_econtext", and adjust other logic to match.  Back-patch to all
  supported versions.  Andres Freund, Jan Urbański, Tom Lane
  http://git.postgresql.org/pg/commitdiff/58a9596ed4a509467e1781b433ff9c65a4e5b5ce

Michael Meskes pushed:

- Allow the connection keyword array to carry all seven items in ecpglib.
  http://git.postgresql.org/pg/commitdiff/0ee23b53beb851d60c0eff9bde8fd7a303270720

Heikki Linnakangas pushed:

- When building with LWLOCK_STATS, initialize the stats in
  LWLockWaitUntilFree.  If LWLockWaitUntilFree was called before the
  first LWLockAcquire call, you would either crash because of access
  to uninitialized array or account the acquisition incorrectly.
  LWLockConditionalAcquire doesn't have this problem because it
  doesn't update the lwlock stats.  In practice, this never happens
  because there is no codepath where you would call
  LWLockWaitUntilfree before LWLockAcquire after a new process is
  launched. But that's just accidental, there's no guarantee that
  that's always going to be true in the future.  Spotted by Jeff
  Janes.
  http://git.postgresql.org/pg/commitdiff/15ad6f1510f8979a39e4c39078c742b5fdb121ce

- Fix typo in comment.
  http://git.postgresql.org/pg/commitdiff/5ece8ecae88fd7fd8616429790bd70ca2ffad260

- Rename LWLockWaitUntilFree to LWLockAcquireOrWait.
  LWLockAcquireOrWait makes it more clear that the lock is acquired if
  it's free.
  http://git.postgresql.org/pg/commitdiff/1a01560cbb78ff363fc7d70298328aa23f05bfb5

- Add new keywords SNAPSHOT and TYPES to the keyword list in gram.y.
  These were added to kwlist.h as unreserved keywords in separate
  patches, but authors forgot to add them to the corresponding list in
  gram.y.  Because of that, even though they were supposed to be
  unreserved keywords, they could not be used as identifiers.
  src/tools/check_keywords.pl is your friend.
  http://git.postgresql.org/pg/commitdiff/82e73ba0d1f8990a76a20b3a57957e633945fb2b

Peter Eisentraut pushed:

- createuser: Disable prompting by default.  Do not prompt when
  options were not specified.  Assume --no-createdb, --no-createrole,
  --no-superuser by default.  Also disable prompting for user name in
  dropdb, unless --interactive was specified.  reviewed by Josh
  Kupershmidt
  http://git.postgresql.org/pg/commitdiff/a347f96b99741e44276cdd481a72d04f1b2ac5fc

- pg_regress: Use target-specific variable instead of overriding make
  rule.  Use a target-specific variable to add to CPPFLAGS instead of
  writing a custom .c -> .o rule.  This will ensure that dependency
  tracking is used when enabled.
  http://git.postgresql.org/pg/commitdiff/d66b31c94faf2b8b8aac9d273c8cd1708a54887e

- pg_dump: Add some const qualifiers
  http://git.postgresql.org/pg/commitdiff/e09509bd33d691b2d2d3473b4e112efd59518b31

- psql: Support zero byte field and record separators.  Add new psql
  settings and command-line options to support setting the field and
  record separators for unaligned output to a zero byte, for easier
  interfacing with other shell tools.  reviewed by Abhijit Menon-Sen
  http://git.postgresql.org/pg/commitdiff/169c8a911260bd5a8b6910c458afa57a1ae29627

Bruce Momjian pushed:

- Add opensp as a requirement for building the docs on Debian ---
  tested on Debian Squeeze.
  http://git.postgresql.org/pg/commitdiff/a870c7fdbca71e2c651fd80b3dec902c49040c7a

Magnus Hagander pushed:

- Have pg_receivexlog always send an invalid log position in status
  messages.  This prevents pg_basebackup and pg_receivexlog from
  becoming a synchronous standby in case 'write' is used for
  synchronous_commit.  Fujii Masao
  http://git.postgresql.org/pg/commitdiff/d7ea9193d18d29bea82b30604cdd087e90c5ee27

== Rejected Patches (for now) ==

No one was disappointed this week :-)

== Pending Patches ==

Marco Nenciarini sent in another revision of the patch to allow the
elements of arrays to be enforced as foreign keys.

Fujii Masao sent in two revisions of a patch to fix incorrect handling
of the timeout in pg_receivexlog.

Marko Kreen sent in another revision of the patch to create a new
tuple storage in libpq and use same to make dblink more efficient in
some cases.

Chetan Suttraway sent in another revision of the patch to implement
SPI_gettypemod().

Fujii Masao sent in a patch to fix an issue where pg_basebackup -x
stream from the standby gets stuck.

Chetan Suttraway sent in a patch to prevent the specification of
conflicting transaction read/write options.

Euler Taveira de Oliveira sent in another revision of the patch to do
xlog location arithmetic.

Shigeru HANADA sent in two more revisions of the patch to add a
PostgreSQL FDW.

Marti Raudsepp sent in another revision of a patch to remove an
optimization barrier involving the volatility of text-any
concatenation.

Alvaro Herrera and Alex Hunsaker traded patches to fix an issue with
missing keywords in make.

Peter Eisentraut sent in a patch to fix some of the missing things in
psql's SELECT tab completion.

Marti Raudsepp sent in a patch to make TRUNCATE more MVCC-safe.

Peter Geoghegan sent in another revision of the patch for fast-path
ordering, b-tree index creation time.

Alex Hunsaker sent in a patch to fix a bug in PL/Perl in databases
encoded as SQL_ASCII.

Shigeru HANADA sent in another revision of the patch to collect
statistics on CSV files attached via FDW.

Jean-Baptiste Quenot sent in a patch to fix an issue with PL/Python's
handling of result metadata.

Kevin Grittner sent in a patch to ensure that if a GUC has a check
function, it is run on a RESET just like it is on a SET, to make sure
that the resulting value is valid to set within the context.

Andrew Dunstan sent in two revisions of a patch to fix a case where
auto_explain can produce invalid JSON.

Jeff Janes sent in a WIP patch to to set XLP_FIRST_IS_CONTRECORD, this
being part of the continuing effort to move more work outside
WALInsertLock.

Vik Reykja sent in a patch to optimize referential integrity checks.