== PostgreSQL Weekly News - April 26 2015 ==

== PostgreSQL Weekly News - April 26 2015 ==

== PostgreSQL Product News ==

"Bottled Water", a system for getting data from PostgreSQL into Kafka,
released.
http://blog.confluent.io/2015/04/23/bottled-water-real-time-integration-of-postgresql-and-kafka/

PGDay Campinas 2015 will take place in Campinas on August 7.
The CfP is open through May 31.
http://pgdaycampinas.com.br/english/

pgFormatter v1.4, a SQL formatter/beautifier which supports keywords
from SQL-92, SQL-99, SQL-2003, SQL-2008, SQL-2011 and PostgreSQL
keywords are not in the standard, released.
http://sqlformat.darold.net/

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

PipelineDB, a streaming-SQL analytics database based on PostgreSQL, is
in beta.
http://www.pipelinedb.com/

== PostgreSQL Jobs for April ==

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

== PostgreSQL Local ==

The 4th Turkish PostgreSQL Conference will be held on May 9, 2015 in
Istanbul.
http://pgday.postgresql.org.tr/en/index.html

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/

Call for Papers for PGDay in Belfort, France ends April 13, 2015.
The conference 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

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

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

Peter Eisentraut pushed:

- Move pg_test_fsync from contrib/ to src/bin/. Reviewed-by: Michael
Paquier <michael(dot)paquier(at)gmail(dot)com>
http://git.postgresql.org/pg/commitdiff/00882d9e5ce7b693aa536346f68a826064c8683a

- Move pg_test_timing from contrib/ to src/bin/. Reviewed-by: Michael
Paquier <michael(dot)paquier(at)gmail(dot)com>
http://git.postgresql.org/pg/commitdiff/528c2e44ab0a5ba49a5a04689dae3f674b71c15e

- Move pg_xlogdump from contrib/ to src/bin/. Reviewed-by: Michael
Paquier <michael(dot)paquier(at)gmail(dot)com>
http://git.postgresql.org/pg/commitdiff/b0a738f428ca4e52695c0f019c1560c64cc59aef

- Fix shell error on Solaris. Apparently, the Bourne shell on Solaris
doesn't like "for" loops with an empty list, so have "make" skip the
loop in that case.
http://git.postgresql.org/pg/commitdiff/2aa0fb032ecdc5624a3706a2db0a7ca42bbaf211

- Improve speed of make check-world. Before, make check-world would
create a new temporary installation for each test suite, which is
slow and wasteful. Instead, we now create one test installation
that is used by all test suites that are part of a make run. The
management of the temporary installation is removed from pg_regress
and handled in the makefiles. This allows for better control, and
unifies the code with that of test suites not run through
pg_regress. Review and msvc support by Michael Paquier
<michael(dot)paquier(at)gmail(dot)com>. More review by Fabien Coelho
<coelho(at)cri(dot)ensmp(dot)fr>
http://git.postgresql.org/pg/commitdiff/dcae5faccab64776376d354decda0017c648bb53

- Fix misspellings. Amit Langote and Thom Brown
http://git.postgresql.org/pg/commitdiff/9ba978c8cc80d6f9e78b2ceef7f94834317b46aa

- doc: Move ALTER TABLE IF EXISTS description to better place. It was
previously mixed in with the description of ALTER TABLE subcommands.
Move it to the Parameters section, which is where it is on other
reference pages. pointed out by Amit Langote
http://git.postgresql.org/pg/commitdiff/d64a9c8c833c497a926208c7ca898e270ecc6990

- Replace backslashes by forward slashes in MSVC build code. This
makes it possible to run some stages of these build scripts on
non-Windows systems. That way, we can more easily test whether file
moves or makefile changes might break the MSVC build. Peter
Eisentraut and Michael Paquier
http://git.postgresql.org/pg/commitdiff/854adb83711da8fda2a8f028c27ad8956179c04a

- Don't overwrite EXTRA_INSTALL. The temp-install target sets
EXTRA_INSTALL to install the current directory. But when doing so,
it should append instead of overwrite, otherwise settings of
EXTRA_INSTALL from a makefile won't take effect. This would cause
the earthdistance test to fail when called directly, because it
would miss installing the cube module.
http://git.postgresql.org/pg/commitdiff/ee8d392765f29b1b8adb52f75008e301823f094b

- Add transforms feature. This provides a mechanism for specifying
conversions between SQL data types and procedural languages. As
examples, there are transforms for hstore and ltree for PL/Perl and
PL/Python. reviews by Pavel Stěhule and Andres Freund
http://git.postgresql.org/pg/commitdiff/cac76582053ef8ea07df65fed0757f352da23705

- Fix hstore_plperl regression tests on some platforms. On some
platforms, plperl and plperlu cannot be loaded at the same time. So
split the test into two separate test files.
http://git.postgresql.org/pg/commitdiff/f95425478aea3797f841e253cc9b216c50560c68

Bruce Momjian pushed:

- Honor OID status of CREATE LIKE'd tables. Previously, tables
created by CREATE LIKE never had OIDs. Report by Tom Lane
http://git.postgresql.org/pg/commitdiff/d992f8a8961c09ec219373ffe2b5e6473febd065

- Revert: Honor OID status of CREATE LIKE'd tables. Reverts
d992f8a8961c09ec219373ffe2b5e6473febd065 Report by Tom Lane
http://git.postgresql.org/pg/commitdiff/764ce22af30df3d62524d41815c41b4d696f3c10

Andres Freund pushed:

- Add 'active_in' column to pg_replication_slots. Right now it is
visible whether a replication slot is active in any session, but not
in which. Adding the active_in column, containing the pid of the
backend having acquired the slot, makes it much easier to associate
pg_replication_slots entries with the corresponding
pg_stat_replication/pg_stat_activity row. This should have been
done from the start, but I (Andres) dropped the ball there somehow.
Author: Craig Ringer, revised by me Discussion:
CAMsr+YFKgZca5_7_ouaMWxA5PneJC9LNViPzpDHusaPhU9pA7g(at)mail(dot)gmail(dot)com
http://git.postgresql.org/pg/commitdiff/d811c037cea2e929ee5823aab6e1bca0df8be245

- Rename pg_replication_slot's new active_in to active_pid. In
d811c037ce active_in was added but discussion since showed that
active_pid is preferred as a name. Discussion:
CAMsr+YFKgZca5_7_ouaMWxA5PneJC9LNViPzpDHusaPhU9pA7g(at)mail(dot)gmail(dot)com
http://git.postgresql.org/pg/commitdiff/cef939c3470006c1195a17c2ca3861360587fe15

- Fix possible division by zero in pg_xlogdump. When displaying stats
it was possible that a floating point division by zero occured when
no FPIs were issued for a type of record. Author: Abhijit Menon-Sen
Discussion: 20150417091811(dot)GA14008(at)toroid(dot)org
http://git.postgresql.org/pg/commitdiff/9fe1d9ac6820cff354a5402114b96dae1221eba3

- Fix various typos and grammar errors in comments. Author: Dmitriy
Olshevskiy Discussion: 553D00A6(dot)4090205(at)bk(dot)ru
http://git.postgresql.org/pg/commitdiff/6aab1f45acaa4cf90e62357ebdf5e6a38829204e

- Also correct therefor to therefore. Since both forms are arguably
legal I wasn't sure about changing this. But then Tom argued for
'therefore'... Author: Dmitriy Olshevskiy Discussion:
34789(dot)1430067832(at)sss(dot)pgh(dot)pa(dot)us
http://git.postgresql.org/pg/commitdiff/2e3ca04e2ee711cf8b87030b04f257316d990bde

Robert Haas pushed:

- Update FDW docs to reflect existence of CHECK constraints.
Generalize the remarks previously made about NOT NULL constraints to
CHECK constraints. Etsuro Fujita
http://git.postgresql.org/pg/commitdiff/0275ecf31cda9329ee822a6a806a2018144520ed

Heikki Linnakangas pushed:

- Don't leave 'tmp_check' directory behind in pg_rewind regression
tests.
http://git.postgresql.org/pg/commitdiff/4d930eee89954ef8ac5402f3d568c11ff6256035

- Add missing installcheck target to pg_rewind's Makefile. Michael
Paquier
http://git.postgresql.org/pg/commitdiff/060a1224af3f66c172cfb14e5ca9d0ffa575201a

- Make the pg_rewind regression tests more robust on slow systems.
There were a couple of hard-coded sleeps in the tests: to wait for
standby to catch up with master, and to wait for promotion with
"pg_ctl promote" to complete. Instead of a fixed, hard-coded sleep,
poll the server with a query once a second. This isn't ideal either,
and I wish we had a better solution for real-world applications too,
but this should fix the immediate problem. Patch by Michael
Paquier, with some editing by me.
http://git.postgresql.org/pg/commitdiff/54a16df0100da445be7c79eb81dfb96fd0685e6d

- Fix deadlock at startup, if max_prepared_transactions is too small.
When the startup process recovers transactions by scanning
pg_twophase directory, it should clear MyLockedGxact after it's done
processing each transaction. Like we do during normal operation, at
PREPARE TRANSACTION. Otherwise, if the startup process exits due to
an error, it will try to clear the locking_backend field of the last
recovered transaction. That's usually harmless, but if the error
happens in MarkAsPreparing, while holding TwoPhaseStateLock, the
shmem-exit hook will try to acquire TwoPhaseStateLock again, and
deadlock with itself. This fixes bug #13128 reported by Grant
McAlister. The bug was introduced by commit bb38fb0d, so backpatch
to all supported versions like that commit.
http://git.postgresql.org/pg/commitdiff/2c47fe16a709e01dea403643a1ff4d00c8c8c1ad

- Move functions related to index maintenance to separate source file.
There is enough code here to deserve a file of their own, not be
buried in the middle of execUtils.c.
http://git.postgresql.org/pg/commitdiff/62420ae7d6675453a48e408b62030b6f0780e878

- Add comments explaining how unique and exclusion constraints are
enforced.
http://git.postgresql.org/pg/commitdiff/61a553a09177c4f0a5570168e3b667eff87e354c

Stephen Frost pushed:

- Pull in tableoid for inheiritance with rowMarks. As noted by Etsuro
Fujita [1] and Dean Rasheed[2],
cb1ca4d800621dcae67ca6c799006de99fa4f0a5 changed
ExecBuildAuxRowMark() to always look for the tableoid in the target
list, but didn't also change preprocess_targetlist() to always
include the tableoid. This resulted in errors with soon-to-be-added
RLS with inheritance tests, and errors when using inheritance with
foreign tables. Authors: Etsuro Fujita and Dean Rasheed
(independently) Minor word-smithing on the comments by me. [1]
552CF0B6(dot)8010006(at)lab(dot)ntt(dot)co(dot)jp [2]
CAEZATCVmFUfUOwwhnBTcgi6AquyjQ0-1fyKd0T3xBWJvn+xsFA(at)mail(dot)gmail(dot)com
http://git.postgresql.org/pg/commitdiff/4ccc5bd28e7f0c0d1b221683398ae178515b9f76

- RLS fixes, new hooks, and new test module. In
prepend_row_security_policies(), defaultDeny was always true, so if
there were any hook policies, the RLS policies on the table would
just get discarded. Fixed to start off with defaultDeny as false
and then properly set later if we detect that only the default deny
policy exists for the internal policies. The infinite recursion
detection in fireRIRrules() didn't properly manage the activeRIRs
list in the case of WCOs, so it would incorrectly report infinite
recusion if the same relation with RLS appeared more than once in
the rtable, for example "UPDATE t ... FROM t ...". Further, the RLS
expansion code in fireRIRrules() was handling RLS in the main loop
through the rtable, which lead to RTEs being visited twice if they
contained sublink subqueries, which prepend_row_security_policies()
attempted to handle by exiting early if the RTE already had
securityQuals. That doesn't work, however, since if the query
involved a security barrier view on top of a table with RLS, the RTE
would already have securityQuals (from the view) by the time
fireRIRrules() was invoked, and so the table's RLS policies would be
ignored. This is fixed in fireRIRrules() by handling RLS in a
separate loop at the end, after dealing with any other sublink
subqueries, thus ensuring that each RTE is only visited once for RLS
expansion. The inheritance planner code didn't correctly handle
non-target relations with RLS, which would get turned into
subqueries during planning. Thus an update of the form "UPDATE t1
... FROM t2 ..." where t1 has inheritance and t2 has RLS quals would
fail. Fix by making sure to copy in and update the securityQuals
when they exist for non-target relations. process_policies() was
adding WCOs to non-target relations, which is unnecessary, and could
lead to a lot of wasted time in the rewriter and the planner. Fix by
only adding WCO policies when working on the result relation. Also
in process_policies, we should be copying the USING policies to the
WITH CHECK policies on a per-policy basis, fix by moving the copying
up into the per-policy loop. Lastly, as noted by Dean, we were
simply adding policies returned by the hook provided to the list of
quals being AND'd, meaning that they would actually restrict records
returned and there was no option to have internal policies and
hook-based policies work together permissively (as all internal
policies currently work). Instead, explicitly add support for both
permissive and restrictive policies by having a hook for each and
combining the results appropriately. To ensure this is all done
correctly, add a new test module (test_rls_hooks) to test the
various combinations of internal, permissive, and restrictive hook
policies. Largely from Dean Rasheed (thanks!):
CAEZATCVmFUfUOwwhnBTcgi6AquyjQ0-1fyKd0T3xBWJvn+xsFA(at)mail(dot)gmail(dot)com
Author: Dean Rasheed, though I added the new hooks and test module.
http://git.postgresql.org/pg/commitdiff/0bf22e0c8b1114ae37939c500535307abefd38e1

- Fix installcheck for test_rls_hooks. As pointed out by the
buildfarm, test_rls_hooks wasn't functioning properly with a clean
installcheck. test_rls_hooks needs to explicitly load the library
with the hooks in it, to allow installcheck to work; using the
--temp-config doesn't help since that isn't used when running
installcheck and it isn't exactly fair to the buildfarm to modify
the installed config prior to calling installcheck. Also, have
test_rls_hooks clean up after itself.
http://git.postgresql.org/pg/commitdiff/450fa1b5ba0e986a20d8c017500c0c0bbf1e0b4b

- Copy the relation name for error reporting in WCOs. In
get_row_security_policies(), we need to make a copy of the relation
name when building the WithCheckOptions structure, since
RelationGetRelationName just returns a pointer into the local
Relation structure. The relation name in the WCO structure is only
used for error reporting. Pointed out by Robert and Christian
Ullrich, who noted that the buildfarm members with
-DCLOBBER_CACHE_ALWAYS were failing.
http://git.postgresql.org/pg/commitdiff/cb087ec03bbb1d52845a4de83a6bf634dac2639f

- Perform RLS WITH CHECK before constraints, etc. The RLS capability
is built on top of the WITH CHECK OPTION system which was added for
auto-updatable views, however, unlike WCOs on views (which are
mandated by the SQL spec to not fire until after all other
constraints and checks are done), it makes much more sense for RLS
checks to happen earlier than constraint and uniqueness checks.
This patch reworks the structure which holds the WCOs a bit to be
explicitly either VIEW or RLS checks and the RLS-related checks are
done prior to the constraint and uniqueness checks. This also
allows better error reporting as we are now reporting when a
violation is due to a WITH CHECK OPTION and when it's due to an RLS
policy violation, which was independently noted by Craig Ringer as
being confusing. The documentation is also updated to include a
paragraph about when RLS WITH CHECK handling is performed, as there
have been a number of questions regarding that and the documentation
was previously silent on the matter. Author: Dean Rasheed, with
some kabitzing and comment changes by me.
http://git.postgresql.org/pg/commitdiff/e89bd02f58ac07e44e0388a32b7ee1b42f1fd7c6

- Fix file comment for test_rls_hooks.c. The file-level comment
wasn't updated when it was copied from the shared memory queue test
module. Fixed. Noted by Dean Rasheed.
http://git.postgresql.org/pg/commitdiff/410cbfd6dd778e8f388fd0d7ee9d84f833700da5

Álvaro Herrera pushed:

- Use the right type OID after creating a shell type. Commit
a2e35b53c39b2a neglected to update the type OID to use further down
in DefineType when TypeShellMake was changed to return ObjectAddress
instead of OID (it got it right in DefineRange, however.) This
resulted in an internal error message being issued when looking up
I/O functions. Author: Michael Paquier. Also add Asserts() to a
couple of other places to ensure that the type OID being used is as
expected.
http://git.postgresql.org/pg/commitdiff/50a16e30ebd76e70fc76abb2c8f0cd1e71deac41

Tom Lane pushed:

- Fix obsolete comment in set_rel_size(). The cross-reference to
set_append_rel_pathlist() was obsoleted by commit
e2fa76d80ba571d4de8992de6386536867250474, which split what had been
set_rel_pathlist() and child routines into two sets of functions.
But I (tgl) evidently missed updating this comment. Back-patch to
9.2 to avoid unnecessary divergence among branches. Amit Langote
http://git.postgresql.org/pg/commitdiff/70d44dd9de2b781436ef1d55906614d241e02249

- Fix up .gitignore and cleanup actions in some src/test/
subdirectories. examples/, locale/, and thread/ lacked .gitignore
files and were also not connected up to top-level "make clean" etc.
This had escaped notice because none of those directories are built
in normal scenarios. Still, they have working Makefiles, so if
someone does a "make" in one of these directories it would be good
if (a) git doesn't bleat about the product files and (b) cleaning up
removes them. This is a longstanding oversight, but since this
behavior is probably only of interest to developers, there seems no
need for back-patching. Michael Paquier and Tom Lane
http://git.postgresql.org/pg/commitdiff/732b33f8ae4ecc9d7a9f07fd4cb74a60a2a5e2c2

- Prevent improper reordering of antijoins vs. outer joins. An outer
join appearing within the RHS of an antijoin can't commute with the
antijoin, but somehow I missed teaching make_outerjoininfo() about
that. In Teodor Sigaev's recent trouble report, this manifests as a
"could not find RelOptInfo for given relids" error within
eqjoinsel(); but I think silently wrong query results are possible
too, if the planner misorders the joins and doesn't happen to
trigger any internal consistency checks. It's broken as far back as
we had antijoins, so back-patch to all supported branches.
http://git.postgresql.org/pg/commitdiff/3cf8686014f91174018f20e01dbb0dafdcad0473

- Add comments warning against generalizing default_with_oids.
pg_dump has historically assumed that default_with_oids affects only
plain tables and not other relkinds. Conceivably we could make it
apply to some newly invented relkind if we did so from the get-go,
but changing the behavior for existing object types will break
existing dump scripts. Add code comments warning about this
interaction. Also, make sure that default_with_oids doesn't cause
parse_utilcmd.c to think that CREATE FOREIGN TABLE will create an
OID column. I think this is only a latent bug right now, since we
don't allow UNIQUE/PKEY constraints in CREATE FOREIGN TABLE, but
it's better to be consistent and future-proof.
http://git.postgresql.org/pg/commitdiff/0bd11d9711b88e72d2022e25b9227c480aca4978

- Fix typo in linux startup script. Missed a "$" in what was meant to
be a variable substitution. Careless mistake in commit
f23425fa950fec3aff458de117037c9caadbc35c.
http://git.postgresql.org/pg/commitdiff/f320cbb615e0374b18836337713239da58705cf3

Noah Misch pushed:

- Remove obsolete -I options from ECPG library compilation. The MSVC
build system already omitted these.
http://git.postgresql.org/pg/commitdiff/c8aa893862275614d54a0657d1fb336020c98f60

- Remove superfluous -DFRONTEND. The majority practice is to add
-DFRONTEND in directories building files that are, at other times,
built for the backend. Some directories lacking that property added
a noise -DFRONTEND in one build system. Remove the excess flags,
for consistency.
http://git.postgresql.org/pg/commitdiff/bcd7e8897cd5345b38232efdbeb3ed12d8790ed5

- Build every ECPG library with -DFRONTEND. Each of the libraries
incorporates src/port files, which often check FRONTEND. Build
systems disagreed on whether to build libpgtypes this way. Only
libecpg incorporates files that rely on it today. Back-patch to 9.0
(all supported versions) to forestall surprises.
http://git.postgresql.org/pg/commitdiff/151e74719b0cc5c040bd3191b51b95f925773dd1

Andrew Dunstan pushed:

- Try to unbreak some MSVC builds following forward slash change.
Michael Paquier.
http://git.postgresql.org/pg/commitdiff/04f1542d390fdd95dadc66e86c623ecdc1bca401

== Rejected Patches (for now) ==

No one was disappointed this week :-)

== Pending Patches ==

Jeff Janes sent in a patch to help make vacuum truncation scans
faster.

Peter Geoghegan sent in another revision of a patch to implement
INSERT ... ON CONFLICT IGNORE (and UPDATE).

Michael Paquier sent in another revision of a patch to ensure that
logical decoding follows timelines.

Heikki Linnakangas sent in another revision of a patch to implement
access menthods for sequences.

Michael Paquier sent in two more revisions of a patch to support TAP
tests with MSVC and Windows.

SAWADA Masahiko sent in another revision of a patch to help avoid
freezing very large tables.

Etsuro Fujita sent in another revision of a patch to allow foreign
tables to be created with OIDs.

Michael Paquier sent in another revision of a patch to ensure that
Install.bat works when the target directory contains a space.

Michael Paquier sent in another revision of a patch to add
pg_settings.pending_restart.

Kaigai Kouhei sent in another revision of a patch to add a custom join
API.

Jan de Visser sent in another revision of a patch to let pg_ctl check
the result of a postmaster config reload.

Petr Jelinek sent in a patch to add catalog regression tests for the
TABLESAMPLE feature.

Michael Paquier sent in a patch to fix a comment in
src/backend/access/transam/xlog.c.

Michael Paquier sent in a patch to allow releasing LWLocks on failure
in cases where that makes sense.

Amit Kapila sent in another revision of a patch to implement parallel
sequential scan.

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

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

David Steele sent in another revision of a patch to add pg_audit.

Kyotaro HORIGUCHI sent in two revisions of a patch to fix an issue
where coerce_type() fails for Var of type UNKNOWNOID.

Andres Freund sent in another revision of a patch to implement
replication identifiers.

Michael Paquier sent in a patch to fix broken collate.linux.utf8 test
coverage.

Pavel Stehule sent in two more revisions of a patch to tune the
displayability of context messages in PL/pgSQL errors.

Fabien COELHO sent in another revision of a patch to extend pgbench
expressions with functions.

Fabien COELHO sent in another revision of a patch to fix pgbench
--progress report under (very) low rate.

Euler Taveira de Oliveira sent in a patch to fix some issues in the
parallel mode and parallel contexts.