== PostgreSQL Weekly News - December 15 2013 ==

== PostgreSQL Weekly News - December 15 2013 ==

The CfP for PGCon 2014 is open through January 19, 2014.
http://www.pgcon.org/2014/

The first meeting of PUGS, the PostgreSQL Users' Group of Singapore,
will take place 19th December, 2013.
http://www.meetup.com/PUGS-Postgres-Users-Group-Singapore/events/154707822/

== PostgreSQL Product News ==

devart dotConnect 7.2 for PostgreSQL, an ADO.NET provider, released.
http://www.devart.com/dotconnect/postgresql/

Npgsql2 2.0.14, a .NET connector for PostgreSQL, released.
http://fxjr.blogspot.com/2013/12/npgsql-2014-released.html

== PostgreSQL Jobs for December ==

http://archives.postgresql.org/pgsql-jobs/2013-12/threads.php

== PostgreSQL Local ==

FOSDEM PGDay, a one day conference held before FOSDEM in Brussels,
Belgium, will be on Jan 31st, 2014. Details:
http://fosdem2014.pgconf.eu/

The 7th annual "Prague PostgreSQL Developers Day" (P2D2) conference
organized by CSPUG (Czech and Slovak PostgreSQL Users Group), will be
held on February 6, 2014 at Faculty of Mathematics and Physics,
Charles University (Malostranske namesti 25, Prague). Czech language
info below. The CfP will be open through 2014/01/03.
http://www.p2d2.cz/

== PostgreSQL in the News ==

Planet PostgreSQL: http://planet.postgresql.org/

PostgreSQL Weekly News is brought to you this week by David Fetter

Submit news and announcements by Sunday at 3:00pm Pacific time.
Please send English language ones to david(at)fetter(dot)org, German language
to pwn(at)pgug(dot)de, Italian language to pwn(at)itpug(dot)org(dot) Spanish language
to pwn(at)arpug(dot)com(dot)ar(dot)

== Applied Patches ==

Robert Haas pushed:

- Fixups for dsm.c's file descriptor handling. Per complaint from Tom
Lane.
http://git.postgresql.org/pg/commitdiff/d9250da032e723d80bb0150b9276cc544df6a087

- Add new wal_level, logical, sufficient for logical decoding. When
wal_level=logical, we'll log columns from the old tuple as
configured by the REPLICA IDENTITY facility added in commit
07cacba983ef79be4a84fcd0e0ca3b5fcb85dd65. This makes it possible a
properly-configured logical replication solution to correctly follow
table updates even if they change the chosen key columns, or, with
REPLICA IDENTITY FULL, even if the table has no key at all. Note
that updates which do not modify the replica identity column won't
log anything extra, making the choice of a good key (i.e. one that
will rarely be changed) important to performance when
wal_level=logical is configured. Each insert, update, or delete to
a catalog table will also log the CMIN and/or CMAX values of stamped
by the current transaction. This is necessary because logical
decoding will require access to historical snapshots of the catalog
in order to decode some data types, and the CMIN/CMAX values that we
may need in order to judge row visibility may have been overwritten
by the time we need them. Andres Freund, reviewed in various
versions by myself, Heikki Linnakangas, KONDO Mitsumasa, and many
others.
http://git.postgresql.org/pg/commitdiff/e55704d8b2fe522fbc9435acbb5bc59033478bd5

- Add a new reloption, user_catalog_table. When this reloption is set
and wal_level=logical is configured, we'll record the CIDs stamped
by inserts, updates, and deletes to the table just as we would for
an actual catalog table. This will allow logical decoding to use
historical MVCC snapshots to access such tables just as they access
ordinary catalog tables. Replication solutions built around the
logical decoding machinery will likely need to set this operation
for their configuration tables; it might also be needed by
extensions which perform table access in their output functions.
Andres Freund, reviewed by myself and others.
http://git.postgresql.org/pg/commitdiff/66abc2608c7c00fcd449e00a9e23f13f02e65d04

- Add a new option, -g, to createuser, to add membership in a role.
Chistopher Browne, reviewed by Sameer Thakur, Amit Kapila, and Peter
Eisentraut.
http://git.postgresql.org/pg/commitdiff/6bea96dd49619122bdb3fe9ddfd559a82b26ff73

- Under wal_level=logical, when saving old tuples, always save OID.
There's no real point in not doing this. It doesn't cost anything
in performance or space. So let's go wild. Andres Freund, with
substantial editing as to style by me.
http://git.postgresql.org/pg/commitdiff/60dd40bbda92cb1818d5a2651b9d2ba49afd804c

Bruce Momjian pushed:

- pgcrypto docs: update encryption timings and add relative times.
Miles Elam
http://git.postgresql.org/pg/commitdiff/d6464fdc0a591662e5e5ee1b0303932e89cb027c

- pgcrypto docs: update cpu type used in duration testing
http://git.postgresql.org/pg/commitdiff/01cc1fecfdecc37649b3996100ab1f03ccc8ab7e

Noah Misch pushed:

- Rename TABLE() to ROWS FROM(). SQL-standard TABLE() is a subset of
UNNEST(); they deal with arrays and other collection types. This
feature, however, deals with set-returning functions. Use a
different syntax for this feature to keep open the possibility of
implementing the standard TABLE().
http://git.postgresql.org/pg/commitdiff/53685d79813a46c7e3a46ae4b1210f608d41d6ab

Tom Lane pushed:

- Fix possible crash with nested SubLinks. An expression such as
WHERE (... x IN (SELECT ...) ...) IN (SELECT ...) could produce an
invalid plan that results in a crash at execution time, if the
planner attempts to flatten the outer IN into a semi-join. This
happens because convert_testexpr() was not expecting any nested
SubLinks and would wrongly replace any PARAM_SUBLINK Params
belonging to the inner SubLink. (I think the comment denying that
this case could happen was wrong when written; it's certainly been
wrong for quite a long time, since very early versions of the
semijoin flattening logic.) Per report from Teodor Sigaev.
Back-patch to all supported branches.
http://git.postgresql.org/pg/commitdiff/9ec6199d18d6235cc4b4d5e4e8986e73b55b14d8

- Tweak placement of explicit ANALYZE commands in the regression
tests. Make the COPY test, which loads most of the large static
tables used in the tests, also explicitly ANALYZE those tables.
This allows us to get rid of various ad-hoc, and rather redundant,
ANALYZE commands that had gotten stuck into various test scripts
over time to ensure we got consistent plan choices. (We could have
done a database-wide ANALYZE, but that would cause stats to get
attached to the small static tables too, which results in plan
changes compared to the historical behavior. I'm not sure that's a
good idea, so not going that far for now.) Back-patch to 9.0, since
9.0 and 9.1 are currently sometimes failing regression tests for
lack of an "ANALYZE tenk1" in the subselect test. There's no need
for this in 8.4 since we didn't print any plans back then.
http://git.postgresql.org/pg/commitdiff/b5e0a2a38401273d3d11b81f4424681db9845882

- Add a regression test case for plpython function returning setof
RECORD. We had coverage for functions returning setof a named
composite type, but not for anonymous records, which is a somewhat
different code path. In view of recent crash report from Sergey
Konoplev, this seems worth testing, though I doubt there's any
deterministic bug here today.
http://git.postgresql.org/pg/commitdiff/6bff0e7d92133d50c064d9f652f538891873aa32

- Remove bogus executable permissions on xlog.c. Apparently
fat-fingered in 1a3d104475ce01326fc00601ed66ac4d658e37e5. Noted by
Peter Geoghegan.
http://git.postgresql.org/pg/commitdiff/22310b808d45f67db4037a74aa47f44d716a4a83

- Improve EXPLAIN to print the grouping columns in Agg and Group
nodes. Per request from Kevin Grittner.
http://git.postgresql.org/pg/commitdiff/f26099057a2818d85edc2a16e2d3161f4bd96bdc

- Fix ancient docs/comments thinko: XID comparison is mod 2^32, not
2^31. Pointed out by Gianni Ciolli.
http://git.postgresql.org/pg/commitdiff/ccca6f56f5e027f28b033e5e2754e9ad18e8c9ae

- Don't let timeout interrupts happen unless ImmediateInterruptOK is
set. Serious oversight in commit
16e1b7a1b7f7ffd8a18713e83c8cd72c9ce48e07: we should not allow an
interrupt to take control away from mainline code except when
ImmediateInterruptOK is set. Just to be safe, let's adopt the same
save-clear-restore dance that's been used for many years in
HandleCatchupInterrupt and HandleNotifyInterrupt, so that nothing
bad happens if a timeout handler invokes code that tests or even
manipulates ImmediateInterruptOK. Per report of "stuck spinlock"
failures from Christophe Pettus, though many other symptoms are
possible. Diagnosis by Andres Freund.
http://git.postgresql.org/pg/commitdiff/e8312b4f03315a98a987997e05a6e9d6d8d84ddf

- Add HOLD/RESUME_INTERRUPTS in
HandleCatchupInterrupt/HandleNotifyInterrupt. This prevents a
possible longjmp out of the signal handler if a timeout or SIGINT
occurs while something within the handler has transiently set
ImmediateInterruptOK. For safety we must hold off the timeout or
cancel error until we're back in mainline, or at least till we reach
the end of the signal handler when ImmediateInterruptOK was true at
entry. This syncs these functions with the logic now present in
handle_sig_alarm. AFAICT there is no live bug here in 9.0 and up,
because I don't think we currently can wait for any heavyweight lock
inside these functions, and there is no other code (except
read-from-client) that will turn on ImmediateInterruptOK. However,
that was not true pre-9.0: in older branches ProcessIncomingNotify
might block trying to lock pg_listener, and then a SIGINT could lead
to undesirable control flow. It might be all right anyway given the
relatively narrow code ranges in which NOTIFY interrupts are
enabled, but for safety's sake I'm back-patching this.
http://git.postgresql.org/pg/commitdiff/2efc6dc256dc71ee876304b51dcad29fafbc37d3

- Fix inherited UPDATE/DELETE with UNION ALL subqueries. Fix an
oversight in commit b3aaf9081a1a95c245fd605dcf02c91b3a5c3a29: we do
indeed need to process the planner's append_rel_list when copying
RTE subqueries, because if any of them were flattenable UNION ALL
subqueries, the append_rel_list shows which subquery RTEs were
pulled up out of which other ones. Without this, UNION ALL
subqueries aren't correctly inserted into the update plans for
inheritance child tables after the first one, typically resulting in
no update happening for those child table(s). Per report from
Victor Yegorov. Experimentation with this case also exposed a fault
in commit a7b965382cf0cb30aeacb112572718045e6d4be7: if an inherited
UPDATE/DELETE was proven totally dummy by constraint exclusion, we
might arrive at add_rtes_to_flat_rtable with root->simple_rel_array
being NULL. This should be interpreted as not having any
RelOptInfos. I chose to code the guard as a check against
simple_rel_array_size, so as to also provide some protection against
indexing off the end of the array. Back-patch to 9.2 where the
faulty code was added.
http://git.postgresql.org/pg/commitdiff/c03ad5602f529787968fa3201b35c119bbc6d782

- Allow empty target list in SELECT. This fixes a problem noted as a
followup to bug #8648: if a query has a semantically-empty target
list, e.g. SELECT * FROM zero_column_table, ruleutils.c will dump it
as a syntactically-empty target list, which was not allowed. There
doesn't seem to be any reliable way to fix this by hacking ruleutils
(note in particular that the originally zero-column table might
since have had columns added to it); and even if we had such a fix,
it would do nothing for existing dump files that might contain bad
syntax. The best bet seems to be to relax the syntactic
restriction. Also, add parse-analysis errors for SELECT DISTINCT
with no columns (after *-expansion) and RETURNING with no columns.
These cases previously produced unexpected behavior because the
parsed Query looked like it had no DISTINCT or RETURNING clause,
respectively. If anyone ever offers a plausible use-case for this,
we could work a bit harder on making the situation distinguishable.
Arguably this is a bug fix that should be back-patched, but I'm
worried that there may be client apps or PLs that expect "SELECT ;"
to throw a syntax error. The issue doesn't seem important enough to
risk changing behavior in minor releases.
http://git.postgresql.org/pg/commitdiff/1b4f7f93b4693858cb983af3cd557f6097dab67b

Peter Eisentraut pushed:

- doc: Fix DocBook table column count declaration. This was broken in
d6464fdc0a591662e5e5ee1b0303932e89cb027c.
http://git.postgresql.org/pg/commitdiff/a06af43695cd51184dacc260aa394685991fe15f

- PL/Perl: Add event trigger support. From: Dimitri Fontaine
<dimitri(at)2ndQuadrant(dot)fr>
http://git.postgresql.org/pg/commitdiff/e5dc4cc24d2e1e94ac572a2c64103710bf15d21e

- configure: Allow adding a custom string to PG_VERSION. This can be
used to mark custom built binaries with an extra version string such
as a git describe identifier or distribution package release
version. From: Oskari Saarenmaa <os(at)ohmu(dot)fi>
http://git.postgresql.org/pg/commitdiff/46328916eefc5f9eaf249518e96f68afcd35923b

Kevin Grittner pushed:

- Add table name to VACUUM statement in matview.c. The test only
needs the one table to be vacuumed. Vacuuming the database may
affect other tests. Per gripe from Tom Lane. Back-patch to 9.3,
where the test was was added.
http://git.postgresql.org/pg/commitdiff/09df854b8acbba5c1d1dc5cdf30c8e84f59145e2

Simon Riggs pushed:

- Regression tests for ALTER TABLESPACE RENAME,OWNER. Hari Babu Kommi
reviewed by David Rowley
http://git.postgresql.org/pg/commitdiff/b921a26fb89cf52ec32b6c19a8528524883beeb5

- Regression tests for SCHEMA commands. Hari Babu Kommi reviewed by
David Rowley
http://git.postgresql.org/pg/commitdiff/cf589c9c1f5f7f89018684b763d74d7b02a2504b

- Allow time delayed standbys and recovery. Set
min_recovery_apply_delay to force a delay in recovery apply for
commit and restore point WAL records. Other records are replayed
immediately. Delay is measured between WAL record time and local
standby time. Robert Haas, Fabrízio de Royes Mello and Simon Riggs
Detailed review by Mitsumasa Kondo
http://git.postgresql.org/pg/commitdiff/36da3cfb457b77a55582f68208d815f11ac1399e

- New autovacuum_work_mem parameter. If autovacuum_work_mem is set,
autovacuum workers now use this parameter in preference to
maintenance_work_mem. Peter Geoghegan
http://git.postgresql.org/pg/commitdiff/8693559cacf1765697c32fc38574af3c19ce61c1

Heikki Linnakangas pushed:

- Display old and new values in pg_resetxlog -n output. For extra
clarity. Rajeev Rastogi, reviewed by Amit Kapila
http://git.postgresql.org/pg/commitdiff/108e3992cdae890c9b5b4778c3598fce35eea4ca

- Fix WAL-logging of setting the visibility map bit. The operation
that removes the remaining dead tuples from the page must be
WAL-logged before the setting of the VM bit. Otherwise, if you
replay the WAL to between those two records, you end up with the VM
bit set, but the dead tuples are still there. Backpatch to 9.3,
where this bug was introduced.
http://git.postgresql.org/pg/commitdiff/a49633d8dc6c66f6b2e92c8598d3dfa2cc72a259

- Add GUC to enable WAL-logging of hint bits, even with checksums
disabled. WAL records of hint bit updates is useful to tools that
want to examine which pages have been modified. In particular, this
is required to make the pg_rewind tool safe (without checksums).
This can also be used to test how much extra WAL-logging would occur
if you enabled checksums, without actually enabling them (which you
can't currently do without re-initdb'ing). Sawada Masahiko, docs by
Samrat Revagade. Reviewed by Dilip Kumar, with further changes by
me.
http://git.postgresql.org/pg/commitdiff/50e547096c4858a68abf09894667a542cc418315

- Fix more instances of "the the" in comments. Plus one instance of
"to to" in the docs.
http://git.postgresql.org/pg/commitdiff/dde6282500ac650cd0257fc24aeb5688962fddf9

Tatsuo Ishii pushed:

- Fix progress logging when scale factor is large. Integer overflow
showed minus percent and minus remaining time something like this.
239300000 of 3800000000 tuples (-48%) done (elapsed 226.86 s,
remaining -696.10 s).
http://git.postgresql.org/pg/commitdiff/841a65482d6e198b71a0df987b8b9822608d5ef4

- Add "SHIFT_JIS" as an accepted encoding name for locale checking.
When locale is "ja_JP.SJIS", nl_langinfo(CODESET) returns
"SHIFT_JIS" on some platforms, at least on RedHat Linux. So the
encoding/locale match table (encoding_match_list) needs the entry.
Otherwise client encoding is set to SQL_ASCII. Back patch to all
supported branches.
http://git.postgresql.org/pg/commitdiff/1f0626ee40f2decd93ca96c6f9e9a9b5edff57ba

Magnus Hagander pushed:

- Fix double "the" in the documentation. Erik Rijkers
http://git.postgresql.org/pg/commitdiff/56afe8509ec3057e06ebe244e51272511f05decb

Alvaro Herrera pushed:

- Rework MultiXactId cache code. The original performs too poorly; in
some scenarios it shows way too high while profiling. Try to make
it a bit smarter to avoid excessive cosst. In particular, make it
have a maximum size, and have entries be sorted in LRU order; once
the max size is reached, evict the oldest entry to avoid it from
growing too large. Per complaint from Andres Freund in connection
with new tuple freezing code.
http://git.postgresql.org/pg/commitdiff/d881dd6233f4eec6404f003bb08312e9e650e0e2

- Fix typo
http://git.postgresql.org/pg/commitdiff/60eea3780c40fe7ac0332d83173947199e59f5cd

== Rejected Patches (for now) ==

No one was disappointed this week :-)

== Pending Patches ==

MauMau sent in a patch to enable recovery to a backup point.

Peter Geoghegan sent in a patch to store pg_stat_statements query
texts externally and move pg_stat_statements into core.

Euler Taveira de Oliveira sent in a patch to implement a json output
plugin for logical decoding of the WAL stream.

Heikki Linnakangas and Alexander Korotkov traded patches adding
information to GIN indexes.

Heikki Linnakangas sent in a patch to use posix_fadvise in ANALYZE.

Antonin Houska sent in another revision of a patch to implement backup
throttling.

Peter Eisentraut sent in another revision of a patch to add
TRANSFORMS.

Fujii Masao sent in a patch to fix a bug in libpq's PQhost().

Mitsumasa KONDO sent in another revision of a patch to optimize kernel
readahead using buffer access strategy.

Haribabu Kommi sent in another revision of a patch to allow specifying
a separate xlog directory in pg_basebackup.

Andres Freund sent in a patch to implement replication node
identifiers including an SQL API.

Sergey Muraviov sent in another revision of a patch to improve the
look of psql in the case of wide output.

Kyotaro HORIGUCHI sent in a patch to fix an issue where recovery can
fail in 9.3+.

Haribabu Kommi sent in another revision of a patch to fix a race
condition between high-volume writing and VACUUM.

Tatsuo Ishii sent in two revisions of a patch to allow pgbench to take
large scale factors.

Andres Freund sent in a patch always to include oids in WAL-logged
replica identities.

Amit Kapila sent in another revision of a patch to reduce the WAL for
update operations.

KaiGai Kohei sent in another revision of a patch to implement custom
scans and use same to implement custom TID scans as a contrib module,
and add join pushdowns in the PostgreSQL FDW.

Rajeev Rastogi sent in another revision of a patch to ensure that COPY
... FROM STDIN shows a count tag.

Peter Geoghegan sent in another revision of a patch to implement
INSERT ... ON DUPLICATE KEY LOCK FOR UPDATE.

Pavel Stehule sent in two more revisions of a patch to create
make_timestamp functions.

Marko Kreen sent in another revision of a patch to have SSL choose
from an updated list of cypher suites.

MauMau sent in another revision of a patch to fix a bug in psql on
Windows where pg_ctl always uses the same event source.

David Rowley sent in a patch to quiet a compiler warning in relcache.

Etsuro Fujita sent in a patch to Show lossy heap block info in EXPLAIN
ANALYZE for bitmap heap scan.

Etsuro Fujita sent in a patch to fix a comment in
src/backend/commands/copy.c.

Pavel Stehule sent in another revision of a patch to enable an
--if-exists switch in pg_dump.

MauMau sent in a patch to fix an issue where multibyte messages are
displayed incorrectly on the client during startup.

Craig Ringer sent in another revision of a patch to implement
updateable security barrier views.

Greg Smith sent in another revision of a patch to implement row-based
access control on the read side.

Andres Freund sent in a patch to fix some issues pointed out to by
clang.

Alexander Korotkov sent in a PoC patch to implement partial sorting.

David Rowley sent in two revisions of a patch to allow aggregates with
negative (or reverse, or inverse) transition functions.