== PostgreSQL Weekly News - December 22 2013 ==

== PostgreSQL Weekly News - December 22 2013 ==

The CfP for PGConf NYC 2014 is open through January 10, 2014.
Notifications will go out on January 15, 2014.
http://nyc.pgconf.us/2014/

== PostgreSQL Product News ==

psqlODBC 09.03.0100 released.
http://www.postgresql.org/ftp/odbc/versions/

== 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/
http://fosdem2014.pgconf.eu/registration/

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/

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

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

- Mark variables 'static' where possible. Move GinFuzzySearchLimit to
ginget.c. Per "clang -Wmissing-variable-declarations" output,
posted by Andres Freund. I didn't silence all those warnings,
though, only the most obvious cases.
http://git.postgresql.org/pg/commitdiff/30b96549ab41ce23399256d4ea9723a05c139558

Alvaro Herrera pushed:

- Rework tuple freezing protocol. Tuple freezing was broken in
connection to MultiXactIds; commit 8e53ae025de9 tried to fix it, but
didn't go far enough. As noted by Noah Misch, freezing a tuple
whose Xmax is a multi containing an aborted update might cause locks
in the multi to go ignored by later transactions. This is because
the code depended on a multixact above their cutoff point not having
any lock-only member older than the cutoff point for Xids, which is
easily defeated in READ COMMITTED transactions. The fix for this
involves creating a new MultiXactId when necessary. But this cannot
be done during WAL replay, and moreover multixact examination
requires using CLOG access routines which are not supposed to be
used during WAL replay either; so tuple freezing cannot be done with
the old freeze WAL record. Therefore, separate the freezing
computation from its execution, and change the WAL record to carry
all necessary information. At WAL replay time, it's easy to
re-execute freezing because we don't need to re-compute the new
infomask/Xmax values but just take them from the WAL record. While
at it, restructure the coding to ensure all page changes occur in a
single critical section without much room for failures. The
previous coding wasn't using a critical section, without any
explanation as to why this was acceptable. In replication scenarios
using the 9.3 branch, standby servers must be upgraded before their
master, so that they are prepared to deal with the new WAL record
once the master is upgraded; failure to do so will cause WAL replay
to die with a PANIC message. Later upgrade of the standby will
allow the process to continue where it left off, so there's no
disruption of the data in the standby in any case. Standbys know
how to deal with the old WAL record, so it's okay to keep the master
running the old code for a while. In master, the old freeze WAL
record is gone, for cleanliness' sake; there's no compatibility
concern there. Backpatch to 9.3, where the original bug was
introduced and where the previous fix was backpatched. Álvaro
Herrera and Andres Freund
http://git.postgresql.org/pg/commitdiff/3b97e6823b949624afdc3ce4c92b29a80429715f

- Don't ignore tuple locks propagated by our updates. If a tuple was
locked by transaction A, and transaction B updated it, the new
version of the tuple created by B would be locked by A, yet visible
only to B; due to an oversight in HeapTupleSatisfiesUpdate, the lock
held by A wouldn't get checked if transaction B later deleted (or
key-updated) the new version of the tuple. This might cause
referential integrity checks to give false positives (that is, allow
deletes that should have been rejected). This is an easy oversight
to have made, because prior to improved tuple locks in commit
0ac5ad5134f it wasn't possible to have tuples created by our own
transaction that were also locked by remote transactions, and so
locks weren't even considered in that code path. It is recommended
that foreign keys be rechecked manually in bulk after installing
this update, in case some referenced rows are missing with some
referencing row remaining. Per bug reported by Daniel Wood in
CAPweHKe5QQ1747X2c0tA=5zf4YnS2xcvGf13Opd-1Mq24rF1cQ(at)mail(dot)gmail(dot)com
http://git.postgresql.org/pg/commitdiff/11ac4c73cb89551d7e0d0180b58d82186f072f8d

- Optimize updating a row that's locked by same xid. Updating or
locking a row that was already locked by the same transaction under
the same Xid caused a MultiXact to be created; but this is
unnecessary, because there's no usefulness in being able to
differentiate two locks by the same transaction. In particular, if
a transaction executed SELECT FOR UPDATE followed by an UPDATE that
didn't modify columns of the key, we would dutifully represent the
resulting combination as a multixact -- even though a single
key-update is sufficient. Optimize the case so that only the
strongest of both locks/updates is represented in Xmax. This can
save some Xmax's from becoming MultiXacts, which can be a
significant optimization. This missed optimization opportunity was
spotted by Andres Freund while investigating a bug reported by
Oliver Seemann in message
CANCipfpfzoYnOz5jj=UZ70_R=CwDHv36dqWSpwsi27vpm1z5sA(at)mail(dot)gmail(dot)com
and also directly as a performance regression reported by Dong Ye in
message d54b8387(dot)000012d8(dot)00000010(at)YED-DEVD1(dot)vmware(dot)com Reportedly,
this patch fixes the performance regression. Since the missing
optimization was reported as a significant performance regression
from 9.2, backpatch to 9.3. Andres Freund, tweaked by Álvaro
Herrera
http://git.postgresql.org/pg/commitdiff/13aa6244313df2315b0952a9e7f6e68bb09c9d98

- Make stdout unbuffered. This ensures that all stdout output is
flushed immediately, to match stderr. This eliminates the need for
fflush(stdout) calls sprinkled all over the place. Per Daniel Wood
in message 519A79C6(dot)90308(at)salesforce(dot)com
http://git.postgresql.org/pg/commitdiff/73bcb76b77c2e47dd066a1961309cf950cf19412

- isolationtester: Ensure stderr is unbuffered, too
http://git.postgresql.org/pg/commitdiff/6eda3e9c27781dec369542a9b20cba7c3d832a5e

- Avoid useless palloc during transaction commit. We can allocate the
initial relations-to-drop array when first needed, instead of at
function entry; this avoids allocating it when the function is not
going to do anything, which is most of the time. Backpatch to 9.3,
where this behavior was introduced by commit 279628a0a7cf5. There's
more that could be done here, such as possible reworking of the code
to avoid having to palloc anything, but that doesn't sound as
backpatchable as this relatively minor change. Per complaint from
Noah Misch in 20131031145234(dot)GA621493(at)tornado(dot)leadboat(dot)com
http://git.postgresql.org/pg/commitdiff/6130208e75c98d76b526ac2ac08cedbd17b9f00d

Bruce Momjian pushed:

- Comment: COPY comment improvement. Etsuro Fujita
http://git.postgresql.org/pg/commitdiff/dba5a9dda9adbda16a72c46e1c012ee6552c248a

- Fix incorrect error message reported for non-existent users.
Previously, lookups of non-existent user names could return
"Success"; it will now return "User does not exist" by resetting
errno. This also centralizes the user name lookup code in
libpgport. Report and analysis by Nicolas Marchildon; patch by me
http://git.postgresql.org/pg/commitdiff/613c6d26bd42dd8c2dd0664315be9551475b8864

- Move pg_upgrade_support global variables to their own include file.
Previously their declarations were spread around to avoid accidental
access.
http://git.postgresql.org/pg/commitdiff/527fdd9df11eb338fdd372d16f62cd067de5204b

Tatsuo Ishii pushed:

- Add ALTER SYSTEM command to edit the server configuration file.
Patch contributed by Amit Kapila. Reviewed by Hari Babu, Masao
Fujii, Boszormenyi Zoltan, Andres Freund, Greg Smith and others.
http://git.postgresql.org/pg/commitdiff/65d6e4cb5c62371dae6c236a7e709d503ae6ddf8

Robert Haas pushed:

- Allow on-detach callbacks for dynamic shared memory segments. Just
as backends must clean up their shared memory state (releasing
lwlocks, buffer pins, etc.) before exiting, they must also perform
any similar cleanups related to dynamic shared memory segments they
have mapped before unmapping those segments. So add a mechanism to
ensure that. Existing on_shmem_exit hooks include both "user level"
cleanup such as transaction abort and removal of leftover temporary
relations and also "low level" cleanup that forcibly released
leftover shared memory resources. On-detach callbacks should run
after the first group but before the second group, so create a new
before_shmem_exit function for registering the early callbacks and
keep on_shmem_exit for the regular callbacks. (An earlier draft of
this patch added an additional argument to on_shmem_exit, but that
had a much larger footprint and probably a substantially higher risk
of breaking third party code for no real gain.) Patch by me,
reviewed by KaiGai Kohei and Andres Freund.
http://git.postgresql.org/pg/commitdiff/001a573a2011d605f2a6e10aee9996de8581d099

- Fix compiler warning. get_user_name returns const char *, but we
were assigning the result to a char * variable.
http://git.postgresql.org/pg/commitdiff/6bb9d3017c173fa6102010d83ced7ad4409e5a49

- pg_prewarm, a contrib module for prewarming relationd data. Patch
by me. Review by Álvaro Herrera, Amit Kapila, Jeff Janes, Gurjeet
Singh, and others.
http://git.postgresql.org/pg/commitdiff/c32afe53c2e87a56e2ff930798a5588db0f7a516

- Change the way we mark tuples as frozen. Instead of changing the
tuple xmin to FrozenTransactionId, the combination of
HEAP_XMIN_COMMITTED and HEAP_XMIN_INVALID, which were previously
never set together, is now defined as HEAP_XMIN_FROZEN. A variety
of previous proposals to freeze tuples opportunistically before
vacuum_freeze_min_age is reached have foundered on the objection
that replacing xmin by FrozenTransactionId might hinder debugging
efforts when things in this area go awry; this patch is intended to
solve that problem by keeping the XID around (but largely ignoring
the value to which it is set). Third-party code that checks for
HEAP_XMIN_INVALID on tuples where HEAP_XMIN_COMMITTED might be set
will be broken by this change. To fix, use the new accessor macros
in htup_details.h rather than consulting the bits directly.
HeapTupleHeaderGetXmin has been modified to return
FrozenTransactionId when the infomask bits indicate that the tuple
is frozen; use HeapTupleHeaderGetRawXmin when you already know that
the tuple isn't marked commited or frozen, or want the raw value
anyway. We currently do this in routines that display the xmin for
user consumption, in tqual.c where it's known to be safe and
important for the avoidance of extra cycles, and in the
function-caching code for various procedural languages, which
shouldn't invalidate the cache just because the tuple gets frozen.
Robert Haas and Andres Freund
http://git.postgresql.org/pg/commitdiff/37484ad2aacef5ec794f4dd3d5cf814475180a78

Fujii Masao pushed:

- Fix typo in docs for min_recovery_apply_delay. Bernd Helmle
http://git.postgresql.org/pg/commitdiff/f83a75451f96ecf950b6b8e29f487723d481ce1b

- Add tab completion for ALTER SYSTEM SET in psql.
http://git.postgresql.org/pg/commitdiff/084e385a2fc0b596ff6aa0ede8d360a889a6c084

- Rename wal_log_hintbits to wal_log_hints, per discussion on
pgsql-hackers. Sawada Masahiko
http://git.postgresql.org/pg/commitdiff/961bf59fb7a7e4fab751e20e9372de7ab37c5649

Peter Eisentraut pushed:

- Upgrade to Autoconf 2.69
http://git.postgresql.org/pg/commitdiff/94b899b829657332bda856ac3f06153d09077bd1

== Rejected Patches (for now) ==

No one was disappointed this week :-)

== Pending Patches ==

KaiGai Kohei sent in another revision of a patch to implement a
CustomScan API and use same in FDWs.

Andres Freund sent in a patch to prevent "replica identity" messages
from being sent for system tables.

Tatsuo Ishii sent in two more revisions of a patch to implement a
toregclass.

MauMau sent in two revisions of a patch to fix an issue where disk
space increases unnecessarily while performing archive recovery.

Heikki Linnakangas sent in a flock of patches to improve GIN indexing
by storing some additional information.

David Rowley sent in five more revisions of a patch to implement
inverse transition functions.

MauMau sent in a patch to fix an issue where ECPG does not recognize
service files.

Emre Hasegeli sent in a patch to implement GiST indexing support for
inet datatypes.

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

Pavel Stehule sent in two more revisions of a patch to implement a
simple function to make timestamps.

Christian Kruse sent in a patch to show xid and xmin in pg_stat_activity
and pg_stat_replication.

Maciek Sakrejda sent in a patch to fix the documentation for VACUUM
FREEZE, which was missing a reference to the vacuum_freeze_table_age
GUC.

Robert Haas sent in two more revisions of a patch to preserve forensic
information when freezing.

Robert Haas sent in another revision of a patch to implement CLUSTER
FREEZE.

MauMau sent in two more revisions of a patch to fix an issue where
pg_ctl always uses the same event source.

Marko (johto) Tiikkaja sent in a patch to implement a one-argument
form of array_length which requires (i.e. errors out unless) the
following conditions hold: that the array is one-dimensional; that its
lowest bound is 1.

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

Haribabu Kommi sent in another revision of a patch to allow specifying
an alternate pg_xlog directory in pg_basebackup.

MauMau sent in a patch to fix a bug in psql where \copy doesn't end if
the backend is killed.

Pavel Stehule sent in another revision of a patch to add an if-exists
option to pg_dump and pg_restore.

Oskari Saarenmaa sent in a patch to make various variables read-only
(const).

Pavel Stehule sent in a patch to implement a make_interval function
similar to the make_timestamp functions already submitted.

Pavel Raiskup sent in a patch to make pg_upgrade's locale comparison
more tolerant.

Fabien COELHO sent in a patch to fix a bug in the ISN extension.

Amit Kapila sent in a patch to fix an issue with ALTER SYSTEM where
error_conf_filename was not displayed.

Alexander Korotkov sent in another WIP revision of a patch to
implement partial sorting as a planner optimization.

Amit Kapila sent in a patch to fix an issue with ALTER SYSTEM where
PGC_BACKEND GUCs were not being handled correctly.