== PostgreSQL Weekly News - August 31 2014 ==

== PostgreSQL Weekly News - August 31 2014 ==

== PostgreSQL Product News ==

psycopg2 2.5.4, a Python connector for PostgreSQL, released.
http://initd.org/psycopg/articles/2014/08/30/psycopg-254-released/

== PostgreSQL Jobs for August ==

http://archives.postgresql.org/pgsql-jobs/2014-08/threads.php

== PostgreSQL Local ==

PgDay Portland, Oregon 2014 will be held Saturday September 6, 2014.
https://wiki.postgresql.org/wiki/PDXPUGDay2014

Postgres Open 2014 will be in Chicago, IL, USA, September 17-19.
Tickets and Tutorials now available for purchase.
https://postgresopen.org/2014/tickets/
http://postgresopen.org/2014/callforpapers/

The 4th PgDay Ecuador will be held on Tuesday 7th in October at the
city of Quito, as part of the 5th International Congress of Free
Software. Send talk proposals to ecpug AT postgresql DOT org.

The sixth PGDay Cubano be held on 13 and 14 October 2014 in Habana.
https://postgresql.uci.cu/?p=380

PGConf.EU 2014 in Madrid, Spain on October 21-24 is now open for
registration.
http://2014.pgconf.eu/registration/

PGDay.IT 2014 will take place in Prato on November the 7th 2014. The
International Call For Papers is now open:
http://2014.pgday.it/call-for-papers-en/

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

Heikki Linnakangas pushed:

- Don't track DEALLOCATE in pg_stat_statements. We also don't track
PREPARE, nor do we track planning time in general, so let's ignore
DEALLOCATE as well for consistency. Backpatch to 9.4, but not
further than that. Although it seems unlikely that anyone is relying
on the current behavior, this is a behavioral change. Fabien Coelho
http://git.postgresql.org/pg/commitdiff/3cd934f635699b7c8b0027be69fddfc36be6f9b8

- Show schema names in pg_dump verbose output. Fabrízio de Royes
Mello, reviewed by Michael Paquier
http://git.postgresql.org/pg/commitdiff/2bde29739d1e28f58e901b7e53057b8ddc0ec286

- Implement IF NOT EXISTS for CREATE SEQUENCE. Fabrízio de Royes
Mello
http://git.postgresql.org/pg/commitdiff/0076f264b6f24854219d49b159602e2042c38ee7

- Add regression tests for SELECT FOR UPDATE/SHARE NOWAIT. Thomas
Munro
http://git.postgresql.org/pg/commitdiff/9ee16b49f0aac819bd4823d9b94485ef608b34e8

- Fix bug in compressed GIN data leaf page splitting code. The list
of posting lists it's dealing with can contain placeholders for
deleted posting lists. The placeholders are kept around so that they
can be WAL-logged, but we must be careful to not try to access them.
This fixes bug #11280, reported by Mårten Svantesson. Backpatch to
9.4, where the compressed data leaf page code was added.
http://git.postgresql.org/pg/commitdiff/88231ec5784a00ce406553276be8a5a443dd3ba0

Andres Freund pushed:

- Fix typos in some error messages thrown by extension scripts when
fed to psql. Some of the many error messages introduced in 458857cc
missed 'FROM unpackaged'. Also e016b724 and 45ffeb7e forgot to quote
extension version numbers. Backpatch to 9.1, just like 458857cc
which introduced the messages. Do so because the error messages
thrown when the wrong command is copy & pasted aren't easy to
understand.
http://git.postgresql.org/pg/commitdiff/d153b801611f6fdc58c7941e85dddb0cb25ca4ef

- Specify the port in dblink and postgres_fdw tests. That allows to
run those tests against a postmaster listening on a nonstandard port
without requiring to export PGPORT in postmaster's environment.
This still doesn't support connecting to a nondefault host without
configuring it in postmaster's environment. That's harder and less
frequently used though. So this is a useful step.
http://git.postgresql.org/pg/commitdiff/57ca1d4f0175f60e9cfb0f997c864173d9c72607

- Don't hardcode contrib_regression dbname in postgres_fdw and dblink
tests. That allows parallel installcheck to succeed inside
contrib/. The output is not particularly pretty unless make's -O
option to synchronize the output is used. There's other tests,
outside contrib, that use a hardcoded, non-unique, database name.
Those prohibit paralell installcheck to be used across more
directories; but that's something for a separate patch.
http://git.postgresql.org/pg/commitdiff/ddc2504dbcf2af97b58a91b071768c17774d58d1

- Mark IsBinaryUpgrade as PGDLLIMPORT to fix windows builds after
a7ae1dc. Author: David Rowley
http://git.postgresql.org/pg/commitdiff/5569d75d6a82f0d11d26404fceb848ae59869126

- Allow escaping of option values for options passed at connection
start. This is useful to allow to set GUCs to values that include
spaces; something that wasn't previously possible. The primary case
motivating this is the desire to set default_transaction_isolation
to 'repeatable read' on a per connection basis, but other usecases
like seach_path do also exist. This introduces a slight backward
incompatibility: Previously a \ in an option value would have been
passed on literally, now it'll be taken as an escape. The relevant
mailing list discussion starts with
20140204125823(dot)GJ12016(at)awork2(dot)anarazel(dot)de(dot)
http://git.postgresql.org/pg/commitdiff/11a020eb6e4023a1570a2788ba22dd6aafbd02dc

- Make backend local tracking of buffer pins memory efficient. Since
the dawn of time (aka Postgres95) multiple pins of the same buffer
by one backend have been optimized not to modify the shared refcount
more than once. This optimization has always used a NBuffer sized
array in each backend keeping track of a backend's pins. That array
(PrivateRefCount) was one of the biggest per-backend memory
allocations, depending on the shared_buffers setting. Besides the
waste of memory it also has proven to be a performance bottleneck
when assertions are enabled as we make sure that there's no
remaining pins left at the end of transactions. Also, on servers
with lots of memory and a correspondingly high shared_buffers
setting the amount of random memory accesses can also lead to poor
cpu cache efficiency. Because of these reasons a backend's buffers
pins are now kept track of in a small statically sized array that
overflows into a hash table when necessary. Benchmarks have shown
neutral to positive performance results with considerably lower
memory usage. Patch by me, review by Robert Haas. Discussion:
20140321182231(dot)GA17111(at)alap3(dot)anarazel(dot)de
http://git.postgresql.org/pg/commitdiff/4b4b680c3d6d8485155d4d4bf0a92d3a874b7a65

- Declare two variables in snapbuild.c as static. Neither is accessed
externally, I just seem to have missed the static when writing the
code.
http://git.postgresql.org/pg/commitdiff/8fff977e29cda7c1641c43f9b293134adc77b65a

- Protect definition of SpinlockSemaArray, just like its declaration.
Found via clang's -Wmissing-variable-declarations.
http://git.postgresql.org/pg/commitdiff/5c1faa7ba729c780b0ac598dbc23dbb1d6e1b872

- Declare lwlock.c's LWLockAcquireCommon() as a static inline.
68a2e52bbaf98f136 has introduced LWLockAcquireCommon() containing
the previous contents of LWLockAcquire() plus added functionality.
The latter then calls it, just like LWLockAcquireWithVar(). Because
the majority of callers don't need the added functionality, declare
the common code as inline. The compiler then can optimize away the
unused code. Doing so is also useful when looking at profiles, to
differentiate the users. Backpatch to 9.4, the first branch to
contain LWLockAcquireCommon().
http://git.postgresql.org/pg/commitdiff/9c4b55db1dc901dc184b1b3ab9cca3f2089ec311

Alvaro Herrera pushed:

- Editorial review of SET UNLOGGED. Add a succint comment explaining
why it's correct to change the persistence in this way. Also
s/loggedness/persistence/ because native speakers didn't like the
latter term. Fabrízio and Álvaro
http://git.postgresql.org/pg/commitdiff/7636c0c821f669e134a0f2fd1c6225bb3ec820d4

- Revert XactLockTableWait context setup in conditional multixact
wait. There's no point in setting up a context error callback when
doing conditional lock acquisition, because we never actually wait
and so the user wouldn't be able to see the context message
anywhere. In fact, this is more in line with what
ConditionalXactLockTableWait is doing. Backpatch to 9.4, where this
was added.
http://git.postgresql.org/pg/commitdiff/3adba736620c85112e0f9f6d043d3574e1c5830f

- More psprintf goodness
http://git.postgresql.org/pg/commitdiff/d6d6020f1cf33a63bbae59d370b75fa5e98017f0

- Oops, forgot to "git add" one last change
http://git.postgresql.org/pg/commitdiff/ac41769fd9dca9f4a6ee5fa859a56c08e8bb95fe

- Have CREATE TABLE AS and REFRESH return an OID Other DDL commands
are already returning the OID, which is required for future
additional event trigger work. This is merely making these commands
in line with the rest of utility command support.
http://git.postgresql.org/pg/commitdiff/301fcf33eb7fcbcf1da113dabeed90b8eaf656eb

- Use newly added InvalidCommandId instead of 0. The symbol was added
by 71901ab6d; the original code was introduced by 6868ed749.
Development of both overlapped which is why we apparently failed to
notice. This is a (very slight) behavior change, so I'm not
backpatching this to 9.4 for now, even though the symbol does exist
there.
http://git.postgresql.org/pg/commitdiff/6f822952eea112d3e19a0ddaad32e99d1943f29d

- DefineType: return base type OID, not its array. Event triggers
want to know the OID of the interesting object created, which is the
main type. The array created as part of the operation is just a
subsidiary object which is not of much interest.
http://git.postgresql.org/pg/commitdiff/832a12f65e91d4295be73e26079aa8508de0cf32

- Fix FOR UPDATE NOWAIT on updated tuple chains. If SELECT FOR UPDATE
NOWAIT tries to lock a tuple that is concurrently being updated, it
might fail to honor its NOWAIT specification and block instead of
raising an error. Fix by adding a no-wait flag to EvalPlanQualFetch
which it can pass down to heap_lock_tuple; also use it in
EvalPlanQualFetch itself to avoid blocking while waiting for a
concurrent transaction. Authors: Craig Ringer and Thomas Munro,
tweaked by Alvaro Herrera
http://www.postgresql.org/message-id/51FB6703.9090801@2ndquadrant.com
Per Thomas Munro in the course of his SKIP LOCKED feature
submission, who also provided one of the isolation test specs.
Backpatch to 9.4, because that's as far back as it applies without
conflicts (although the bug goes all the way back). To that branch
also backpatch Thomas Munro's new NOWAIT test cases, committed in
master by Heikki as commit 9ee16b49f0aac819bd4823d9b94485ef608b34e8
.
http://git.postgresql.org/pg/commitdiff/1c9701cfe58267cf5d79543a42ee4f0967cc73ab

Bruce Momjian pushed:

- upgrade docs: highlight pg_upgrade, warn about globals
preservation. Also, remove OID preservation mention, mention
non-text dump formats Backpatch through 9.4
http://git.postgresql.org/pg/commitdiff/bf1c866597a0e59a30b13a66f3ae505655b61853

- pg_ctl, pg_upgrade: allow multiple -o/-O options, append them.
Report by Pavel Raiskup
http://git.postgresql.org/pg/commitdiff/ebe30ad59bb2d127f576be07e036a473999c4b80

- pg_upgrade: add SET log_min_error_statement = warning. Add to
commit 73d78e11a0f7183c80b93eefbbb6026fe9664015
http://git.postgresql.org/pg/commitdiff/b0488e5c4fbfdce8acc989bdc17d9f0ec09ac281

- pg_upgrade: remove support for 8.3 old clusters. This trims down
the code, and is in preparation for hardening pg_upgrade against
auto-oid assignment.
http://git.postgresql.org/pg/commitdiff/2209b3923a7afe0b6033ecfea972219df252ca8e

- revert "Throw error for ALTER TABLE RESET of an invalid option".
Reverts commits 73d78e11a0f7183c80b93eefbbb6026fe9664015 and
b0488e5c4fbfdce8acc989bdc17d9f0ec09ac281. Also reverts pg_upgrade
changes.
http://git.postgresql.org/pg/commitdiff/6cb74a67e26523eb2408f441bfc589c80f76c465

- Throw error for ALTER TABLE RESET of an invalid option. Also adjust
pg_upgrade to not use this method for optional TOAST table creation.
Patch by Fabrízio de Royes Mello
http://git.postgresql.org/pg/commitdiff/73d78e11a0f7183c80b93eefbbb6026fe9664015

- pg_upgrade docs: update docs for 8.3 support removal
http://git.postgresql.org/pg/commitdiff/e8c81b1b8e51475dd5a787660e6754c4bafbff47

- pg_upgrade: use CTE query rather than temp table. Now that 8.3 is
not supported, we can use a CTE and not temp tables. This allows
for auto-oid assignment protection in a future patch.
http://git.postgresql.org/pg/commitdiff/5d16332e96977d8166ea631891f6e2cf3ff11f45

- rename macro isTempOrToastNamespace to isTempOrTempToastNamespace.
Done for clarity
http://git.postgresql.org/pg/commitdiff/73fe87503f23144a27f0bdecc55587deb5aa425f

- pg_upgrade: prevent automatic oid assignment. Prevent automatic
oid assignment when in binary upgrade mode. Also throw an error
when contrib/pg_upgrade_support functions are called when not in
binary upgrade mode. This prevent automatically-assigned oids from
conflicting with later pre-assigned oids coming from the old
cluster. It also makes sure oids are preserved in call important
cases.
http://git.postgresql.org/pg/commitdiff/a7ae1dcf4939cf643c5abe8dd673e4b87a6efd42

- pg_is_xlog_replay_paused(): remove super-user-only restriction.
Also update docs to mention which function are super-user-only.
Report by sys-milan(at)statpro(dot)com Backpatch through 9.4
http://git.postgresql.org/pg/commitdiff/01363beae52700c7425cb2d2452177133dad3e93

- Update C comment for pg_attribute.attislocal. Indicates if column
has ever been local/non-inherited
http://git.postgresql.org/pg/commitdiff/c6eaa880eea67a711b30903a9ee2e996bccb775e

- Again update C comments for pg_attribute.attislocal
http://git.postgresql.org/pg/commitdiff/d5d7d07765443175a49441bad7a799dedc072511

Kevin Grittner pushed:

- Fix superuser concurrent refresh of matview owned by another. Use
SECURITY_LOCAL_USERID_CHANGE while building temporary tables; only
escalate to SECURITY_RESTRICTED_OPERATION while potentially running
user-supplied code. The more secure mode was preventing temp table
creation. Add regression tests to cover this problem. This fixes
Bug #11208 reported by Bruno Emanuel de Andrade Silva. Backpatch to
9.4, where the bug was introduced.
http://git.postgresql.org/pg/commitdiff/a9d0f1cff3fb10151be05be61d24ac9b680c170c

- doc: Various typo/grammar fixes. Errors detected using Topy
(https://github.com/intgr/topy), all changes verified by hand and
some manual tweaks added. Marti Raudsepp Individual changes
backpatched, where applicable, as far as 9.0.
http://git.postgresql.org/pg/commitdiff/05258761bf12a64befc9caec1947b254cdeb74c5

Robert Haas pushed:

- Fix typo in b34e37bfefbed1bf9396dde18f308d8b96fd176c. Spotted by
Peter Geoghegan.
http://git.postgresql.org/pg/commitdiff/9522ec3e707a93d25a99e477af76d7b186fe20de

Peter Eisentraut pushed:

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

- doc: Document valid checkpoint_timeout range. Author: Fabien COELHO
<coelho(at)cri(dot)ensmp(dot)fr>
http://git.postgresql.org/pg/commitdiff/8e484bc4abccc03d49af25d32a5ffaecd3c6cf2d

- Assorted message improvements
http://git.postgresql.org/pg/commitdiff/65c9dc231a261691c76550f61f5b22f954dfcfd5

- doc: Revert ALTER TABLESPACE summary line. It was changed when
ALTER TABLESPACE / MOVE was added but then not updated when that was
moved back out.
http://git.postgresql.org/pg/commitdiff/14b7c8f2d062d50f2ad5d306c5cc26b7ec5cf11e

Stephen Frost pushed:

- Fix Var handling for security barrier views. In some cases, not all
Vars were being correctly marked as having been modified for
updatable security barrier views, which resulted in invalid plans
(eg: when security barrier views were created over top of
inheiritance structures). In passing, be sure to update both
varattno and varonattno, as _equalVar won't consider the Vars
identical otherwise. This isn't known to cause any issues with
updatable security barrier views, but was noticed as missing while
working on RLS and makes sense to get fixed. Back-patch to 9.4
where updatable security barrier views were introduced.
http://git.postgresql.org/pg/commitdiff/e414ba93add966dfe03e22994764e718ed89ee98

Fujii Masao pushed:

- Add header comments to receivelog.h and streamutil.h. This commit
also adds the include guards to those header files. Michael Paquier
http://git.postgresql.org/pg/commitdiff/9a2d94892f6a793d52ebabb8283b98a6e3ede3d6

- Revert "Allow units to be specified in relation option setting
value." This reverts commit
e23014f3d40f7d2c23bc97207fd28efbe5ba102b. As the side effect of the
reverted commit, when the unit is specified, the reloption was
stored in the catalog with the unit. This broke pg_dump
(specifically, it prevented pg_dump from outputting restorable
backup regarding the reloption) and turned the buildfarm red. Revert
the commit until the fixed version is ready.
http://git.postgresql.org/pg/commitdiff/9df492664a74a948c87232f523eea637920a516b

- Allow units to be specified in relation option setting value. This
introduces an infrastructure which allows us to specify the units
like ms (milliseconds) in integer relation option, like GUC
parameter. Currently only autovacuum_vacuum_cost_delay reloption
can accept the units. Reviewed by Michael Paquier
http://git.postgresql.org/pg/commitdiff/e23014f3d40f7d2c23bc97207fd28efbe5ba102b

Jeff Davis pushed:

- Allow multibyte characters as escape in SIMILAR TO and SUBSTRING.
Previously, only a single-byte character was allowed as an escape.
This patch allows it to be a multi-byte character, though it still
must be a single character. Reviewed by Heikki Linnakangas and Tom
Lane.
http://git.postgresql.org/pg/commitdiff/8167a3883a3c49f2f77785f8e5f638920c9f14ef

Tom Lane pushed:

- Fix citext upgrade script for disallowance of oidvector element
assignment. In commit 45e02e3232ac7cc5ffe36f7986159b5e0b1f6fdc, we
intentionally disallowed updates on individual elements of oidvector
columns. While that still seems like a sane idea in the abstract,
we (I) forgot that citext's "upgrade from unpackaged" script did in
fact perform exactly such updates, in order to fix the problem that
citext indexes should have a collation but would not in databases
dumped or upgraded from pre-9.1 installations. Even if we wanted to
add casts to allow such updates, there's no practical way to do so
in the back branches, so the only real alternative is to make
citext's kluge even klugier. In this patch, I cast the oidvector to
text, fix its contents with regexp_replace, and cast back to
oidvector. (Ugh!) Since the aforementioned commit went into all
active branches, we have to fix this in all branches that contain
the now-broken update script. Per report from Eric Malm.
http://git.postgresql.org/pg/commitdiff/7f7eec89b66947e4098773cf286653b9c4f01c88

- Add min and max aggregates for inet/cidr data types. Haribabu
Kommi, reviewed by Muhammad Asif Naeem
http://git.postgresql.org/pg/commitdiff/6c40f8316ed38a92049784b3e3d3b514ed379b5a

Noah Misch pushed:

- Always use our getaddrinfo.c on Windows. Commit
a16bac36eca8158cbf78987e95376f610095f792 let "configure" detect the
system getaddrinfo() when building under 64-bit MinGW-w64. However,
src/include/port/win32/sys/socket.h assumes all native Windows
configurations use our replacement. This change placates buildfarm
member jacana until we establish a plan for getaddrinfo() on
Windows.
http://git.postgresql.org/pg/commitdiff/ec544a65c9090bc9da11ea384d1369fd552ca8b0

== Rejected Patches (for now) ==

No one was disappointed this week :-)

== Pending Patches ==

SAWADA Masahiko sent in a WIP patch to add "-C (--concurrently)"
option for reindexdb command for concurrently reindexing.

Andrew (RhodiumToad) Gierth sent in three more revisions of a patch to
add GROUPING SETS and make necessary associated changes to the contrib
code.

Andres Freund sent in a patch to add .NOTPARALLEL to contrib/Makefile.

Fujii Masao sent in another revision of a patch to specify the unit
in storage parameters.

Thomas Munro sent in four more revisions of a patch to add SKIP LOCKED
DATA.

Petr (PJMODOS) Jelinek and Tom Lane traded patches to add built-in
binning functions.

Michael Paquier sent in another revision of a patch to ensure that
pg_dump shows the schema name.

Alvaro Herrera sent in another revision of a patch to replicate DROP
commands across servers.

Michael Paquier sent in another revision of a patch to implement
REINDEX CONCURRENTLY.

Michael Paquier sent in two more revisions of a patch to add a missing
comment block at the top of streamutil.h and receivelog.h.

David Rowley sent in another revision of a patch to support SEMI and
ANTI join removal.

Tom Lane and Arthur Silva traded patches to fix a situation where
JSONB's structure plays poorly with TOAST compression.

Fujii Masao sent in two more revisions of a patch to add a GUC for
logging replication commands.

Andres Freund sent in two more revisions of a patch to make backend
local tracking of buffer pins memory efficient.

Alvaro Herrera sent in a patch to introduce a "delay equivalent" to
sort out some oddness in autovacuum cost calculations.

Kevin Grittner sent in two more revisions of a patch to implement
delta relations, among other places, in AFTER triggers.

Fabien COELHO sent in six more revisions of a patch to implement a
pgbench throttling latency limit.

Andres Freund and SAWADA Masahiko traded patches to add line number as
an optional prompt in psql.

Joachim Wieland sent in another revision of a patch to refactor
pg_dump to remove global variables.

Michael Paquier sent in another revision of a patch to add support for
physical slot creation/deletion in pg_receivexlog.

Emre Hasegeli sent in three more revisions of a patch to add
selectivity estimation for inet operators.

Michael Paquier sent in a patch to simplify calls of pg_class_aclcheck
when multiple modes are used.

Fujii Masao and Amit Kapila traded patches to allow auditing logout.

Pavel Stehule and Andres Freund traded patches to give access to
help_variables and usage from psql via psql command.

Peter Geoghegan sent in a patch to extend the INSERT statement, adding
a new ON CONFLICT {UPDATE | IGNORE} clause.

Julien Rouhaud sent in another revision of a patch to track the number
of files ready to be archived in pg_stat_archiver.

Kaigai Kouhei sent in another revision of a patch to implement a
custom plan API.

Heikki Linnakangas and Andres Freund traded patches to make the
bgwriter do a better job of controlling latency.

Michael Paquier sent in another revision of a patch to enable support
for N synchronous standby servers, N>1.

Etsuro Fujita sent in another revision of a patch to implement INHERIT
support for foreign tables.

Amit Kapila sent in another revision of a patch to permit scaling
shared buffer eviction.

Kyotaro HORIGUCHI sent in another revision of a patch to fix some
brokein code around socket emulation on win32.

Alvaro Herrera sent in another revision of a patch to create a better
REINDEX syntax.

Etsuro Fujita sent in another revision of a patch to optimize updating
foreign tables in the PostgreSQL FDW.

Alexey Klyukin sent in another revision of a patch to implement
subject alternative names support for SSL connections.

Fujii Masao sent in a patch to fix an infelicity in psql's \watch vs.
\timing.

Craig Ringer sent in a patch to allow distdir to be overridden on the
"make" command line.

Ali Akbar sent in a patch to fix xpath() to return namespace
definitions.

Amit Kapila sent in another revision of a patch to implement ALTER
SYSTEM RESET.

Michael Paquier sent in two more revisions of a patch to fix a
misleading error message in logical decoding for binary plugins.

Adam Brightwell sent in another revision of a patch to provide the
capability to create multiple named row level security policies for a
table on a per command basis and assign them to be applied to specific
roles/users.

Ali Akbar sent in another revision of a patch to accept empty values
as xml "content" values.

Peter Eisentraut sent in another revision of a patch to improve the
speed of "make check-world".