== PostgreSQL Weekly News - September 14 2008 ==

== PostgreSQL Weekly News - September 14 2008 ==

CommitFest Update: thanks to more than half a dozen new reviewers
volunteering, almost all patches have been assigned reviewers. This
commitfest might actually finish in a week. Patches committed this
week include, to_date format validaton, GUC setting source display,
Boyer-Moore string searching, Command-line function definition
retrieval, Code coverage of regression tests, New permission:
Truncate, and Improvements in pg_bench, pg_dumpall, psql, PITR and
plan invalidation. Several patches have been sent back for more work,
including Windowing Functions, PL/Proxy (not going to make 8.4,
unfortunately), and Grant Insert on sequences.

Francisco J. Morosini of the Peruvian PostgreSQL group has posted
Podcast No. 3 with David Fetter, PostgreSQL, DBI-Link, PL/Perl,
experiences with PostgreSQL and discussions on the community and how
to welcome new people.
http://www.postgresql.org.pe/?q=node/24

PgUS is now excepting memberships and nominations to board.
https://www.postgresql.us/node/43

The Russian PostgreSQL community calendar is at
http://www.google.com/calendar/embed?src=4af2not88un2arkgnhhho2h7lk(at)group(dot)calendar(dot)google(dot)com&ctz=Europe/Moscow

== PostgreSQL Product News ==

Slony-I 1.2.15 released.

== PostgreSQL Jobs for September ==

http://archives.postgresql.org/pgsql-jobs/2008-09/threads.php

== PostgreSQL Local ==

The Russian PostgreSQL community will meet September 16 in Moscow.
http://postgresmen.ru/news/view/113

Selena Deckelmann and Gabrielle Roth will be giving a presentation on
filesystem performance for PostgreSQL at the Linux Plumbers'
Conference.
http://linuxplumbersconf.org/program/speakers/getspeaker.php?speaker=mwong.txt

PDXPUG will meet September 18. Tom Raney will talk about his Visual
Planner tool.
http://pugs.postgresql.org/node/468

The Prato Linux User Group is having PostgreSQL talks in September.
Schedule:
http://www.prato.linux.it/serate_a_tema_2008

PGCon Brazil 2008 will be on September 26-27 at Unicamp in Campinas.
http://pgcon.postgresql.org.br/index.en.html
Registration:
http://pgcon.postgresql.org.br/inscricoes.en.html

PgDay.fr will be October 4 in Toulouse. The Call for Papers is open:
http://www.postgresqlfr.org/?q=node/1686
Registration:
http://www.pgday.fr/doku.php/inscription

The Highload++ conference will be October 6-8 in Moscow, Russia.
Gavin Roy, Asko Oja and Maxim Boguk will talk about things PostgreSQL.
http://highload.ru

PostgreSQL Conference West 2008 will be October 10-12 at Portland
State University in Portland, Oregon.
http://www.postgresqlconference.org/
PostgreSQL Conference West (October 10-12) is now accepting registrations.
http://www.postgresqlconference.org/west08/register

PGDay.(IT|EU) 2008 will be October 17 and 18 in Prato. Registration
will open soon.
http://www.pgday.org/en/

PostgreSQL has a table at LinuxLive, Olympia, London, UK on 23-25
October, 2008. Write to Dave Page to participate.
dpage AT pgamin DOT org

== PostgreSQL in the News ==

Planet PostgreSQL: http://www.planetpostgresql.org/

PostgreSQL Weekly News is brought to you this week by David Fetter,
Josh Berkus, Michael Brewer, Joshua Drake, Devrim GUNDUZ, Francisco J.
Morosini, and Nikolay Samokhvalov.

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:

- Make pg_dump --data-only try to order the table dumps so that
foreign keys' referenced tables are dumped before the referencing
tables. This avoids failures when the data is loaded with the FK
constraints already active. If no such ordering is possible because
of circular or self-referential constraints, print a NOTICE to warn
the user about it.

- Fix a couple of problems pointed out by Fujii Masao in the
2008-Apr-05 patch for pg_stop_backup. First, it is possible that
the history file name is not alphabetically later than the last WAL
file name, so we should explicitly check that both have been
archived. Second, the previous coding would wait forever if a
checkpoint had managed to remove the WAL file before we look for it.
Simon Riggs, plus some code cleanup by me.

- Improve the plan cache invalidation mechanism to make it invalidate
plans when user-defined functions used in a plan are modified. Also
invalidate plans when schemas, operators, or operator classes are
modified; but for these cases we just invalidate everything rather
than tracking exact dependencies, since these types of objects
seldom change in a production database. Tom Lane; loosely based on
a patch by Martin Pihlak.

- In pgsql/src/pl/plpgsql/src/gram.y, fix a couple of places where the
plpgsql grammar would produce an unhelpful 'syntax error' message,
rather than something that might draw one's attention to a missing
or wrong-type variable declaration. Per recent gripe.

- In pgsql/src/interfaces/libpq/fe-exec.c, avoid using sprintf() for a
simple octal conversion in PQescapeByteaInternal. Improves
performance, per suggestion from Rudolf Leitgeb (bug #4414). The
backend did this right already, but not libpq.

- Make our parsing of INTERVAL literals spec-compliant (or at least a
heck of a lot closer than it was before). To do this, tweak
coerce_type() to pass through the typmod information when invoking
interval_in() on an UNKNOWN constant; then fix DecodeInterval to pay
attention to the typmod when deciding how to interpret a units-less
integer value. I changed one or two other details as well. I
believe the code now reacts as expected by spec for all the literal
syntaxes that are specifically enumerated in the spec. There are
corner cases involving strings that don't exactly match the set of
fields called out by the typmod, for which we might want to tweak
the behavior some more; but I think this is an area of user
friendliness rather than spec compliance. There remain some
non-compliant details about the SQL syntax (as opposed to what's
inside the literal string); but at least we'll throw error rather
than silently doing the wrong thing in those cases.

- In pgsql/src/backend/utils/misc/guc.c, tweak newly added
set_config_sourcefile() so that the target record isn't left corrupt
if guc_strdup should fail.

- Adjust the parser to accept the typename syntax INTERVAL ...
SECOND(n) and the literal syntax INTERVAL 'string' ... SECOND(n), as
required by the SQL standard. Our old syntax put (n) directly after
INTERVAL, which was a mistake, but will still be accepted for
backward compatibility as well as symmetry with the TIMESTAMP cases.
Change intervaltypmodout to show it in the spec's way, too. (This
could potentially affect clients, if there are any that analyze the
typmod of an INTERVAL in any detail.) Also fix interval input to
handle 'min:sec.frac' properly; I had overlooked this case in my
previous patch. Document the use of the interval fields qualifier,
which up to now we had never mentioned in the docs. (I think the
omission was intentional because it didn't work per spec; but it
does now, or at least close enough to be credible.)

- Tighten up to_date/to_timestamp so that they are more likely to
reject erroneous input, rather than silently producing bizarre
results as formerly happened. Brendan Jurd

- Add a duration option to pgbench, so that test length can be
specified in seconds instead of by number of transactions to run.
Takahiro Itagaki

- In pgsql/src/backend/optimizer/path/indxpath.c, skip opfamily check
in eclass_matches_any_index() when the index isn't a btree. We
can't easily tell whether clauses generated from the equivalence
class could be used with such an index, so just assume that they
might be. This bit of over-optimization prevented use of non-btree
indexes for nestloop inner indexscans, in any case where the join
uses an equality operator that is also a btree operator --- which in
particular is typically true for hash indexes. Noted while trying
to test the current hash index patch.

- In pgsql/doc/src/sgml/citext.sgml, update citext's documentation to
match the recently-applied patch, per David Wheeler.

Alvaro Herrera committed:

- In pgsql/src/pl/plpgsql/src/pl_exec.c, improve plpgsql's ability to
report tuple incompatibility problems. Volkan YAZICI.

- Add "source file" and "source line" information to each GUC
variable. initdb forced due to changes in the pg_settings view.
Magnus Hagander and Alvaro Herrera.

- Initialize the minimum frozen Xid in vac_update_datfrozenxid using
GetOldestXmin() instead of RecentGlobalXmin; this is safer because
we do not depend on the latter being correctly set elsewhere, and
while it is more expensive, this code path is not
performance-critical. This is a real risk for autovacuum, because
it can execute whole cycles without doing a single vacuum, which
would mean that RecentGlobalXmin would stay at its initialization
value, FirstNormalTransactionId, causing a bogus value to be
inserted in pg_database. This bug could explain some recent reports
of failure to truncate pg_clog. At the same time, change the
initialization of RecentGlobalXmin to InvalidTransactionId, and
ensure that it's set to something else whenever it's going to be
used. Using it as FirstNormalTransactionId in HOT page pruning
could incur in data loss. InitPostgres takes care of setting it to
a valid value, but the extra checks are there to prevent "special"
backends from behaving in unusual ways. Per Tom Lane's detailed
problem dissection in 29544(dot)1221061979(at)sss(dot)pgh(dot)pa(dot)us

Peter Eisentraut committed:

- In pgsql/doc/src/sgml/docguide.sgml, update man page build
instructions.

== Rejected Patches (for now) ==

No one was disappointed this week :-)

== Pending Patches ==

Brendan Jurd sent in another revision of his patch to add validation
to to_date().

Alvaro Herrera sent in a patch to make GUC source file and line number
visible.

Tatsuo Ishii sent in another revision of the Common Table Expressions
patch.

Tom Lane sent in a patch to make SQL interval literal syntax conform
to SQL:2008

Heikki Linnakangas sent in another revision of his FSM patch.

Ramon Lawrence sent in two revisions of a patch to disable the
physical-tlist optimization for hash join if the number of batches is
greater than 1.

Simon Riggs sent in another revision of his recovery infrastructure
patch.

David Wheeler sent in two revisions of a patch which adds support for
char to case-insensitive text.

Heikki Linnakangas sent in a set of performance tests for his FSM
patch.

KaiGai Kohei sent in another set of patches for SE-PostgreSQL.

Asif Naeem sent in a patch which correctly copies plugins to the
lib/plugins directory.