== PostgreSQL Weekly News – March 27 2011 ==

== PostgreSQL Weekly News – March 27 2011 ==

Greg Smith published a revision of the Performance Farm code, based on
Andrew Dunstan’s buildfarm code.

pgbr will be in Sao Paulo, Brazil November 3-4, 2011.

PG Session 2, on PostGIS, will be held on June 23rd in Paris. The CfP
is open!

== PostgreSQL Product News ==

Dumbo 0.50, a terminal client for PostgreSQL, released.

MyJSQLView 3.26, a GUI tool that can be used with PostgreSQL, released.

Npgsql 2.0.12beta1, a .NET data provider for PostgreSQL, released.

== PostgreSQL Jobs for March ==


== PostgreSQL Local ==

There will be a large PostgreSQL presence at the MySQL Conference and
Expo, April 11-14, 2011 in Santa Clara, California.

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: 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@fetter.org, German language
to pwn@pgug.de, Italian language to pwn@itpug.org. Spanish language
to pwn@arpug.com.ar.

== Reviews ==

== Applied Patches ==

Heikki Linnakangas pushed:

- When two base backups are started at the same time with
pg_basebackup, ensure that they use different checkpoints as the
starting point. We use the checkpoint redo location as a unique
identifier for the base backup in the end-of-backup record, and in
the backup history file name. Bug spotted by Fujii Masao.

Tom Lane pushed:

- Fix check_exclusion_constraint() to insert correct collations in

- Reimplement planner’s handling of MIN/MAX aggregate optimization
(again). Instead of playing cute games with pathkeys, just build a
direct representation of the intended sub-select, and feed it
through query_planner to get a Path for the index access. This is a
bit slower than 9.1′s previous method, since we’ll duplicate most of
the overhead of query_planner; but since the whole optimization only
applies to rather simple single-table queries, that probably won’t
be much of a problem in practice. The advantage is that we get to
do the right thing when there’s a partial index that needs the
implicit IS NOT NULL clause to be usable. Also, although this makes
planagg.c be a bit more closely tied to the ordering of operations
in grouping_planner, we can get rid of some coupling to lower-level
parts of the planner. Per complaint from Marti Raudsepp.

- Avoid potential deadlock in InitCatCachePhase2(). Opening a
catcache’s index could require reading from that cache’s own
catalog, which of course would acquire AccessShareLock on the
catalog. So the original coding here risks locking index before
heap, which could deadlock against another backend trying to get
exclusive locks in the normal order. Because InitCatCachePhase2 is
only called when a backend has to start up without a relcache init
file, the deadlock was seldom seen in the field. (And by the same
token, there’s no need to worry about any performance disadvantage;
so not much point in trying to distinguish exactly which catalogs
have the risk.) Bug report, diagnosis, and patch by Nikhil Sontakke.
Additional commentary by me. Back-patch to all supported branches.

- Throw error for indeterminate collation of an ORDER/GROUP/DISTINCT
target. This restores a parse error that was thrown (though only in
the ORDER BY case) by the original collation patch. I had removed
it in my recent revisions because it was thrown at a place where
collations now haven’t been computed yet; but I thought of another
way to handle it. Throwing the error at parse time, rather than
leaving it to be done at runtime, is good because a syntax error
pointer is helpful for localizing the problem. We can reasonably
assume that the comparison function for a collatable datatype will
complain if it doesn’t have a collation to use. Now the planner
might choose to implement GROUP or DISTINCT via hashing, in which
case no runtime error would actually occur, but it seems better to
throw error consistently rather than let the error depend on what
the planner chooses to do. Another possible objection is that the
user might specify a nondefault sort operator that doesn’t care
about collation … but that’s surely an uncommon usage, and it
wouldn’t hurt him to throw in a COLLATE clause anyway. This change
also makes the ORDER BY/GROUP BY/DISTINCT case more consistent with
the UNION/INTERSECT/EXCEPT case, which was already coded to throw
this error even though the same objections could be raised there.

- Improve reporting of run-time-detected indeterminate-collation
errors. pg_newlocale_from_collation does not have enough context to
give an error message that’s even a little bit useful, so move the
responsibility for complaining up to its callers. Also, reword
ERRCODE_INDETERMINATE_COLLATION error messages in a less jargony,
more message-style-guide-compliant fashion.

- Make initdb ignore locales for client-only encodings. While putting
such entries into pg_collation is harmless (since backends will
ignore entries that don’t match the database encoding), it’s also

- Fix ancient typo in user-defined-aggregates documentation. The
description of the initcond value for the built-in avg(float8)
aggregate has been wrong since it was written. Noted by Disc

- Improve user-defined-aggregates documentation. On closer
inspection, that two-element initcond value seems to have been a
little white lie to avoid explaining the full behavior of
float8_accum. But if people are going to expect the examples to be
exactly correct, I suppose we’d better explain. Per comment from
Thom Brown.

- Clean up handling of COLLATE clauses in index column definitions.
Ensure that COLLATE at the top level of an index expression is
treated the same as a grammatically separate COLLATE. Fix bogus
reverse-parsing logic in pg_get_indexdef.

- Fix handling of collation in SQL-language functions. Ensure that
parameter symbols receive collation from the function’s resolved
input collation, and fix inlining to behave properly. BTW, this
commit lays about 90% of the infrastructure needed to support use of
argument names in SQL functions. Parsing of parameters is now done
via the parser-hook infrastructure … we’d just need to supply a
column-ref hook …

- Fix collation handling in plpgsql functions. Make plpgsql treat the
input collation as a polymorphism variable, so that we cache
separate plans for each input collation that’s used in a particular
session, as per recent discussion. Propagate the input collation to
all collatable input parameters. I chose to also propagate the
input collation to all declared variables of collatable types, which
is a bit more debatable but seems to be necessary for
non-astonishing behavior. (Copying a parameter into a separate
local variable shouldn’t result in a change of behavior, for
example.) There is enough infrastructure here to support declaring a
collation for each local variable to override that default, but I
thought we should wait to see what the field demand is before adding
such a feature. In passing, remove exec_get_rec_fieldtype(), which
wasn’t used anywhere. Documentation patch to follow.

- Document collation handling in SQL and plpgsql functions. This is
pretty minimal but covers the bare facts.

- Fix failure to propagate collation in negate_clause(). Turns out it
was this, and not so much plpgsql, that was at fault in Stefan
Huehner’s collation-error-in-a-trigger bug report of a couple weeks

- Pass collation to makeConst() instead of looking it up internally.
In nearly all cases, the caller already knows the correct collation,
and in a number of places, the value the caller has handy is more
correct than the default for the type would be. (In particular,
this patch makes it significantly less likely that
eval_const_expressions will result in changing the exposed collation
of an expression.) So an internal lookup is both expensive and

- Clean up a few failures to set collation fields in expression nodes.
I’m not sure these have any non-cosmetic implications, but I’m not
sure they don’t, either. In particular, ensure the CaseTestExpr
generated by transformAssignmentIndirection to represent the base
target column carries the correct collation, because parse_collate.c
won’t fix that. Tweak lsyscache.c API so that we ca