== PostgreSQL Weekly News - January 27 2013 ==
== PostgreSQL Jobs for January ==
== PostgreSQL Local ==
The FOSDEM PGDay conference that will be held before FOSDEM in
Brussels, Belgium, on Feb 1st, 2013.
The first edition of the Australian PostgreSQL Day (PGDay.AU 2013)
will be held in Melbourne on February 4, 2013. Registration is free of
charge. Schedule and info:
PyPgDay will be held on March 13th at the Santa Clara Convention
Center, the first day of PyCon. Info here:
PGDay NYC 2013 will be held on March 22, 2013 in New York City.
PostgreSQL Session will be held on March 28th, 2013 in Paris,
France. The Call for Papers is open.
PGCon 2013 will be held May 23-24 2013, in Ottawa at the University of
The 6th annual "Prague PostgreSQL Developers Day" conference,
organized by CSPUG (Czech and Slovak PostgreSQL Users Group), will be
held on May 30, 2013 at Faculty of Mathematics and Physics, Charles
University (Malostranske namesti 25, Prague). The CfP is open until
April 14, 2013 . More information in Czech is at
== 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 [email protected], German language
to [email protected], Italian language to [email protected] Spanish language
to [email protected]
== Applied Patches ==
Alvaro Herrera pushed:
- Refactor ALTER some-obj RENAME implementation. Remove duplicate
implementations of catalog munging and miscellaneous privilege
checks. Instead rely on already existing data in objectaddress.c to
do the work. Author: KaiGai Kohei, changes by me. Reviewed by:
Robert Haas, Álvaro Herrera, Dimitri Fontaine
- Improve concurrency of foreign key locking This patch introduces two
additional lock modes for tuples: "SELECT FOR KEY SHARE" and "SELECT
FOR NO KEY UPDATE". These don't block each other, in contrast with
already existing "SELECT FOR SHARE" and "SELECT FOR UPDATE". UPDATE
commands that do not modify the values stored in the columns that
are part of the key of the tuple now grab a SELECT FOR NO KEY UPDATE
lock on the tuple, allowing them to proceed concurrently with tuple
locks of the FOR KEY SHARE variety. Foreign key triggers now use
FOR KEY SHARE instead of FOR SHARE; this means the concurrency
improvement applies to them, which is the whole point of this patch.
The added tuple lock semantics require some rejiggering of the
multixact module, so that the locking level that each transaction is
holding can be stored alongside its Xid. Also, multixacts now need
to persist across server restarts and crashes, because they can now
represent not only tuple locks, but also tuple updates. This means
we need more careful tracking of lifetime of pg_multixact SLRU
files; since they now persist longer, we require more infrastructure
to figure out when they can be removed. pg_upgrade also needs to be
careful to copy pg_multixact files over from the old server to the
new, or at least part of multixact.c state, depending on the
versions of the old and new servers. Tuple time qualification rules
(HeapTupleSatisfies routines) need to be careful not to consider
tuples with the "is multi" infomask bit set as being only locked;
they might need to look up MultiXact values (i.e. possibly do
pg_multixact I/O) to find out the Xid that updated a tuple, whereas
they previously were assured to only use information readily
available from the tuple header. This is considered acceptable,
because the extra I/O would involve cases that would previously
cause some commands to block waiting for concurrent transactions to
finish. Another important change is the fact that locking tuples
that have previously been updated causes the future versions to be
marked as locked, too; this is essential for correctness of foreign
key checks. This causes additional WAL-logging, also (there was
previously a single WAL record for a locked tuple; now there are as
many as updated copies of the tuple there exist.) With all this in
place, contention related to tuples being checked by foreign key
rules should be much reduced. As a bonus, the old behavior that a
subtransaction grabbing a stronger tuple lock than the parent
(sub)transaction held on a given tuple and later aborting caused the
weaker lock to be lost, has been fixed. Many new spec files were
added for isolation tester framework, to ensure overall behavior is
sane. There's probably room for several more tests. There were
several reviewers of this patch; in particular, Noah Misch and
Andres Freund spent considerable time in it. Original idea for the
patch came from Simon Riggs, after a problem report by Joel
Jacobson. Most code is from me, with contributions from Marti
Raudsepp, Alexander Shulgin, Noah Misch and Andres Freund. This
patch was discussed in several pgsql-hackers threads; the most
important start at the following message-ids:
- isolationtester: add a few fflush(stderr) calls. The lack of them
is causing failures in some BF members. Per Andrew Dunstan.
- Make output identical to pg_resetxlog's
- Don't require oldestMultixact if server doesn't have it
- Use the catversion to distinguish old/new clusters. This makes 9.3
-> 9.3 upgrades work when they cross the commit that added
persistent multixacts; early 9.3 pg_controldata did not have the
required oldestMultiXact line, and so would fail to upgrade. per
- Redefine HEAP_XMAX_IS_LOCKED_ONLY Tuples marked SELECT FOR UPDATE in
a cluster that's later processed by pg_upgrade would have a
different infomask bit pattern than those produced by 9.3dev; that
bit pattern was being seen as "dead" by HEAD (because they would
fail the "is this tuple locked" test, and so the visibility rules
would thing they're updated, even though there's no HEAP_UPDATED
version of them). In other words, some rows could silently
disappear after pg_upgrade. With this new definition, those tuples
become visible again. This is breakage resulting from my commit
Robert Haas pushed:
- Add ddl_command_end support for event triggers. Dimitri Fontaine,
with slight changes by me
- Typo fixes. Noted by Thom Brown.
- Fix CREATE EVENT TRIGGER syntax synopsis in documentation. Dimitri
Fontaine, per a report from Thom Brown
- Fix a few small bugs in yesterday's event trigger patch. Dimitri
- Update comments and output for event_trigger regression test.
- Further documentation tweaks for event triggers. Per discussion
between Dimitri Fontaine, myself, and others.
- pg_isready. New command-line utility to test whether a server is
ready to accept connections. Phil Sorber, reviewed by Michael
Paquier and Peter Eisentraut
- Clarify that connection parameters aren't totally meaningless for
PQping. Per discussion with Phil Sorber.
- Make it easy to time out pg_isready, and make the default 3 seconds.
Along the way, add a missing line to the help message. Phil Sorber,
reviewed by Fujii Masao
Tom Lane pushed:
- Add infrastructure for storing a VARIADIC ANY function's VARIADIC
flag. Originally we didn't bother to mark FuncExprs with any
indication whether VARIADIC had been given in the source text,
because there didn't seem to be any need for it at runtime.
However, because we cannot fold a VARIADIC ANY function's arguments
into an array (since they're not necessarily all the same type), we
do actually need that information at runtime if VARIADIC ANY
functions are to respond unsurprisingly to use of the VARIADIC
keyword. Add the missing field, and also fix ruleutils.c so that
VARIADIC ANY function calls are dumped properly. Extracted from a
larger patch that also fixes concat() and format() (the only two
extant VARIADIC ANY functions) to behave properly when VARIADIC is
specified. This portion seems appropriate to review and commit
separately. Pavel Stehule
- Fix failure to rotate postmaster log file for size reasons on
Windows. When we eliminated "unnecessary" wakeups of the syslogger
process, we broke size-based logfile rotation on Windows, because on
that platform data transfer is done in a separate thread. While
non-Windows platforms would recheck the output file size after every
log message, Windows only did so when the control thread woke up for
some other reason, which might be quite infrequent. Per bug #7814
from Tsunezumi. Back-patch to 9.2 where the problem was introduced.
- Fix SPI documentation for new handling of ExecutorRun's count
parameter. Since 9.0, the count parameter has only limited the
number of tuples actually returned by the executor. It doesn't
affect the behavior of INSERT/UPDATE/DELETE unless RETURNING is
specified, because without RETURNING, the ModifyTable plan node
doesn't return control to execMain.c for each tuple. And we only
check the limit at the top level. While this behavioral change was
unintentional at the time, discussion of bug #6572 led us to the
conclusion that we prefer the new behavior anyway, and so we should
just adjust the docs to match rather than change the code.
Accordingly, do that. Back-patch as far as 9.0 so that the docs
match the code in each branch.
- Fix concat() and format() to handle VARIADIC-labeled arguments
correctly. Previously, the VARIADIC labeling was effectively
ignored, but now these functions act as though the array elements
had all been given as separate arguments. Pavel Stehule
- Change plan caching to honor, not resist, changes in search_path.
In the initial implementation of plan caching, we saved the active
search_path when a plan was first cached, then reinstalled that path
anytime we needed to reparse or replan. The idea of that was to try
to reselect the same referenced objects, in somewhat the same way
that views continue to refer to the same objects in the face of
schema or name changes. Of course, that analogy doesn't bear close
inspection, since holding the search_path fixed doesn't cope with
object drops or renames. Moreover sticking with the old path seems
to create more surprises than it avoids. So instead of doing that,
consider that the cached plan depends on search_path, and force
reparse/replan if the active search_path is different than it was
when we last saved the plan. This gets us fairly close to having
"transparency" of plan caching, in the sense that the cached
statement acts the same as if you'd just resubmitted the original
query text for another execution. There are still some corner cases
where this fails though: a new object added in the search path
schema(s) might capture a reference in the query text, but we'd not
realize that and force a reparse. We might try to fix that in the
future, but for the moment it looks too expensive and complicated.
- Fix plpython's handling of functions used as triggers on multiple
tables. plpython tried to use a single cache entry for a trigger
function, but it needs a separate cache entry for each table the
trigger is applied to, because there is table-dependent data in
there. This was done correctly before 9.1, but commit
46211da1b84bc3537e799ee1126098e71c2428e8 broke it by simplifying the
lookup key from "function OID and triggered table OID" to "function
OID and is-trigger boolean". Go back to using both OIDs as the
lookup key. Per bug report from Sandro Santilli. Andres Freund
- Make LATERAL implicit for functions in FROM. The SQL standard does
not have general functions-in-FROM, but it does allow UNNEST() there
(see the production), and the semantics
of that are defined to include lateral references. So spec
compliance requires allowing lateral references within UNNEST() even
without an explicit LATERAL keyword. Rather than making UNNEST() a
special case, it seems best to extend this flexibility to any
function-in-FROM. We'll still allow LATERAL to be written
explicitly for clarity's sake, but it's now a noise word in this
context. In theory this change could result in a change in behavior
of existing queries, by allowing what had been an outer reference in
a function-in-FROM to be captured by an earlier FROM-item at the
same level. However, all pre-9.3 PG releases have a bug that causes
them to match variable references to earlier FROM-items in
preference to outer references (and then throw an error). So no
previously-working query could contain the type of ambiguity that
would risk a change of behavior. Per a suggestion from Andrew
Gierth, though I didn't use his patch.
Peter Eisentraut pushed:
- doc: Fix declared number of columns in table. This was broken in
Bruce Momjian pushed:
- pg_upgrade: remove --single-transaction usage. With AtEOXact
applied, --single-transaction makes pg_restore slower, and has the
potential to require lock table configuration, so remove the
argument. Per suggestion from Tom.
- pg_upgrade: report failed cluster name. When pg_upgrade can't find
required pg_controldata information, report _which_ cluster is
failing, with this message: The %s cluster lacks some required
- pg_upgrade: detect stale postmaster.pid lock files. If the
postmaster.pid lock file exists, try starting/stopping the cluster
to check if the lock file is valid. Per request from Tom.
- doc: correct sepgsql doc about permission checking of CASCADE.
Backpatch to 9.2. Patch from Kohei KaiGai
- doc: add mention of ssi read anomolies to mvcc docs. From Jeff
Davis, modified by Kevin Grittner
- doc: improve wording of "foreign data server" in file-fdw docs.
Backpatch to 9.2 Shigeru HANADA
- docs: In ecpg, clarify how username/password colon parameters are
used. Backpatch to 9.2. Patch from Alan B
- doc: split search_path index entries into separate secondaries.
Karl O. Pinc
- doc: adjust search_path secondary index mention. per Tom Lane
- doc: merge ecpg username/password example into C comment.
Backpatch to 9.2, per Tom Lane
- doc: mention commit_delay is only honored if fsync is enabled. per
- doc: revert 80c20fcf3df17309b3c131962045825f42e45bc7 and
0e93959a70ac6e7c7858d1d6fb00645e7540a1cc. Revert patch that
modified doc index mentions of search_path. Per Peter Eisentraut.
- Issue ERROR if FREEZE mode can't be honored by COPY. Previously
non-honored FREEZE mode was ignored. This also issues an
appropriate error message based on the cause of the failure, per
suggestion from Tom. Additional regression test case added.
- Allow CREATE TABLE IF EXIST so succeed if the schema is nonexistent
Previously, CREATE TABLE IF EXIST threw an error if the schema was
nonexistent. This was done by passing 'missing_ok' to the function
that looks up the schema oid.
- Update LookupExplicitNamespace() comments; commit message update.
Also, commit 7e2322dff30c04d90c0602d2b5ae24b4881db88b affected DROP
TABLE IF EXISTS, not CREATE TABLE IF EXISTS.
- Update comments in new DROP IF EXISTS code; commit message update.
DROP IF EXISTS with a missing schema in commit
7e2322dff30c04d90c0602d2b5ae24b4881db88b applies not only to tables,
but to DROP IF EXISTS with missing schemas for indexes, views,
sequences, and foreign tables. Yeah!
Heikki Linnakangas pushed:
- Fix more issues with cascading replication and timeline switches.
When a standby server follows the master using WAL archive, and it
chooses a new timeline (recovery_target_timeline='latest'), it only
fetches the timeline history file for the chosen target timeline,
not any other history files that might be missing from pg_xlog. For
example, if the current timeline is 2, and we choose 4 as the new
recovery target timeline, the history file for timeline 3 is not
fetched, even if it's part of this server's history. That's enough
for the standby itself - the history file for timeline 4 includes
timeline 3 as well - but if a cascading standby server wants to
recover to timeline 3, it needs the history file. To fix, when a new
recovery target timeline is chosen, try to copy any missing history
files from the archive to pg_xlog between the old and new target
timeline. A second similar issue was with the WAL files. When a
standby recovers from archive, and it reaches a segment that
contains a switch to a new timeline, recovery fetches only the WAL
file labelled with the new timeline's ID. The file from the new
timeline contains a copy of the WAL from the old timeline up to the
point where the switch happened, and recovery recovers it from the
new file. But in streaming replication, walsender only tries to read
it from the old timeline's file. To fix, change walsender to read it
from the new file, so that it behaves the same as recovery in that
sense, and doesn't try to open the possibly nonexistent file with
the old timeline's ID.
- Implement pg_unreachable() on MSVC.
- Also fix rotation of csvlog on Windows. Backpatch to 9.2, like the
- Add some randomness to the choice of which GiST page to insert to.
When descending the tree for an insert, and there are multiple
equally good pages we could insert to, make the choice in random.
Previously, we would always choose the tuple with lowest offset
number. That meant that when two non-leaf pages overlap - in the
extreme case they might have exactly the same key - all but the
first such page went unused. That wasn't optimal for space usage; if
you deleted some tuples from the non-first pages, the space would
never be reused. With this patch, the other pages are sometimes
chosen too, although there's still a heavy bias towards low-offset
tuples, so that we don't lose cache locality when doing a lot of
inserts with similar keys. Original idea by Alexander Korotkov,
although this patch version was written by me and copy-edited by Tom
- Add prosecdef to \df+ output. Jon Erdman, reviewed by Phil Sorber
and Stephen Frost.
Andrew Dunstan pushed:
- Gitignore vcxproj files. Per request from Craig Ringer.
- Use correct output device for Windows prompts. This ensures that
mapping of non-ascii prompts to the correct code page occurs. Bug
report and original patch from Alexander Law, reviewed and reworked
by Noah Misch. Backpatch to all live branches.
Simon Riggs pushed:
- Docs shouldn't say HOT Standby. Not an acronym. Jeff Janes
- Fix rare missing cancellations in Hot Standby. The machinery around
XLOG_HEAP2_CLEANUP_INFO failed to correctly pass through the
necessary information on latestRemovedXid, avoiding cancellations in
some infrequent concurrent update/cleanup scenarios. Backpatchable
fix to 9.0 Detailed bug report and fix by Noah Misch, backpatchable
version by me.
Magnus Hagander pushed:
- Make pg_dump exclude unlogged table data on hot standby slaves.
Noted by Joe Van Dyk
Michael Meskes pushed:
- Made ecpglib use translated messages. Bug reported and fixed by
Chen Huajun chenhj AT cn DOT fujitsu DOT com.
== Rejected Patches (for now) ==
No one was disappointed this week :-)
== Pending Patches ==
Ian Lawrence Barwick sent in a patch to fix an infelicity in psql's \s
with absolute path names.
Amit Kapila sent in two more revisions of a patch to improve
performance by reducing the volume of WAL for an update operation.
Amit Kapila sent in another revision of a patch to compute max LSN of
Jeff Davis sent in another revision of a patch to remove
Josh Kupershmidt sent in a patch to fix pg_ctl with relative paths.
David Fetter sent in a patch on behalf of Andrew (RhodiumToad) Gierth
to clean up the API in addRangeTableEntryForFunction in preparation
for adding WITH ORDINALITY to UNNEST per SQL:2011 and to other
functions for consistency.
David Fetter sent in a patch on behalf of Andrew (RhodiumToad) Gierth
to add WITH ORDINALITY to UNNEST and other functions in the FROM
Amit Kapila sent in two more revisions of a patch to enable changing
postgresql.conf via SQL.
Michael Paquier sent in two more revisions of a patch to overhaul
Heikki Linnakangas and Alexander Korotkov traded patches to enable
indexing regexes which can be expressed as deterministic finite
Etsuro Fujita sent in another revision of a patch to add input and
output filtering for COPY.
Jeevan Chalke sent in another revision of a patch to fix an infelicity
in the interaction of unlogged tables and GiST indexing.
Phil Sorber sent in a patch to add a Makefile dependency in
bin/scripts for libpgport.
Pavel Stehule sent in a patch to make --echo-hidden work for \sf in
Jeff Janes sent in a patch to fix the capitalization in bgwriter's
references to hot standby.
Craig Ringer and Noah Misch traded patches to make MS Visual Studio
2012 work to compile PostgreSQL.
Steve Singer and Andres Freund traded patches to add logical changeset
Hari Babu sent in a patch to add an option to pass a connection string
Heikki Linnakangas and Tom Lane traded patches to fix bloating in
Jameison Martin sent in another revision of a patch to optimize the
case of many trailing NULLs in a table.
Xi Wang sent in a patch to fix x + y < x overflow checks which icc
mis-handles with wriggle room from the C standard.
Xi Wang sent in a patch to fix x +1 < x overflow checks.
Xi Wang sent in a patch to fix overflow checks in repeat().
Simon Riggs sent in a patch to skip checkpoint in 'pg_ctl promote -m
Kevin Grittner sent in another revision of a patch to implement
Michael Paquier sent in another revision of a patch to implement
Alvaro Herrera sent in a patch to set the priority for auto-wraparound
tables higher than others.
Jeff Davis sent in another revision of a patch to enable page
Dhruv Ahuja sent in a patch to change the pg_ctl promote exit status.
Hannu Krosing sent in a WIP patch to solve the typeio function
recursion problem in PL/python by simply testing if the function we
are currently in is a type-io function (fn_oid ==
argTypeStruct->typoutput ... )
Andrew Dunstan sent in another revision of a patch to implement the
Marko (johto) Tiikkaja sent in another revision of a patch to allow
PL/pgsql to check for STRICT-ness in PL/pgsql SELECTs.
Satoshi Nagayasu sent in another revision of a patch to upgrade
pgbench to work better in 64-bit environments.
KaiGai Kohei sent in another revision of a patch to implement
OAT_POST_ALTER object access hooks.
Pavan Deolasee sent in another revision of a patch to set the
visibility map in VACUUM's second phase.
Marko (johto) Tiikkaja sent in two more revisions of a patch to add an
option to pg_dump to pretty-print views.
Dimitri Fontaine sent in another revision of a patch to add in-catalog
Extension Scripts and Control parameters.
Tatsuo Ishii sent in another revision of a patch to allow writing only
aggregates to the pgbench log.
Peter Geoghegan sent in another revision of a patch to make a firm
recommendation about the value of commit_delay.
Sent via pgsql-announce mailing list ([email protected])
To make changes to your subscription: