== PostgreSQL Weekly News - June 07 2015 ==

== PostgreSQL Weekly News - June 07 2015 ==

Bug fix releases 9.4.3, 9.3.8, 9.2.12, 9.1.17, and 9.0.21 released.
Upgrade!
https://wiki.postgresql.org/wiki/May_2015_Fsync_Permissions_Bug

== PostgreSQL Product News ==

BDR 0.9.1, a replication system based on logical WALs, released.
http://bdr-project.org/docs/stable/release-0.9.1.html

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 Jul 15.
http://pgbr.postgresql.org.br/2015/en/#call-for-papers

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

StakePoint technical preview, a Portfolio Project Management solution
using PostgreSQL, released.
http://stakepoint.com/

tds_fdw 1.0.2, a foreign data wrapper for MS-SQL Server and Sybase,
released.
https://github.com/GeoffMontee/tds_fdw/releases

== PostgreSQL Jobs for June ==

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

== PostgreSQL Local ==

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

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

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

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

Andrew Dunstan pushed:

- Add a couple of missing JsonbValue type initialisers.
http://git.postgresql.org/pg/commitdiff/28b29f7e44534339f88ea914794f8b64e13bc528

- Rename jsonb_replace to jsonb_set and allow it to add new values.
The function is given a fourth parameter, which defaults to true.
When this parameter is true, if the last element of the path is
missing in the original json, jsonb_set creates it in the result and
assigns it the new value. If it is false then the function does
nothing unless all elements of the path are present, including the
last. Based on some original code from Dmitry Dolgov, heavily
modified by me. Catalog version bumped.
http://git.postgresql.org/pg/commitdiff/37def4224505f3a23a5eef000f0d05daea59c5b5

- Avoid naming a variable "new", and remove bogus initializer. Per
gripe from Tom Lane.
http://git.postgresql.org/pg/commitdiff/50ab76d3c19c95589f4eb19683e25cb88a2506e2

Bruce Momjian pushed:

- pgindent: add typedef blog URL
http://git.postgresql.org/pg/commitdiff/ab959cc0ea7ee143e017e18fae23e4269a1ba435

Tom Lane pushed:

- Release notes for 9.4.3, 9.3.8, 9.2.12, 9.1.17, 9.0.21. Also sneak
entries for commits 97ff2a564 et al into the sections for the
previous releases in the relevant branches. Those fixes did go out
in the previous releases, but missed getting documented.
http://git.postgresql.org/pg/commitdiff/82ec7d28211b97a2c9917b7a71edbe6b019578da

- Fix planner's cost estimation for SEMI/ANTI joins with inner
indexscans. When the inner side of a nestloop SEMI or ANTI join is
an indexscan that uses all the join clauses as indexquals, it can be
presumed that both matched and unmatched outer rows will be
processed very quickly: for matched rows, we'll stop after fetching
one row from the indexscan, while for unmatched rows we'll have an
indexscan that finds no matching index entries, which should also be
quick. The planner already knew about this, but it was nonetheless
charging for at least one full run of the inner indexscan, as a
consequence of concerns about the behavior of materialized inner
scans --- but those concerns don't apply in the fast case. If the
inner side has low cardinality (many matching rows) this could make
an indexscan plan look far more expensive than it actually is. To
fix, rearrange the work in initial_cost_nestloop/final_cost_nestloop
so that we don't add the inner scan cost until we've inspected the
indexquals, and then we can add either the full-run cost or just the
first tuple's cost as appropriate. Experimentation with this fix
uncovered another problem: add_path and friends were coded to
disregard cheap startup cost when considering parameterized paths.
That's usually okay (and desirable, because it thins the path herd
faster); but in this fast case for SEMI/ANTI joins, it could result
in throwing away the desired plain indexscan path in favor of a
bitmap scan path before we ever get to the join costing logic. In
the many-matching-rows cases of interest here, a bitmap scan will do
a lot more work than required, so this is a problem. To fix, add a
per-relation flag consider_param_startup that works like the
existing consider_startup flag, but applies to parameterized paths,
and set it for relations that are the inside of a SEMI or ANTI join.
To make this patch reasonably safe to back-patch, care has been
taken to avoid changing the planner's behavior except in the very
narrow case of SEMI/ANTI joins with inner indexscans. There are
places in compare_path_costs_fuzzily and add_path_precheck that are
not terribly consistent with the new approach, but changing them
will affect planner decisions at the margins in other cases, so
we'll leave that for a HEAD-only fix. Back-patch to 9.3; before
that, the consider_startup flag didn't exist, meaning that the
second aspect of the patch would be too invasive. Per a complaint
from Peter Holzer and analysis by Tomas Vondra.
http://git.postgresql.org/pg/commitdiff/3f59be836c555fa679bbe0ec76de50a8b5cb23e0

- Fix some questionable edge-case behaviors in add_path() and friends.
add_path_precheck was doing exact comparisons of path costs, but it
really needs to do them fuzzily to be sure it won't reject paths
that could survive add_path's comparisons. (This can only matter if
the initial cost estimate is very close to the final one, but that
turns out to often be true.) Also, it should ignore startup cost for
this purpose if and only if compare_path_costs_fuzzily would do so.
The previous coding always ignored startup cost for parameterized
paths, which is wrong as of commit 3f59be836c555fa6; it could result
in improper early rejection of paths that we care about for
SEMI/ANTI joins. It also always considered startup cost for
unparameterized paths, which is just as wrong though the only effect
is to waste planner cycles on paths that can't survive. Instead, it
should consider startup cost only when directed to by the
consider_startup/ consider_param_startup relation flags. Likewise,
compare_path_costs_fuzzily should have symmetrical behavior for
parameterized and unparameterized paths. In this case, the best
answer seems to be that after establishing that total costs are
fuzzily equal, we should compare startup costs whether or not the
consider_xxx flags are on. That is what it's always done for
unparameterized paths, so let's make the behavior for parameterized
paths match. These issues were noted while developing the SEMI/ANTI
join costing fix of commit 3f59be836c555fa6, but we chose not to
back-patch these fixes, because they can cause changes in the
planner's choices among nearly-same-cost plans. (There is in fact
one minor change in plan choice within the core regression tests.)
Destabilizing plan choices in back branches without very clear
improvements is frowned on, so we'll just fix this in HEAD.
http://git.postgresql.org/pg/commitdiff/3b0f77601b9f9f3a2e36a813e4cd32c00e0864d6

- Stabilize query plans in rowsecurity regression test. Some recent
buildfarm failures can be explained by supposing that autovacuum or
autoanalyze fired on the tables created by this test, resulting in
plan changes. Do a proactive VACUUM ANALYZE on the test's principal
tables to try to forestall such changes.
http://git.postgresql.org/pg/commitdiff/5cdf25e16843dff33dbc2ddc02941458032e3ad4

- Stabilize results of brin regression test. This test used seqscans
on tenk1, with LIMIT, to build test data. That works most of the
time, but if the synchronized-seqscan logic kicks in, we get varying
test data. This seems likely to explain the erratic test failures
on buildfarm member chipmunk, which uses smaller-than-default
shared_buffers. To fix, add ORDER BY clauses to force the ordering
to be what it was implicitly being assumed to be. Peter Geoghegan
had noticed this with respect to one of the trouble spots, though
not the ones actually causing the chipmunk issue.
http://git.postgresql.org/pg/commitdiff/bac99475eb6e9e6d69a91fee30b5420b8e0115be

- Fix brin "char" test to actually test what it meant to test.
Casting to char, without quotes, does not give the same results as
casting to "char". That meant we were not testing the brin "char"
paths at all, since we ended up with a text operator not a "char"
operator.
http://git.postgresql.org/pg/commitdiff/78e72794a76fef3233c06800c6046aaad0704a22

- Tighten the per-operator testing done in brin regression test.
Verify that the number of matches is exactly what it should be, not
just that it not be zero. This should help us detect any
environment-dependent issues. Also, verify that we're getting the
expected type of scan plan (either bitmap or seqscan as
appropriate). Right now, this is failing on the cidrcol test cases,
as shown in the output file. I'll look into that in a bit, but it
seems good to commit this as-is temporarily to verify that it
behaves as expected on the buildfarm.
http://git.postgresql.org/pg/commitdiff/79454c696bd3346a9f00f5e940398fb01a337fad

- Fix brin regression test so it actually tests cidr. The problem
noted in my previous commit was simpler than I thought: we weren't
getting an index plan because the column wasn't indexed.
http://git.postgresql.org/pg/commitdiff/1676e4381f48f7bf211f0965ad23abe10a683818

- Second try at stabilizing query plans in rowsecurity regression
test. This reverts commit 5cdf25e16843dff33dbc2ddc02941458032e3ad4,
which was almost immediately proven insufficient by the buildfarm.
On second thought, the tables involved are not large enough that
autovacuum or autoanalyze would notice them; what seems far more
likely to be the culprit is the database-wide "vacuum analyze" in
the concurrent gist test. That thing has given us one headache too
many, so get rid of it in favor of targeted vacuuming of that test's
own tables only.
http://git.postgresql.org/pg/commitdiff/1d27842519999cbac7e1cca8beaef053be9c7825

- Fix incorrect order of database-locking operations in
InitPostgres(). We should set MyProc->databaseId after acquiring
the per-database lock, not beforehand. The old way risked deadlock
against processes trying to copy or delete the target database,
since they would first acquire the lock and then wait for processes
with matching databaseId to exit; that left a window wherein an
incoming process could set its databaseId and then block on the
lock, while the other process had the lock and waited in vain for
the incoming process to exit. CountOtherDBBackends() would time out
and fail after 5 seconds, so this just resulted in an unexpected
failure not a permanent lockup, but it's still annoying when it
happens. A real-world example of a use-case is that short-duration
connections to a template database should not cause CREATE DATABASE
to fail. Doing it in the other order should be fine since the
contract has always been that processes searching the ProcArray for
a database ID must hold the relevant per-database lock while
searching. Thus, this actually removes the former race condition
that required an assumption that storing to MyProc->databaseId is
atomic. It's been like this for a long time, so back-patch to all
active branches.
http://git.postgresql.org/pg/commitdiff/ac23b711dd6ccb82fb70ca0f153fe755fd809a46

- Get rid of a //-style comment. Not sure how "//XXX" got into a
committed patch in the first place, as it's both content-free and
against project style. pgindent made a bit of a hash of it, too.
Going forward, we should have at least one buildfarm member using
"gcc -ansi" to catch such things, at least till such time as we
decide the project target language isn't C90 any more. I've turned
this option on on dromedary.
http://git.postgresql.org/pg/commitdiff/1497369e5df8bb129256f677a85327f80d3767d3

- Use a safer method for determining whether relcache init file is
stale. When we invalidate the relcache entry for a system catalog
or index, we must also delete the relcache "init file" if the init
file contains a copy of that rel's entry. The old way of doing this
relied on a specially maintained list of the OIDs of relations
present in the init file: we made the list either when reading the
file in, or when writing the file out. The problem is that when
writing the file out, we included only rels present in our local
relcache, which might have already suffered some deletions due to
relcache inval events. In such cases we correctly decided not to
overwrite the real init file with incomplete data --- but we still
used the incomplete initFileRelationIds list for the rest of the
current session. This could result in wrong decisions about whether
the session's own actions require deletion of the init file,
potentially allowing an init file created by some other concurrent
session to be left around even though it's been made stale. Since
we don't support changing the schema of a system catalog at runtime,
the only likely scenario in which this would cause a problem in the
field involves a "vacuum full" on a catalog concurrently with other
activity, and even then it's far from easy to provoke. Remarkably,
this has been broken since 2002 (in commit
786340441706ac1957a031f11ad1c2e5b6e18314), but we had never seen a
reproducible test case until recently. If it did happen in the
field, the symptoms would probably involve unexpected "cache lookup
failed" errors to begin with, then "could not open file" failures
after the next checkpoint, as all accesses to the affected catalog
stopped working. Recovery would require manually removing the stale
"pg_internal.init" file. To fix, get rid of the initFileRelationIds
list, and instead consult syscache.c's list of relations used in
catalog caches to decide whether a relation is included in the init
file. This should be a tad more efficient anyway, since we're
replacing linear search of a list with ~100 entries with a binary
search. It's a bit ugly that the init file contents are now so
directly tied to the catalog caches, but in practice that won't make
much difference. Back-patch to all supported branches.
http://git.postgresql.org/pg/commitdiff/f3b5565dd4e59576be4c772da364704863e6a835

Fujii Masao pushed:

- Minor improvement to txid_current() documentation. Michael Paquier,
reviewed by Christoph Berg and Naoya Anzai
http://git.postgresql.org/pg/commitdiff/37013621f3b0e296aa71b812ca9d46871ead95e2

- Fix some issues in pg_class.relminmxid and pg_database.datminmxid
documentation. Correct the name of directory which those catalog
columns allow to be shrunk. Correct the name of symbol which is
used as the value of pg_class.relminmxid when the relation is not a
table. Fix "ID ID" typo. Backpatch to 9.3 where those cataog
columns were introduced.
http://git.postgresql.org/pg/commitdiff/38d500ac2e5d4d4f3468b505962fb85850c1ff4b

- Remove -i/--ignore-version option from pg_dump, pg_dumpall and
pg_restore. The commit c22ed3d523782c43836c163c16fa5a7bb3912826
turned the -i/--ignore-version options into no-ops and marked as
deprecated. Considering we shipped that in 8.4, it's time to remove
all trace of those switches, per discussion. We'd still have to wait
a couple releases before it'd be safe to use -i for something else,
but it'd be a start.
http://git.postgresql.org/pg/commitdiff/232cd63b1f26e2ee3b3e03da8fc7348f4b067946

Peter Eisentraut pushed:

- doc: Fix PDF build with FOP. Because of a bug in the DocBook XSL FO
style sheet, an xref to a varlistentry whose term includes an
indexterm fails to build. One such instance was introduced in
commit 5086dfceba79ecd5d1eb28b8f4ed5221838ff3a6. Fix by adding the
upstream bug fix to our customization layer.
http://git.postgresql.org/pg/commitdiff/afae1f78547b8ff02cd2d07fe845a28e37a3b272

Robert Haas pushed:

- docs: Fix list of object types pg_table_is_visible() can handle.
Materialized views and foreign tables were missing from the list,
probably because they are newer than the other object types that
were mentioned. Etsuro Fujita
http://git.postgresql.org/pg/commitdiff/1c645da8ebb5532105481ad77bb1d9a671b1f086

- doc: Session identifiers truncate, not round, the backend start
time. Joel Jacobson
http://git.postgresql.org/pg/commitdiff/99cfd5e136e2a20c77021390a1378d18a24b7388

- Cope with possible failure of the oldest MultiXact to exist. Recent
commits, mainly b69bf30b9bfacafc733a9ba77c9587cf54d06c0c and
53bb309d2d5a9432d2602c93ed18e58bd2924e15, introduced mechanisms to
protect against wraparound of the MultiXact member space: the number
of multixacts that can exist at one time is limited to 2^32, but the
total number of members in those multixacts is also limited to 2^32,
and older code did not take care to enforce the second limit,
potentially allowing old data to be overwritten while it was still
needed. Unfortunately, these new mechanisms failed to account for
the fact that the code paths in which they run might be executed
during recovery or while the cluster was in an inconsistent state.
Also, they failed to account for the fact that users who used
pg_upgrade to upgrade a PostgreSQL version between 9.3.0 and 9.3.4
might have might oldestMultiXid = 1 in the control file despite the
true value being larger. To fix these problems, first, avoid
unnecessarily examining the mmembers of MultiXacts when the cluster
is not known to be consistent. TruncateMultiXact has done this for
a long time, and this patch does not fix that. But the new calls
used to prevent member wraparound are not needed until we reach
normal running, so avoid calling them earlier. (SetMultiXactIdLimit
is actually called before InRecovery is set, so we can't rely on
that; we invent our own multixact-specific flag instead.) Second,
make failure to look up the members of a MultiXact a non-fatal
error. Instead, if we're unable to determine the member offset at
which wraparound would occur, postpone arming the member wraparound
defenses until we are able to do so. If we're unable to determine
the member offset that should force autovacuum, force it
continuously until we are able to do so. If we're unable to
deterine the member offset at which we should truncate the members
SLRU, log a message and skip truncation. An important consequence
of these changes is that anyone who does have a bogus oldestMultiXid
= 1 value in pg_control will experience immediate emergency
autovacuuming when upgrading to a release that contains this fix.
The release notes should highlight this fact. If a user has no
pg_multixact/offsets/0000 file, but has oldestMultiXid = 1 in the
control file, they may wish to vacuum any tables with relminmxid = 1
prior to upgrading in order to avoid an immediate emergency
autovacuum after the upgrade. This must be done with a PostgreSQL
version 9.3.5 or newer and with vacuum_multixact_freeze_min_age and
vacuum_multixact_freeze_table_age set to 0. This patch also adds an
additional log message at each database server startup, indicating
either that protections against member wraparound have been engaged,
or that they have not. In the latter case, once autovacuum has
advanced oldestMultiXid to a sane value, the message indicating that
the guards have been engaged will appear at the next checkpoint. A
few additional messages have also been added at the DEBUG1 level so
that the correct operation of this code can be properly audited.
Along the way, this patch fixes another, related bug in
TruncateMultiXact that has existed since PostgreSQL 9.3.0: when no
MultiXacts exist at all, the truncation code looks up
NextMultiXactId, which doesn't exist yet. This can lead to
TruncateMultiXact removing every file in pg_multixact/offsets
instead of keeping one around, as it should. This in turn will
cause the database server to refuse to start afterwards. Patch by
me. Review by Álvaro Herrera, Andres Freund, Noah Misch, and Thomas
Munro.
http://git.postgresql.org/pg/commitdiff/068cfadf9e2190bdd50a30d19efc7c9f0b825b5e

== Rejected Patches (for now) ==

No one was disappointed this week :-)

== Pending Patches ==

Peter Geoghegan sent in a patch to illustrate a possible security bug
in RLS.

Dean Rasheed sent in a patch to refactor the implementation of RLS.

Laurenz Albe sent in a patch to allow using literal tabs in psql.

Etsuro Fujita sent in a doc patch to add materialized views and
foreign tables to database objects that pg_table_is_visible() can be
used with.

Michael Paquier sent in two more revisions of a patch to fix a memory
leak in XLogFileCopy.

Fabien COELHO sent in two revisions of a patch to enable flushing
while writing during checkpoints.

Andreas Seltenreich sent in a patch to add error handling to byteaout.

Michael Paquier sent in a patch to remove the use of %.*s in several
parts of the psql code to make the code more solid when facing
non-ASCII strings.

Craig Ringer sent in another revision of a patch to allow sampling of
only some queries by auto_explain.

Peter Geoghegan sent in a patch to desupport jsonb subscript deletion
on objects, which was causing surprising outcomes.

Jeevan Chalke sent in another revision of a patch to implement a
two-argument version of current_setting() with fallback.

Kaigai Kouhei sent in another revision of a patch to allow custom-join
children.

Amit Kapila and Andrew Dunstan traded patches to remove only symlinks
during recovery.

Julien Rouhaud sent in a patch fix an issue where when archiver
aborts, pg_stat_archiver doesn't report those failed attempts.

Petr Korobeinikov sent in another revision of a patch to implement \ev
and \sv (edit view, and show view, respectively) in psql.

Peter Geoghegan sent in a patch to add a regression test in cases
where RLS again fails to play nicely with UPDATE ... WHERE CURRENT OF.

Thomas Munro sent in a patch to fix a bogus subtrans wraparound error.