== PostgreSQL Weekly News - June 28 2015 ==

== PostgreSQL Weekly News - June 28 2015 ==

PGConf.DE will be held November 26-27 in Hamburg, Germany, at the
Lindner Hotel am Michel. The CfP is open until September 13, 2015.
http://2015.pgconf.de/

== PostgreSQL Product News ==

HypoPG, hypothetical indexes for PostgreSQL, released.
https://github.com/dalibo/hypopg

par_psql v0.1: 'Parallel psql', which adds special markings for
queries to be run in parallel, released.
http://github.com/gbb/par_psql/

== PostgreSQL Jobs for June ==

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

== PostgreSQL Local ==

PGDay UK, Conference will be taking place on 7th July 2015 – it is aimed at
the UK PostgreSQL Community. The CfP is open until 13 April 2015.
http://www.postgresqlusergroup.org.uk

PGDay Campinas 2015 will take place in Campinas on August 7.
http://pgdaycampinas.com.br/english/

The Call For Papers for PostgresOpen 2015, being held in Dallas, Texas
from September 16th to 18th, is now open.
http://2015.postgresopen.org/callforpapers/

PostgreSQL Session #7, will be held September 24th, 2015 in Paris,
France,
http://www.postgresql-sessions.org/7/about

PostgreSQL Conference Europe 2015 will be held on October 27-30 in the
Vienna Marriott Hotel, in Vienna, Austria. The CfP is open until
August 7.
http://2015.pgconf.eu/

PGConf Silicon Valley 2015 is November 17-18 at the South San
Francisco Convention Center. Discount registration is available
through June 30.
http://www.pgconfsv.com

PGBR2015 (The Brazilian PostgreSQL Conference) will take place in Porto
Alegre, Rio Grande do Sul, on November 18, 19 and 20.
The CfP is open through July 15.
http://pgbr.postgresql.org.br/2015/en/#call-for-papers

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

Noah Misch pushed:

- Truncate strings in tarCreateHeader() with strlcpy(), not sprintf().
This supplements the GNU libc bug #6530 workarounds introduced in
commit 54cd4f04576833abc394e131288bf3dd7dcf4806. On affected
systems, a tar-format pg_basebackup failed when some filename
beneath the data directory was not valid character data in the
postmaster/walsender locale. Back-patch to 9.1, where pg_basebackup
was introduced. Extant, bug-prone conversion specifications receive
only ASCII bytes or involve low-importance messages.
http://git.postgresql.org/pg/commitdiff/4318118edd5582696027f357771e0a8b091fe2bf

Robert Haas pushed:

- psql: Add some tab completion for TABLESAMPLE. Petr Jelinek,
reviewed by Brendan Jurd
http://git.postgresql.org/pg/commitdiff/da9ee026a0ddd100785b00defd1201b317c0797b

- Update get_relation_info comment. Thomas Munro
http://git.postgresql.org/pg/commitdiff/51d0fe5d5682a65e3bce7aa62d8666509fd08aa2

- Allow background workers to connect to no particular database. The
documentation claims that this is supported, but it didn't actually
work. Fix that. Reported by Pavel Stehule; patch by me.
http://git.postgresql.org/pg/commitdiff/f7bb7f0625771bc71869cdadafcf54450b2db08f

- Improve handling of CustomPath/CustomPlan(State) children. Allow
CustomPath to have a list of paths, CustomPlan a list of plans, and
CustomPlanState a list of planstates known to the core system, so
that custom path/plan providers can more reasonably use this
infrastructure for nodes with multiple children. KaiGai Kohei, per
a design suggestion from Tom Lane, with some further kibitzing by
me.
http://git.postgresql.org/pg/commitdiff/5ca611841bcd37c7ee8448c46c8398ef8d8edcc4

- Reduce log level for background worker events from LOG to DEBUG1.
Per discussion, LOG is just too chatty for something that will
happen as routinely as this. Pavel Stehule
http://git.postgresql.org/pg/commitdiff/91118f1a59f2038f072552fdbb98e01363e30b59

- Don't warn about creating temporary or unlogged hash indexes.
Warning people that no WAL-logging will be done doesn't make sense
in this case. Michael Paquier
http://git.postgresql.org/pg/commitdiff/9043ef390f4f0b4586cfe59cbd22314b9c3e2957

- release notes: Combine items for pg_upgrade and pg_upgrade_support
moves. Per suggestions from Amit Langote and Álvaro Herrera.
http://git.postgresql.org/pg/commitdiff/31c018ecda9f40fe80055d8ba95248c023593fb4

- Remove unnecessary NULL test. Spotted by Coverity and reported by
Michael Paquier. Per discussion, we don't necessarily care about
making Coverity happy in all such instances, but we can go ahead and
change them where it otherwise seems to improve the code.
http://git.postgresql.org/pg/commitdiff/8a8c581a8c99b9beecbdc517957da866f427f297

- release notes: Add entry for commit 5ea86e6e6. Peter Geoghegan and
Robert Haas
http://git.postgresql.org/pg/commitdiff/c66bc72e8a1318e43ea657ffa3798fa95f491650

- Be more conservative about removing tablespace "symlinks". Don't
apply rmtree(), which will gleefully remove an entire subtree, and
don't even apply unlink() unless it's symlink or a directory, the
only things that we expect to find. Amit Kapila, with minor tweaks
by me, per extensive discussions involving Andrew Dunstan, Fujii
Masao, and Heikki Linnakangas, at least some of whom also reviewed
the code.
http://git.postgresql.org/pg/commitdiff/8f15f74a44f68f9cb3a644786d3c732a5eeb237a

- Fix grammar. Reported by Peter Geoghegan.
http://git.postgresql.org/pg/commitdiff/7c02d48e698ad38bec1399a9dcc543c80b8f5b8f

Tom Lane pushed:

- Improve inheritance_planner()'s performance for large inheritance
sets. Commit c03ad5602f529787968fa3201b35c119bbc6d782 introduced a
planner performance regression for UPDATE/DELETE on large
inheritance sets. It required copying the append_rel_list (which is
of size proportional to the number of inherited tables) once for
each inherited table, thus resulting in O(N^2) time and memory
consumption. While it's difficult to avoid that in general, the
extra work only has to be done for append_rel_list entries that
actually reference subquery RTEs, which inheritance-set entries will
not. So we can buy back essentially all of the loss in cases
without subqueries in FROM; and even for those, the added work is
mainly proportional to the number of UNION ALL subqueries.
Back-patch to 9.2, like the previous commit. Tom Lane and Dean
Rasheed, per a complaint from Thomas Munro.
http://git.postgresql.org/pg/commitdiff/2cb9ec1bcb35dd6b4cf7a4a325aaa9791444e69d

- Docs: fix claim that to_char('FM') removes trailing zeroes. Of
course, what it removes is leading zeroes. Seems to have been a
thinko in commit ffe92d15d53625d5ae0c23f4e1984ed43614a33d. Noted by
Hubert Depesz Lubaczewski.
http://git.postgresql.org/pg/commitdiff/d759b7eb6aee12bd52516905d790072845b4356f

- Fix the logic for putting relations into the relcache init file.
Commit f3b5565dd4e59576be4c772da364704863e6a835 was a couple of
bricks shy of a load; specifically, it missed putting
pg_trigger_tgrelid_tgname_index into the relcache init file, because
that index is not used by any syscache. However, we have
historically nailed that index into cache for performance reasons.
The upshot was that load_relcache_init_file always decided that the
init file was busted and silently ignored it, resulting in a
significant hit to backend startup speed. To fix, reinstantiate
RelationIdIsInInitFile() as a wrapper around
RelationSupportsSysCache(), which can know about additional
relations that should be in the init file despite being unknown to
syscache.c. Also install some guards against future mistakes of
this type: make write_relcache_init_file Assert that all nailed
relations get written to the init file, and make
load_relcache_init_file emit a WARNING if it takes the "wrong number
of nailed relations" exit path. Now that we remove the init files
during postmaster startup, that case should never occur in the
field, even if we are starting a minor-version update that added or
removed rels from the nailed set. So the warning shouldn't ever be
seen by end users, but it will show up in the regression tests if
somebody breaks this logic. Back-patch to all supported branches,
like the previous commit.
http://git.postgresql.org/pg/commitdiff/5d1ff6bd559ea8df1b7302e245e690b01b9a4fa4

- Avoid passing NULL to memcmp() in lookups of zero-argument
functions. A few places assumed they could pass NULL for the
argtypes array when looking up functions known to have zero
arguments. At first glance it seems that this should be safe
enough, since memcmp() is surely not allowed to fetch any bytes if
its count argument is zero. However, close reading of the C
standard says that such calls have undefined behavior, so we'd
probably best avoid it. Since the number of places doing this is
quite small, and some other places looking up zero-argument
functions were already passing dummy arrays, let's standardize on
the latter solution rather than hacking the function lookup code to
avoid calling memcmp() in these cases. I also added Asserts to
catch any future violations of the new rule. Given the utter lack
of any evidence that this actually causes any problems in the field,
I don't feel a need to back-patch this change. Per report from
Piotr Stefaniak, though this is not his patch.
http://git.postgresql.org/pg/commitdiff/0a52d378b03b7d5ab1d64627a87edaf5ed311c6c

Peter Eisentraut pushed:

- pg_rewind: Improve message wording
http://git.postgresql.org/pg/commitdiff/e98d635d5dbf25e5cde282af111af9fdffafa557

- pg_basebackup: Remove redundant newline in error message
http://git.postgresql.org/pg/commitdiff/747781f25e7eaa2e5cb5ed69bdae3e5f61795d2e

Heikki Linnakangas pushed:

- Add missing newline to debug-message. Michael Paquier
http://git.postgresql.org/pg/commitdiff/9cb36981fbbf2f298db2476101f4475c52d00fbb

- Fix a couple of bugs with wal_log_hints. 1. Replay of the WAL
record for setting a bit in the visibility map contained an
assertion that a full-page image of that record type can only occur
with checksums enabled. But it can also happen with wal_log_hints,
so remove the assertion. Unlike checksums, wal_log_hints can be
changed on the fly, so it would be complicated to figure out if it
was enabled at the time that the WAL record was generated. 2.
wal_log_hints has the same effect on the locking needed to read the
LSN of a page as data checksums. BufferGetLSNAtomic() didn't get the
memo. Backpatch to 9.4, where wal_log_hints was added.
http://git.postgresql.org/pg/commitdiff/4b8e24b9ad308c30dbe2184e06848e638e018114

- Fix typo in comment. Etsuro Fujita
http://git.postgresql.org/pg/commitdiff/7845db2aa778aa751b41cff72c41c94993e975e3

- Add missing_ok option to the SQL functions for reading files. This
makes it possible to use the functions without getting errors, if
there is a chance that the file might be removed or renamed
concurrently. pg_rewind needs to do just that, although this could
be useful for other purposes too. (The changes to pg_rewind to use
these functions will come in a separate commit.) The
read_binary_file() function isn't very well-suited for
extensions.c's purposes anymore, if it ever was. So bite the bullet
and make a copy of it in extension.c, tailored for that use case.
This seems better than the accidental code reuse, even if it's a
some more lines of code. Michael Paquier, with plenty of kibitzing
by me.
http://git.postgresql.org/pg/commitdiff/cb2acb1081e13b4b27a76c6b5311115528e49c59

- Don't choke on files that are removed while pg_rewind runs. If a
file is removed from the source server, while pg_rewind is running,
the invocation of pg_read_binary_file() will fail. Use the
just-added missing_ok option to that function, to have it return
NULL instead, and handle that gracefully. And similarly for
pg_ls_dir and pg_stat_file. Reported by Fujii Masao, fix by Michael
Paquier.
http://git.postgresql.org/pg/commitdiff/b36805f3c54fe0e50e58bb9e6dad66daca46fbf6

- Fix double-XLogBeginInsert call in GIN page splits. If data
checksums or wal_log_hints is on, and a GIN page is split, the code
to find a new, empty, block was called after having already called
XLogBeginInsert(). That causes an assertion failure or PANIC, if
finding the new block involves updating a FSM page that had not been
modified since last checkpoint, because that update is WAL-logged,
which calls XLogBeginInsert again. Nested XLogBeginInsert calls are
not supported. To fix, rearrange GIN code so that XLogBeginInsert
is called later, after finding the victim buffers. Reported by Jeff
Janes.
http://git.postgresql.org/pg/commitdiff/a45c70acf35e43257d86313dcbb7bb0e5201fab1

- Promote the assertion that XLogBeginInsert() is not called twice
into ERROR. Seems like cheap insurance for WAL bugs. A spurious
call to XLogBeginInsert() in itself would be fairly harmless, but if
there is any data registered and the insertion is not
completed/cancelled properly, there is a risk that the data ends up
in a wrong WAL record. Per Jeff Janes's suggestion.
http://git.postgresql.org/pg/commitdiff/a32c3ec893cafbd3a4b42c34270a80198f28f123

- Fix markup in docs. Oops. I could swear I built the docs before
pushing, but I guess not..
http://git.postgresql.org/pg/commitdiff/6ab4d38ab085b0177d7ce63f7e1f2fb3f3a8e4a5

Fujii Masao pushed:

- Add index terms for functions jsonb_set and jsonb_pretty.
http://git.postgresql.org/pg/commitdiff/0b157a0dad4f88f6f4420faa4cddab1e5112988f

Andres Freund pushed:

- Fix the fallback memory barrier implementation to be reentrant.
This was essentially "broken" since 0c8eda62; but until more
recently (14e8803f) barriers usage in signal handlers was
infrequent. The failure to be reentrant was noticed because the
test_shm_mq, which uses memory barriers at a high frequency,
occasionally got stuck on some solaris buildfarm animals. Turns out,
those machines use sun studio 12.1, which doesn't yet have efficient
memory barrier support. A machine with a newer sun studio did not
fail. Forcing the barrier fallback to be used on x86 allows to
reproduce the problem. The new fallback is to use
kill(PostmasterPid, 0) based on the theory that that'll always imply
a barrier due to checking the liveliness of PostmasterPid on systems
old enough to need fallback support. It's hard to come up with a
good and performant fallback. I'm not backpatching this for now -
the problem isn't active in the back branches, and we haven't
backpatched barrier changes for now. Additionally master looks
entirely different than the back branches due to the new atomics
abstraction. It seems better to let this rest in master, where the
non-reentrancy actively causes a problem, and then consider
backpatching. Found-By: Robert Haas Discussion:
55626265(dot)3060800(at)dunslane(dot)net
http://git.postgresql.org/pg/commitdiff/1b468a131bd260c9041484f78b8580c7f232d580

- Fix test_decoding's handling of nonexistant columns in old tuple
versions. test_decoding used fastgetattr() to extract column
values. That's wrong when decoding updates and deletes if a table's
replica identity is set to FULL and new columns have been added
since the old version of the tuple was created. Due to the lack of a
crosscheck with the datum's natts values an invalid value will be
output, leading to errors or worse. Bug: #13470 Reported-By:
Krzysztof Kotlarski Discussion:
20150626100333(dot)3874(dot)90852(at)wrigleys(dot)postgresql(dot)org Backpatch to 9.4,
where the feature, including the bug, was added.
http://git.postgresql.org/pg/commitdiff/d47a1136e441cebe7ae7fe72d70eb8ce278d5cd6

Álvaro Herrera pushed:

- Fix BRIN xlog replay. There was a confusion about which block
number to use when storing an item's pointer in the revmap -- the
revmap page's blkno was being used, not the data page's blkno.
Spotted-by: Jeff Janes
http://git.postgresql.org/pg/commitdiff/402822246866e1094d35a617775a65b4be93d322

- Fix DDL command collection for TRANSFORM. Commit b488c580ae, which
added the DDL command collection feature, neglected to update the
code that commit cac76582053e had previously added two weeks earlier
for the TRANSFORM feature. Reported by Michael Paquier.
http://git.postgresql.org/pg/commitdiff/7d60b2af34842ae89b1abdd31fb5d303bd43c514

Simon Riggs pushed:

- Avoid hot standby cancels from VAC FREEZE VACUUM FREEZE generated
false cancelations of standby queries on an otherwise idle master.
Caused by an off-by-one error on cutoff_xid which goes back to
original commit. Backpatch to all versions 9.0+ Analysis and report
by Marco Nenciarini Bug fix by Simon Riggs
http://git.postgresql.org/pg/commitdiff/66fbcb0d2e1b201477dd2977b6eb93b1cfd9dd6c

Kevin Grittner pushed:

- Add opaque declaration of HTAB to tqual.h. Commit
b89e151054a05f0f6d356ca52e3b725dd0505e53 added the
ResolveCminCmaxDuringDecoding declaration to tqual.h, which uses an
HTAB parameter, without declaring HTAB. It accidentally fails to
fail to build with current sources because a declaration happens to
be included, directly or indirectly, in all source files that
currently use tqual.h before tqual.h is first included, but we
shouldn't count on that. Since an opaque declaration is enough
here, just use that, as was done in snapmgr.h. Backpatch to 9.4,
where the HTAB reference was added to tqual.h.
http://git.postgresql.org/pg/commitdiff/604e99396de02f6f23950ee373c13335d2ccdf05

- Fix comment for GetCurrentIntegerTimestamp(). The unit of measure
is microseconds, not milliseconds. Backpatch to 9.3 where the
function and its comment were added.
http://git.postgresql.org/pg/commitdiff/cca8ba9529f8815acd23fe88c32763765d0e1b68

Tatsuo Ishii pushed:

- Fix function declaration style to respect the coding standard.
http://git.postgresql.org/pg/commitdiff/527e6d3f099df22783465ca7046fc0c8a534c921

== Rejected Patches (for now) ==

No one was disappointed this week :-)

== Pending Patches ==

Peter Geoghegan sent in a patch to allow JSON[B] arrays to take
negative subscripts.

Tomas Vondra sent in a patch to add density correction to sampling for
statistics collection.

Michael Paquier sent in a patch to fix some white space in pg_rewind
error messages.

Michael Paquier and Robert Haas traded patches to fix an issue where a
dangerous rm -rf was being issued in the global makefile.

Michael Paquier sent in two more revisions of a patch to update the
hash index creation warning.

Abhijit Menon-Sen sent in a patch to introduce
XLogLockBlockRangeForCleanup().

Fabien COELHO sent in another revision of a patch to add checkpointer
continuous flushing.

Michael Paquier sent in another revision of a patch to add support for
TAP tests on Windows.

Michael Paquier sent in a patch to improve log capture of TAP tests
and fix race conditions.

Fabrízio de Royes Mello sent in two more revisions of a patch to add
CINE for ALTER TABLE ... ADD COLUMN.

Uriy Zhuravlev sent in another revision of a patch to implement ALTER
OPERATOR.

Craig Ringer sent in a patch to implement ALTER TABLE ... ALTER
CONSTRAINT ... SET DEFERRABLE on UNIQUE or PK.

Oskari Saarenmaa sent in a patch to add -lrt to configure for
sched_yield on Solaris.

Jim Nasby sent in a patch to ensure that object_classes is properly
sized.

Marco Nenciarini sent in a patch to fix an off-by-one bug which caused
VACUUM FREEZE to mistakenly cancel standby sessions.

Peter Geoghegan sent in a patch to add some compatibility notes for
UPSERT with foreign data wrappers.

Amit Kapila sent in a patch to improve the performance of DROP TABLE
when the shared_buffers setting is high.

Jeff Janes sent in a patch to make pg_trgm perform better by
supporting the triconsistent function, introduced in version 9.4 of
the server, to make it faster to implement indexed queries where some
keys are common and some are rare.

Tom Lane sent in a patch to refactor the way the pg_file_settings view
works.