Features Download
From: David Fetter <david <at> fetter.org>
Subject: == PostgreSQL Weekly News - November 17 2013 ==
Newsgroups: gmane.comp.db.postgresql.announce
Date: Monday 18th November 2013 06:41:59 UTC (over 4 years ago)
== PostgreSQL Weekly News - November 17 2013 ==

The third commitfest of the 9.4 cycle has begun.  Review!

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

PostgreSQL user groups have formed in Singapore and Toronto.

== PostgreSQL Product News ==

MJSQLView Version 3.47, a Java-based UI which supports PostgreSQL,

== PostgreSQL Jobs for November ==


== PostgreSQL Local ==

PGDay Cuba will be November 28-29, 2013.

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

Magnus Hagander pushed:

- Don't abort pg_basebackup when receiving empty WAL block.  This is a
  similar fix as c6ec8793aa59d1842082e14b4b4aae7d4bd883fd 9.2. This
  should never happen in 9.3 and newer since the special case cannot
  happen there, but this patch synchronizes up the code so there is no
  confusion on why they're different. An empty block is as harmless in
  9.3 as it was in 9.2, and can safely be ignored.

- Fix doc links in README file to work with new website layout.  Per
  report from Colin 't Hart

Tom Lane pushed:

- Re-allow duplicate aliases within aliased JOINs.  Although the SQL
  spec forbids duplicate table aliases, historically we've allowed
  queries like SELECT ... FROM tab1 x CROSS JOIN (tab2 x CROSS JOIN
  tab3 y) z on the grounds that the aliased join (z) hides the aliases
  within it, therefore there is no conflict between the two RTEs named
  "x".  The LATERAL patch broke this, on the misguided basis that "x"
  could be ambiguous if tab3 were a LATERAL subquery.  To avoid
  breaking existing queries, it's better to allow this situation and
  complain only if tab3 actually does contain an ambiguous reference.
  We need only remove the check that was throwing an error, because
  the column lookup code is already prepared to handle ambiguous
  references.  Per bug #8444.

- Fix ruleutils pretty-printing to not generate trailing whitespace.
  The pretty-printing logic in ruleutils.c operates by inserting a
  newline and some indentation whitespace into strings that are
  already valid SQL.  This naturally results in leaving some trailing
  whitespace before the newline in many cases; which can be annoying
  when processing the output with other tools, as complained of by Joe
  Abbate.  We can fix that in a pretty localized fashion by deleting
  any trailing whitespace before we append a pretty-printing newline.
  In addition, we have to modify the code inserted by commit
  2f582f76b1945929ff07116cd4639747ce9bb8a1 so that we also delete
  trailing whitespace when transposing items from temporary buffers
  into the main result string, when a temporary item starts with a
  newline.  This results in rather voluminous changes to the
  regression test results, but it's easily verified that they are only
  removal of trailing whitespace.  Back-patch to 9.3, because the
  aforementioned commit resulted in many more cases of trailing
  whitespace than had occurred in earlier branches.

- Fix failure with whole-row reference to a subquery.  Simple
  oversight in commit 1cb108efb0e60d87e4adec38e7636b6e8efbeb57 ---
  recursively examining a subquery output column is only sane if the
  original Var refers to a single output column.  Found by Kevin

- Clarify CREATE FUNCTION documentation about handling of typmods.
  The previous text was a bit misleading, as well as unnecessarily
  vague about what information would be discarded.  Per gripe from
  Craig Skinner.

- Fix incorrect column name in psql \d code.  pg_index.indisreplident
  had at one time in its development been called indisidentity.
  describe.c got missed when it was renamed.  Bug introduced in commit
  07cacba983ef79be4a84fcd0e0ca3b5fcb85dd65.  Andres Freund

- Add a regression test case for \d on an index.  Previous commit
  shows the need for this.  The coverage isn't really thorough, but
  it's better than nothing.

- Minor comment corrections for sequence hashtable patch.  There were
  enough typos in the comments to annoy me ...

- Prevent leakage of cached plans and execution trees in plpgsql DO
  blocks.  plpgsql likes to cache query plans and simple-expression
  execution state trees across calls.  This is a considerable win for
  multiple executions of the same function.  However, it's useless for
  DO blocks, since by definition those are executed only once and
  discarded.  Nonetheless, we were allowing a DO block's expression
  execution trees to survive until end of transaction, resulting in a
  significant intra-transaction memory leak, as reported by Yeb
  Havinga.  Worse, if the DO block exited with an error, the compiled
  form of the block's code was leaked till end of session --- along
  with subsidiary plancache entries.  To fix, make DO blocks keep
  their expression execution trees in a private EState that's deleted
  at exit from the block, and add a PG_TRY block to
  plpgsql_inline_handler to make sure that memory cleanup happens even
  on error exits.  Also add a regression test covering error handling
  in a DO block, because my first try at this broke that.  (The test
  is not meant to prove that we don't leak memory anymore, though it
  could be used for that with a much larger loop count.) Ideally we'd
  back-patch this into all versions supporting DO blocks; but the
  patch needs to add a field to struct PLpgSQL_execstate, and that
  would break ABI compatibility for third-party plugins such as the
  plpgsql debugger.  Given the small number of complaints so far,
  fixing this in HEAD only seems like an acceptable choice.

- Compute correct em_nullable_relids in get_eclass_for_sort_expr().
  Bug #8591 from Claudio Freire demonstrates that
  get_eclass_for_sort_expr must be able to compute valid
  em_nullable_relids for any new equivalence class members it creates.
  I'd worried about this in the commit message for
  db9f0e1d9a4a0842c814a464cdc9758c3f20b96c, but claimed that it wasn't
  a problem because multi-member ECs should already exist when it
  runs.  That is transparently wrong, though, because this function is
  also called by initialize_mergeclause_eclasses, which runs during
  deconstruct_jointree.  The example given in the bug report (which
  the new regression test item is based upon) fails because the
  COALESCE() expression is first seen by
  initialize_mergeclause_eclasses rather than process_equivalence.
  Fixing this requires passing the appropriate nullable_relids set to
  get_eclass_for_sort_expr, and it requires new code to compute that
  set for top-level expressions such as ORDER BY, GROUP BY, etc.  We
  store the top-level nullable_relids in a new field in PlannerInfo to
  avoid computing it many times.  In the back branches, I've added the
  new field at the end of the struct to minimize ABI breakage for
  planner plugins.  There doesn't seem to be a good alternative to
  changing get_eclass_for_sort_expr's API signature, though.  There
  probably aren't any third-party extensions calling that function
  directly; moreover, if there are, they probably need to think about
  what to pass for nullable_relids anyway.  Back-patch to 9.2, like
  the previous patch in this area.

- Clean up password prompting logic in streamutil.c.  The previous
  coding was fairly unreadable and drew double-free warnings from
  clang.  I believe the double free was actually not reachable,
  because PQconnectionNeedsPassword is coded to not return true if a
  password was provided, so that the loop can't iterate more than
  twice.  Nonetheless it seems worth rewriting.  No back-patch since
  this is just cosmetic.

- Speed up printing of INSERT statements in pg_dump.  In --inserts and
  especially --column-inserts mode, we can get a useful speedup by
  generating the common prefix of all a table's INSERT commands just
  once, and then printing the prebuilt string for each row.  This
  avoids multiple invocations of fmtId() and other minor fooling
  around.  David Rowley

- Fix incorrect loop counts in tidbitmap.c.  A couple of places that
  should have been iterating over WORDS_PER_CHUNK words were iterating
  over WORDS_PER_PAGE words instead.  This thinko accidentally failed
  to fail, because (at least on common architectures with default
  BLCKSZ) WORDS_PER_CHUNK is a bit less than WORDS_PER_PAGE, and the
  extra words being looked at were always zero so nothing happened.
  Still, it's a bug waiting to happen if anybody ever fools with the
  parameters affecting TIDBitmap sizes, and it's a small waste of
  cycles too.  So back-patch to all active branches.  Etsuro Fujita

- Remove pgbench's hardwired limit on line length in custom script
  files.  pgbench formerly failed on lines longer than BUFSIZ,
  unexpectedly splitting them into multiple commands.  Allow it to
  work with any length of input line.  Sawada Masahiko

- Allow aggregates to provide estimates of their transition state data
  size.  Formerly the planner had a hard-wired rule of thumb for
  guessing the amount of space consumed by an aggregate function's
  transition state data.  This estimate is critical to deciding
  whether it's OK to use hash aggregation, and in many situations the
  built-in estimate isn't very good.  This patch adds a column to
  pg_aggregate wherein a per-aggregate estimate can be provided,
  overriding the planner's default, and infrastructure for setting the
  column via CREATE AGGREGATE.  It may be that additional smarts will
  be required in future, perhaps even a per-aggregate estimation
  function.  But this is already a step forward.  This is extracted
  from a larger patch to improve the performance of numeric and int8
  aggregates.  I (tgl) thought it was worth reviewing and committing
  this infrastructure separately.  In this commit, all built-in
  aggregates are given aggtransspace = 0, so no behavior should
  change.  Hadi Moshayedi, reviewed by Pavel Stehule and Tomas Vondra

- Improve performance of numeric sum(), avg(), stddev(), variance(),
  etc.  This patch improves performance of most built-in aggregates
  that formerly used a NUMERIC or NUMERIC array as their transition
  type; this includes not only aggregates on numeric inputs, but some
  aggregates on integer inputs where overflow of an int8 value is a
  possibility.  The code now uses a special-purpose data structure to
  avoid array construction and deconstruction overhead, as well as
  packing and unpacking overhead for numeric values.  These
  aggregates' transition type is now declared as INTERNAL, since it
  doesn't correspond to any SQL data type.  To keep the planner from
  thinking that that means a lot of storage will be used, we make use
  of the just-added pg_aggregate.aggtransspace feature.  The space
  estimate is set to 128 bytes, which is at least in the right
  ballpark.  Hadi Moshayedi, reviewed by Pavel Stehule and Tomas

- Add make_date() and make_time() functions.  Pavel Stehule, reviewed
  by Jeevan Chalke and Atri Sharma

Peter Eisentraut pushed:

- Remove leftovers of IRIX port.  This removes the remaining pieces of
  the IRIX port that was removed by

- gitattributes: Make syntax compatible with older Git versions.
  Avoid the use of **, which was only introduced in Git version 1.8.2.

- Move variable closer to where it is used.  This avoids an unused
  variable warning on Windows when building without asserts From:
  David Rowley 

- Fix whitespace

- pg_upgrade: Fix some whitespace oddities

Robert Haas pushed:

- doc: Fix typo.  Reported by Thom Brown.

- Try again to make pg_isolation_regress work its build directory.  We
  can't search for the isolationtester binary until after we've set up
  the environment, because otherwise when find_other_exec() tries to
  invoke it with the -V option, it might fail for inability to locate
  a working libpq.  So postpone that step.  Andres Freund

- Fix relfilenodemap.c's handling of cache invalidations.  The old
  code entered a new hash table entry first, then scanned pg_class to
  determine what value to fill in, and then populated the entry.  This
  fails to work properly if a cache invalidation happens as a result
  of opening pg_class.  Repair.  Along the way, get rid of the idea of
  blowing away the entire hash table as a method of processing
  invalidations.  Instead, just delete all the entries one by one.
  This is probably not quite as cheap but it's simpler, and shouldn't
  happen often.  Andres Freund

- doc: Restore proper alphabetical order.  Colin 't Hart

Heikki Linnakangas pushed:

- Fix bug in GIN posting tree root creation.  The root page is filled
  with as many items as fit, and the rest are inserted using normal
  insertions. However, I fumbled the variable names, and the code
  actually memcpy'd all the items on the page, overflowing the buffer.
  While at it, rename the variable to make the distinction more clear.
  Reported by Teodor Sigaev. This bug was introduced by my recent
  refactorings, so no backpatching required.

- Use a hash table to store current sequence values.  This speeds up
  nextval() and currval(), when you touch a lot of different sequences
  in the same backend.  David Rowley

- Fix bogus hash table creation.  Andres Freund

Kevin Grittner pushed:

- Free ignorelist after each regression test schedule.  It's a trivial
  amount of RAM held until the end of the regression test run; but
  it's probably worth fixing to silence future warnings from code
  analyzers.  This was the only memory leak pointed out by clang's
  static code analysis tool.

- Fix buffer overrun in isolation test program.  Commit
  061b88c732952c59741374806e1e41c1ec845d50 saved argv0 to a global
  buffer without ensuring that it was zero terminated, allowing
  references to it to overrun the buffer and access other memory.
  This probably would not have presented any security risk, but could
  have resulted in very confusing failures if the path to the
  executable was very long.  Reported by David Rowley

Bruce Momjian pushed:

- docs:  clarify MVCC introduction to allow for per-statement

Andrew Dunstan pushed:

- Fix isolation check for MSVC to handle recent changes.

== Rejected Patches (for now) ==

No one was disappointed this week :-)

== Pending Patches ==

Zoltan Boszormenyi sent in a patch to implement ECPG fetch readahead.

Andres Freund sent in another flock of patches for logical changeset

Haribabu Kommi sent in another revision of a patch to fix an issue
where tables can bloat under heavy load despite autovacuum.

Dimitri Fontaine sent in another revision of a patch to implement

Pavel Stehule sent in another revision of a patch to implement DROP

Mark Travis sent in a patch to help operate pgbench to large scales.

Peter Eisentraut sent in a patch to split off mmfatal() from mmerror()
in ECPG.

Teodor Sigaev sent in a WIP patch to add nesting, types, and arrays to

Kyotaro HORIGUCHI sent in another revision of a patch to allow ORDER
BY to use unique indexes.

Peter Eisentraut sent in another revision of a patch to add CREATE
TRANSFORM and friends.

Jaime Casanova sent in two more revisions of a patch to turn
recovery.conf into GUCs.

Kyotaro HORIGUCHI sent in another revision of a patch to enable UNION
ALL on partitioned tables to use indexes.

Colin 't Hart sent in another revision of a patch to clarify the
differences between TABLE and SELECT * FROM.

Heikki Linnakangas sent in a flock of patches to clean up GIN index

Robert Berry sent in a patch to print out GUC cost parameters in
explain text output.

Peter Eisentraut sent in a patch to report the exit codes from
external recovery commands properly.

Shigeru HANADA sent in another revision of a patch to allow foreign
tables to inherit from local ones.

David Rowley sent in a patch to speed up json out functions by
changing a call to cstring_to_text to cstring_to_text_with_len using
information already available.

Mika Eloranta sent in two revisions of a patch to throttle
pg_basebackup so it shows progress reports no more than once per

Peter Eisentraut sent in a patch to fix an issue where pg_upgrade
incorrectly reports errors in the case "disk full".

Heikki Linnakangas sent in a patch to fix a race condition in B-tree
page deletion.

Amit Kapila sent in three more revisions of a patch to implement ALTER

Amit Kapila sent in another revision of a patch to allow starting a
standalone backend with full FE/BE protocol enabled.

SAKAMOTO Masahiko sent in two revisions of a patch to add a new
wal_level 'all' which logs WAL when updating hint bits.

Kyotaro HORIGUCHI sent in another revision of a patch to allow using
indexes for UNION queries.

Heikki Linnakangas sent in a patch to handle incomplete B-tree splits.

Andres Freund sent in a patch to add replication node identifiers and
crash-safe apply progress for LCRs.

Marko Kreen sent in a patch to use a better default cypher suite for

Mitsumasa KONDO sent in a patch to add min and max statement execution
time to pg_stat_statements.

Nigel Heron sent in another revision of a patch to add stats for
network traffic.

Peter Eisentraut sent in a PoC patch to implement ASSERTIONs in SQL.

Alexander Korotkov sent in three more revisions of a patch to improve
GIN by adding new information to the indexes.

Simon Riggs sent in another revision of a patch to implement a
sequence access method along with some code to exercise same.

Michael Paquier sent in another revision of a patch to implement

Alexander Korotkov sent in another revision of a patch to enable fast
scans on GIN indexes.

Peter Geoghegan sent in two revisions of a patch to allow
pg_stat_statements to store arbitrarily long query texts, using an
external, transparently managed lookaside file.

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

Atri Sharma sent in another revision of a patch to implement WITHIN
GROUP, some applications of which include MEDIAN and quantiles.

Mitsumasa KONDO sent in a patch to optimize kernel readahead using
buffer access strategy.

David Rowley sent in two more revisions of a patch to change certain
calls to appendPQExpBuffer() into calls to appendPQExpBufferStr() for

Pavel Stehule sent in another revision of a patch to add assertions to

Haribabu Kommi sent in three more revisions of a patch to add an
option to pg_basebackup to specify a different directory for pg_xlog.

Andres Freund sent in another revision of a patch to add better

David Rowley sent in a patch to change some instances of strncpy to

Fabrízio de Royes Mello sent in a patch to fix an issue where
pg_isready's -d function did not work.

Andres Freund sent in a flock of patches to implement a wait-free LW
shared acquisition.

Heikki Linnakangas sent in another revision of a patch to use HUGE_TLB
where supported.

Andrew Dunstan sent in two revisions of a patch to implement
pre-commit triggers.

Chris Browne sent in another revision of a patch to enhance createuser
by allowing the caller to specify roles the user will have.

Mikhail Tyurin sent in a patch to fix an issue where freeze couldn't
be finished.

Alexey Klyukin sent in a patch to enhance wildcards in .pgpass.

Ian Lawrence Barwick sent in another revision of a patch to add more
autocomplete to psql's \pset.

Tom Lane sent in another revision of a patch to implement UNNEST with
multiple arguments and TABLE() with multiple functions.

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