== PostgreSQL Weekly News - June 08 2014 ==

== PostgreSQL Weekly News - June 08 2014 ==

Postgres Open 2014 Early Bird Tickets and Tutorials are on sale.
https://postgresopen.org/2014/tickets/

== PostgreSQL Product News ==

s2x (SQL to Anything) 1.1 released:
http://s2x.co

== PostgreSQL Jobs for June ==

http://archives.postgresql.org/pgsql-jobs/2014-06/threads.php

== PostgreSQL Local ==

The first Swiss Postgres Conference will be on June 24th, 2014 at the
HSR Hochschule für Technik Rapperswil.
http://www.postgres-conference.ch/

Char(14) and PGday UK will be held July 8 and 9, 2014.
http://www.char14.info
http://postgresqlusergroup.org.uk/

The sixth PGDay Cubano be held on 13 and 14 October 2014 in Habana.
https://postgresql.uci.cu/?p=380

Postgres Open 2014 will be in Chicago, IL, USA, September 17-19. The
CfP is open!
http://postgresopen.org/2014/callforpapers/

PostgreSQL Conference Europe 2014 will be held on October 21-24 in
Madrid, Spain, at the Hotel Miguel Angel.
http://2014.pgconf.eu/

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

Bruce Momjian pushed:

- doc: fix JSON function prototype variable label. from_jsonb ->
from_json, for consistency. Patch by rudolf (private report)
http://git.postgresql.org/pg/commitdiff/19c9da1d9654cbfd95bae26caff497d78277fb6e

Andres Freund pushed:

- Use unaligned output in another regression test query to reduce diff
noise. Use the unaligned/no rowcount output mode in a regression
tests that shows all built-in leakproof functions. Currently a new
leakproof function will often change the alignment of all existing
functions, making it hard to see the actual difference and creating
unnecessary patch conflicts. Noticed while looking over a patch
introducing new leakproof functions.
http://git.postgresql.org/pg/commitdiff/5eebb8d954adf9d600b1a157682539db1e8a3ada

- Set the process latch when processing recovery conflict interrupts.
Because RecoveryConflictInterrupt() didn't set the process latch
anything using the latter to wait for events didn't get notified
about recovery conflicts. Most latch users are never the target of
recovery conflicts, which explains the lack of reports about this
until now. Since 9.3 two possible affected users exist though: The
sql callable pg_sleep() now uses latches to wait and background
workers are expected to use latches in their main loop. Both would
currently wait until the end of WaitLatch's timeout. Fix by adding
a SetLatch() to RecoveryConflictInterrupt(). It'd also be possible
to fix the issue by having each latch user set set_latch_on_sigusr1.
That seems failure prone and though, as most of these callsites
won't often receive recovery conflicts and thus will likely only be
tested against normal query cancels et al. It'd also be
unnecessarily verbose. Backpatch to 9.1 where latches were
introduced. Arguably 9.3 would be sufficient, because that's where
pg_sleep() was converted to waiting on the latch and background
workers got introduced; but there could be user level code making
use of the latch pre 9.3.
http://git.postgresql.org/pg/commitdiff/44445b28d265fe91fea3da47bd3a8c4007ce87e5

- Fix longstanding bug in HeapTupleSatisfiesVacuum().
HeapTupleSatisfiesVacuum() didn't properly discern between
DELETE_IN_PROGRESS and INSERT_IN_PROGRESS for rows that have been
inserted in the current transaction and deleted in a aborted
subtransaction of the current backend. At the very least that caused
problems for CLUSTER and CREATE INDEX in transactions that had
aborting subtransactions producing rows, leading to warnings like:
WARNING: concurrent delete in progress within table "..." possibly
in an endless, uninterruptible, loop. Instead of treating
*InProgress xmins the same as *IsCurrent ones, treat them as being
distinct like the other visibility routines. As implemented this
separatation can cause a behaviour change for rows that have been
inserted and deleted in another, still running, transaction. HTSV
will now return INSERT_IN_PROGRESS instead of DELETE_IN_PROGRESS for
those. That's both, more in line with the other visibility routines
and arguably more correct. The latter because a INSERT_IN_PROGRESS
will make callers look at/wait for xmin, instead of xmax. The only
current caller where that's possibly worse than the old behaviour is
heap_prune_chain() which now won't mark the page as prunable if a
row has concurrently been inserted and deleted. That's harmless
enough. As a cautionary measure also insert a interrupt check
before the gotos in IndexBuildHeapScan() that lead to the
uninterruptible loop. There are other possible causes, like a row
that several sessions try to update and all fail, for repeated loops
and the cost of doing so in the retry case is low. As this bug goes
back all the way to the introduction of subtransactions in
573a71a5da backpatch to all supported releases. Reported-By: Sandro
Santilli
http://git.postgresql.org/pg/commitdiff/621a99a666ba1a27b852dc5ddc0e1b224c388f53

- Move regression test listing of builtin leakproof functions to
opr_sanity.sql. The original location in create_function_3.sql
didn't invite the close structinity warranted for adding new
leakproof functions. Add comments to the test explaining that
functions should only be added after careful consideration and
understanding what a leakproof function is. Per complaint from Tom
Lane after 5eebb8d954ad.
http://git.postgresql.org/pg/commitdiff/e0cb4aa89dd61cb1a76d348ced634681255b6a07

- Consistently spell a replication slot's name as slot_name.
Previously there's been a mix between 'slotname' and 'slot_name'.
It's not nice to be unneccessarily inconsistent in a new feature. As
a post beta1 initdb now is required in the wake of eeca4cd35e, fix
the inconsistencies. Most the changes won't affect usage of
replication slots because the majority of changes is around function
parameter names. The prominent exception to that is that the
recovery.conf parameter 'primary_slotname' is now named
'primary_slot_name'.
http://git.postgresql.org/pg/commitdiff/f0c108560bce3a5481db57ffc3743e71b5f7b2d1

- Fix off-by-one in decoding causing one-record events to be skipped.
A ReorderBufferTransaction's end_lsn, the sentPtr advocated by
walsender keepalive messages, and the end location remembered by the
decoding get_*changes* SQL functions all use the location of the
last read record + 1. I.e. the LSN points to the beginning of the
next record. That cannot realistically be changed without changing
the replication protocol because that's how keepalive messages have
worked since 9.0. The bug is that the logic inside the snapshot
builder, which decides whether a transaction's contents should be
decoded, assumed the start location would point towards the last
byte of the last record. The reason this didn't actually cause
visible problems is that currently that decision is only made for
commit records. Since interesting transactions always have at least
one additional record - containing actual data - we'd never skip a
transaction. But if there ever were transactions, or other events,
with just one record containing important information, we'd skip
them after stopping and restarting logical decoding.
http://git.postgresql.org/pg/commitdiff/fe7337f2dc3177da19b647fcda3a33b73da82e14

Tom Lane pushed:

- Make plpython_unicode regression test work in more database
encodings. This test previously used a data value containing
U+0080, and would therefore fail if the database encoding didn't
have an equivalent to that; which only about half of our supported
server encodings do. We could fall back to using some plain-ASCII
character, but that seems like it's losing most of the point of the
test. Instead switch to using U+00A0 (no-break space), which
translates into all our supported encodings except the four in the
EUC_xx family. Per buildfarm testing. Back-patch to 9.1, which is
as far back as this test is expected to succeed everywhere. (9.0
has the test, but without back-patching some 9.1 code changes we
could not expect to get consistent results across platforms anyway.)
http://git.postgresql.org/pg/commitdiff/2dfa15de5510b3c19ffb92b512c39d5440a07b1e

- Bump PG_CONTROL_VERSION for previous 9.4 changes. This should have
been done in 6bc8ef0b7f1f1df3998745a66e1790e27424aa0c and/or
50e547096c4858a68abf09894667a542cc418315, but better late than
never. If we don't change this then we risk 9.3 pg_controldata or
pg_resetxlog being inappropriately used against a 9.4 pg_control
file, or vice versa.
http://git.postgresql.org/pg/commitdiff/eeca4cd35e284c72b2ea1b4494e64e7738896e81

- Add btree and hash opclasses for pg_lsn. This is needed to allow
ORDER BY, DISTINCT, etc to work as expected for pg_lsn values. We
had previously decided to put this off for 9.5, but in view of
commit eeca4cd35e284c72b2ea1b4494e64e7738896e81 there's no reason to
avoid a catversion bump for 9.4beta2, and this does make a pretty
significant usability difference for pg_lsn. Michael Paquier, with
fixes from Andres Freund and Tom Lane
http://git.postgresql.org/pg/commitdiff/4c8ab1b91d2bd154031f7bc8529b9e6e2f1cc443

- Tweak new regression test case for better portability. Buildfarm
says we get different plans on 32-bit and 64-bit platforms, probably
because of MAXALIGN-related differences in memory-consumption
calculations. Add some dummy WHERE clauses so that the planner
estimates different sizes for the three generate_series() relations;
that should stabilize the choice of join order.
http://git.postgresql.org/pg/commitdiff/d4d48a5edd9eb28a7f2ee2e4cbe20d984274982e

- Add defenses against running with a wrong selection of LOBLKSIZE.
It's critical that the backend's idea of LOBLKSIZE match the way
data has actually been divided up in pg_largeobject. While we don't
provide any direct way to adjust that value, doing so is a one-line
source code change and various people have expressed interest
recently in changing it. So, just as with TOAST_MAX_CHUNK_SIZE, it
seems prudent to record the value in pg_control and cross-check that
the backend's compiled-in setting matches the on-disk data. Also
tweak the code in inv_api.c so that fetches from pg_largeobject
explicitly verify that the length of the data field is not more than
LOBLKSIZE. Formerly we just had Asserts() for that, which is no
protection at all in production builds. In some of the call sites
an overlength data value would translate directly to a
security-relevant stack clobber, so it seems worth one extra runtime
comparison to be sure. In the back branches, we can't change the
contents of pg_control; but we can still make the extra checks in
inv_api.c, which will offer some amount of protection against
running with the wrong value of LOBLKSIZE.
http://git.postgresql.org/pg/commitdiff/5f93c37805e7485488480916b4585e098d3cc883

Andrew Dunstan pushed:

- Output timestamps in ISO 8601 format when rendering JSON. Many JSON
processors require timestamp strings in ISO 8601 format in order to
convert the strings. When converting a timestamp, with or without
timezone, to a JSON datum we therefore now use such a format rather
than the type's default text output, in functions such as to_json().
This is a change in behaviour from 9.2 and 9.3, as noted in the
release notes.
http://git.postgresql.org/pg/commitdiff/f30015b6d794c15d52abbb3df3a65081fbefb1ed

- Do not escape a unicode sequence when escaping JSON text.
Previously, any backslash in text being escaped for JSON was doubled
so that the result was still valid JSON. However, this led to some
perverse results in the case of Unicode sequences, These are now
detected and the initial backslash is no longer escaped. All other
backslashes are still escaped. No validity check is performed, all
that is looked for is \uXXXX where X is a hexidecimal digit. This
is a change from the 9.2 and 9.3 behaviour as noted in the Release
notes. Per complaint from Teodor Sigaev.
http://git.postgresql.org/pg/commitdiff/0ad1a816320a2b539a51628e2a0b1e83ff096b1d

- Use EncodeDateTime instead of to_char to render JSON timestamps.
Per gripe from Peter Eisentraut and Tom Lane. The output is
slightly different, but still ISO 8601 compliant: to_char doesn't
output the minutes when time zone offset is an integer number of
hours, while EncodeDateTime outputs ":00". The code is slightly
adapted from code in xml.c
http://git.postgresql.org/pg/commitdiff/ab14a73a6ca5cc4750f0e00a48bdc25a2293034a

Peter Eisentraut pushed:

- Silence Bison deprecation warnings. Bison >=3.0 issues warnings
about %name-prefix="base_yy" instead of the now preferred
%name-prefix "base_yy" but the latter doesn't work with Bison 2.3 or
less. So for now we silence the deprecation warnings.
http://git.postgresql.org/pg/commitdiff/55fb759ab3e7543a6be72a35e6b6961455c5b393

Fujii Masao pushed:

- Save pg_stat_statements statistics file into $PGDATA/pg_stat
directory at shutdown. 187492b6c2e8cafc5b39063ca3b67846e8155d24
changed pgstat.c so that the stats files were saved into
$PGDATA/pg_stat directory when the server was shutdowned. But it
accidentally forgot to change the location of pg_stat_statements
permanent stats file. This commit fixes pg_stat_statements so that
its stats file is also saved into $PGDATA/pg_stat at shutdown.
Since this fix changes the file layout, we don't back-patch it to
9.3 where this oversight was introduced.
http://git.postgresql.org/pg/commitdiff/654e8e444749f053c3bf3fd543d10deb6aa6dd09

- Add description of pg_stat directory into doc. Back-patch to 9.3
where pg_stat directory was introduced.
http://git.postgresql.org/pg/commitdiff/c8c9c1f5a3fcd2db0f94d3126e142dee91c53632

Heikki Linnakangas pushed:

- Adjust SP-GiST WAL record formats to reduce alignment padding. The
way the code was written, the padding was copied from uninitialized
memory areas.. Because the structs are local variables in the code
where the WAL records are constructed, making them larger and
zeroing the padding bytes would not make the code very pretty, so
rather than fixing this directly by zeroing out the padding bytes,
it seems more clear to not try to align the tuples in the WAL
records. The redo functions are taught to copy the tuple header to a
local variable to avoid unaligned access. Stable-branches have the
same problem, but we can't change the WAL format there, so fix in
master only. Reading a few random extra bytes at the stack is
harmless in practice, so it's not worth crafting a different
back-patchable fix. Per reports from Kevin Grittner and Andres
Freund, using clang static analyzer and Valgrind, respectively.
http://git.postgresql.org/pg/commitdiff/8776faa81cb651322b8993422bdd4633f1f6a487

== Rejected Patches (for now) ==

No one was disappointed this week :-)

== Pending Patches ==

Haribabu Kommi sent in another WIP patch to add a priority or cache
table.

Haribabu Kommi and Andres Freund traded patches to allow adding
aggregates for the inet data type, among others.

David Rowley sent in another revision of a patch to enable removing
certain semi- and anti-joins.

Gurjeet Singh sent in another revision of a patch to implement
pg_hibernator.

Vik Fearing sent in two revisions of a patch to implement a timeout
for "idle in transaction" scenarios.

Fujii Masao sent in a patch to fix an issue where pg_basebackup fails
to back up large (>= 4GiB) files because it uses an integer to measure
their size.

Jeff Janes sent in a patch to fix an issue where anti-wraparound
VACUUM could not finish when the stop limit was reached.

Abhijit Menon-Sen sent in a patch to allow printing summary statistics
instead of individual records via a new --stats option to pg_xlogdump.

Amul Sul sent in a patch to pass the Form_pg_attribute to
examine_attribute rather than the Relation structure.

Pavel Stehule sent in two more revisions of a patch to allow psql to
show only failed queries.

Andres Freund sent in a patch to hide 'Execution time' in EXPLAIN
(COSTS OFF).

Peter Eisentraut sent in a patch to allow the regression tests for
client programs to work in VPATH builds.

Tom Lane sent in a patch to add a new dummy value to distinguish the
node label of a pushed-down allTheSame tuple from end-of-string in
SP-GiST.

Andres Freund sent in a patch to make "slot_name" the standard
spelling for all user-visible things.

Marc Mamin sent in another revision of a patch to add "pivot
aggregation" to contrib/intarray.

Furuya Osamu sent in two revisions of a patch to add a synchronous
mode to pg_receivelog.

Peter Geoghegan sent in a patch to implement a poor-man's hyperloglog.

Tom Lane sent in a patch to suppress unused query output columns.

Amit Kapila sent in another revision of a patch to scale shared buffer
eviction.

Naoya Anzai sent in a patch to fix a condition where the message
"cancelling statement due to user request error" occurs even though
the transaction has committed.

David Rowley sent in a patch to allow NOT IN to use anti-joins.

Noah Misch sent in another revision of a patch to secure "make check"

Kevin Grittner sent in a patch to fix an issue with NUMA systems where
memory at start would stay too non-uniform by default.

Noah Misch sent in a patch to fix a problem in the MinGW/Cygwin
builds.

Noah Misch sent in a patch to fix the emacs configuration file so it
works with both the newest GNU emacs and older ones.

John Lumby sent in another revision of a patch to enable extended
prefetching using asynchronous I/O.