== PostgreSQL Weekly News - May 28 2007 ==

== PostgreSQL Weekly News - May 28 2007 ==

PgCon was a smashing success this past week.

== PostgreSQL Product News ==

No product announcements this week.

== PostgreSQL Jobs for May ==

http://archives.postgresql.org/pgsql-jobs/2007-05/threads.php

== PostgreSQL Local ==

Registration for pgday.it in Prato, Tuscany, Italy on July 6 and 7 is
open.

Some Important URLs:

Registration: http://www.pgday.it/en/generale/registrazione
PGDay's web site: http://www.pgday.it/en/
Sponsorship campaign: http://www.pgday.it/en/sponsorizzazioni/come
How to get to Prato: http://www.pgday.it/en/logistica/raggiungere_prato
Accommodations in Prato: http://www.pgday.it/en/logistica/dove_dormire
PostgreSQL T-shirts http://www.prato.linux.it/node/30

Important dates:

May 31: Deadline for the call for papers
June 5: Special accommodation rates expire for Hotel San Marco and Art
Hotel Milano

== PostgreSQL in the News ==

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

General Bits, Archives and occasional new articles:
http://www.varlena.com/GeneralBits/

PostgreSQL Weekly News is brought to you this week by David Fetter

To get your submission into the upcoming issue, make sure it arrives
at david(at)fetter(dot)org or in German at pwn(at)pgug(dot)de by Sunday at 3:00pm
Pacific Time.

== Applied Patches ==

Alvaro Herrera committed:

- Pavel Stehule's patch which removes unsupported -u option from
pg_standby example usage.

Tom Lane committed:

- Fix up pgstats counting of live and dead tuples to recognize that
committed and aborted transactions have different effects; also
teach it not to assume that prepared transactions are always
committed. Along the way, simplify the pgstats API by tying
counting directly to Relations; I cannot detect any redeeming social
value in having stats pointers in HeapScanDesc and IndexScanDesc
structures. And fix a few corner cases in which counts might be
missed because the relation's pgstat_info pointer hadn't been set.

- Repair two constraint-exclusion corner cases triggered by proving
that an inheritance child of an UPDATE/DELETE target relation can be
excluded by constraints. I had rearranged some code in
set_append_rel_pathlist() to avoid "useless" work when a child is
excluded, but overdid it and left the child with no cheapest_path
entry, causing possible failure later if the appendrel was involved
in a join. Also, it seems that the dummy plan generated by
inheritance_planner() when all branches are excluded has to be a bit
less dummy now than was required in 8.2. Per report from Jan Wieck.
Add his test case to the regression tests.

- Create hooks to let a loadable plugin monitor (or even replace) the
planner and/or create plans for hypothetical situations; in
particular, investigate plans that would be generated using
hypothetical indexes. This is a heavily-rewritten version of the
hooks proposed by Gurjeet Singh for his Index Advisor project. In
this formulation, the index advisor can be entirely a loadable
module instead of requiring a significant part to be in the core
backend, and plans can be generated for hypothetical indexes without
requiring the creation and rolling-back of system catalog entries.
The index advisor patch as-submitted is not compatible with these
hooks, but it needs significant work anyway due to other 8.2-to-8.3
planner changes. With these hooks in the core backend, development
of the advisor can proceed as a pgfoundry project.

- Remove ruleutils.c's use of varnoold/varoattno as a shortcut for
determining what a Var node refers to. This is no longer necessary
because the new flat-range-table representation of plan trees makes
it relatively easy to dig down through child plan levels to find the
original reference; and to keep doing it that way, we'd have to
store joinaliasvars lists in flattened RTEs, as demonstrated by bug
report from Leszek Trenkner. This change makes varnoold/varoattno
truly just debug aids, which wasn't quite the case before. Perhaps
we should drop them, or only have them in assert-enabled builds?

- Avoid assuming that the fields of struct timeval have exactly type
long. This is probably incorrect on some platforms, and definitely
draws a compiler warning on Darwin.

- Repair planner bug introduced in 8.2 by ability to rearrange outer
joins: in cases where a sub-SELECT inserts a WHERE clause between
two outer joins, that clause may prevent us from re-ordering the two
outer joins. The code was considering only the joins' own
ON-conditions in determining reordering safety, which is not good
enough. Add a "delay_upper_joins" flag to OuterJoinInfo to flag
that we have detected such a clause and higher-level outer joins
shouldn't be permitted to commute with this one. (This might seem
overly coarse, but given the current rules for OJ reordering, it's
sufficient AFAICT.) The failure case is actually pretty narrow: it
needs a WHERE clause within the RHS of a left join that checks the
RHS of a lower left join, but is not strict for that RHS (else we'd
have simplified the lower join to a plain join). Even then no
failure will be manifest unless the planner chooses to rearrange the
join order. Per bug report from Adam Terrey.

- Fix best_inner_indexscan to return both the cheapest-total-cost and
cheapest-startup-cost innerjoin indexscans, and make joinpath.c
consider both of these (when different) as the inside of a nestloop
join. The original design was based on the assumption that
indexscan paths always have negligible startup cost, and so total
cost is the only important figure of merit; an assumption that's
obviously broken by bitmap indexscans. This oversight could lead to
choosing poor plans in cases where fast-start behavior is more
important than total cost, such as LIMIT and IN queries.
8.1-vintage brain fade exposed by an example from Chuck D.

- Teach tuplestore.c to throw away data before the "mark" point when
the caller is using mark/restore but not rewind or backward-scan
capability. Insert a materialize plan node between a mergejoin and
its inner child if the inner child is a sort that is expected to
spill to disk. The materialize shields the sort from the need to do
mark/restore and thereby allows it to perform its final merge pass
on-the-fly; while the materialize itself is normally cheap since it
won't spill to disk unless the number of tuples with equal key
values exceeds work_mem. Gregory Stark, with some kibitzing from
Tom Lane.

Peter Eisentraut committed:

- In pgsql/contrib/xml2/README.xml2, add deprecation notice about
planned removal in 8.4 and advice to try using the newer XML stuff
in core. This should probably also be referred to in the release
notes.

- Nikolay Samokhvalov's xpath fixes, with code cleanup and
documentation work by myself.

- Fix spurious German index entry

Michael Meskes committed:

- Removed errant ISODOW

- Backported fix from HEAD that removes a superfluous function which
Vista has a problem with.

- Also removed the function not just the call, sorry.

- Do not try to find local timezone in DecodeDateTime() because the
result is not used anyway. This also fixes Vista's build problems.

== Rejected Patches (for now) ==

No one was disappointed this week :-)

== Pending Patches ==

Gregory Stark sent in another revision of his concurrent psql patch.

NikhilS sent in a patch implementing CREATE TABLE LIKE INCLUDING
INDEXES.

Andrew Dunstan sent in several revisions of patches to UTF8MatchText.

Henry B. Hotz sent in another revision of his GSSAPI patch.

Greg Smith sent in another revision of his patch to enable COPY-able
csv log outputs.

Arul Shaji sent in another revision of his patch to make cursors
updateable.

Heikki Linnakangas sent in a several revisions of a Heikki/Simon
collaboration on a "scan-resistant buffer manager" patch.