== PostgreSQL Weekly News - November 22 2009 ==

== PostgreSQL Weekly News - November 22 2009 ==

A report from the Brazilian PostgreSQL conference in Portuguese is at:
http://pgcon.postgresql.org.br/2009/asl/pgcconbr-2009_prest_contas.pdf

== PostgreSQL Product News ==

pgstat 1.1 released.
http://pgfoundry.org/projects/pgstat2/

== PostgreSQL Jobs for November ==

http://archives.postgresql.org/pgsql-jobs/2009-11/threads.php

== PostgreSQL Local ==

The third edition of the Italian PostgreSQL Day will be held at the
University of Pisa on the 4th of December. Entrance is free.
Sponsors are welcome.
http://www.pgday.it/

Stefan Keller will be teach a course called, "Introducion to
PostGIS/PostgreSQL" at the University of Applied Sciences Rapperswil
(HSR) on January 14, 2010 in Zurich, Switzerland.
http://www.gis.hsr.ch/wiki/Agenda

FOSDEM 2010 will be in Brussels, Belgium on February 6-7, 2010.
http://www.fosdem.org/

Chemnitzer Linuxtage will be in Chemnitz, Germany on March 13-14, 2010.
http://chemnitzer.linux-tage.de/

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

== Applied Patches ==

Tom Lane committed:

- In pgsql/src/backend/optimizer/plan/setrefs.c, while doing the final
setrefs.c pass over a plan tree, try to match up non-Var sort/group
expressions using ressortgroupref labels instead of depending
entirely on equal()-ity of the upper node's tlist expressions to the
lower node's. This avoids emitting the wrong outputs in cases where
there are textually identical volatile sort/group expressions, as
for example:
SELECT DISTINCT random(), random() FROM generate_series(1,10);
Per report from Andrew Gierth. Backpatch to 8.4. Arguably this is
wrong all the way back, but the only known case where there's an
observable problem is when using hash aggregation to implement
DISTINCT, which is new as of 8.4. So for the moment I'll refrain
from backpatching further.

- Provide a parenthesized-options syntax for VACUUM, analogous to that
recently adopted for EXPLAIN. This will allow additional options to
be implemented in future without having to make them fully-reserved
keywords. The old syntax remains available for existing options,
however. Itagaki Takahiro

- Add a hook to CREATE/ALTER ROLE to allow an external module to check
the strength of database passwords, and create a sample
implementation of such a hook as a new contrib module
"passwordcheck". Laurenz Albe, reviewed by Takahiro Itagaki.

- In pgsql/src/backend/postmaster/syslogger.c, fix memory leak in
syslogger: logfile_rotate() would leak a copy of the output filename
if CSV logging was enabled and only one of the two possible output
files got rotated during a particular call (which would, in fact,
typically be the case during a size-based rotation). This would
amount to about MAXPGPATH (1KB) per rotation, and it's been there
since the CSV code was put in, so it's surprising that nobody
noticed it before. Per bug #5196 from Thomas Poindessous.

- Add a WHEN clause to CREATE TRIGGER, allowing a boolean expression
to be checked to determine whether the trigger should be fired. For
BEFORE triggers this is mostly a matter of spec compliance; but for
AFTER triggers it can provide a noticeable performance improvement,
since queuing of a deferred trigger event and re-fetching of the
row(s) at end of statement can be short-circuited if the trigger
does not need to be fired. Takahiro Itagaki, reviewed by KaiGai
Kohei.

- Fix display and dumping of UPDATE OR TRUNCATE triggers (a bizarre
combination maybe, but we should get it right). Bug noted while
reviewing TRIGGER WHEN patch. Already fixed in HEAD.

- Refactor ecpg grammar so that it uses the core grammar's
unreserved_keyword list, minus a few specific words that have to be
treated specially. This replaces a hard-wired list of keywords that
would have needed manual maintenance, and was not getting it. The
8.4 coding was already missing these words, causing ecpg to
incorrectly treat them as reserved words: CALLED, CATALOG, DEFINER,
ENUM, FOLLOWING, INVOKER, OPTIONS, PARTITION, PRECEDING, RANGE,
SECURITY, SERVER, UNBOUNDED, WRAPPER. In HEAD we were additionally
missing COMMENTS, FUNCTIONS, SEQUENCES, TABLES. Per gripe from
Bosco Rama.

- Improve psql's tabular display of wrapped-around data by inserting
markers in the formerly-always-blank columns just to left and right
of the data. Different marking is used for a line break caused by a
newline in the data than for a straight wraparound. A newline break
is signaled by a "+" in the right margin column in ASCII mode, or a
carriage return arrow in UNICODE mode. Wraparound is signaled by a
dot in the right margin as well as the following left margin in
ASCII mode, or an ellipsis symbol in the same places in UNICODE
mode. "\pset linestyle old-ascii" is added to make the previous
behavior available if anyone really wants it. In passing, this
commit also cleans up a few regression test files that had
unintended spacing differences from the current actual output.
Roger Leigh, reviewed by Gabrielle Roth and other members of PDXPUG.

- In pgsql/src/test/regress/expected/xmlmap.out, ooops, forgot to
check the libxml cases for psql output wrapping change.

- In pgsql/src/test/regress/pg_regress.c, remove -w
(--ignore-all-space) option from pg_regress's diff calls. We have
used -w for a long time as a means of reducing the reported diff
volume when one element of a result table isn't of the expected
width. However, most of the time the results just pass anyway, so
this isn't as important as it once was. Meanwhile, the risk of
missing potentially significant deviations has gone up, particularly
with psql's ability to report error cursor positions. So, let's
switch over to space-sensitive comparisons. Per my proposal of
yesterday. (All the expected files that I can test here seem to be
ready for this already, but we'll see what the buildfarm thinks
about others.)

- In pgsql/src/test/regress/output/largeobject_1.source, adjust
expected-results spacing per buildfarm results.

- In pgsql/doc/src/sgml/ref/psql-ref.sgml, assorted wordsmithing on
the documentation of \pset --- try to make it a bit more consistent
and less obviously written by different people at different times.

Peter Eisentraut committed:

- In pgsql/src/bin/pg_dump/pg_restore.c, typo: dump -> restore. Fixed
in 8.4 and 8.5 Author: Guillaume Lelarge <guillaume(at)lelarge(dot)info>

- In pgsql/src/bin/psql/mainloop.c, ignore UTF-8-encoded Unicode
byte-order mark at the beginning of a file if the client encoding is
UTF-8. A limited version of a patch proposed by Itagaki Takahiro

Magnus Hagander committed:

- Revert backpatch of inheritable-ACE patch for Win32, since it broke
compatibility with pre-Windows 2000 versions.

Heikki Linnakangas committed:

- In pgsql/src/backend/optimizer/path/allpaths.c, remove superfluous
curly brace, fixing compilation with OPTIMIZER_DEBUG. Jan Urbanski.

== Rejected Patches (for now) ==

No one was disappointed this week :-)

== Pending Patches ==

ITAGAKI Takahiro sent in another revision of the VACUUM FULL
refactoring patch.

Josh Tolley sent in three more revisions of the patch to enable
PL/Perl[U] anonymous DO blocks. Tim Bunce sent in a patch atop same
loosening some restrictions.

Zoltan Boszormenyi sent in new revisions of the patches for ECPG
implementing dynamic cursor names, SQLDA support, DESCRIBE [OUTPUT]
and out-of-scope cursor support in Informix mode.

Alvaro Herrera sent in a patch to add copydatum to SPI.

Pavel Stehule sent in another revision of the patch to allow SQL to
use psql parameters.

Heikki Linnakangas sent in a patch intended to fix a bug in the
interaction between multixacts and prepared transactions.

Alex Hunsaker sent in a patch atop Marko (johto) Tiikkaja's writeable
CTE patch along with a review.

Pavel Stehule sent in a patch to add a USING clause to OPEN FOR
EXECUTE in PL/pgsql.

Robert Haas sent in a patch atop Jeff Davis's patch to replace the
existing strategy numbers with operator OIDs.

Hannu Valtonen sent in a patch to add in-line (DO) support to
PL/PythonU.

Euler Taveira de Oliveira sent in a doc patch which adds a
PG_MODULE_MAGIC block to C examples.

George Gensure sent in a patch to reference function parameters by
name.

Joachim Wieland sent in two more revisions of the patch to add
payloads to NOTIFY.

James Pye sent in a patch to add the PL/Python3 language.

Emmanuel Cecchet sent in three more revisions of the partitioning
grammar patch.

David Wheeler sent in a doc patch for hstore.

Hiroshi Saito sent in a patch to fix win32.mak.

Andrew Dunstan sent in a WIP patch to log queries automatically in
auto-explain.