== PostgreSQL Weekly News - July 22 2012 ==

== PostgreSQL Weekly News - July 22 2012 ==

== PostgreSQL Jobs for July ==

http://archives.postgresql.org/pgsql-jobs/2012-07/threads.php

== PostgreSQL Local ==

PostgreSQL Session will be held on October 4th, 2012, in Paris,
France. More information at:
http://www.postgresql-sessions.org/en/4/

PostgreSQL Conference Europe 2012 will be in Prague, Czech Republic
on October 23-26. The call for papers is open.
http://2012.pgconf.eu/

PostgreSQL Day Argentina 2012 will be held on November 13th in Bernal,
Buenos Aires, at the National University of Quilmes. It will cover
topics for PostgreSQL users, developers and contributors, as well as
decision and policy makers. For more information about the
conference, please see the website at
http://www.pgday.com.ar/quilmes2012?lang=en

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

Tom Lane pushed:

- Avoid pre-determining index names during CREATE TABLE LIKE parsing.
Formerly, when trying to copy both indexes and comments, CREATE
TABLE LIKE had to pre-assign names to indexes that had comments,
because it made up an explicit CommentStmt command to apply the
comment and so it had to know the name for the index. This creates
bad interactions with other indexes, as shown in bug #6734 from
Daniele Varrazzo: the preassignment logic couldn't take any other
indexes into account so it could choose a conflicting name. To fix,
add a field to IndexStmt that allows it to carry a comment to be
assigned to the new index. (This isn't a user-exposed feature of
CREATE INDEX, only an internal option.) Now we don't need
preassignment of index names in any situation. I also took the
opportunity to refactor DefineIndex to accept the IndexStmt as such,
rather than passing all its fields individually in a mile-long
parameter list. Back-patch to 9.2, but no further, because it seems
too dangerous to change IndexStmt or DefineIndex's API in released
branches. The bug exists back to 9.0 where CREATE TABLE LIKE grew
the ability to copy comments, but given the lack of prior complaints
we'll just let it go unfixed before 9.2.
http://git.postgresql.org/pg/commitdiff/c92be3c0595d504a1516e7e158d085150ff1c4dc

- Put back storage/proc.h in postmaster.c. I took this out thinking
it wasn't needed anymore, but the EXEC_BACKEND code still needs it.
Per buildfarm.
http://git.postgresql.org/pg/commitdiff/57b9bdda39e05d127f6930045d289ffc9935c971

- Improve coding around the fsync request queue. In all branches back
to 8.3, this patch fixes a questionable assumption in
CompactCheckpointerRequestQueue/CompactBgwriterRequestQueue that
there are no uninitialized pad bytes in the request queue structs.
This would only cause trouble if (a) there were such pad bytes,
which could happen in 8.4 and up if the compiler makes enum
ForkNumber narrower than 32 bits, but otherwise would require
not-currently-planned changes in the widths of other typedefs; and
(b) the kernel has not uniformly initialized the contents of shared
memory to zeroes. Still, it seems a tad risky, and we can easily
remove any risk by pre-zeroing the request array for ourselves. In
addition to that, we need to establish a coding rule that struct
RelFileNode can't contain any padding bytes, since such structs are
copied into the request array verbatim. (There are other places
that are assuming this anyway, it turns out.) In 9.1 and up, the
risk was a bit larger because we were also effectively assuming that
struct RelFileNodeBackend contained no pad bytes, and with fields of
different types in there, that would be much easier to break.
However, there is no good reason to ever transmit fsync or delete
requests for temp files to the bgwriter/checkpointer, so we can
revert the request structs to plain RelFileNode, getting rid of the
padding risk and saving some marginal number of bytes and cycles in
fsync queue manipulation while we are at it. The savings might be
more than marginal during deletion of a temp relation, because the
old code transmitted an entirely useless but nonetheless
expensive-to-process ForgetRelationFsync request to the background
process, and also had the background process perform the file
deletion even though that can safely be done immediately. In
addition, make some cleanup of nearby comments and small
improvements to the code in
CompactCheckpointerRequestQueue/CompactBgwriterRequestQueue.
http://git.postgresql.org/pg/commitdiff/73b796a52c50d6f44400c99eff1a01c89d08782f

- Improve pg_upgrade's load_directory() function. Error out on
out-of-memory, rather than returning -1, which the sole existing
caller wasn't checking for anyway. There doesn't seem to be any
use-case for making the caller check for failure here. Detect
failure return from readdir(). Use a less platform-dependent method
of calculating the entrysize. It's possible, but not yet confirmed,
that this explains bug #6733, in which Mike Wilson reports a
pg_upgrade crash that did not occur in 9.1. (Note that
load_directory is effectively new code in 9.2, at least on platforms
that have scandir().) Fix up comments, avoid uselessly using two
counters, reduce the number of realloc calls to something sane.
http://git.postgresql.org/pg/commitdiff/3d6ec663bb701b7192822f83a6a1cf4f7574d67e

- Get rid of useless global variable in pg_upgrade. Since the
scandir() emulation was taken out of pg_upgrade, there's no longer
any need for scandir_file_pattern to exist as a global variable.
Replace it with a local in the one remaining function that was
making use of it.
http://git.postgresql.org/pg/commitdiff/faf26bf1175530cc97ce3e804ff10dc2be7026d3

- Fix management of pendingOpsTable in auxiliary processes. mdinit()
was misusing IsBootstrapProcessingMode() to decide whether to create
an fsync pending-operations table in the current process. This led
to creating a table not only in the startup and checkpointer
processes as intended, but also in the bgwriter process, not to
mention other auxiliary processes such as walwriter and walreceiver.
Creation of the table in the bgwriter is fatal, because it absorbs
fsync requests that should have gone to the checkpointer; instead
they just sit in bgwriter local memory and are never acted on. So
writes performed by the bgwriter were not being fsync'd which could
result in data loss after an OS crash. I think there is no live bug
with respect to walwriter and walreceiver because those never
perform any writes of shared buffers; but the potential is there for
future breakage in those processes too. To fix, make
AuxiliaryProcessMain() export the current process's AuxProcType as a
global variable, and then make mdinit() test directly for the types
of aux process that should have a pendingOpsTable. Having done
that, we might as well also get rid of the random bool flags such as
am_walreceiver that some of the aux processes had grown. (Note that
we could not have fixed the bug by examining those variables in
mdinit(), because it's called from BaseInit() which is run by
AuxiliaryProcessMain() before entering any of the
process-type-specific code.) Back-patch to 9.2, where the problem
was introduced by the split-up of bgwriter and checkpointer
processes. The bogus pendingOpsTable exists in walwriter and
walreceiver processes in earlier branches, but absent any evidence
that it causes actual problems there, I'll leave the older branches
alone.
http://git.postgresql.org/pg/commitdiff/4a9c30a8a1d3a786abc4b8d95f0182463f66f919

- Fix statistics breakage from bgwriter/checkpointer process split.
ForwardFsyncRequest() supposed that it could only be called in
regular backends, which used to be true; but since the splitup of
bgwriter and checkpointer, it is also called in the bgwriter. We do
not want to count such calls in pg_stat_bgwriter.buffers_backend
statistics, so fix things so that they aren't. (It's worth noting
here that this implies an alarmingly large increase in the expected
amount of cross-process fsync request traffic, which may well mean
that the process splitup was not such a hot idea.)
http://git.postgresql.org/pg/commitdiff/80e373c3a8c43812bdc98fe0d433b9990acce5ad

- Send only one FORGET_RELATION_FSYNC request when dropping a
relation. We were sending one per fork, but a little bit of
refactoring allows us to send just one request with forknum ==
InvalidForkNumber. This not only reduces pressure on the
shared-memory request queue, but saves repeated traversals of the
checkpointer's hash table.
http://git.postgresql.org/pg/commitdiff/3072b7bade26d4cf72ad453ad7d3323927b1ea64

- Rethink checkpointer's fsync-request table representation. Instead
of having one hash table entry per relation/fork/segment, just have
one per relation, and use bitmapsets to represent which specific
segments need to be fsync'd. This eliminates the need to scan the
whole hash table to implement FORGET_RELATION_FSYNC, which fixes the
O(N^2) behavior recently demonstrated by Jeff Janes for cases
involving lots of TRUNCATE or DROP TABLE operations during a single
checkpoint cycle. Per an idea from Robert Haas.
(FORGET_DATABASE_FSYNC still sucks, but since dropping a database is
a pretty expensive operation anyway, we'll live with that.) In
passing, improve the delayed-unlink code: remove the pass over the
list in mdpreckpt, since it wasn't doing anything for us except
supporting a useless Assert in mdpostckpt, and fix mdpostckpt so
that it will absorb fsync requests every so often when clearing a
large backlog of deletion requests.
http://git.postgresql.org/pg/commitdiff/be86e3dd5b42c33387ae976c014e6276c9439f7f

- Fix whole-row Var evaluation to cope with resjunk columns (again).
When a whole-row Var is reading the result of a subquery, we need it
to ignore any "resjunk" columns that the subquery might have
evaluated for GROUP BY or ORDER BY purposes. We've hacked this area
before, in commit 68e40998d058c1f6662800a648ff1e1ce5d99cba, but that
fix only covered whole-row Vars of named composite types, not those
of RECORD type; and it was mighty klugy anyway, since it just
assumed without checking that any extra columns in the result must
be resjunk. A proper fix requires getting hold of the subquery's
targetlist so we can actually see which columns are resjunk
(whereupon we can use a JunkFilter to get rid of them). So bite the
bullet and add some infrastructure to make that possible. Per
report from Andrew Dunstan and additional testing by Merlin Moncure.
Back-patch to all supported branches. In 8.3, also back-patch
commit 292176a118da6979e5d368a4baf27f26896c99a5, which for some
reason I had not done at the time, but it's a prerequisite for this
change.
http://git.postgresql.org/pg/commitdiff/8e617e29aaccfdd1b85af7f50dc83aa6dd7ef550

- Account for SRFs in targetlists in planner rowcount estimates. We
made use of the ROWS estimate for set-returning functions used in
FROM, but not for those used in SELECT targetlists; which is a bit
of an oversight considering there are common usages that require the
latter approach. Improve that. (I had initially thought it might
be worth folding this into cost_qual_eval, but after investigation
concluded that that wouldn't be very helpful, so just do it
separately.) Per complaint from David Johnston. Back-patch to 9.2,
but not further, for fear of destabilizing plan choices in existing
releases.
http://git.postgresql.org/pg/commitdiff/31c7c642b6419b43eff903285e3da65e3f1901d6

- Suppress volatile-related warning seen in some compilers. Antique
versions of gcc complain about vars that are initialized outside
PG_TRY and then modified within it. Rather than marking the var
volatile, expend one more line of code.
http://git.postgresql.org/pg/commitdiff/1f115d98b984c80e635392aed10cc6185d1f8f4b

- Use --nosync during make check's initdb call. We left this out of
commit b966dd6c4228d696b291c1cdcb5ab8c8475fefa8 so as to get some
more buildfarm testing of the new fsync code in initdb. But since
no problems have turned up, it's probably time to save the cycles.
http://git.postgresql.org/pg/commitdiff/2c4f5b4bc5385b37d062451642abd384536eeeb3

- Improve copydir() code for the case that fsync is off. We should
avoid calling sync_file_range or posix_fadvise in this case, since
(a) we don't really care if the data gets synced, and might as well
save the kernel calls; (b) at least on Linux we know that the kernel
might block us until it's scheduled the write. Also, avoid making a
useless second traversal of the directory tree if we're not actually
going to call fsync(2) after all.
http://git.postgresql.org/pg/commitdiff/2d46a57ddcad394e514bbefb193a4a03e766f163

- Fix name collision between concurrent regression tests. Commit
f5bcd398addcbeb785f0513cf28cba5d1ecd2c8a introduced a test using a
table named "circles" in inherit.sql. Unfortunately, the
concurrently executed constraints test was already using that table
name, so the parallel regression tests would sometimes fail. Rename
table to dodge the problem. Per buildfarm.
http://git.postgresql.org/pg/commitdiff/b71258af5613e627bb2b3b1e1874abe5c0226dc4

Peter Eisentraut pushed:

- Add comment why seemingly dead code is necessary
http://git.postgresql.org/pg/commitdiff/a76c857eba977a91a07ab752d4811eb5734f0b5c

- Remove unreachable code. The Solaris Studio compiler warns about
these instances, unlike more mainstream compilers such as gcc. But
manual inspection showed that the code is clearly not reachable, and
we hope no worthy compiler will complain about removing this code.
http://git.postgresql.org/pg/commitdiff/dd16f9480ac67ab0c6b0102d110cd5121ed9ab46

- Show step titles in the pg_upgrade man page. The upstream XSLT
stylesheets missed that case. found by Álvaro Herrera
http://git.postgresql.org/pg/commitdiff/d6ce58c01056fa0f475ea468f1633890f6a1d74c

- PL/Python: Remove PLy_result_ass_item. It is apparently no longer
used after the new slicing support was implemented
(a97207b6908f1d4a7d19b37b818367bb0171039f), so let's remove the dead
code and see if anything cares.
http://git.postgresql.org/pg/commitdiff/71f2dd23210f9607d1584fad89e0f8df9750e921

- pg_dump: Simplify mkdir() error checking. mkdir() can check for
errors itself. We don't need to code that ourselves again.
http://git.postgresql.org/pg/commitdiff/8ca03aa41488a9f08dffe64724eb04692174350c

Alvaro Herrera pushed:

- Introduce timeout handling framework. Management of timeouts was
getting a little cumbersome; what we originally had was more than
enough back when we were only concerned about deadlocks and query
cancel; however, when we added timeouts for standby processes, the
code got considerably messier. Since there are plans to add more
complex timeouts, this seems a good time to introduce a central
timeout handling module. External modules register their timeout
handlers during process initialization, and later enable and disable
them as they see fit using a simple API; timeout.c is in charge of
keeping track of which timeouts are in effect at any time,
installing a common SIGALRM signal handler, and calling setitimer()
as appropriate to ensure timely firing of external handlers.
timeout.c additionally supports pluggable modules to add their own
timeouts, though this capability isn't exercised anywhere yet.
Additionally, as of this commit, walsender processes are aware of
timeouts; we had a preexisting bug there that made those ignore
SIGALRM, thus being subject to unhandled deadlocks, particularly
during the authentication phase. This has already been fixed in
back branches in commit 0bf8eb2a, which see for more details. Main
author: Zoltán Böszörményi. Some review and cleanup by Álvaro
Herrera. Extensive reworking by Tom Lane.
http://git.postgresql.org/pg/commitdiff/f34c68f09671c4566854c7e20e9253d4f335c0b0

- Remove recently added PL/Perl encoding tests. These only pass
cleanly on UTF8 and SQL_ASCII encodings, besides the Japanese
encoding in which they were originally written, which is clearly not
good enough. Since the functionality they test has not ever been
tested from PL/Perl, the best answer seems to be to remove the new
tests completely. Per buildfarm results and ensuing discussion.
http://git.postgresql.org/pg/commitdiff/65558995a214026ec544326465711588eafd2e95

- connoinherit may be true only for CHECK constraints. The code was
setting it true for other constraints, which is bogus. Doing so
caused bogus catalog entries for such constraints, and in particular
caused an error to be raised when trying to drop a constraint of
types other than CHECK from a table that has children, such as
reported in bug #6712. In 9.2, additionally ignore
connoinherit=true for other constraint types, to avoid having to
force initdb; existing databases might already contain bogus catalog
entries. Includes a catversion bump (in HEAD only). Bug report
from Miroslav Šulc Analysis from Amit Kapila and Noah Misch; Amit
also contributed the patch.
http://git.postgresql.org/pg/commitdiff/f5bcd398addcbeb785f0513cf28cba5d1ecd2c8a

Robert Haas pushed:

- Syntax support and documentation for event triggers. They don't
actually do anything yet; that will get fixed in a follow-on commit.
But this gets the basic infrastructure in place, including
CREATE/ALTER/DROP EVENT TRIGGER; support for COMMENT, SECURITY
LABEL, and ALTER EXTENSION .. ADD/DROP EVENT TRIGGER; pg_dump and
psql support; and documentation for the anticipated initial feature
set. Dimitri Fontaine, with review and a bunch of additional
hacking by me. Thom Brown extensively reviewed earlier versions of
this patch set, but there's not a whole lot of that code left in
this commit, as it turns out.
http://git.postgresql.org/pg/commitdiff/3855968f328918b6cd1401dd11d109d471a54d40

- Make new event trigger facility actually do something. Commit
3855968f328918b6cd1401dd11d109d471a54d40 added syntax, pg_dump, psql
support, and documentation, but the triggers didn't actually fire.
With this commit, they now do. This is still a pretty basic
facility overall because event triggers do not get a whole lot of
information about what the user is trying to do unless you write
them in C; and there's still no option to fire them anywhere except
at the very beginning of the execution sequence, but it's better
than nothing, and a good building block for future work. Along the
way, add a regression test for ALTER LARGE OBJECT, since testing of
event triggers reveals that we haven't got one. Dimitri Fontaine
and Robert Haas
http://git.postgresql.org/pg/commitdiff/3a0e4d36ebd7f477822d5bae41ba121a40d22ccc

- Temporary patch to try to debug why event trigger patch broke
Windows. Apologies for the ugliness.
http://git.postgresql.org/pg/commitdiff/0a248208a038d7847ef98996c6b70209d782415c

- Repair plpgsql_validator breakage. Commit
3a0e4d36ebd7f477822d5bae41ba121a40d22ccc arranged to reference
stack-allocated variables after they were out of scope. That's no
good, so let's arrange to not do that after all.
http://git.postgresql.org/pg/commitdiff/0635c0b524368862da08271fbca626faf44290a2

- Revert temporary patch to debug Windows breakage. This reverts
commit 0a248208a038d7847ef98996c6b70209d782415c.
http://git.postgresql.org/pg/commitdiff/ed0af3324702685cce63aed0641b4cbb45816b50

Heikki Linnakangas pushed:

- Refactor the way code is shared between some range type functions.
Functions like range_eq, range_before etc. are exposed at the
SQL-level, but they're also used internally by the GiST consistent
support function. The code sharing was done by a hack,
TrickFunctionCall2, which relied on the knowledge that all the
functions used fn_extra the same way. This commit splits the
functions into internal versions that take a TypeCacheEntry as
argument, and thin wrappers to expose the functions at the
SQL-level. The internal versions can then be called directly and in
a less hacky way from the GiST consistent function. This is just
cosmetic, but backpatch to 9.2 anyway, to avoid having a different
version of this code in the 9.2 branch. That would make backpatching
fixes in this area more difficult. Alexander Korotkov
http://git.postgresql.org/pg/commitdiff/a7a4add6c4243cbcf50a554bce4c34cb72a344b5

Andrew Dunstan pushed:

- Remove prepared transactions from main isolation test schedule.
There is no point in running this test when prepared transactions
are disabled, which is the default. New make targets that include
the test are provided. This will save some useless waste of cycles
on buildfarm machines. Backpatch to 9.1 where these tests were
introduced.
http://git.postgresql.org/pg/commitdiff/ae55d9fbe3871a5e6309d9b91629f1b0ff2b8cba

- Remove now unneeded results file for disabled prepared transactions
case.
http://git.postgresql.org/pg/commitdiff/a1e5705c9fa6a46d5f284101765085182d3eb600

== Rejected Patches (for now) ==

No one was disappointed this week :-)

== Pending Patches ==

Fujii Masao sent in another revision of the patch to fix some issues
in pg_receivexlog.

Pavel Stehule sent in another revison of the patch to enhance error
fields.

Amit Kapila sent in a patch to enable pg_resetxlog to restore from WAL
files.

Jan Urbanski sent in two more revisions of a patch to fix the mapping
of PostgreSQL encodings to Python encodings.

Tom Lane sent in a patch to fix an issue with row literals.

Andres Freund sent in another revision of the patch to add XLogReader
functionality.

Joel Jacobson sent in a patch to make pg_dump dump overloaded
functions in a deterministic order.

Alvaro Herrera sent in two revisions of a patch to move the extra
parameters of a CHECK constraint from before the parenthesized boolean
expression to after it, clarifying what's being affected by the
parameters.

Jeff Janes sent in a patch to re-order vacuuming in pg_bench -i.

Andrew Dunstan sent in a WIP patch to add a settable ceiling on
shared_buffers.

Marko Kreen sent in another revision of the one-row-at-a-time
interface for libpq.

Zoltan Boszormenyi sent in another revision of the lock timeout patch.