== PostgreSQL Weekly News - August 12 2012 == - Mailing list pgsql-announce

== PostgreSQL Weekly News - August 12 2012 ==

== PostgreSQL Jobs for August ==

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

== PostgreSQL Local ==

PostgreSQL Session will be held on October 4th, 2012, in Paris,
France.  More information at:
http://www.postgresql-sessions.org/en/4/

PostgreSQL Conference Europe 2012 will be in Prague, Czech Republic
on October 23-26.  The call for papers is open.
http://2012.pgconf.eu/

PostgreSQL Day Argentina 2012 will be held on November 13th in Bernal,
Buenos Aires, at the National University of Quilmes.  It will cover
topics for PostgreSQL users, developers and contributors, as well as
decision and policy makers.  For more information about the
conference, please see the website at
http://www.pgday.com.ar/quilmes2012?lang=en


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

Simon Riggs pushed:

- fsync backup_label after pg_start_backup().  Dave Kerr
  http://git.postgresql.org/pg/commitdiff/0f04fc67f71f7cb29ccedb2e7ddf443b9e52b958

- Fix minor bug in XLogFileRead() that accidentally worked.  Cascading
  replication copied the incoming file into pg_xlog but didn't set
  path correctly, so the first attempt to open file failed causing it
  to loop around and look for file in pg_xlog. So the earlier coding
  worked, but accidentally rather than by design.  Spotted by Fujii
  Masao, fix by Fujii Masao and Simon Riggs
  http://git.postgresql.org/pg/commitdiff/8143a56854a4f46968b12badb5b008f448e08ef5

- Doc correction to point out that 9.2+ can overwrite pg_xlog files.
  Noted by Noah Misch, patch by Fujii Masao
  http://git.postgresql.org/pg/commitdiff/42020a89c740846870f9e42274e8730f1986edd3

- Force archive_status of .done for xlogs created by
  dearchival/replication.  This prevents spurious attempts to archive
  xlog files after promotion of standby, a bug introduced by cascading
  replication patch in 9.2.  Fujii Masao, simplified and extended to
  cover streaming by Simon Riggs
  http://git.postgresql.org/pg/commitdiff/6f4b8a4f4f7a2d683ff79ab59d3693714b965e3d

- Ensure all replication message info is available and correct via
  WalRcv
  http://git.postgresql.org/pg/commitdiff/87d8bd7c9fdc63d892968e1248e639c8078af615

- Turn off WalSender keepalives by default, users can enable if
  desired
  http://git.postgresql.org/pg/commitdiff/da4efa13d801ccc179f1d2c24d8a60c4a2f8ede9

Alvaro Herrera pushed:

- Fix redundant wording
  http://git.postgresql.org/pg/commitdiff/3a42a3ffd898114e6e19dafe0911b23921b288f4

- Fix typo in comment
  http://git.postgresql.org/pg/commitdiff/92ec0370eb65c4f059f2b60ee6e458b312bb7cbf

Bruce Momjian pushed:

- Fix pg_upgrade file share violation on Windows created by the commit
  4741e9afb93f0d769655b2d18c2b73b86f281010.  This was done by adding
  an optional second log file parameter to exec_prog(), and closing
  and reopening the log file between system() calls.  Backpatch to
  9.2.
  http://git.postgresql.org/pg/commitdiff/fbcfa90bb8ceb77f7763b6ae16bcbb777849ccee

- Fix to_char(), to_date(), and to_timestamp() to handle negative/BC
  century specifications just like positive/AD centuries.  Previously
  the behavior was either wrong or inconsistent with positive/AD
  handling.  Centuries without years now always assume the first year
  of the century, which is now documented.
  http://git.postgresql.org/pg/commitdiff/ac78c4178bec7b252d22a39515ad6efd6c1a7d7e

- Add additional C comments for to_date/to_char() fixes.
  http://git.postgresql.org/pg/commitdiff/2751740ab5c01b2ffc8afdb86b4c2c2bf37203e0

- Fix pgtest to return proper error code based on 'make' return code.
  http://git.postgresql.org/pg/commitdiff/99ed473acb47cb0cda47032e3d2392beca569d59

- Adjust pgtest coding to be less complex.
  http://git.postgresql.org/pg/commitdiff/914b1301cc7b4c1ffba9f4c037e6e76f8e787e05

- Prevent pg_upgrade from crashing if it can't write to the current
  directory.  Backpatch to 9.2.
  http://git.postgresql.org/pg/commitdiff/85642ec00b5e0c58d2504b10c079693b8fb28cc5

Robert Haas pushed:

- Fix memory leaks in event trigger code.  Spotted by Jeff Davis.
  http://git.postgresql.org/pg/commitdiff/eea65943c605215456d0f55f65e1ec802f5e221e

- Fix cache flush hazard in event trigger cache.  Bug spotted by Jeff
  Davis using -DCLOBBER_CACHE_ALWAYS.
  http://git.postgresql.org/pg/commitdiff/21786db81fca3fca0a17da3f58cc6590615e1580

- Make psql -1 < file behave as expected.  Previously, the -1 option
  was silently ignored.  Also, emit an error if -1 is used in a
  context where it won't be respected, to avoid user confusion.
  Original patch by Fabien COELHO, but this version is quite different
  from the original submission.
  http://git.postgresql.org/pg/commitdiff/be690e291d59e8d0c9f4df59abe09f1ff6cc0da9

Tom Lane pushed:

- Tweak new Perl pgindent for compatibility with middle-aged Perls.
  We seem to have a rough policy that our Perl scripts should work
  with Perl 5.8, so make this one do so.  Main change is to not use
  the newfangled \h character class in regexes; "[ \t]" is a
  serviceable replacement.
  http://git.postgresql.org/pg/commitdiff/5078be480412790e4f1b2aeda04f8c65fc7a3b93

- Implement SQL-standard LATERAL subqueries.  This patch implements
  the standard syntax of LATERAL attached to a sub-SELECT in FROM, and
  also allows LATERAL attached to a function in FROM, since
  set-returning function calls are expected to be one of the principal
  use-cases.  The main change here is a rewrite of the mechanism for
  keeping track of which relations are visible for column references
  while the FROM clause is being scanned.  The parser "namespace"
  lists are no longer lists of bare RTEs, but are lists of
  ParseNamespaceItem structs, which carry an RTE pointer as well as
  some visibility-controlling flags.  Aside from supporting LATERAL
  correctly, this lets us get rid of the ancient hacks that required
  rechecking subqueries and JOIN/ON and function-in-FROM expressions
  for invalid references after they were initially parsed.  Invalid
  column references are now always correctly detected on sight.  In
  passing, remove assorted parser error checks that are now dead code
  by virtue of our having gotten rid of add_missing_from, as well as
  some comments that are obsolete for the same reason.  (It was mainly
  add_missing_from that caused so much fudging here in the first
  place.) The planner support for this feature is very minimal, and
  will be improved in future patches.  It works well enough for
  testing purposes, though.  catversion bump forced due to new field
  in RangeTblEntry.
  http://git.postgresql.org/pg/commitdiff/5ebaaa49445eb1ba7b299bbea3a477d4e4c0430b

- Fix TwoPhaseGetDummyBackendId().  This was broken in commit
  ed0b409d22346b1b027a4c2099ca66984d94b6dd, which revised the
  GlobalTransactionData struct to not include the associated PGPROC as
  its first member, but overlooked one place where a cast was used in
  reliance on that equivalence.  The most effective way of fixing this
  seems to be to create a new function that looks up the
  GlobalTransactionData struct given the XID, and make both
  TwoPhaseGetDummyBackendId and TwoPhaseGetDummyProc rely on that.
  Per report from Robert Ross.
  http://git.postgresql.org/pg/commitdiff/db108349bf7fe7fe82e2ff32e42436cfbc4f37dc

- Update isolation tests' README file.  The directions explaining
  about running the prepared-transactions test were not updated in
  commit ae55d9fbe3871a5e6309d9b91629f1b0ff2b8cba.
  http://git.postgresql.org/pg/commitdiff/633f2fbd8835b5a46959e2fda88003f45f7fdba0

- Merge parser's p_relnamespace and p_varnamespace lists into a single
  list.  Now that we are storing structs in these lists, the
  distinction between the two lists can be represented with a couple
  of extra flags while using only a single list.  This simplifies the
  code and should save a little bit of palloc traffic, since the
  majority of RTEs are represented in both lists anyway.
  http://git.postgresql.org/pg/commitdiff/f630157496a70f8ece4fd4c27eeead88c74b9015

- Centralize the logic for detecting misplaced aggregates, window
  funcs, etc.  Formerly we relied on checking after-the-fact to see if
  an expression contained aggregates, window functions, or sub-selects
  when it shouldn't.  This is grotty, easily forgotten (indeed, we had
  forgotten to teach DefineIndex about rejecting window functions),
  and none too efficient since it requires extra traversals of the
  parse tree.  To improve matters, define an enum type that classifies
  all SQL sub-expressions, store it in ParseState to show what kind of
  expression we are currently parsing, and make
  transformAggregateCall, transformWindowFuncCall, and
  transformSubLink check the expression type and throw error if the
  type indicates the construct is disallowed.  This allows removal of
  a large number of ad-hoc checks scattered around the code base.  The
  enum type is sufficiently fine-grained that we can still produce
  error messages of at least the same specificity as before.  Bringing
  these error checks together revealed that we'd been none too
  consistent about phrasing of the error messages, so standardize the
  wording a bit.  Also, rewrite checking of aggregate arguments so
  that it requires only one traversal of the arguments, rather than up
  to three as before.  In passing, clean up some more comments left
  over from add_missing_from support, and annotate some tests that I
  think are dead code now that that's gone.  (I didn't risk actually
  removing said dead code, though.)
  http://git.postgresql.org/pg/commitdiff/eaccfded98a9c677d3a2e849c1747ec90e8596a6

- Update overlooked comment.
  http://git.postgresql.org/pg/commitdiff/a67d6d9a78fc7ae84e37b8984f2ecbb40a587400

- Support having multiple Unix-domain sockets per postmaster.  Replace
  unix_socket_directory with unix_socket_directories, which is a list
  of socket directories, and adjust postmaster's code to allow zero or
  more Unix-domain sockets to be created.  This is mostly a
  straightforward change, but since the Unix sockets ought to be
  created after the TCP/IP sockets for safety reasons (better chance
  of detecting a port number conflict), AddToDataDirLockFile needs to
  be fixed to support out-of-order updates of data directory lockfile
  lines.  That's a change that had been foreseen to be necessary
  someday anyway.  Honza Horak, reviewed and revised by Tom Lane
  http://git.postgresql.org/pg/commitdiff/c9b0cbe98bd783e24a8c4d8d8ac472a494b81292

- Fix dependencies generated during ALTER TABLE ADD CONSTRAINT USING
  INDEX.  This command generated new pg_depend entries linking the
  index to the constraint and the constraint to the table, which match
  the entries made when a unique or primary key constraint is built de
  novo.  However, it did not bother to get rid of the entries linking
  the index directly to the table.  We had considered the issue when
  the ADD CONSTRAINT USING INDEX patch was written, and concluded that
  we didn't need to get rid of the extra entries.  But this is wrong:
  ALTER COLUMN TYPE wasn't expecting such redundant dependencies to
  exist, as reported by Hubert Depesz Lubaczewski.  On reflection it
  seems rather likely to break other things as well, since there are
  many bits of code that crawl pg_depend for one purpose or another,
  and most of them are pretty naive about what relationships they're
  expecting to find.  Fortunately it's not that hard to get rid of the
  extra dependency entries, so let's do that.  Back-patch to 9.1,
  where ALTER TABLE ADD CONSTRAINT USING INDEX was added.
  http://git.postgresql.org/pg/commitdiff/b53800355f9b3a4a4ee6e5e610accab77af8d1c3

- Add link from COPY ref page to psql \copy.  Jeff Janes
  http://git.postgresql.org/pg/commitdiff/83af58f6b5657840f5924332fccecca1e3556abe

- Fix some issues with LATERAL(SELECT UNION ALL SELECT).  The LATERAL
  marking has to be propagated down to the UNION leaf queries when we
  pull them up.  Also, fix the formerly stubbed-off
  set_append_rel_pathlist().  It does already have enough smarts to
  cope with making a parameterized Append path at need; it just has to
  not assume that there *must* be an unparameterized path.
  http://git.postgresql.org/pg/commitdiff/e76af54137c051cafcb1e39f68383a31d1d55ff6

- More fixes for planner's handling of LATERAL.  Re-allow subquery
  pullup for LATERAL subqueries, except when the subquery is below an
  outer join and contains lateral references to relations outside that
  outer join.  If we pull up in such a case, we risk introducing
  lateral cross-references into outer joins' ON quals, which is
  something the code is entirely unprepared to cope with right now;
  and I'm not sure it'll ever be worth coping with.  Support lateral
  refs in VALUES (this seems to be the only additional path type that
  needs such support as a consequence of re-allowing subquery pullup).
  Put in a slightly hacky fix for joinpath.c's refusal to consider
  parameterized join paths even when there cannot be any
  unparameterized ones.  This was causing "could not devise a query
  plan for the given query" failures in queries involving more than
  two FROM items.  Put in an even more hacky fix for
  distribute_qual_to_rels() being unhappy with join quals that contain
  references to rels outside their syntactic scope; which is to say,
  disable that test altogether.  Need to think about how to preserve
  some sort of debugging cross-check here, while not expending more
  cycles than befits a debugging cross-check.
  http://git.postgresql.org/pg/commitdiff/c1774d2c8193a322706f681dd984ac439d3a9dbb

Magnus Hagander pushed:

- Fix upper limit of superuser_reserved_connections, add limit for
  wal_senders.  Should be limited to the maximum number of connections
  excluding autovacuum workers, not including.  Add similar check for
  max_wal_senders, which should never be higher than max_connections.
  http://git.postgresql.org/pg/commitdiff/b3055ab4fb5839a872bfe354b2b5ac31e6903ed6

== Rejected Patches (for now) ==

No one was disappointed this week :-)

== Pending Patches ==

Pavel Stehule sent in a patch to allow pretty-printing queries.

Alexander Korotkov sent in another revision of the patch to make
SP-GiST for ranges more efficient using 2-d mapping and quad-tree.

Pavel Stehule sent in another revision of the patch to allow assigning
a single-row query's target list to a corresponding list of psql
variables using a new command \gset.

Fujii Masao sent in a patch to fix a bug in pg_trgm.

Jeff Davis sent in another revision of the patch to add 16-bit
checksums to data pages.

Alexander Korotkov sent in two more revisions of a patch to improve
statistics and selectivity estimation for ranges.

Craig Ringer sent in a patch to implement value_to_json for
single-datum conversion.