== PostgreSQL Weekly News - May 17 2015 ==

== PostgreSQL Weekly News - May 17 2015 ==

There is an Italian language channel for PostgreSQL content.
https://vimeo.com/user10626375/videos

The CLI Latinamerican Conference in Informatics will be held in
Arequipa, Peru, from October 19th to 23rd, 2015.
https://sites.google.com/a/spc.org.pe/clei2015/

Informatica 2016, which will be held in Cuba, is looking for
PostgreSQL content for its open source workshop.
http://www.informaticahabana.cu/

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/

SwissPUG is now open to new members.
http://www.swisspug.org/wiki/index.php/Special:Registration

== PostgreSQL Product News ==

pgBadger 7.0, a parallel PostgreSQL log analyzer written in Perl, released:
http://dalibo.github.io/pgbadger/
Development:
https://github.com/dalibo/pgbadger/

Postgres Toolkit 0.2 released.
https://github.com/uptimejp/postgres-toolkit

== PostgreSQL Jobs for May ==

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

== PostgreSQL Local ==

The second Swiss Postgres Conference will be held June 25-26, 2015 at
HSR Rapperswil.
http://www.postgres-conference.ch/

There is a Postgres track in a database technology conference(DTCC) in
April 18, 2015 in Beijing, China.
http://dtcc.it168.com/list_jiabin.html

pgDay Paris will be held in Paris France on April 21, 2015.
http://pgday.paris/

PGDay in Belfort, France will be held June 2, 2015.
http://select-2-6-2015-as-pgday.org

PGCon 2015 is June 16-20 in Ottawa, Canada.
http://www.pgcon.org/2015/

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.
The CfP is open through May 31.
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/

The CfP for PostgreSQL Session #7, September 24th, 2015 in Paris,
France, is open until June 15, 2015. call-for-paper <AT>
postgresql-sessions <DOT> org.
http://www.postgresql-sessions.org/7/about

PGConf Silicon Valley 2015 is November 17-18 at the South San
Francisco Convention Center. The CfP is open through June 15.
http://www.pgconfsv.com

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

Robert Haas pushed:

- Fix DetermineSafeOldestOffset for the case where there are no
mxacts. Commit b69bf30b9bfacafc733a9ba77c9587cf54d06c0c failed to
take into account the possibility that there might be no multixacts
in existence at all. Report by Thomas Munro; patch by me.
http://git.postgresql.org/pg/commitdiff/312747c224be4c0a880b5172a010e980f8b654ca

- Advance the stop point for multixact offset creation only at
checkpoint. Commit b69bf30b9bfacafc733a9ba77c9587cf54d06c0c
advanced the stop point at vacuum time, but this has subsequently
been shown to be unsafe as a result of analysis by myself and Thomas
Munro and testing by Thomas Munro. The crux of the problem is that
the SLRU deletion logic may get confused about what to remove if, at
exactly the right time during the checkpoint process, the head of
the SLRU crosses what used to be the tail. This patch, by me, fixes
the problem by advancing the stop point only following a checkpoint.
This has the additional advantage of making the removal logic work
during recovery more like the way it works during normal running,
which is probably good. At least one of the calls to
DetermineSafeOldestOffset which this patch removes was already dead,
because MultiXactAdvanceOldest is called only during recovery and
DetermineSafeOldestOffset was set up to do nothing during recovery.
That, however, is inconsistent with the principle that recovery and
normal running should work similarly, and was confusing to boot.
Along the way, fix some comments that previous patches in this area
neglected to update. It's not clear to me whether there's any
concrete basis for the decision to use only half of the multixact ID
space, but it's neither necessary nor sufficient to prevent
multixact member wraparound, so the comments should not say
otherwise.
http://git.postgresql.org/pg/commitdiff/f6a6c46d7fd72878d37c75d4a3215d5a62128d0b

- Even when autovacuum=off, force it for members as we do in other
cases. Thomas Munro, with some adjustments by me.
http://git.postgresql.org/pg/commitdiff/04e6d3b877e060d8445eb653b7ea26b1ee5cec6b

- Increase threshold for multixact member emergency autovac to 50%.
Analysis by Noah Misch shows that the 25% threshold set by commit
53bb309d2d5a9432d2602c93ed18e58bd2924e15 is lower than any other,
similar autovac threshold. While we don't know exactly what value
will be optimal for all users, it is better to err a little on the
high side than on the low side. A higher value increases the risk
that users might exhaust the available space and start seeing errors
before autovacuum can clean things up sufficiently, but a user who
hits that problem can compensate for it by reducing
autovacuum_multixact_freeze_max_age to a value dependent on their
average multixact size. On the flip side, if the emergency cap
imposed by that patch kicks in too early, the user will experience
excessive wraparound scanning and will be unable to mitigate that
problem by configuration. The new value will hopefully reduce the
risk of such bad experiences while still providing enough headroom
to avoid multixact member exhaustion for most users. Along the way,
adjust the documentation to reflect the effects of commit
04e6d3b877e060d8445eb653b7ea26b1ee5cec6b, which taught autovacuum to
run for multixact wraparound even when autovacuum is configured off.
http://git.postgresql.org/pg/commitdiff/b4d4ce1d50bbdf82cd2e2c1c7172b936df01c01d

- Remove useless assertion. Here, snapshot->xcnt is an unsigned type,
so it will always be non-negative.
http://git.postgresql.org/pg/commitdiff/ae6157164faf5ec1636a9acfe18bfd28a31db098

- Extend abbreviated key infrastructure to datum tuplesorts. Andrew
Gierth, reviewed by Peter Geoghegan and by me.
http://git.postgresql.org/pg/commitdiff/78efd5c1edb59017f06ef96773e64e6539bfbc86

- Fix comment. Commit 78efd5c1edb59017f06ef96773e64e6539bfbc86
overlooked this. Report by Peter Geoghegan.
http://git.postgresql.org/pg/commitdiff/61f68e0bed63aa5090e8be7c912843e49b30fc1e

- doc: CREATE FOREIGN TABLE now allows CHECK ( ... ) NO INHERIT.
Etsuro Fujita
http://git.postgresql.org/pg/commitdiff/92edba2665ae7bf43ed03538311e63652f9e2373

Bruce Momjian pushed:

- pg_dump: suppress "Tablespace:" comment for default tablespaces.
Report by Hans Ginzel
http://git.postgresql.org/pg/commitdiff/c71e273402bbb4262a3d09f045880d485076cd40

- docs: add "serialization anomaly" to transaction isolation table.
Also distinguish between SQL-standard and Postgres behavior. Report
by David G. Johnston
http://git.postgresql.org/pg/commitdiff/23c33198b961f27c80655a7cf439d49ef5a1833d

- initdb: only recommend pg_ctl to start the server. Previously we
mentioned the 'postgres' binary method as well.
http://git.postgresql.org/pg/commitdiff/2200713aa8bb857ae2737f659073907628c9e2ca

- pg_upgrade: use single or double-quotes in command-line strings.
This is platform-dependent.
http://git.postgresql.org/pg/commitdiff/9d15292cfc581d2916778b79df0f0e86e032a677

- doc: prevent SGML 'make check' from building temp install. Report
by Alvaro Herrera
http://git.postgresql.org/pg/commitdiff/e8c19263e45ce1f3133d2d403de9d380cdcd1fce

- doc build: use unique Makefile variable to control temp install
http://git.postgresql.org/pg/commitdiff/ea12b3ca8cf4d5d4a35f6ca49f8e5128f716fa90

- doc: list bigint as mapping to int8 and int64. Report by Paul
Jungwirth
http://git.postgresql.org/pg/commitdiff/333a870f94ac23f29befa346324f9dab7f3265e9

- pg_upgrade: make controldata checks more consistent. Also add
missing float8_pass_by_value check.
http://git.postgresql.org/pg/commitdiff/5a3022fde018aca9b23b7f7506233b437d943de2

- docs: consistently uppercase index method and add spacing.
Consistently uppercase index method names, e.g. GIN, and add space
after the index method name and the parentheses enclosing the column
names.
http://git.postgresql.org/pg/commitdiff/f6d65f0c7068bab6a9ca55a82f18fd52e8fd1e5e

- pg_upgrade: only allow template0 to be non-connectable. This patch
causes pg_upgrade to error out during its check phase if: (1)
template0 is marked connectable. or (2) any other database is
marked non-connectable. This is done because, in the first case,
pg_upgrade would fail because the pg_dumpall --globals restore would
fail, and in the second case, the database would not be restored,
leading to data loss. Report by Matt Landry (1), Stephen Frost (2).
Backpatch through 9.0
http://git.postgresql.org/pg/commitdiff/fb694d959c97c912b49ec47326e6a05a8c0da11d

- pg_upgrade: force timeline 1 in the new cluster. Previously, this
prevented promoted standby servers from being upgraded because of a
missing WAL history file. (Timeline 1 doesn't need a history file,
and we don't copy WAL files anyway.) Report by Christian Echerer(?),
Alexey Klyukin. Backpatch through 9.0
http://git.postgresql.org/pg/commitdiff/4c5e060049a3714dd27b7f4732fe922090edea69

- pg_upgrade: no need to check for matching float8_pass_by_value.
Report by Noah Misch
http://git.postgresql.org/pg/commitdiff/750ccaef2964cfb8b0530e0e3628038fce343786

Tom Lane pushed:

- Fix incorrect checking of deferred exclusion constraint after a HOT
update. If a row that potentially violates a deferred exclusion
constraint is HOT-updated later in the same transaction, the
exclusion constraint would be reported as violated when the check
finally occurs, even if the row(s) the new row originally conflicted
with have since been removed. This happened because the wrong TID
was passed to check_exclusion_constraint(), causing the live
HOT-updated row to be seen as a conflicting row rather than
recognized as the row-under-test. Per bug #13148 from Evan Martin.
It's been broken since exclusion constraints were invented, so
back-patch to all supported branches.
http://git.postgresql.org/pg/commitdiff/20781765f77c1fb6465aba97d211636ce92e7a0e

- Add support for doing late row locking in FDWs. Previously, FDWs
could only do "early row locking", that is lock a row as soon as
it's fetched, even though local restriction/join conditions might
discard the row later. This patch adds callbacks that allow FDWs to
do late locking in the same way that it's done for regular tables.
To make use of this feature, an FDW must support the "ctid" column
as a unique row identifier. Currently, since ctid has to be of type
TID, the feature is of limited use, though in principle it could be
used by postgres_fdw. We may eventually allow FDWs to specify
another data type for ctid, which would make it possible for more
FDWs to use this feature. This commit does not modify postgres_fdw
to use late locking. We've tested some prototype code for that, but
it's not in committable shape, and besides it's quite unclear
whether it actually makes sense to do late locking against a remote
server. The extra round trips required are likely to outweigh any
benefit from improved concurrency. Etsuro Fujita, reviewed by
Ashutosh Bapat, and hacked up a lot by me
http://git.postgresql.org/pg/commitdiff/afb9249d06f47d7a6d4a89fea0c3625fe43c5a5d

- Fix postgres_fdw to return the right ctid value in EvalPlanQual
cases. If a postgres_fdw foreign table is a non-locked source
relation in an UPDATE, DELETE, or SELECT FOR UPDATE/SHARE, and the
query selects its ctid column, the wrong value would be returned if
an EvalPlanQual recheck occurred. This happened because the foreign
table's result row was copied via the ROW_MARK_COPY code path, and
EvalPlanQualFetchRowMarks just unconditionally set the reconstructed
tuple's t_self to "invalid". To fix that, we can have
EvalPlanQualFetchRowMarks copy the composite datum's t_ctid field,
and be sure to initialize that along with t_self when postgres_fdw
constructs a tuple to return. If we just did that much then
EvalPlanQualFetchRowMarks would start returning "(0,0)" as ctid for
all other ROW_MARK_COPY cases, which perhaps does not matter much,
but then again maybe it might. The cause of that is that
heap_form_tuple, which is the ultimate source of all composite
datums, simply leaves t_ctid as zeroes in newly constructed tuples.
That seems like a bad idea on general principles: a field that's
really not been initialized shouldn't appear to have a valid value.
So let's eat the trivial additional overhead of doing
"ItemPointerSetInvalid(&(td->t_ctid))" in heap_form_tuple. This
closes out our handling of Etsuro Fujita's report that tableoid and
ctid weren't correctly set in postgres_fdw EvalPlanQual cases.
Along the way we did a great deal of work to improve FDWs' ability
to control row locking behavior; which was not wasted effort by any
means, but it didn't end up being a fix for this problem because
that feature would be too expensive for postgres_fdw to use all the
time. Although the fix for the tableoid misbehavior was
back-patched, I'm hesitant to do so here; it seems far less likely
that people would care about remote ctid than tableoid, and even
such a minor behavioral change as this in heap_form_tuple is perhaps
best not back-patched. So commit to HEAD only, at least for the
moment. Etsuro Fujita, with some adjustments by me
http://git.postgresql.org/pg/commitdiff/0bb8528b5c738b45d0b65844750588c16bf75c52

- Fix distclean/maintainer-clean targets to remove top-level
tmp_install dir. The top-level makefile removes tmp_install in its
"clean" target, but the distclean and maintainer-clean targets
overlooked that (and they don't simply invoke clean, because that
would result in an extra tree traversal). While at it, let's just
make sure that removing GNUmakefile itself is the very last step of
the recipe.
http://git.postgresql.org/pg/commitdiff/9660710e2f5bbbf1b18640fbc5edcceafe7a10ad

- Support "expanded" objects, particularly arrays, for better
performance. This patch introduces the ability for complex
datatypes to have an in-memory representation that is different from
their on-disk format. On-disk formats are typically optimized for
minimal size, and in any case they can't contain pointers, so they
are often not well-suited for computation. Now a datatype can
invent an "expanded" in-memory format that is better suited for its
operations, and then pass that around among the C functions that
operate on the datatype. There are also provisions (rudimentary as
yet) to allow an expanded object to be modified in-place under
suitable conditions, so that operations like assignment to an
element of an array need not involve copying the entire array. The
initial application for this feature is arrays, but it is not hard
to foresee using it for other container types like JSON, XML and
hstore. I have hopes that it will be useful to PostGIS as well. In
this initial implementation, a few heuristics have been hard-wired
into plpgsql to improve performance for arrays that are stored in
plpgsql variables. We would like to generalize those hacks so that
other datatypes can obtain similar improvements, but figuring out
some appropriate APIs is left as a task for future work. (The
heuristics themselves are probably not optimal yet, either, as they
sometimes force expansion of arrays that would be better left
alone.) Preliminary performance testing shows impressive speed gains
for plpgsql functions that do element-by-element access or update of
large arrays. There are other cases that get a little slower, as a
result of added array format conversions; but we can hope to improve
anything that's annoyingly bad. In any case most applications
should see a net win. Tom Lane, reviewed by Andres Freund
http://git.postgresql.org/pg/commitdiff/1dc5ebc9077ab742079ce5dac9a6664248d42916

- Suppress uninitialized-variable warning.
http://git.postgresql.org/pg/commitdiff/6c9e93d3ffbf99435636103ad69d6469c64e2aef

- Docs: fix erroneous claim about max byte length of GB18030. This
encoding has characters up to 4 bytes long, not 2.
http://git.postgresql.org/pg/commitdiff/333d0779627a6c6125c018ea39da4427d3bdd93f

- Fix portability issue in pg_audit. "%ld" is not a portable way to
print int64's. This may explain the buildfarm crashes we're seeing
--- it seems to make dromedary happy, at least.
http://git.postgresql.org/pg/commitdiff/35a1e1d1593f4355c9d87bbc8208a8736801a607

- Teach UtfToLocal/LocalToUtf to support algorithmic encoding
conversions. Until now, these functions have only supported
encoding conversions using lookup tables, which is fine as long as
there's not too many code points to convert. However, GB18030
expects all 1.1 million Unicode code points to be convertible, which
would require a ridiculously-sized lookup table. Fortunately, a
large fraction of those conversions can be expressed through
arithmetic, ie the conversions are one-to-one in certain defined
ranges. To support that, provide a callback function that is used
after consulting the lookup tables. (This patch doesn't actually
change anything about the GB18030 conversion behavior, just provide
infrastructure for fixing it.) Since this requires changing the APIs
of UtfToLocal/LocalToUtf anyway, take the opportunity to rearrange
their argument lists into what seems to me a saner order. And
beautify the call sites by using lengthof() instead of error-prone
sizeof() arithmetic. In passing, also mark all the lookup tables
used by these calls "const". This moves an impressive amount of
stuff into the text segment, at least on my machine, and is safer
anyhow.
http://git.postgresql.org/pg/commitdiff/7730f48ede0d222e7f750541d3d5f0f74d75d99b

- Honor traditional SGML NAMELEN limit. We've conformed to this limit
in the past, so might as well continue to. Aaron Swenson
http://git.postgresql.org/pg/commitdiff/4b8f797f672bef07b4e87b4650b4035731b61d84

- Fix insufficiently-paranoid GB18030 encoding verifier. The previous
coding effectively only verified that the second byte of a multibyte
character was in the expected range; moreover, it wasn't careful to
make sure that the second byte even exists in the buffer before
touching it. The latter seems unlikely to cause any real problems
in the field (in particular, it could never be a problem with
null-terminated input), but it's still a bug. Since GB18030 is not
a supported backend encoding, the only thing we'd really be doing
with GB18030 text is converting it to UTF8 in LocalToUtf, which
would fail anyway on any invalid character for lack of a match in
its lookup table. So the only user-visible consequence of this
change should be that you'll get "invalid byte sequence for
encoding" rather than "character has no equivalent" for malformed
GB18030 input. However, impending changes to the GB18030 conversion
code will require these tighter up-front checks to avoid producing
bogus results.
http://git.postgresql.org/pg/commitdiff/a868931fecdf93f3ceb1c9431bb93757b706269d

- Fix outdated src/test/mb/ tests, and add a GB18030 test. The
expected-output files for these tests were broken by the recent
addition of a warning for hash indexes. Update them. Also add a
test case for GB18030 encoding, similar to the other ones. This is
a pretty weak test, but it's better than nothing.
http://git.postgresql.org/pg/commitdiff/07af523870bcfe930134054febd3a6a114942e5b

- Extend GB18030 encoding conversion to cover full Unicode range. Our
previous code for GB18030 <-> UTF8 conversion only covered Unicode
code points up to U+FFFF, but the actual spec defines conversions
for all code points up to U+10FFFF. That would be rather
impractical as a lookup table, but fortunately there is a simple
algorithmic conversion between the additional code points and the
equivalent GB18030 byte patterns. Make use of the just-added
callback facility in LocalToUtf/UtfToLocal to perform the additional
conversions. Having created the infrastructure to do that, we can
use the same code to map certain linearly-related subranges of the
Unicode space below U+FFFF, allowing removal of the corresponding
lookup table entries. This more than halves the lookup table size,
which is a substantial savings; utf8_and_gb18030.so drops from
nearly a megabyte to about half that. In support of doing that,
replace ISO10646-GB18030.TXT with the data file gb-18030-2000.xml
(retrieved from
http://source.icu-project.org/repos/icu/data/trunk/charset/data/xml/
) in which these subranges have been deleted from the simple lookup
entries. Per bug #12845 from Arjen Nienhuis. The conversion code
added here is based on his proposed patch, though I whacked it
around rather heavily.
http://git.postgresql.org/pg/commitdiff/8d3e0906df5496b853cc763f87b9ffd2ae27adbe

- Fix uninitialized variable. Per compiler warnings.
http://git.postgresql.org/pg/commitdiff/66493dd7aa02e19e93f7d5687acaab70075db34f

- Improve test for CONVERT() with GB18030 <-> UTF8. Add a bit of
coverage of high code points. Arjen Nienhuis
http://git.postgresql.org/pg/commitdiff/199f5973c50fe94e128508ff2218c42126fd0ee1

- Update time zone data files to tzdata release 2015d. DST law
changes in Egypt, Mongolia, Palestine. Historical corrections for
Canada and Chile. Revised zone abbreviation for America/Adak
(HST/HDT not HAST/HADT).
http://git.postgresql.org/pg/commitdiff/9d366c1f3d758c3b80cd482a6d4528960c4fc325

- Avoid direct use of INFINITY. It's not very portable. Per
buildfarm.
http://git.postgresql.org/pg/commitdiff/12cc299c65ba6124c7d459f2605404ad43909db3

- More portability fixing for bipartite_match.c. <float.h> is
required for isinf() on some platforms. Per buildfarm.
http://git.postgresql.org/pg/commitdiff/26058bf0dc5fca0a5107b2ee136a81a86cf36049

- Fix docs typo. I don't think "respectfully" is what was meant here...
http://git.postgresql.org/pg/commitdiff/c65aa7a87e4232d7eefea3d78ec7e1d9f8b4708b

- First-draft release notes for 9.4.2 et al. As usual, the release
notes for older branches will be made by cutting these down, but put
them up for community review first.
http://git.postgresql.org/pg/commitdiff/0563b4c0c3b7cc2323cfb63e11d723764e2d5f7d

- Release notes for 9.4.2, 9.3.7, 9.2.11, 9.1.16, 9.0.20.
http://git.postgresql.org/pg/commitdiff/a0891d2d0136ea06cde957635338c0c238df87de

Stephen Frost pushed:

- Allow LOCK TABLE .. ROW EXCLUSIVE MODE with INSERT. INSERT acquires
RowExclusiveLock during normal operation and therefore it makes
sense to allow LOCK TABLE .. ROW EXCLUSIVE MODE to be executed by
users who have INSERT rights on a table (even if they don't have
UPDATE or DELETE). Not back-patching this as it's a behavior change
which, strictly speaking, loosens security restrictions. Per
discussion with Tom and Robert (circa 2013).
http://git.postgresql.org/pg/commitdiff/fa2642438f189c2b169ace3ac1df19533b9c7781

- pgbench: Don't fail during startup. In pgbench, report, but ignore,
any errors returned when attempting to vacuum/truncate the default
tables during startup. If the tables are needed, we'll error out
soon enough anyway. Per discussion with Tatsuo, David Rowley, Jim
Nasby, Robert, Andres, Fujii, Fabrízio de Royes Mello, Tomas Vondra,
Michael Paquier, Peter, based on a suggestion from Jeff Janes, patch
from Robert, additional message wording from Tom.
http://git.postgresql.org/pg/commitdiff/aa4a0b9571232f44e4b8d9effca3c540e657cebb

- Fix buildfarm with regard to pg_audit. Remove the check that
pg_audit be installed by shared_preload_libraries as that's not
going to work when running the regressions tests in the buildfarm.
That check was primairly a nice to have and isn't required anyway.
http://git.postgresql.org/pg/commitdiff/dfb7624a13277e7662a2c983e4440dcd522c16f1

- Further fixes for the buildfarm for pg_audit. The database built by
the buildfarm is specific to the extension, use \connect - instead.
http://git.postgresql.org/pg/commitdiff/c703b1e689010961c6bb7ede1e88c2ae296858b6

- Further fixes for the buildfarm for pg_audit. Also, use a function
to load the extension ahead of all other calls, simulating load from
shared_libraries_preload, to make sure the hooks are in place before
logging start.
http://git.postgresql.org/pg/commitdiff/8a2e1edd2ba0817313c1c0ef76b03a5ab819d17f

- Improve pg_audit regression tests. Instead of creating a new
superuser role, extract out what the current user is and use that
user instead. Further, clean up and drop all objects created by the
regression test. Pointed out by Tom.
http://git.postgresql.org/pg/commitdiff/ed6ea8e815b4331c94f827f45054817e7e1a7784

- Remove useless pg_audit.conf. No need to have pg_audit.conf any
longer since the regression tests are just loading the module at the
start of each session (to simulate being in
shared_preload_libraries, which isn't something we can actually make
happen on the buildfarm itself, it seems). Pointed out by Tom
http://git.postgresql.org/pg/commitdiff/aff27e33797c5161a322a10b44a260d848b73154

- Make repeated 'make installcheck' runs work. In pg_audit, set
client_min_messages up to warning, then reset the role attributes,
to completely reset the session while not making the regression
tests depend on being run by any particular user.
http://git.postgresql.org/pg/commitdiff/b22b770683806db0a1c0a52a4601a3b6755891e0

- pg_audit Makefile, REINDEX changes. Clean up the Makefile, per
Michael Paquier. Classify REINDEX as we do in core, use '1.0' for
the version, per Fujii.
http://git.postgresql.org/pg/commitdiff/a936743b336ed0b53f1ddb28b4535f4c27bd2b23

- Add pg_audit, an auditing extension. This extension provides
detailed logging classes, ability to control logging at a per-object
level, and includes fully-qualified object names for logged
statements (DML and DDL) in independent fields of the log output.
Authors: Ian Barwick, Abhijit Menon-Sen, David Steele Reviews by:
Robert Haas, Tatsuo Ishii, Sawada Masahiko, Fujii Masao, Simon
Riggs. Discussion with: Josh Berkus, Jaime Casanova, Peter
Eisentraut, David Fetter, Yeb Havinga, Alvaro Herrera, Petr Jelinek,
Tom Lane, MauMau, Bruce Momjian, Jim Nasby, Michael Paquier,
Fabrízio de Royes Mello, Neil Tiffin.
http://git.postgresql.org/pg/commitdiff/ac52bb0442f79076b14acd8ad5b696946c1053b8

Peter Eisentraut pushed:

- Replace some appendStringInfo* calls with more appropriate variants.
Author: David Rowley <dgrowleyml(at)gmail(dot)com>
http://git.postgresql.org/pg/commitdiff/d02f16470f117db3038dbfd87662d5f0eb5a2a9b

- PL/Python: Remove procedure cache invalidation. This was added to
react to changes in the pg_transform catalog, but building with
CLOBBER_CACHE_ALWAYS showed that PL/Python was not prepared for
having its procedure cache cleared. Since this is a marginal use
case, and we don't do this for other catalogs anyway, we can
postpone this to another day.
http://git.postgresql.org/pg/commitdiff/dcf5e319089e750d726a0e18a8c90b258c39362c

- Add pg_settings.pending_restart column. with input from David G.
Johnston, Robert Haas, Michael Paquier
http://git.postgresql.org/pg/commitdiff/a486e35706eaea17e27e5fa0a2de6bc98546de1e

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

- hstore_plpython: Fix regression tests under Python 3
http://git.postgresql.org/pg/commitdiff/fab6ca23eaf114d1ae12377c7f5c8c952b5e0159

Álvaro Herrera pushed:

- "Fix" test_ddl_deparse regress test schedule. MSVC is not smart
enough to figure it out, so dumb down the Makefile and remove the
schedule file. Also add a .gitignore file. Author: Michael Paquier
http://git.postgresql.org/pg/commitdiff/007c932e5aaf6d68087f134b8557bbb7db149e94

- Allow on-the-fly capture of DDL event details. This feature lets
user code inspect and take action on DDL events. Whenever a
ddl_command_end event trigger is installed, DDL actions executed are
saved to a list which can be inspected during execution of a
function attached to ddl_command_end. The set-returning function
pg_event_trigger_ddl_commands can be used to list actions so
captured; it returns data about the type of command executed, as
well as the affected object. This is sufficient for many uses of
this feature. For the cases where it is not, we also provide a
"command" column of a new pseudo-type pg_ddl_command, which is a
pointer to a C structure that can be accessed by C code. The struct
contains all the info necessary to completely inspect and even
reconstruct the executed command. There is no actual deparse code
here; that's expected to come later. What we have is enough
infrastructure that the deparsing can be done in an external
extension. The intention is that we will add some deparsing code in
a later release, as an in-core extension. A new test module is
included. It's probably insufficient as is, but it should be
sufficient as a starting point for a more complete and future-proof
approach. Authors: Álvaro Herrera, with some help from Andres
Freund, Ian Barwick, Abhijit Menon-Sen. Reviews by Andres Freund,
Robert Haas, Amit Kapila, Michael Paquier, Craig Ringer, David
Steele. Additional input from Chris Browne, Dimitri Fontaine,
Stephen Frost, Petr Jelínek, Tom Lane, Jim Nasby, Steven Singer,
Pavel Stěhule. Based on original work by Dimitri Fontaine, though I
didn't use his code. Discussion:
https://www.postgresql.org/message-id/m2txrsdzxa.fsf@2ndQuadrant.fr
https://www.postgresql.org/message-id/20131108153322.GU5809@eldon.alvh.no-ip.org
https://www.postgresql.org/message-id/20150215044814.GL3391@alvh.no-ip.org
http://git.postgresql.org/pg/commitdiff/b488c580aef4e05f39be5daaab6464da5b22a494

- Move strategy numbers to include/access/stratnum.h. For upcoming
BRIN opclasses, it's convenient to have strategy numbers defined in
a single place. Since there's nothing appropriate, create it. The
StrategyNumber typedef now lives there, as well as existing strategy
numbers for B-trees (from skey.h) and R-tree-and-friends (from
gist.h). skey.h is forced to include stratnum.h because of the
StrategyNumber typedef, but gist.h is not; extensions that currently
rely on gist.h for rtree strategy numbers might need to add a new A
few .c files can stop including skey.h and/or gist.h, which is a
nice side benefit. Per discussion:
https://www.postgresql.org/message-id/20150514232132.GZ2523@alvh.no-ip.org
Authored by Emre Hasegeli and Álvaro. (It's not clear to me why
bootscanner.l has any #include lines at all.)
http://git.postgresql.org/pg/commitdiff/26df7066cc229887d4defdf1d105c0a22b8a88fb

- Add BRIN infrastructure for "inclusion" opclasses. This lets BRIN
be used with R-Tree-like indexing strategies. Also provided are
operator classes for range types, box and inet/cidr. The
infrastructure provided here should be sufficient to create operator
classes for similar datatypes; for instance, opclasses for PostGIS
geometries should be doable, though we didn't try to implement one.
(A box/point opclass was also submitted, but we ripped it out before
commit because the handling of floating point comparisons in
existing code is inconsistent and would generate corrupt indexes.)
Author: Emre Hasegeli. Cosmetic changes by me. Review: Andreas
Karlsson
http://git.postgresql.org/pg/commitdiff/b0b7be61337fc64147f2ad0af5bf2c0e6b8a709f

Andrew Dunstan pushed:

- pg_basebackup -F t now succeeds with a long symlink target
http://git.postgresql.org/pg/commitdiff/97e0aa697983cf7f7f79e69f2dc248fdefb7dbf6

- Map basebackup tablespaces using a tablespace_map file. Windows
can't reliably restore symbolic links from a tar format, so instead
during backup start we create a tablespace_map file, which is used
by the restoring postgres to create the correct links in pg_tblspc.
The backup protocol also now has an option to request this file to
be included in the backup stream, and this is used by pg_basebackup
when operating in tar mode. This is done on all platforms, not just
Windows. This means that pg_basebackup will not not work in tar
mode against 9.4 and older servers, as this protocol option isn't
implemented there. Amit Kapila, reviewed by Dilip Kumar, with a
little editing from me.
http://git.postgresql.org/pg/commitdiff/72d422a5227ef6f76f412486a395aba9f53bf3f0

- Fix some errors from jsonb functions patch. The catalog version
should have been bumped, and the alternative regression result file
was not up to date with the name of jsonb_pretty.
http://git.postgresql.org/pg/commitdiff/5c7df74204e2fb9440b576518d40fcf3ac65c8ac

- Fix jsonb replace and delete on scalars and empty structures. These
operations now error out if attempted on scalars, and simply return
the input if attempted on empty arrays or objects. Along the way we
remove the unnecessary cloning of the input when it's known to be
unchanged. Regression tests covering these cases are added.
http://git.postgresql.org/pg/commitdiff/3f2cec797ecceb7467e365410506c0817f9d0163

- Additional functions and operators for jsonb. jsonb_pretty(jsonb)
produces nicely indented json output. jsonb || jsonb concatenates
two jsonb values. jsonb - text removes a key and its associated
value from the json jsonb - int removes the designated array element
jsonb - text[] removes a key and associated value or array element
at the designated path jsonb_replace(jsonb,text[],jsonb) replaces
the array element designated by the path or the value associated
with the key designated by the path with the given value. Original
work by Dmitry Dolgov, adapted and reworked for PostgreSQL core by
Andrew Dunstan, reviewed and tidied up by Petr Jelinek.
http://git.postgresql.org/pg/commitdiff/c6947010ceb42143d9f047c65c1eac2b38928ab7

Andres Freund pushed:

- Fix ON CONFLICT bugs that manifest when used in rules. Specifically
the tlist and rti of the pseudo "excluded" relation weren't properly
treated by expression_tree_walker, which lead to errors when
excluded was referenced inside a rule because the varnos where not
properly adjusted. Similar omissions in OffsetVarNodes and
expression_tree_mutator had less impact, but should obviously be
fixed nonetheless. A couple tests of for ON CONFLICT UPDATE into
INSERT rule bearing relations have been added. In passing I updated
a couple comments.
http://git.postgresql.org/pg/commitdiff/4af6e61a363246cf7fff3368a76603b0ce9945dd

- Add pgstattuple_approx() to the pgstattuple extension. The new
function allows to estimate bloat and other table level statics in a
faster, but approximate, way. It does so by using information from
the free space map for pages marked as all visible in the visibility
map. The rest of the table is actually read and free space/bloat is
measured accurately. In many cases that allows to get bloat
information much quicker, causing less IO. Author: Abhijit
Menon-Sen Reviewed-By: Andres Freund, Amit Kapila and Tomas Vondra
Discussion: 20140402214144(dot)GA28681(at)kea(dot)toroid(dot)org
http://git.postgresql.org/pg/commitdiff/5850b20f58a594ac69f4f77b24cad94fc3bfd946

- Support GROUPING SETS, CUBE and ROLLUP. This SQL standard
functionality allows to aggregate data by different GROUP BY clauses
at once. Each grouping set returns rows with columns grouped by in
other sets set to NULL. This could previously be achieved by doing
each grouping as a separate query, conjoined by UNION ALLs. Besides
being considerably more concise, grouping sets will in many cases be
faster, requiring only one scan over the underlying data. The
current implementation of grouping sets only supports using sorting
for input. Individual sets that share a sort order are computed in
one pass. If there are sets that don't share a sort order,
additional sort & aggregation steps are performed. These additional
passes are sourced by the previous sort step; thus avoiding repeated
scans of the source data. The code is structured in a way that
adding support for purely using hash aggregation or a mix of hashing
and sorting is possible. Sorting was chosen to be supported first,
as it is the most generic method of implementation. Instead of, as
in an earlier versions of the patch, representing the chain of sort
and aggregation steps as full blown planner and executor nodes, all
but the first sort are performed inside the aggregation node itself.
This avoids the need to do some unusual gymnastics to handle having
to return aggregated and non-aggregated tuples from underlying
nodes, as well as having to shut down underlying nodes early to
limit memory usage. The optimizer still builds Sort/Agg node to
describe each phase, but they're not part of the plan tree, but
instead additional data for the aggregation node. They're a
convenient and preexisting way to describe aggregation and sorting.
The first (and possibly only) sort step is still performed as a
separate execution step. That retains similarity with existing group
by plans, makes rescans fairly simple, avoids very deep plans
(leading to slow explains) and easily allows to avoid the sorting
step if the underlying data is sorted by other means. A somewhat
ugly side of this patch is having to deal with a grammar ambiguity
between the new CUBE keyword and the cube extension/functions named
cube (and rollup). To avoid breaking existing deployments of the
cube extension it has not been renamed, neither has cube been made a
reserved keyword. Instead precedence hacking is used to make GROUP
BY cube(..) refer to the CUBE grouping sets feature, and not the
function cube(). To actually group by a function cube(), unlikely as
that might be, the function name has to be quoted. Needs a
catversion bump because stored rules may change. Author: Andrew
Gierth and Atri Sharma, with contributions from Andres Freund
Reviewed-By: Andres Freund, Noah Misch, Tom Lane, Svenne Krap, Tomas
Vondra, Erik Rijkers, Marti Raudsepp, Pavel Stehule Discussion:
CAOeZVidmVRe2jU6aMk_5qkxnB7dfmPROzM7Ur8JPW5j8Y5X-Lw(at)mail(dot)gmail(dot)com
http://git.postgresql.org/pg/commitdiff/f3d3118532175541a9a96ed78881a3b04a057128

Fujii Masao pushed:

- Support VERBOSE option in REINDEX command. When this option is
specified, a progress report is printed as each index is reindexed.
Per discussion, we agreed on the following syntax for the
extensibility of the options. REINDEX (flexible options) { INDEX |
... } name Sawada Masahiko. Reviewed by Robert Haas, Fabrízio
Mello, Alvaro Herrera, Kyotaro Horiguchi, Jim Nasby and me.
Discussion:
CAD21AoA0pK3YcOZAFzMae+2fcc3oGp5zoRggDyMNg5zoaWDhdQ(at)mail(dot)gmail(dot)com
http://git.postgresql.org/pg/commitdiff/ecd222e770d352121590363ffdf981147a43e976

- Support --verbose option in reindexdb. Sawada Masahiko, reviewed by
Fabrízio Mello
http://git.postgresql.org/pg/commitdiff/458a07701e2e464558238aeb6058b5179b133921

Simon Riggs pushed:

- Separate block sampling functions. Refactoring ahead of tablesample
patch. Requested and reviewed by Michael Paquier. Petr Jelinek
http://git.postgresql.org/pg/commitdiff/83e176ec18d2a91dbea1d0d1bd94c38dc47cd77c

- TABLESAMPLE, SQL Standard and extensible. Add a TABLESAMPLE clause
to SELECT statements that allows user to specify random BERNOULLI
sampling or block level SYSTEM sampling. Implementation allows for
extensible sampling functions to be written, using a standard API.
Basic version follows SQLStandard exactly. Usable concrete use cases
for the sampling API follow in later commits. Petr Jelinek.
Reviewed by Michael Paquier and Simon Riggs
http://git.postgresql.org/pg/commitdiff/f6d208d6e51810c73f0e02c477984a6b44627f11

- TABLESAMPLE system_rows(limit). Contrib module implementing a
tablesample method that allows you to limit the sample by a hard row
limit. Petr Jelinek. Reviewed by Michael Paquier, Amit Kapila and
Simon Riggs
http://git.postgresql.org/pg/commitdiff/9689290ff0c065f9bdc4e3a76e7931e84554224b

- contrib/tsm_system_rows
http://git.postgresql.org/pg/commitdiff/4d40494b11883f154e40f3996ad414fb0933bc12

- TABLESAMPLE system_time(limit). Contrib module implementing a
tablesample method that allows you to limit the sample by a hard
time limit. Petr Jelinek. Reviewed by Michael Paquier, Amit Kapila
and Simon Riggs
http://git.postgresql.org/pg/commitdiff/149f6f15768e731c760392679ef8ddaf00dae6c8

- Tablesample method API docs. Petr Jelinek
http://git.postgresql.org/pg/commitdiff/910baf0a96a43a629c3d469b97f31e1f5103ff9e

- SQL Standard feature T613 Sampling now supported.
http://git.postgresql.org/pg/commitdiff/1e98fa0bf8de9d25d97fb346755a9c9c17bba549

- Add to contrib/Makefile
http://git.postgresql.org/pg/commitdiff/df259759fbd0a18e8c64f551824a0ec941ba067b

- Add docs for tablesample system_rows().
http://git.postgresql.org/pg/commitdiff/6e4415c6aa428132dd41c8bf23a0885fca0f2271

- Add docs for tablesample system_time().
http://git.postgresql.org/pg/commitdiff/f941d033296eb99eeac60f22fc70938893bf7a30

- contrib/tsm_system_time
http://git.postgresql.org/pg/commitdiff/56e121a508a1745c6b0c66ad2f237e9b5f03b19d

Heikki Linnakangas pushed:

- Allow GiST distance function to return merely a lower-bound. The
distance function can now set *recheck = false, like index quals.
The executor will then re-check the ORDER BY expressions, and use a
queue to reorder the results on the fly. This makes it possible to
do kNN-searches on polygons and circles, which don't store the exact

- Fix datatype confusion with the new lossy GiST distance functions.
We can only support a lossy distance function when the distance
function's datatype is comparable with the original ordering
operator's datatype. The distance function always returns a float8,
so we are limited to float8, and float4 (by a hard-coded cast of the
float8 to float4). In light of this limitation, it seems like a
good idea to have a separate 'recheck' flag for the ORDER BY
expressions, so that if you have a non-lossy distance function, it
still works with lossy quals. There are cases like that with the
build-in or contrib opclasses, but it's plausible. There was a
hidden assumption that the ORDER BY values returned by GiST match
the original ordering operator's return type, but there are plenty
of examples where that's not true, e.g. in btree_gist and pg_trgm.
As long as the distance function is not lossy, we can tolerate that
and just not return the distance to the executor (or rather, always
return NULL). The executor doesn't need the distances if there are
no lossy results. There was another little bug: the recheck
variable was not initialized before calling the distance function.
That revealed the bigger issue, as the executor tried to reorder
tuples that didn't need reordering, and that failed because of the
datatype mismatch.
http://git.postgresql.org/pg/commitdiff/98edd617f3b62a02cb2df9b418fcc4ece45c7ec0

- Fix docs build. Oops.
http://git.postgresql.org/pg/commitdiff/8b0f105d2d179dc1085b16f0594c8fa78d13267d

- Add archive_mode='always' option. In 'always' mode, the standby
independently archives all files it receives from the primary.
Original patch by Fujii Masao, docs and review by me.
http://git.postgresql.org/pg/commitdiff/ffd37740ee6fcd434416ec0c5461f7040e0a11de

- Silence another create_index regression test failure. More platform
differences in the less-significant digits in output. Per buildfarm
member rover_firefly, still.
http://git.postgresql.org/pg/commitdiff/11a83bbedd73800db70f6f2af5a8eb10d15d39d7

- Silence create_index regression test failure. The expected output
contained some floating point values which might get rounded
slightly differently on different platforms. The exact output isn't
very interesting in this test, so just round it. Per buildfarm
member rover_firefly.
http://git.postgresql.org/pg/commitdiff/9feaba28e27820f91d13c3de6581bb3b8c3234c6

value in the index, but just a bounding box. Alexander Korotkov and
me
http://git.postgresql.org/pg/commitdiff/35fcb1b3d038a501f3f4c87c05630095abaaadab

Magnus Hagander pushed:

- Minor docs fixes for pg_audit. Peter Geoghegan
http://git.postgresql.org/pg/commitdiff/6b665454e38580a72f355dcae37952cf1c82f119

- Fix typos in comments. Dmitriy Olshevskiy
http://git.postgresql.org/pg/commitdiff/3b075e9d7be0181f8434e7b24007d87b618934c0

- Fix wording error caused by recent typo fixes. It wasn't just a
typo, but bad wording. This should make it more clear. Pointed out
by Tom Lane.
http://git.postgresql.org/pg/commitdiff/de6109b8cc42be11e40aeeafff90ee145367fa00

== Rejected Patches (for now) ==

No one was disappointed this week :-)

== Pending Patches ==

Andres Freund sent in another revision of a patch to manipulate
complex types as non-contiguous structures in-memory.

Michael Paquier sent in a patch to improve regression tests to check
LOCK TABLE and table permissions.

Michael Paquier sent in a patch to remove leftovers after dcae5fac
(improve speed of make check-world) in git tree with check-world.

Abhijit Menon-Sen sent in two more revisions of a patch to create a
fast bloat measurement tool.

Heikki Linnakangas sent in two more revisions of a patch to fix some
interactions between streaming replication and WAL archiving.

Kaigai Kouhei sent in three more revisions of a patch to improve the
custom/foreign join API.

Stephen Frost sent in two more revisions of a patch to add default
roles for functionality.

Álvaro Herrera and Petr Jelinek traded patches to add an access method
for sequences.

Kyotaro HORIGUCHI sent in another revision of a patch to add
multi-column statistics.

Petr A. Korobeinikov sent in another revision of a patch to support
for \ev viewname and \sv viewname in psql.

Fabien COELHO sent in a patch to allow backslash-continuations in
custom scripts.

Etsuro Fujita sent in a doc patch for ALTER FOREIGN TABLE.

Robert Haas sent in a patch to fix the documentation of
BackgroundWorkerInitializeConnection.

Beena Emerson sent in another revision of a patch to add support for N
synchronous standby servers.

Shigeru HANADA sent in another revision of a patch to add postgres_fdw
join pushdown.