PostgreSQL Weekly News – December 18 2011

== PostgreSQL Weekly News – December 18 2011 ==

== PostgreSQL Product News ==

HTSQL 2.2, a high-level query language for relational databases, released.

psycopg2 2.4.3, a Python connector for PostgreSQL, released.

== PostgreSQL Jobs for December ==

== PostgreSQL Local ==

FOSDEM 2012 – PostgreSQL Devroom: Call for Speakers is open until
December 20, 2011.

The fifth annual “Prague PostgreSQL Developers Day” conference,
organized by CSPUG (Czech and Slovak PostgreSQL Users Group), will be
held on February 9, 2012 in Prague. The Call for Papers is open.
Please send proposals including contact information, topic, and
expected length of session to info AT p2d2 DOT cz.

The Call for Papers for is open for FLOSS UK, which will be held in
Edinburgh from the 20th to the 22nd March 2012. The deadline for
proposals is the 18th November 2011 and selected speakers will be
notified by the 25th November 2011. Proposals should be submitted to
postgresql2012 AT flossuk DOT org. More information at:

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

== Applied Patches ==

Heikki Linnakangas pushed:

– Revert the behavior of inet/cidr functions to not unpack the
arguments. I forgot to change the functions to use the
PG_GETARG_INET_PP() macro, when I changed DatumGetInetP() to unpack
the datum, like Datum*P macros usually do. Also, I screwed up the
definition of the PG_GETARG_INET_PP() macro, and didn’t notice
because it wasn’t used. This fixes the memory leak when sorting
inet values, as reported by Jochen Erwied and debugged by Andres
Freund. Backpatch to 8.3, like the previous patch that broke it.

– Add support for passing cursor parameters in named notation in
PL/pgSQL. Yeb Havinga, reviewed by Kevin Grittner, with small
changes by me.

– Fix reference to “verify-ca” and “verify-full” in a note in the

Tom Lane pushed:

– Move BKP_REMOVABLE bit from individual WAL records to WAL page
headers. Removing this bit from xl_info allows us to restore the
old limit of four (not three) separate pages touched by a WAL
record, which is needed for the upcoming SP-GiST feature, and will
likely be useful elsewhere in future. When we implemented
XLR_BKP_REMOVABLE in 2007, we had to do it like that because no
special WAL-visible action was taken when starting a backup.
However, now we force a segment switch when starting a backup, so a
compressing WAL archiver (such as pglesslog) that uses the state
shown in the current page header will not be fooled as to
removability of backup blocks. The only downside is that the
archiver will not return to compressing mode for up to one WAL page
after the backup is over, which is a small price to pay for getting
back the extra xl_info bit. In any case the archiver could look for
XLOG_BACKUP_END records if it thought it was worth the trouble to do
so. Bump XLOG_PAGE_MAGIC since this is effectively a change in WAL

– Add missing ‘static’ qualifier.

– Add SP-GiST (space-partitioned GiST) index access method. SP-GiST
is comparable to GiST in flexibility, but supports non-balanced
partitioned search structures rather than balanced trees. As
described at PGCon 2011, this new indexing structure can beat GiST
in both index build time and query speed for search problems that it
is well matched to. There are a number of areas that could still
use improvement, but at this point the code seems committable.
Teodor Sigaev and Oleg Bartunov, with considerable revisions by Tom

– Fix compiler warning seen on 64-bit machine.

– Fix some long-obsolete references to XLogOpenRelation. These were
missed in commit a213f1ee6c5a1bbe1f074ca201975e76ad2ed50c, which
removed that function.

– Remove bogus entries in gist point_ops operator class. These
entries could never be matched to an index clause because they don’t
have the index datatype on the left-hand side of the operator.
(Their commutators are in the opclass, which is sensible, but that
doesn’t mean these operators should be.) Spotted by a test that I
recently added to opr_sanity to catch exactly this type of thinko.
AFAICT there is no code in gistproc.c that is specifically meant to
cover these cases, so nothing to remove at that level.

– Defend against null scankeys in spgist searches. Should’ve thought
of that one earlier.

– Replace simple constant pg_am.amcanreturn with an AM support
function. The need for this was debated when we put in the
index-only-scan feature, but at the time we had no near-term
expectation of having AMs that could support such scans for only
some indexes; so we kept it simple. However, the SP-GiST AM forces
the issue, so let’s fix it. This patch only installs the new API;
no behavior actually changes.

Andrew Dunstan pushed:

– Add –exclude-table-data option to pg_dump. Andrew Dunstan,
reviewed by Josh Berkus, Robert Haas and Peter Geoghegan. This
allows dumping of a table definition but not its data, on a per
table basis. Table name patterns are supported just as for

– Fix docs build I inadvertantly broke.

– Disable excessive floating point optimization by recent versions of
gcc. Suggested solution from Tom Lane. Problem discovered, probably
not for the first time, while testing the mingw-w64 32 bit compiler.
Backpatched to all live branches.

– include_if_exists facility for config file. This works the same as
include, except that an error is not thrown if the file is missing.
Instead the fact that it’s missing is logged. Greg Smith, reviewed
by Euler Taveira de Oliveira.

– Add –section option to pg_dump and pg_restore. Valid values are
–pre-data, data and post-data. The option can be given more than
once. –schema-only is equivalent to –section=pre-data
–section=post-data. –data-only is equivalent to –section=data.
Andrew Dunstan, reviewed by Joachim Wieland and Josh Berkus.

– Clarify the post-data status on unvalidated check constraints. Per
gripe from Thom Brown.

– Same clarification for pg_restore.

Peter Eisentraut pushed:

– PL/Python: Refactor subtransaction handling. Lots of repetitive
code was moved into new functions PLy_spi_subtransaction_{begin,commit,abort}.
Jan Urbański

– Split plpython.c into smaller pieces. This moves the code around
from one huge file into hopefully logical and more manageable
modules. For the most part, the code itself was not touched,
except: PLy_function_handler and PLy_trigger_handler were renamed to
PLy_exec_function and PLy_exec_trigger, because they were not
actually handlers in the PL handler sense, and it makes the naming
more similar to the way PL/pgSQL is organized. The initialization
of the procedure caches was separated into a new function
init_procedure_caches to keep the hash tables private to
plpy_procedures.c. Jan Urbański and Peter Eisentraut

– PL/Python: One more file renaming fix to unbreak the build

Robert Haas pushed:

– Fix typo.

– Don’t leave regress_test_role_super lying around. Fixes an
oversight in commit fc6d1006bda783cc002c61a5f072905849dbde4b. Noted
by Tom Lane.

– Improve behavior of concurrent ALTER <relation> .. SET SCHEMA. If
the referrent of a name changes while we’re waiting for the lock, we
must recheck permissons. We also now check the relkind before
locking, since it’s easy to do that long the way. Patch by me;
review by Noah Misch.

– Improve behavior of concurrent rename statements. Previously,
renaming a table, sequence, view, index, foreign table, column, or
trigger checked permissions before locking the object, which meant
that if permissions were revoked during the lock wait, we would
still allow the operation. Similarly, if the original object is
dropped and a new one with the same name is created, the operation
will be allowed if we had permissions on the old object; the
permissions on the new object don’t matter. All this is now fixed.
Along the way, attempting to rename a trigger on a foreign table now
gives the same error message as trying to create one there in the
first place (i.e. that it’s not a table or view) rather than simply
stating that no trigger by that name exists. Patch by me; review by
Noah Misch.

– Various micro-optimizations for GetSnapshopData(). Heikki
Linnakangas had the idea of rearranging GetSnapshotData to avoid
checking for sub-XIDs when no top-level XID is present. This patch
does that plus further a bit of further, related rearrangement.
Benchmarking show a significant improvement on unlogged tables at
higher concurrency levels, and mostly indifferent result on
permanent tables (which are presumably bottlenecked elsewhere).
Most of the benefit seems to come from using the new
NormalTransactionIdPrecedes() macro rather than the function call

Bruce Momjian pushed:

– Remove tabs in SGML file.

Michael Meskes pushed:

– In ecpg removed old leftover check for given connection name. Ever
since we introduced real prepared statements this should work for
different connections. The old solution just emulating prepared
statements, though, wasn’t able to handle this. Closes: #6309

– Added test for cursor handling on different connections to
regression test suite for ecpg.

– Mark variables as const in pgtypeslib if they only carry a format

== Rejected Patches (for now) ==

No one was disappointed this week 🙂

== Pending Patches ==

Simon Riggs sent in two revisions of a patch which introduces regular
keepalives from WALsender to WALreceiver, using a new protocol message ‘k’.

KaiGai Kohei sent in another revision of the patch intended to plug
certain class of information leaks in VIEWs.

Jeff Davis and Alexander Korotkov traded patches to clean up some
infelicities in range types.

Shigeru HANADA and Etsuro Fujita traded revisions of the patch to make
it possible to collect statistics on CSV foreign tables.

Peter Eisentraut sent in another revision of the patch adding type

Marti Raudsepp sent in another revision of the patch to cache the
results of functions with stable arguments.

Brendan Jurd sent in a patch to add arithmetic operators for the
macaddr type.

Alexander Shulgin and Greg Smith traded patches to implement a URI
syntax for PostgreSQL connection strings in libpq.

Robert Haas sent in another revision of the patch to allow taking
fewer snapshots per query.

Greg Smith and Magnus Hagander traded revisions of a patch to allow
users to kill their own queries.

Pavel Stehule sent in four more revisions of the patch to add a CHECK
FUNCTION statement.

Andrew Dunstan sent in a patch to make libpgport build dynamically
rather than statically.

Shigeru HANADA sent in another flock of patches intended both to
enable a PostgreSQL foreign data wrapper along with some new push-down
capabilities for foreign data wrappers in general to make the
aforementioned useful.

Peter Geoghegan sent in another revision of the patch to alter
pg_stat_statements to allow query tree based normalization.

Robert Haas sent in two more revisions of the patch to store hot
members of PGPROC out of the band.

Robert Haas sent in another revision of the Flexlocks patch.

Simon Riggs sent in another revision of a patch to fix some PGPROC
race conditions.

Heikki Linnakangas sent in a patch to move more work outside
WALInsertLock, which bottlenecks operations when >1 core is available.

Alvaro Herrera sent in another revision of the patch to make it
possible to make CHECK constraints not be inherited by child tables in
a table inheritance hierarchy.

Robert Haas sent in another revision of the patch to add JSON as a
first-class data type.

Lionel Elie Mamane sent in a patch to build libpq with Mozilla LDAP
instead of OpenLDAP.

Phil Sorber sent in a WIP patch which improves relation size functions
such as pg_relation_size() to avoid producing an error when called
against a no longer visible relation.

David Fetter sent in a patch to add optional page checksums.

Dimitri Fontaine sent in a patch to make it possible to see and set
EXTENSION dependencies functionally.

Comments are closed.