== PostgreSQL Weekly News - November 24 2013 ==

== PostgreSQL Weekly News - November 24 2013 ==

== PostgreSQL Product News ==

pgCluu 1.0, a Perl program audit PostgreSQL Cluster performance,
released.
http://pgcluu.darold.net/

Sergei Konoplev has released PgCookbook.
https://github.com/grayhemp/pgcookbook

VMware vFabric Postgres 9.3 released.
http://www.vmware.com/products/vfabric-postgres

== PostgreSQL 9.3 Feature of the Week ==

== PostgreSQL Tip of the Week ==

== PostgreSQL Jobs for November ==

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

== PostgreSQL Local ==

PGDay Cuba will be November 28-29, 2013.
http://postgresql.uci.cu/?page_id=474

FOSDEM PGDay, a one day conference held before FOSDEM in Brussels,
Belgium, will be on Jan 31st, 2014.  Details:
http://fosdem2014.pgconf.eu/
CfP is open:
http://fosdem2014.pgconf.eu/callforpapers/

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

Heikki Linnakangas pushed:

- Count locked pages that don't need vacuuming as scanned.
  Previously, if VACUUM skipped vacuuming a page because it's pinned,
  it didn't count that page as scanned. However, that meant that
  relfrozenxid was not bumped up either, which prevented
  anti-wraparound vacuum from doing its job.  Report by Миша Тюрин,
  analysis and patch by Sergey Burladyn and Jeff Janes.  Backpatch to
  9.2, where the skip-locked-pages behavior was introduced.
  http://git.postgresql.org/pg/commitdiff/4c697d8f4845823a8af67788b219ffa4516ad14c

- Replace appendPQExpBuffer(..., <constant>) with
  appendPQExpBufferStr.  Arguably makes the code a bit more readable,
  and might give a small performance gain.  David Rowley
  http://git.postgresql.org/pg/commitdiff/32ceba3ea730b6b1bd3eca786f72d61945ad42b7

- Spell SQL keywords in uppercase in pg_dump's query.  The server
  won't care, but let's be consistent.  David Rowley.
  http://git.postgresql.org/pg/commitdiff/fea437681d68a0027091c2d534c69c0965cf06f1

- Further GIN refactoring.  Merge some functions that were always
  called together. Makes the code little bit more readable.
  http://git.postgresql.org/pg/commitdiff/04965ad40e10677ceec94d871a183e73023b238f

- Refactor the internal GIN B-tree interface for forming a downlink.
  This creates a new gin-btree callback function for creating a
  downlink for a page. Previously, ginxlog.c duplicated the logic used
  during normal operation.
  http://git.postgresql.org/pg/commitdiff/501012631e7d2e1c22f60b11ff8cb62ae021625e

- More GIN refactoring.  Split off the portion of ginInsertValue that
  inserts the tuple to current level into a separate function,
  ginPlaceToPage. ginInsertValue's charter is now to recurse up the
  tree to insert the downlink, when a page split is required.  This is
  in preparation for a patch to change the way incomplete splits are
  handled, which will need to do these operations separately. And IMHO
  makes the code more readable anyway.
  http://git.postgresql.org/pg/commitdiff/04eee1fa9ee80dabf7cf4b8b9106897272e9b291

- Avoid acquiring spinlock when checking if recovery has finished, for
  speed.  RecoveryIsInProgress() can be called very frequently. During
  normal operation, it just checks a backend-local variable and
  returns quickly, but during hot standby, it checks a
  spinlock-protected shared variable.  Those spinlock acquisitions can
  become a point of contention on a busy hot standby system.  Replace
  the spinlock acquisition with a memory barrier.  Per discussion with
  Andres Freund, Ants Aasma and Merlin Moncure.
  http://git.postgresql.org/pg/commitdiff/1a3d104475ce01326fc00601ed66ac4d658e37e5

- Fix Hot-Standby initialization of clog and subtrans.  These bugs can
  cause data loss on standbys started with hot_standby=on at the
  moment they start to accept read only queries, by marking committed
  transactions as uncommited. The likelihood of such corruptions is
  small unless the primary has a high transaction rate.
  5a031a5556ff83b8a9646892715d7fef415b83c3 fixed bugs in HS's startup
  logic by maintaining less state until at least
  STANDBY_SNAPSHOT_PENDING state was reached, missing the fact that
  both clog and subtrans are written to before that. This only failed
  to fail in common cases because the usage of ExtendCLOG in
  procarray.c was superflous since clog extensions are actually WAL
  logged.  f44eedc3f0f347a856eea8590730769125964597/I then tried to
  fix the missing extensions of pg_subtrans due to the former commit's
  changes - which are not WAL logged - by performing the extensions
  when switching to a state > STANDBY_INITIALIZED and not performing
  xid assignments before that - again missing the fact that ExtendCLOG
  is unneccessary - but screwed up twice: Once because
  latestObservedXid wasn't updated anymore in that state due to the
  earlier commit and once by having an off-by-one error in the loop
  performing extensions. This means that whenever a
  CLOG_XACTS_PER_PAGE (32768 with default settings) boundary was
  crossed between the start of the checkpoint recovery started from
  and the first xl_running_xact record old transactions commit bits in
  pg_clog could be overwritten if they started and committed in that
  window.  Fix this mess by not performing ExtendCLOG() in HS at all
  anymore since it's unneeded and evidently dangerous and by
  performing subtrans extensions even before reaching
  STANDBY_SNAPSHOT_PENDING.  Analysis and patch by Andres Freund.
  Reported by Christophe Pettus.  Backpatch down to 9.0, like the
  previous commit that caused this.
  http://git.postgresql.org/pg/commitdiff/98f58a30c1beb6ec0870d6520f49fb40d9d0b566

Robert Haas pushed:

- Use cstring_to_text_with_len when length is known.  This avoids a
  potentially-expensive extra call to strlen().  David Rowley
  http://git.postgresql.org/pg/commitdiff/f1df4731eea6bc05e0769e9cc789e7304722efe4

Bruce Momjian pushed:

- unaccent:  Revert patch 9299f6179838cef8aa1123f6fb76f0d3d6f2decc.
  The reverted patch to change functions from strict to immutable was
  incorrect and needs additional research.
  http://git.postgresql.org/pg/commitdiff/0dbf9a6a91675b99a5097528729c38e7f3015646

- pg_upgrade:  avoid ALTER COLUMN TYPE on inherited columns.  This
  only affects upgrades from 8.3 currently, and is harmless as the
  child just generates an error in the script, but we should get it
  right in case we ever need this for more complex uses.  Per report
  from Peter Eisentraut
  http://git.postgresql.org/pg/commitdiff/dbd786bc4f497210551a7640ee2f6afe877cc3e9

- docs:  update page format to specify page checksum field.  Backpatch
  to 9.3.  Per report from Steffen Hildebrandt
  http://git.postgresql.org/pg/commitdiff/22967d80035427f6e9e31210b7de2e45fafcbc0c

Peter Eisentraut pushed:

- pg_upgrade: Report full disk better.  Previously, pg_upgrade would
  abort copy_file() on a short write without setting errno, which the
  caller would report as an error with the message "Success".  We
  assume ENOSPC in that case, as we do elsewhere in the code.  Also
  set errno in some other error cases in copy_file() to avoid bogus
  "Success" error messages.  This was broken in
  6b711cf37c228749b6a8cef50e16e3c587d18dd4, so 9.2 and before are OK.
  http://git.postgresql.org/pg/commitdiff/bd5a9a50e4946ffc58a632b5717c8cfddd3ea6ce

- ecpg: Split off mmfatal() from mmerror().  This allows decorating
  mmfatal() with noreturn compiler hints, leading to better
  diagnostics.
  http://git.postgresql.org/pg/commitdiff/b21de4e7b32f868a23bdc5507898d36cbe146164

- Tweak streamutil.c further to avoid scan-build warning.  The
  previous change added a new scan-build warning about need_password
  assigned but not read.
  http://git.postgresql.org/pg/commitdiff/f4482a542c30034c7871fd35050128823ef5c6d5

- Avoid potential buffer overflow crash.  A pointer to a C string was
  treated as a pointer to a "name" datum and passed to
  SPI_execute_plan().  This pointer would then end up being passed
  through datumCopy(), which would try to copy the entire 64 bytes of
  name data, thus running past the end of the C string.  Fix by
  converting the string to a proper name structure.  Found by LLVM
  AddressSanitizer.
  http://git.postgresql.org/pg/commitdiff/4053189d594a5eb1949bba26766fdb0de837e255

- Fix thinko in SPI_execute_plan() calls.  Two call sites were
  apparently thinking that the last argument of SPI_execute_plan() is
  the number of query parameters, but it is actually the row limit.
  Change the calls to 0, since we don't care about the limit there.
  The previous code didn't break anything, but it was still wrong.
  http://git.postgresql.org/pg/commitdiff/b7212c9726ee4bdf0ddd938742f03e86d8c431bf

- PL/Tcl: Add event trigger support.  From: Dimitri Fontaine
  <dimitri@2ndQuadrant.fr>
  http://git.postgresql.org/pg/commitdiff/a5036ca998a6058f60913d43a80badfcbb65f5bb

Fujii Masao pushed:

- Add tab completion for \pset in psql.  Pavel Stehule, reviewed by
  Ian Lawrence Barwick
  http://git.postgresql.org/pg/commitdiff/b1543cc8a84bc3ca699497286608274c2533fd53

- Fix pg_isready to handle -d option properly.  Previously, -d option
  for pg_isready was broken. When the name of the database was
  specified by -d option, pg_isready failed with an error.  When the
  conninfo specified by -d option contained the setting of the host
  name but not Numeric IP address (i.e., hostaddr), pg_isready
  displayed wrong connection message. -d option could not handle a
  valid URI prefix at all. This commit fixes these bugs of pg_isready.
  Backpatch to 9.3, where pg_isready was introduced.  Per report from
  Josh Berkus and Robert Haas.  Original patch by Fabrízio de Royes
  Mello, heavily modified by me.
  http://git.postgresql.org/pg/commitdiff/38f432898131270e5b64245786cb67f322538bae

Tom Lane pushed:

- Support multi-argument UNNEST(), and TABLE() syntax for multiple
  functions.  This patch adds the ability to write TABLE( function1(),
  function2(), ...) as a single FROM-clause entry.  The result is the
  concatenation of the first row from each function, followed by the
  second row from each function, etc; with NULLs inserted if any
  function produces fewer rows than others.  This is believed to be a
  much more useful behavior than what Postgres currently does with
  multiple SRFs in a SELECT list.  This syntax also provides a
  reasonable way to combine use of column definition lists with WITH
  ORDINALITY: put the column definition list inside TABLE(), where
  it's clear that it doesn't control the ordinality column as well.
  Also implement SQL-compliant multiple-argument UNNEST(), by turning
  UNNEST(a,b,c) into TABLE(unnest(a), unnest(b), unnest(c)).  The SQL
  standard specifies TABLE() with only a single function, not multiple
  functions, and it seems to require an implicit UNNEST() which is not
  what this patch does.  There may be something wrong with that
  reading of the spec, though, because if it's right then the spec's
  TABLE() is just a pointless alternative spelling of UNNEST().  After
  further review of that, we might choose to adopt a different syntax
  for what this patch does, but in any case this functionality seems
  clearly worthwhile.  Andrew Gierth, reviewed by Zoltán Böszörményi
  and Heikki Linnakangas, and significantly revised by me
  http://git.postgresql.org/pg/commitdiff/784e762e886e6f72f548da86a27cd2ead87dbd1c

- Fix quoting in help messages in uuid-ossp extension scripts.  The
  command we're telling people to type needs to include double-quoting
  around the unfortunately-chosen extension name.  Twiddle the textual
  quoting so that it looks somewhat sane.  Per gripe from roadrunner6.
  http://git.postgresql.org/pg/commitdiff/f29baf92257ae614a2097b4e86b00e8a62802e2e

- Flatten join alias Vars before pulling up targetlist items from a
  subquery.  pullup_replace_vars()'s decisions about whether a
  pulled-up replacement expression needs to be wrapped in a
  PlaceHolderVar depend on the assumption that what looks like a Var
  behaves like a Var.  However, if the Var is a join alias reference,
  later flattening of join aliases might replace the Var with
  something that's not a Var at all, and should have been wrapped.  To
  fix, do a forcible pass of flatten_join_alias_vars() on the subquery
  targetlist before we start to copy items out of it.  We'll re-run
  that processing on the pulled-up expressions later, but that's
  harmless.  Per report from Ken Tanzer; the added regression test
  case is based on his example.  This bug has been there since the
  PlaceHolderVar mechanism was invented, but has escaped detection
  because the circumstances that trigger it are fairly narrow.  You
  need a flattenable query underneath an outer join, which contains
  another flattenable query inside a join of its own, with a dangerous
  expression (a constant or something else non-strict) in that one's
  targetlist.  Having seen this, I'm wondering if it wouldn't be
  prudent to do all alias-variable flattening earlier, perhaps even in
  the rewriter.  But that would probably not be a back-patchable
  change.
  http://git.postgresql.org/pg/commitdiff/f19e92ed040c2afba2333f0ce547848f4dc4ec21

- Ensure _dosmaperr() actually sets errno correctly.  If logging is
  enabled, either ereport() or fprintf() might stomp on errno
  internally, causing this function to return the wrong result.  That
  might only end in a misleading error report, but in any code that's
  examining errno to decide what to do next, the consequences could be
  far graver.  This has been broken since the very first version of
  this file in 2006 ... it's a bit astonishing that we didn't identify
  this long ago.  Reported by Amit Kapila, though this isn't his
  proposed fix.
  http://git.postgresql.org/pg/commitdiff/f145454d57bc9dfd105f7236a03a00dd25395dd2

- Fix array slicing of int2vector and oidvector values.  The previous
  coding labeled expressions such as pg_index.indkey[1:3] as being of
  int2vector type; which is not right because the subscript bounds of
  such a result don't, in general, satisfy the restrictions of
  int2vector.  To fix, implicitly promote the result of slicing
  int2vector to int2[], or oidvector to oid[].  This is similar to
  what we've done with domains over arrays, which is a good analogy
  because these types are very much like restricted domains of the
  corresponding regular-array types.  A side-effect is that we now
  also forbid array-element updates on such columns, eg while "update
  pg_index set indkey[4] = 42" would have worked before if you were
  superuser (and corrupted your catalogs irretrievably, no doubt) it's
  now disallowed.  This seems like a good thing since, again, some
  choices of subscripting would've led to results not satisfying the
  restrictions of int2vector.  The case of an array-slice update was
  rejected before, though with a different error message than you get
  now.  We could make these cases work in future if we added a cast
  from int2[] to int2vector (with a cast function checking the
  subscript restrictions) but it seems unlikely that there's any value
  in that.  Per report from Ronan Dunklau.  Back-patch to all
  supported branches because of the crash risks involved.
  http://git.postgresql.org/pg/commitdiff/45e02e3232ac7cc5ffe36f7986159b5e0b1f6fdc

- Defend against bad trigger definitions in contrib/lo's lo_manage()
  trigger.  This function formerly crashed if called as a
  statement-level trigger, or if a column-name argument wasn't given.
  In passing, add the trigger name to all error messages from the
  function.  (None of them are expected cases, so this shouldn't pose
  any compatibility risk.) Marc Cousin, reviewed by Sawada Masahiko
  http://git.postgresql.org/pg/commitdiff/64d15e424091d8fd2c882356ee4bf07cfc5ff69d

- Fix new and latent bugs with errno handling in
  secure_read/secure_write.  These functions must be careful that they
  return the intended value of errno to their callers.  There were
  several scenarios where this might not happen: 1. The recent SSL
  renegotiation patch added a hunk of code that would execute after
  setting errno.  In the first place, it's doubtful that we should
  consider renegotiation to be successfully completed after a failure,
  and in the second, there's no real guarantee that the called OpenSSL
  routines wouldn't clobber errno.  Fix by not executing that hunk
  except during success exit.  2. errno was left in an unknown state
  in case of an unrecognized return code from SSL_get_error().  While
  this is a "can't happen" case, it seems like a good idea to be sure
  we know what would happen, so reset errno to ECONNRESET in such
  cases.  (The corresponding code in libpq's fe-secure.c already did
  this.) 3. There was an (undocumented) assumption that
  client_read_ended() wouldn't change errno.  While true in the
  current state of the code, this seems less than future-proof.  Add
  explicit saving/restoring of errno to make sure that changes in the
  called functions won't break things.  I see no need to back-patch,
  since #1 is new code and the other two issues are mostly
  hypothetical.  Per discussion with Amit Kapila.
  http://git.postgresql.org/pg/commitdiff/36a3be6540b90c6a5d307c0ed9de2076ce5a821c

Michael Meskes pushed:

- Distinguish between C and SQL mode for C-style comments.  SQL
  standard asks for allowing nested comments, while C does not.
  Therefore the two comments, while mostly similar, have to be parsed
  seperately.
  http://git.postgresql.org/pg/commitdiff/8ac5e88f9fc54c480ed1419c0ce94f54653fe8c3

- Allow C array definitions to use sizeof().  When parsing C variable
  definitions ecpg should allow sizeof() operators as array
  dimensions.
  http://git.postgresql.org/pg/commitdiff/08d1b22b3be2305ad6b83ca275829ff26305f5d7

Jeff Davis pushed:

- Lessen library-loading log level.  Previously, messages were emitted
  at the LOG level every time a backend preloaded a library. That was
  acceptable (though unnecessary) for shared_preload_libraries; but it
  was excessive for local_preload_libraries and
  session_preload_libraries. Reduce to DEBUG1.  Also, there was logic
  in the EXEC_BACKEND case to avoid repeated messages for
  shared_preload_libraries by demoting them to DEBUG2. DEBUG1 seems
  more appropriate there, as well, so eliminate that special case.
  Peter Geoghegan.
  http://git.postgresql.org/pg/commitdiff/559d535819908adfe2d2ca674fb8562bbd133cef


== Rejected Patches (for now) ==

No one was disappointed this week :-)

== Pending Patches ==

Michael Paquier sent in another revision of a patch to implement
REINDEX CONCURRENTLY.

Fabrízio de Royes Mello sent in another revision of a patch to
implement IF NOT EXISTS where appropriate.

Rajeev Rastogi sent in three more revisions of a patch to add a count
tag to COPY FROM STDIN.

Mitsumasa KONDO sent in another revision of a patch to add an average
time to pg_stat_statements.

Peter Geoghegan sent in another revision of a patch to implement
autovacuum_work_mem as a separate parameter from work_mem.

Sergey V. Karpov sent in two more revisions of a patch to improve GIN
indexing with a fast scan.

Haribabu Kommi sent in three more revisions of a patch to allow users
to specify a different xlog directory in pg_basebackup.

KaiGai Kohei and Jim Mlodgenski traded patches implementing the
CustomScan API, providing examples, and documenting same.

Sameer Thakur and Peter Geoghegan traded patches to fix some issues
in pg_stat_statements where a wrong estimate could propagate in
unpleasant ways.

Wim Dumon sent in a patch to fix building libpq standalone on Windows.

Andres Freund sent in another flock of patches for better atomics.

David Rowley sent in another revision of a patch to make CLUSTER
always also FREEZE.

Christopher Browne sent in two more revisions of a patch to
create_user which allows the caller to specify the roles of which the
new user is a member.

Kyotaro HORIGUCHI sent in more patches to add pathkeys and uniqueness
information to the Plan structure and uses same to ensure indexes are
used in certain UNION queries.

Bruce Momjian sent in four more revisions of a patch to issue a
warning when calling SET TRANSACTION outside a transaction block.

Heikki Linnakangas sent in another revision of a patch to implement
INSERT ... ON DUPLICATE KEY LOCK FOR UPDATE.

Fabrízio de Royes Mello sent in a patch to enable storing extension
options.

Peter Eisentraut sent in another revision of a patch to implement
information schema parameter_default.

Zoltan Boszormenyi sent in a flock of patches to change the ECPG
infrastructure.

Atri Sharma sent in another revision of a patch to implement WITHIN
GROUP.

Craig Ringer sent in a WIP patch to implement updatable security
barrier views.

Fabrízio de Royes Mello sent in a patch to allow psql to see disabled
triggers in \d.

Heikki Linnakangas and Michael Paquier traded patches to fix the way
GIN indexes handle incomplete splits.

Pavel Stehule sent in two more revisions of a patch to add new border
styles for psql.

Andres Freund sent in another revision of a patch to preserve forensic
information when freezing.

Amit Kapila sent in two revisions of a patch to ensure that the server
starts with DEBUG5 when requested.

Haribabu Kommi sent in another revision of a patch to fix a race
condition in autovacuum which can cause tables to bloat when
autovacuum is running.

Alvaro Herrera sent in another revision of a patch to fix an issue
where MultiXact was pessimized in 9.3.

Gurjeet Singh sent in another revision of a patch to optimize the
startup sequence.

Kevin Grittner sent in three more revisions of a patch to fix a
mismatch in pg_dump with transaction isolation level.

Rodolfo Campero sent in two more revisions of a patch to enable domain
over array support in PL/PythonU.

Tom Lane sent in another revision of a patch to fix an issue where
PostgreSQL won't start on Windows with certain parameters of excessive
length.

Peter Eisentraut sent in another revision of a patch to document the
way external programs' exit codes are handled during recovery.

J Smith sent in a patch to enable Google coredumper.