== PostgreSQL Weekly News - June 07 2015 == - Mailing list pgsql-announce

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

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