== PostgreSQL Weekly News - November 15 2009 ==

== PostgreSQL Weekly News - November 15 2009 ==

Commitfest for November, 2009 has begun. Start reviewing other
people's patches!

New Survey: Which 8.5 Alpha 2 feature have you tried?
http://www.postgresql.org/community/

== PostgreSQL Jobs for November ==

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

== PostgreSQL Local ==

OpenSQL Camp will be November 14-15, 2009 in Portland, OR
http://www.chesnok.com/daily/2009/07/29/opensql-camp-comes-to-portland-november-14-15-2009/

JPUG 10th Anniversary Conference is November 20-21, 2009 in Tokyo, Japan.
http://archives.postgresql.org/pgsql-announce/2009-05/msg00018.php

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
and Josh Berkus.

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:

- Re-refactor the core scanner's API, in order to get out from under
the problem of different parsers having different YYSTYPE unions
that they want to use with it. I defined a new union core_YYSTYPE
that is just the (very short) list of semantic values returned by
the core scanner. I had originally worried that this would require
an extra interface layer, but actually we can have parser.c's
base_yylex (formerly filtered_base_yylex) take care of that at no
extra cost. Names associated with the core scanner are now
"core_yy_foo", with "base_yy_foo" being used in the core Bison
parser and the parser.c interface layer. This solves the last
serious stumbling block to eliminating plpgsql's separate lexer.
One restriction that will still be present is that plpgsql and the>
core will have to agree on the token numbers assigned to tokens that
can be returned by the core lexer. Since Bison doesn't seem willing
to accept external assignments of those numbers, we'll have to live
with decreeing that core and plpgsql grammars declare these tokens
first and in the same order.

- More incremental refactoring in plpgsql: get rid of gram.y
dependencies on yytext. This is a necessary change if we're going
to have a lexer interface layer that does lookahead, since yytext
won't necessarily be in step with what the grammar thinks is the
current token. yylval and yylloc should be the only side-variables
that we need to manage when doing lookahead.

- In pgsql/src/backend/commands/tablespace.c, revert the temporary
patch to work around Snow Leopard readdir() bug. Apple has fixed
that bug in 10.6.2, and we should encourage users to update to that
version rather than trusting this cosmetic patch. As was recently
noted by Stephen Tyler, this patch was only masking the problem in
the context of DROP TABLESPACE, but the failure could occur in other
places such as pg_xlog cleanup.

- Do not build psql's flex module on its own, but instead include it
in mainloop.c. This ensures that postgres_fe.h is read before
including any system headers, which is necessary to avoid problems
on some platforms where we make nondefault selections of feature
macros for stdio.h or other headers. We have had this policy for
flex modules in the backend for many years, but for some reason it
was not applied to psql. Per trouble report from Alexandra Roy and
diagnosis by Albe Laurenz.

- Remove plpgsql's separate lexer (finally!), in favor of using the
core lexer directly. This was a lot of trouble, but should be worth
it in terms of not having to keep the plpgsql lexer in step with
core anymore. In addition the handling of keywords is significantly
better-structured, allowing us to de-reserve a number of words that
plpgsql formerly treated as reserved.

- Remove pg_parse_string_token() --- not needed anymore.

- Make initdb behave sanely when the selected locale has codeset
"US-ASCII". Per discussion, this should result in defaulting to
SQL_ASCII encoding. The original coding could not support that
because it conflated selection of SQL_ASCII encoding with not being
able to determine the encoding. Adjust pg_get_encoding_from_locale()'s
API to distinguish these cases, and fix callers appropriately. Only
initdb actually changes behavior, since the other callers were
perfectly content to consider these cases equivalent. Per bug #5178
from Boh Yap. Not going to bother back-patching, since no one has
complained before and there's an easy workaround (namely, specify
the encoding you want).

- In pgsql/src/port/chklocale.c, check for C/POSIX before assuming
that nl_langinfo or win32_langinfo will work. Per buildfarm
results.

- In pgsql/src/backend/utils/misc/guc-file.l, the recent patch to log
changes in postgresql.conf settings dumped core if the initial value
of a string variable was NULL, which is entirely possible. Noted
while experimenting with custom_variable_classes.

- Add control knobs for plpgsql's variable resolution behavior, and
make the default be "throw error on conflict", as per discussions.
The GUC variable is plpgsql.variable_conflict, with values "error",
"use_variable", "use_column". The behavior can also be specified
per-function by inserting one of #variable_conflict error,
#variable_conflict use_variable, #variable_conflict use_column at
the start of the function body. The 8.5 release notes will need to
mention using "use_variable" to retain backward-compatible behavior,
although we should encourage people to migrate to the much less
mistake-prone "error" setting. Update the plpgsql documentation to
match this and other recent changes.

- In pgsql/src/include/nodes/parsenodes.h, avoid assuming that enum
CreateStmtLikeOption is unsigned. Zdenek Kotala

- In pgsql/src/backend/parser/parse_utilcmd.c, clean up a couple of
bizarre code formatting choices in recent CREATE LIKE patch.

- Make unaccent's install/uninstall scripts look more like all the
others. Set search_path explicitly, don't use IF EXISTS, etc.

- Improve planning of Materialize nodes inserted atop the inner input
of a mergejoin to shield it from doing mark/restore and refetches.
Put an explicit flag in MergePath so we can centralize the logic
that knows about this, and add costing logic that considers using
Materialize even when it's not forced by the previously-existing
considerations. This is in response to a discussion back in August
that suggested that materializing an inner indexscan can be helpful
when the refetch percentage is high enough.

Bruce Momjian committed:

- In pgsql/doc/src/sgml/plpgsql.sgml: PL/pgSQL FOUND, document that
GET DIAGNOSTICS is affected by EXECUTE, while FOUND is not.

- In pgsql/doc/src/sgml/plpgsql.sgml: PL/pgSQL FOUND, update wording
of GET DIAGNOSTICS/FOUND, per David Fetter.

- In pgsql/src/backend/utils/adt/timestamp.c, add C comment about why
there is no interval_abs(): it is unclear what value to return.
http://archives.postgresql.org/pgsql-general/2009-10/msg01031.php
http://archives.postgresql.org/pgsql-general/2009-11/msg00041.php

Alvaro Herrera committed:

- Fix longstanding problems in VACUUM caused by untimely
interruptions. In VACUUM FULL, an interrupt after the initial
transaction has been recorded as committed can cause postmaster to
restart with the following error message: PANIC: cannot abort
transaction NNNN, it was already committed This problem has been
reported many times. In lazy VACUUM, an interrupt after the table
has been truncated by lazy_truncate_heap causes other backends'
relcache to still point to the removed pages; this can cause future
INSERT and UPDATE queries to error out with the following error
message: could not read block XX of relation 1663/NNN/MMMM: read
only 0 of 8192 bytes The window to this race condition is extremely
narrow, but it has been seen in the wild involving a cancelled
autovacuum process. The solution for both problems is to inhibit
interrupts in both operations until after the respective
transactions have been committed. It's not a complete solution,
because the transaction could theoretically be aborted by some other
error, but at least fixes the most common causes of both problems.

- Support optional FROM/IN in FETCH and MOVE. The main motivation for
this is that it's required for Informix compatibility in ECPG. This
patch makes the ECPG and core grammars a bit closer to one another
for these productions. Zoltan Boszormenyi

- Document the previous FETCH and MOVE changes.

- Change "name" nonterminal in cursor-related productions to
cursor_name. This is a preparatory patch for allowing a dynamic
cursor name be used in the ECPG grammar. Zoltan Boszormenyi.

Peter Eisentraut committed:

- In pgsql/src/bin/psql/describe.c, in psql \du, separate the role
attributes by comma instead of newline, for an arguably more
pleasant display.

- In pgsql/src/backend/tsearch/wparser_def.c, make text search parser
accept underscores in XML attributes (bug #5075).

Teodor Sigaev committed:

- In pgsql/src/backend/access/gin/ginget.c, fix multicolumn GIN's
wrong results with fastupdate enabled. User-defined consistent
functions believes the check array contains at least one true
element which was not a true for scanning pending list. Per report
from Yury Don.

Heikki Linnakangas committed:

- In pgsql/src/backend/parser/parse_expr.c, when you do
"ARRAY[...]::domain", where domain is a domain over an array type,
we need to check domain constraints. We used to do it correctly, but
8.4 introduced a separate code path for the "ARRAY[]::arraytype"
case to infer the type of an empty ARRAY construct from the cast
target, and forgot to take domains into account. Per report from
Florian G. Pflug.

- In pgsql/src/backend/parser/parse_expr.c, a better fix for the
"ARRAY[...]::domain" problem. The previous patch worked, but the
transformed ArrayExpr claimed to have a return type of "domain",
even though the domain constraint was only checked by the enclosing
CoerceToDomain node. With this fix, the ArrayExpr is correctly
labeled with the base type of the domain. Per gripe by Tom Lane.

Magnus Hagander committed:

- Add inheritable ACE when creating a restricted token for execution
on Win32. Also refactor the code around it to be more clear. Jesse
Morris.

- Backpatch the inheritable-ACE patch for Win32 to 8.2 as well, except
for the pg_regress part which did not support admin execution in
8.2.

== Rejected Patches (for now) ==

No one was disappointed this week :-)

== Pending Patches ==

Josh Tolley sent in another revision of the patch to add in-line (DO)
functions to PL/Perl[U]. Brendan Jurd sent some cleanups for it.

Heikki Linnakangas sent in another revision of the Hot Standby patch.

Emmanuel Cecchet sent in two more revisions of the patch to add
partitioning support to COPY.

Joachim Wieland sent in a patch to add a payload to LISTEN/NOTIFY.

ITAGAKI Takahiro sent in another revision of the table partitioning
patch.

Peter Eisentraut sent in another revision of the patch for PL/PythonU
array support.

Peter Eisentraut sent in a patch to add Python 3.1 to PL/PythonU.

ITAGAKI Takahiro sent in an update version of the TRIGGER with WHEN
patch.

Andres Freund sent in another revision of the application name logging
patch.

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

KaiGai Kohei sent in another revision of his SE-PostgreSQL patch.

Andrew (RhodiumToad) Gierth sent in a patch to add ORDER BY to the
inside of aggregates for the non-WINDOW context.

KaiGai Kohei sent in another revision of the patch to add ACLs for
large objects.

Zdenek Kotala sent in a patch to add dtrace probes for memory
management.

Zdenek Kotala sent in another revision of that patch to add dtrace
probe groups for executor and slru.

Andrew (RhodiumToad) Gierth sent in a doc patch for the aggregate
ORDER BY patch.

Hitoshi Harada sent in a patch to add more frame types (ROWS n
PRECEDING/FOLLOWING) to windowing frames.

George Gensure sent in a patch intended to report the schema along
with the table name on errors.

Robert Haas sent in a patch to allow you to set random_page_cost
and seq_page_cost at the granularity of tablespace.

Marko (johto) Tiikkaja sent in another revision of the Writeable CTE
patch.

Heikki Linnakangas sent in another revision of the Hot Standby patch.

Florian Pflug sent in a patch to allow inspection of rowtypes in SQL
and PL/pgsql.

Pavel Stehule sent in another revision of the function typmod patch.

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

Jeff Davis sent in another revision of the operator exclusion
constraints patch.

Roger Leigh sent in three more revisions of the Unicode formatting
patch for psql output.

Pavel Stehule sent in a patch to allow using psql variables as actual
query parameters.

ITAGAKI Takahiro sent in a review of Greg Sabino Mullane's patch to
add YAML output as an option for EXPLAIN [ANALYZE].