== PostgreSQL Weekly News - November 17 2013 ==

== PostgreSQL Weekly News - November 17 2013 ==

The third commitfest of the 9.4 cycle has begun. Review!
https://commitfest.postgresql.org/action/commitfest_view?id=20

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 user groups have formed in Singapore and Toronto.

== PostgreSQL Product News ==

MJSQLView Version 3.47, a Java-based UI which supports PostgreSQL, released.
http://dandymadeproductions.com/projects/MyJSQLView/

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

== 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(at)fetter(dot)org, German language
to pwn(at)pgug(dot)de, Italian language to pwn(at)itpug(dot)org(dot) Spanish language
to pwn(at)arpug(dot)com(dot)ar(dot)

== 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.
http://git.postgresql.org/pg/commitdiff/705556a631c5908cd3caa0b973be13d994ff63e7

- Fix doc links in README file to work with new website layout. Per
report from Colin 't Hart
http://git.postgresql.org/pg/commitdiff/f1d6875916055914920f657ad88011c5b525f220

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.
http://git.postgresql.org/pg/commitdiff/648bd05b13b3624e494ae2996c2d2e0241cefe87

- 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.
http://git.postgresql.org/pg/commitdiff/0b7e660d6c70f45e06f1b52f255116bcb44624eb

- 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
Grittner.
http://git.postgresql.org/pg/commitdiff/ebefbb5fdeba885bb57d2f2e1eb185a46c9d777d

- 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.
http://git.postgresql.org/pg/commitdiff/5d924f067c0cc0b15709bdf604e6ac5f33158eb6

- 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
http://git.postgresql.org/pg/commitdiff/e694cf25d787354ed04310a14aa508692874dcad

- 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.
http://git.postgresql.org/pg/commitdiff/982b82d6b1fd007b9357ce198d13a55544888534

- Minor comment corrections for sequence hashtable patch. There were
enough typos in the comments to annoy me ...
http://git.postgresql.org/pg/commitdiff/80e3a470ba46bc35fb1ec22d4e97126270f3d2b3

- 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.
http://git.postgresql.org/pg/commitdiff/c7b849a89645212121da480091f87d11fac82495

- 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.
http://git.postgresql.org/pg/commitdiff/f3b3b8d5be00ebcfade3644fca7a80e25fd88a70

- 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.
http://git.postgresql.org/pg/commitdiff/3172eea062e779db39df9a48fca0ad7448163f98

- 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
http://git.postgresql.org/pg/commitdiff/97e1ec467099f1e581f491c8a57b7d56d0b9c539

- 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
http://git.postgresql.org/pg/commitdiff/f1f21b2d6fd170faf9824306ef4f4950c32ce49d

- 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
http://git.postgresql.org/pg/commitdiff/61a07bae47886b8333b9cce882d73d5fdaaec618

- 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
http://git.postgresql.org/pg/commitdiff/6cb86143e8e1e855255edc706bce71c6ebfd9a6c

- 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
Vondra
http://git.postgresql.org/pg/commitdiff/69c8fbac201652282e18b0e2e301d4ada991fbde

- Add make_date() and make_time() functions. Pavel Stehule, reviewed
by Jeevan Chalke and Atri Sharma
http://git.postgresql.org/pg/commitdiff/f901bb50e33ad95593bb68f7b3b55eb2e47607dc

Peter Eisentraut pushed:

- Remove leftovers of IRIX port. This removes the remaining pieces of
the IRIX port that was removed by
ea91a6be89575095f61ebf36d67c2df98be093db.
http://git.postgresql.org/pg/commitdiff/3626adf26652cdf76a26ae14b2d75cb7801da7a9

- gitattributes: Make syntax compatible with older Git versions.
Avoid the use of **, which was only introduced in Git version 1.8.2.
http://git.postgresql.org/pg/commitdiff/c0764a542530c7ebf8abd2766d256d49d27e90e8

- Move variable closer to where it is used. This avoids an unused
variable warning on Windows when building without asserts From:
David Rowley <dgrowleyml(at)gmail(dot)com>
http://git.postgresql.org/pg/commitdiff/aa04b323c36931f96159ab1fd6f74f8401ab4c92

- Fix whitespace
http://git.postgresql.org/pg/commitdiff/87d8378f60cef2206b0d4a09eb773ed416fa58fc

- pg_upgrade: Fix some whitespace oddities
http://git.postgresql.org/pg/commitdiff/55c3d86a2a374f9d8fd88fd947601c1f49a4da08

Robert Haas pushed:

- doc: Fix typo. Reported by Thom Brown.
http://git.postgresql.org/pg/commitdiff/9cab81b5721b1a64c8e31feebbc0ae5efd6ddf71

- 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
http://git.postgresql.org/pg/commitdiff/061b88c732952c59741374806e1e41c1ec845d50

- 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
http://git.postgresql.org/pg/commitdiff/c46c803f8ad4ba80472b280703983ecf8021099e

- doc: Restore proper alphabetical order. Colin 't Hart
http://git.postgresql.org/pg/commitdiff/71dd54ada9c3d32dfc0eb082ff2023b12abe881a

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.
http://git.postgresql.org/pg/commitdiff/07fca603b56e39c50478b44070fdfb38313cd51c

- 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
http://git.postgresql.org/pg/commitdiff/21025d4a5322307bcfef1222f497a3aacb2fc79a

- Fix bogus hash table creation. Andres Freund
http://git.postgresql.org/pg/commitdiff/5cb719beeef2e871c9f0b22e799554bf801ac390

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.
http://git.postgresql.org/pg/commitdiff/fe67d252337ef2aba3d4ea06a4aa87cdbc75b933

- 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
http://git.postgresql.org/pg/commitdiff/7cb964acb794078ef033cbf2e3a0e7670c8992a9

Bruce Momjian pushed:

- docs: clarify MVCC introduction to allow for per-statement
snapshots
http://git.postgresql.org/pg/commitdiff/cd8115e0090cb04380292f880adcec9c2fc2e0f3

Andrew Dunstan pushed:

- Fix isolation check for MSVC to handle recent changes.
http://git.postgresql.org/pg/commitdiff/869b1e4a678cc6d78a64ed01682ddf8f8debd781

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

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
EXTENSION TEMPLATEs.

Pavel Stehule sent in another revision of a patch to implement DROP
{TRIGGER|RULE} [IF EXISTS].

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

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

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

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

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

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
REINDEX CONCURRENTLY.

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

Pavel Stehule sent in another revision of a patch to add assertions to
PL/pgsql.

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

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

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.