PostgreSQL Weekly News – February 13 2011

== PostgreSQL Weekly News – February 13 2011 ==

Selena Deckelmann is offering an online class through CodeLesson:

== PostgreSQL Jobs for February ==

== PostgreSQL Local ==

NYPUG is having a meetup on February 15, 2011 at 6:15PM at the Yodle
offices on 50 W 23rd St, New York, NY. More info and RSVP at:

PGDay is scheduled at this year’s Southern California Linux Exposition
(SCALE) held in the LAX Hilton Hotel in the city of Los Angeles,
California, on Friday February 25th, 2011.

PostgreSQL Conference East 2011: NYC March 22nd – 25th

Open Database Camp will be on May 7-9, 2011 in Sardinia, Italy

PGCon will be May 19-20, 2011 at the University of Ottawa, preceded by
two days of tutorials on May 17-18.

== PostgreSQL in the News ==

Planet PostgreSQL:

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, German language
to, Italian language to Spanish language

== Reviews ==

Erik Rijkers reviewed the patch to add range types.

== Applied Patches ==

Bruce Momjian pushed:

– remove tags.;a=commitdiff;h=ad762426333aac5bd8e1ceac753bebe4a6411c28

– Remove tabs in SGML file.

– Remove more SGML tabs.

– Fix pg_get_encoding_from_locale() function call parameters to match
prototype for cases where there is no multi-language support.

– Fix “variable not used” warnings when USE_WIDE_UPPER_LOWER is not

– Properly handle Win32 paths of ‘E:abc’, which can be either absolute
or relative, by creating a function path_is_relative_and_below_cwd()
to check for specific requirements. It is unclear if this fixes a
security problem or not but the new code is more robust.

– Fix reverse ‘if’ test in path_is_relative_and_below_cwd(), per Tom

– Back out libpq doc change; not ready yet.

ITAGAKI Takahiro pushed:

– Fix error messages for FreeFile in COPY command. They are extracted
from COPY API patch. suggested by Noah Misch;a=commitdiff;h=fb7355e0cedfadd437c30c73cfb71af7ff8933b1

– Fix a comment for MergeAttributes. We forgot to adjust it when we
changed relistemp to relpersistence.;a=commitdiff;h=c18f51da17d8cf01d62218e0404e18ba246bde54

– Fix typo in the documentation. By Kevin Grittner.

Andrew Dunstan pushed:

– Supply now required HeUTF8 macro for plperl where it’s missing, per
buildfarm results.;a=commitdiff;h=c852e95b0bcbe708ba64b9499e50b9cc98bd7b71

– Use correct name and URL for MinGW-w64, error noticed by Gurjeet Singh.

Heikki Linnakangas pushed:

– Implement genuine serializable isolation level. Until now, our
Serializable mode has in fact been what’s called Snapshot Isolation,
which allows some anomalies that could not occur in any serialized
ordering of the transactions. This patch fixes that using a method
called Serializable Snapshot Isolation, based on research papers by
Michael J. Cahill (see README-Steve Singer for full references). In
Serializable Snapshot Isolation, transactions run like they do in
Snapshot Isolation, but a predicate lock manager observes the reads
and writes performed and aborts transactions if it detects that an
anomaly might occur. This method produces some false positives, ie.
it sometimes aborts transactions even though there is no anomaly.
To track reads we implement predicate locking, see
storage/lmgr/predicate.c. Whenever a tuple is read, a predicate
lock is acquired on the tuple. Shared memory is finite, so when a
transaction takes many tuple-level locks on a page, the locks are
promoted to a single page-level lock, and further to a single
relation level lock if necessary. To lock key values with no
matching tuple, a sequential scan always takes a relation-level
lock, and an index scan acquires a page-level lock that covers the
search key, whether or not there are any matching keys at the
moment. A predicate lock doesn’t conflict with any regular locks or
with another predicate locks in the normal sense. They’re only used
by the predicate lock manager to detect the danger of anomalies.
Only serializable transactions participate in predicate locking, so
there should be no extra overhead for for other transactions.
Predicate locks can’t be released at commit, but must be remembered
until all the transactions that overlapped with it have completed.
That means that we need to remember an unbounded amount of predicate
locks, so we apply a lossy but conservative method of tracking locks
for committed transactions. If we run short of shared memory, we
overflow to a new “pg_serial” SLRU pool. We don’t currently allow
Serializable transactions in Hot Standby mode. That would be hard,
because even read-only transactions can cause anomalies that
wouldn’t otherwise occur. Serializable isolation mode now means the
new fully serializable level. Repeatable Read gives you the old
Snapshot Isolation level that we have always had. Kevin Grittner
and Dan Ports, reviewed by Jeff Davis, Heikki Linnakangas and Anssi

– Oops, forgot to bump catversion in the Serializable Snapshot
Isolation patch. I thought we didn’t need that, but then I
remembered that it added a new SLRU subdirectory, pg_serial. While
we’re at it, document what pg_serial is.;a=commitdiff;h=47082fa875179ae629edb26807ab3f38a775280b

– Fix copy-pasto in description of pg_serial, and silence compiler
warning about uninitialized field you get on some compilers.;a=commitdiff;h=7202ad7b8dd07864092be70287fe971ec72a3fbc

– UINT64_MAX isn’t defined on MSVC.;a=commitdiff;h=f9f9d696a9c51cb26c24bb28760e6bb1edd0e995

– Fix typo, by Kevin Grittner.;a=commitdiff;h=1703f0e8da2e8e3eccb6e12879c011ba106f8a62

– Fix allocation of RW-conflict pool in the new predicate lock
manager, and also take the RW-conflict pool into account in the
PredicateLockShmemSize() estimate.

– Allocate all entries in the serializable xid hash up-front, so that
you don’t run out of shared memory when you try to assign an xid to
a transaction. Kevin Grittner.

– Send status updates back from standby server to master, indicating
how far the standby has written, flushed, and applied the WAL. At
the moment, this is for informational purposes only, the values are
only shown in pg_stat_replication system view, but in the future
they will also be needed for synchronous replication. Extracted
from Simon riggs’ synchronous replication patch by Robert Haas, with
some tweaking by me.

Robert Haas pushed:

– Avoid having autovacuum workers wait for relation locks. Waiting
for relation locks can lead to starvation – it pins down an
autovacuum worker for as long as the lock is held. But if we’re
doing an anti-wraparound vacuum, then we still wait; maintenance can
no longer be put off. To assist with troubleshooting, if
log_autovacuum_min_duration >= 0, we log whenever an autovacuum or
autoanalyze is skipped for this reason. Per a gripe by Josh Berkus,
and ensuing discussion.;a=commitdiff;h=32896c40ca766146312b28a5a0eb3f66ca0300ed

– Tweak find_composite_type_dependencies API a bit more. Per
discussion with Noah Misch, the previous coding, introduced by my
commit 65377e0b9c0e0397b1598b38b6a7fb8b6f740d39 on 2011-02-06, was
really an abuse of RELKIND_COMPOSITE_TYPE, since the caller in
typecmds.c is actually passing the name of a domain. So go back
having a type name argument, but make the first argument a Relation
rather than just a string so we can tell whether it’s a table or a
foreign table and emit the proper error message.

– Typo fixes. receivedUpto should be capitalized consistently.

– Allow tab-completion of :variable even as first word on a line.
Christoph Berg

– Teach ALTER TABLE .. SET DATA TYPE to avoid some table rewrites.
When the old type is binary coercible to the new type and the using
clause does not change the column contents, we can avoid a full
table rewrite, though any indexes on the affected columns will still
need to be rebuilt. This applies, for example, when changing a
varchar column to be of type text. The prior coding assumed that
the set of operations that force a rewrite is identical to the set
of operations that must be propagated to tables making use of the
affected table’s rowtype. This is no longer true: even though the
tuples in those tables wouldn’t need to be modified, the data type
change invalidate indexes built using those composite type columns.
Indexes on the table we’re actually modifying can be invalidated
too, of course, but the existing machinery is sufficient to handle
that case. Along the way, add some debugging messages that make it
possible to understand what operations ALTER TABLE is actually
performing in these cases. Noah Misch and Robert Haas

Simon Riggs pushed:

– Extend ALTER TABLE to allow Foreign Keys to be added without initial
validation. FK constraints that are marked NOT VALID may later be
VALIDATED, which uses an ShareUpdateExclusiveLock on constraint
table and RowShareLock on referenced table. Significantly reduces
lock strength and duration when adding FKs. New state visible from
psql. Simon Riggs, with reviews from Marko Tiikkaja and Robert Haas;a=commitdiff;h=722bf7017bbe796decc79c1fde03e7a83dae9ada

– Remove rare corner case for data loss when triggering standby
server. If the standby was streaming when trigger file arrives,
check also in the archive for additional WAL files. This is a corner
case since it is unlikely that we would trigger a failover while the
master is still available and sending data to standby, while at the
same time running in archive mode and also while the streaming
standby has fallen behind archive. Someone would eventually be
unlucky; we must plug all gaps however small. Fujii Masao;a=commitdiff;h=faa0550572583f51dba25611ab0f1d1c31de559b

– Basic Recovery Control functions for use in Hot Standby. Pause,
Resume, Status check functions only. Also, new recovery.conf
parameter to pause_at_recovery_target, default on. Simon Riggs,
reviewed by Fujii Masao;a=commitdiff;h=8c6e3adbf792c2bba448e88cbf2c8e03fb802e73

– Named restore points in recovery. Users can record named points,
then new recovery.conf parameter recovery_target_name allows PITR to
specify named points as recovery targets. Jaime Casanova, reviewed
by Euler Taveira de Oliveira, plus minor edits;a=commitdiff;h=c016ce728139be95bb0dc7c4e5640507334c2339

– Continue long tradition of bumping the catalog version a little

Peter Eisentraut pushed:

– Per-column collation support. This adds collation support for
columns and domains, a COLLATE clause to override it per expression,
and B-tree index support. Peter Eisentraut reviewed by Pavel
Stehule, Itagaki Takahiro, Robert Haas, Noah Misch;a=commitdiff;h=414c5a2ea65cbd38d79ffdf9b1fde7cc75c134e0

– Information schema views for collation support. Add the views
character_sets, collations, and collation_character_set_applicability.

– Update comment. It was still claiming that the keyword list is in
keywords.c, when it is now in kwlist.h.

– DDL support for collations: collowner field, CREATE COLLATION, ALTER
extensions, pg_dump support for the above, dependency management,
psql tab completion, psql \dO command

Tom Lane pushed:

– Fix merge conflict.;a=commitdiff;h=0bc0bd07d41169d6de513967615ad9cb3d0f322e

– Core support for “extensions”, which are packages of SQL objects.
This patch adds the server infrastructure to support extensions.
There is still one significant loose end, namely how to make it play
nice with pg_upgrade, so I am not yet committing the changes that
would make all the contrib modules depend on this feature. In
passing, fix a disturbingly large amount of breakage in
AlterObjectNamespace() and callers. Dimitri Fontaine, reviewed by
Anssi Kääriäinen, Itagaki Takahiro, Tom Lane, and numerous others;a=commitdiff;h=d9572c4e3b474031060189050e14ef384b94e001

– Suppress some compiler warnings in recent commits. Older versions
of gcc tend to throw “variable might be clobbered by `longjmp’ or
`vfork'” warnings whenever a variable is assigned in more than one
place and then used after the end of a PG_TRY block. That’s
reasonably easy to work around in execute_extension_script, and the
overhead of unconditionally saving/restoring the GUC variables seems
unlikely to be a serious concern. Also clean up logic in
ATExecValidateConstraint to make it easier to read and less likely
to provoke “variable might be used uninitialized in this function”

– Implement “ALTER EXTENSION ADD object”. This is an essential
component of making the extension feature usable; first because it’s
needed in the process of converting an existing installation
containing “loose” objects of an old contrib module into the
extension-based world, and second because we’ll have to use it in
pg_dump –binary-upgrade, as per recent discussion. Loosely based
on part of Dimitri Fontaine’s ALTER EXTENSION UPGRADE patch.

– Rethink order of operations for dumping extension member objects.
My original idea of doing extension member identification during
getDependencies() didn’t work correctly: we have to mark member
tables as not-to-be-dumped rather earlier than that, else their
subsidiary objects like indexes get dumped anyway. Rearrange code
to mark them early enough.

– Fix pg_upgrade to handle extensions. This follows my proposal of
yesterday, namely that we try to recreate the previous state of the
extension exactly, instead of allowing CREATE EXTENSION to run a SQL
script that might create some entirely-incompatible on-disk state.
In –binary-upgrade mode, pg_dump won’t issue CREATE EXTENSION at
all, but instead uses a kluge function provided by
pg_upgrade_support to recreate the pg_extension row (and
extension-level pg_depend entries) without creating any member
objects. The member objects are then restored in the same way as if
they weren’t members, in particular using pg_upgrade’s normal hacks
to preserve OIDs that need to be preserved. Then, for each member
object, ALTER EXTENSION ADD is issued to recreate the pg_depend
entry that marks it as an extension member. In passing, fix
breakage in pg_upgrade’s enum-type support: somebody didn’t fix it
when the noise word VALUE got added to ALTER TYPE ADD. Also,
rationalize parsetree representation of COMMENT ON DOMAIN and fix
get_object_address() to allow OBJECT_DOMAIN.

– Fix improper matching of resjunk column names for FOR UPDATE in
subselect. Flattening of subquery range tables during setrefs.c
could lead to the rangetable indexes in PlanRowMark nodes not
matching up with the column names previously assigned to the
corresponding resjunk ctid (resp. tableoid or wholerow) columns.
Typical symptom would be either a “cannot extract system attribute
from virtual tuple” error or an Assert failure. This wasn’t a
problem before 9.0 because we didn’t support FOR UPDATE below the
top query level, and so the final flattening could never renumber an
RTE that was relevant to FOR UPDATE. Fix by using a plan-tree-wide
unique number for each PlanRowMark to label the associated resjunk
columns, so that the number need not change during flattening. Per
report from David Johnston (though I’m darned if I can see how this
got past initial testing of the relevant code). Back-patch to 9.0.

– Extend “ALTER EXTENSION ADD object” to permit “DROP object” as well.
Per discussion, this is something we should have sooner rather than
later, and it doesn’t take much additional code to support it.

– Add support for multiple versions of an extension and ALTER
EXTENSION UPDATE. This follows recent discussions, so it’s quite a
bit different from Dimitri’s original. There will probably be more
changes once we get a bit of experience with it, but let’s get it in
and start playing with it. This is still just core code. I’ll
start converting contrib modules shortly. Dimitri Fontaine and Tom

– Clean up installation directory choices for extensions. Arrange for
the control files to be in $SHAREDIR/extension not
$SHAREDIR/contrib, since we’re generally trying to deprecate the
term “contrib” and this is a once-in-many-moons opportunity to get
rid of it in install paths. Fix PGXS to install the $EXTENSION file
into that directory no matter what MODULEDIR is set to; a nondefault
MODULEDIR should only affect the script and secondary extension
files. Fix the control file directory parameter to be interpreted
relative to $SHAREDIR, to avoid a surprising disconnect between how
you specify that and what you set MODULEDIR to. Per discussion with
David Wheeler.

– Refactor ALTER EXTENSION UPDATE to have cleaner multi-step
semantics. This change causes a multi-step update sequence to
behave exactly as if the updates had been commanded one at a time,
including updating the “requires” dependencies afresh at each step.
The initial implementation took the shortcut of examining only the
final target version’s “requires” and changing the catalog entry but
once. But on reflection that’s a bad idea, since it could lead to
executing old update scripts under conditions different than they
were designed/tested for. Better to expend a few extra cycles and
avoid any surprises. In the same spirit, if a CREATE EXTENSION FROM
operation involves applying a series of update files, it will act as
though the CREATE had first been done using the initial script’s
target version and then the additional scripts were invoked with
ALTER EXTENSION UPDATE. I also removed the restriction about not
changing encoding in secondary control files. The new rule is that
a script is assumed to be in whatever encoding the control file(s)
specify for its target version. Since this reimplementation causes
us to read each intermediate version’s control file, there’s no
longer any uncertainty about which encoding setting would get

Magnus Hagander pushed:

– Implement NOWAIT option for BASE_BACKUP command. Specifying this
option makes the server not wait for the xlog to be archived, or
emit a warning that it can’t, instead leaving the responsibility
with the client. This is useful when the log is being streamed
using the streaming protocol in parallel with the backup, without
having log archiving enabled.

– Use NOWAIT when including WAL in base backup. Avoids warning and
waiting for the last segment to be archived, which isn’t necessary
when we’re including the required WAL in the backup itself.

– Track last time for statistics reset on databases and bgwriter.
Tracks one counter for each database, which is reset whenever the
statistics for any individual object inside the database is reset,
and one counter for the background writer. Tomas Vondra, reviewed
by Greg Smith.

Alvaro Herrera pushed:

– Fix the isolation tester compilation on VPATH builds

– Fix isolation tester Makefile so that it runs in a VPATH build

– Fix comment recently obsoleted

== Rejected Patches (for now) ==

No one was disappointed this week 🙂

== Pending Patches ==

Greg Smith sent in another revision of the patch to spread out
checkpoint syncs.

ITAGAKI Takahiro sent in three more revisions of the patch to export
some of the COPY API, which can be used for, among other things,

Shigeru HANADA sent in two more revisions of the patch to add a
file-based foreign data wrapper for SQL/MED.

Shigeru HANADA sent in another revision of the patch to create a
foreign data wrapper API for SQL/MED.

Radoslaw Smogura sent in a patch to improve the performance of
textsend, used in COPY BINARY.

Andrew Dunstan sent in a patch to fix the regression tests which broke
as a result of the COPY API changes.

Steve Singer and Jan Urbanski traded patches to add subtransactions to
PL/PythonU in support of exception handling.

Ibrary Ahmed sent in another revision of the patch to determine
client_encoding from client locale.

Robert Haas and Thom Brown traded patches for synchronous replication.

Shigeru HANADA sent in another revision of the patch to add foreign
scans in SQL/MED.

Shigeru HANADA sent in a patch to avoid catalog lookups in foreign

Tom Lane sent in another revision of the patch to add support for
EXTENSIONs to pg_dump.

Andrew Dunstan sent in a patch implementing “jagged row” CSV text
arrays atop SQL/MED’s file foreign data wrapper feature.

Thom Brown sent in another revision of the patch to fix some boundary
case behavior in generate_series().

Greg Smith sent in a patch to allow pg_archivecleanup to ignore
extensions. This is especially useful for compressed WALs, which
pg_archivecleanup would otherwise have ignored.

Heikki Linnakangas sent in another revision of that patch to add a
PostgreSQL foreign data wrapper.

Jeff Davis sent in another revision of the patch to add range types.

Greg Smith sent in another revision of the patch to expand the maximum
size of pgbench tests.

Alex Hunsaker and Alexey Klyukin traded patches to convert between
PostgreSQL and Perl arrays for PL/Perl.

Stephen Frost and Kevin Grittner traded some more patches to add
support for logging the current role.

Noah Misch sent in another revision of the patch to add FOR KEY LOCK.

Fujii Masao sent in a patch to fix some infelicities in the new
ability to name restore points.

Per review from ITAGAKI Takahiro, Marko (johto) Tiikkaja sent in
another revision of the patch to add transaction-scope advisory locks.

Robert Haas sent in two more revisions of the patch to enable
synchronous replication.

Heikki Linnakangas sent in patches updating the FDW API and the
PostgreSQL FDW, both for SQL/MED.

Jan Urbanski sent in another flock of patches to improve PL/PythonU.

Stephen Frost sent in three more revisions of the patch to add FOR

Alex Hunsaker sent in a patch to fix an infelicity in PL/Perl’s
conversion to and from UTF8.

Per review from YAMAMOTO Takashi, Kevin Grittner sent in a patch to
fix some infelicities in SSI.

Daniel Farina sent in three revisions of a patch to fix some
infelicities in replication server timeout.

Gurjeet Singh sent in a patch to fix some infelicities in re: the
actual existence of hypothetical indexes.

Tom Lane sent in a patch to fix some mismatches between EXTENSIONs and

Daniel Farina sent in another pieces of the synchronous replication
patch, this time for feeding back the xmin of the hot standby.

Peter Eisentraut sent in a patch to add a “make check” target for PLs.

Peter Eisentraut sent in a patch to see to it that psql -l processes
the .psqlrc.

Comments are closed.