== PostgreSQL Weekly News - June 10 2007 ==

== PostgreSQL Weekly News - June 10 2007 ==

Michael Glaesemann's photographs from PGCon 2007 are now online at
http://flickr.com/photos/grzm/sets/72157600270827416/. The photos are
tagged "pgcon" and "pgcon2007". If you've posted photos from the
conference, you're encouraged to do the same to make them more
conveniently found. If you happen to find any errors in captioning or
tagging, please let Michael know at grzm [at] seespotcode [dot] net.
Enjoy the photos!

== PostgreSQL Product News ==

Archiveopteryx 2.0 released.
http://www.archiveopteryx.org/2.0.html

PostgreSQL tablelog 0.4.4 released.
http://pgfoundry.org/projects/tablelog/

== PostgreSQL Jobs for June ==

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

== PostgreSQL Local ==

SFPUG's next meeting is Tuesday, June 12 at Hi5 in San Francisco.
Details below. RSVP for free food.
http://postgresql.meetup.com/1/calendar/5808330/

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 official T-shirts http://www.prato.linux.it/node/30
Sign up to tour Tuscany: http://www.pgday.it/it/node/158

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

Bruce Momjian committed:

- Mark TODO as DONE: "Allow UPDATE/DELETE WHERE CURRENT OF cursor."

- Mark TODO as DONE: "Allow sequential scans to take advantage of
other concurrent sequential scans, also called 'Synchronised
Scanning'"

Alvaro Herrera committed:

- Disallow the cost balancing code from resulting in a zero cost
limit, which causes a division-by-zero error in the vacuum code.
This can happen when there are more workers than cost limit units.
Per report from Galy Lee.

- Avoid passing zero as a value for vacuum_cost_limit, because it's
not a valid value for the vacuum code. Instead, make zero signify
getting the value from a higher level configuration facility, just
like -1 in the original coding. We still document that -1 is the
value that disables the feature, to avoid confusing the user
unnecessarily. Reported by Galy Lee, per subsequent discussion.

- Avoid losing track of data for shared tables in pgstats. Report by
Michael Fuhr, patch from Tom Lane after a messier suggestion by me.

Magnus Hagander committed:

- ITAGAKI Takahiro's patch which changes the Japanese docs in
contrib/pgstattuple to reflect the fact that the functions bt_metap,
bt_page_stats and bt_page_items have moved from there to
pageinspect.

Jan Wieck committed:

- The session_replication_role actually can be changed at will during
a session regardless of the existence of cached plans. The
plancache only needs to be invalidated so that rules affected by the
new setting will be reflected in the new query plans.

Teodor Sigaev committed:

- Move call of MarkBufferDirty() before XLogInsert() as required.
Many thanks to Heikki Linnakangas <heikki(at)enterprisedb(dot)com> for his
sharp eyes.

- Replace ReadBuffer to ReadBufferWithStrategy in all vacuum-involved
places to implement limited-size "ring" of buffers for VACUUM for
GIN & GIST.

Tom Lane committed:

- Support UPDATE/DELETE WHERE CURRENT OF cursor_name, per SQL
standard. Along the way, allow FOR UPDATE in non-WITH-HOLD cursors;
there may once have been a reason to disallow that, but it seems to
work now, and it's really rather necessary if you want to select a
row via a cursor and then update it in a concurrent-safe fashion.
Original patch by Arul Shaji, rather heavily editorialized by Tom
Lane.

- Teach heapam code to know the difference between a real seqscan and
the pseudo HeapScanDesc created for a bitmap heap scan. This avoids
some useless overhead during a bitmap scan startup, in particular
invoking the syncscan code. (We might someday want to do that, but
right now it's merely useless contention for shared memory, to say
nothing of possibly pushing useful entries out of syncscan's small
LRU list.) This also allows elimination of ugly
pgstat_discount_heap_scan() kluge.

- Insert ORDER BY into a few regression test queries that now have
unstable results due to syncscan patch, when shared_buffers is small
enough. Per buildfarm reports and some local testing with
shared_buffers set to the lowest value considered by initdb.

- Allow numeric_fac() to be interrupted, since it can take quite a
while for large inputs. Also cause it to error out immediately if
the result will overflow, instead of grinding through a lot of
calculation first. Per gripe from Jim Nasby.

- Add note that LIMIT without ORDER BY can produce outright
nondeterministic results. Necessary due to introduction of syncscan
patch.

- Arrange for large sequential scans to synchronize with each other,
so that when multiple backends are scanning the same relation
concurrently, each page is (ideally) read only once. Jeff Davis,
with review by Heikki and Tom.

- Redefine IsTransactionState() to only return true for
TRANS_INPROGRESS state, which is the only state in which it's safe
to initiate database queries. It turns out that all but two of the
callers thought that's what it meant; and the other two were using
it as a proxy for "will GetTopTransactionId() return a nonzero XID"?
Since it was in fact an unreliable guide to that, make those two
just invoke GetTopTransactionId() always, then deal with a zero
result if they get one.

- Rework temp_tablespaces patch so that temp tablespaces are assigned
separately for each temp file, rather than once per sort or
hashjoin; this allows spreading the data of a large sort or join
across multiple tablespaces. (I remain dubious that this will make
any difference in practice, but certain people insisted.) Arrange
to cache the results of parsing the GUC variable instead of
recomputing from scratch on every demand, and push usage of the
cache down to the bottommost fd.c level.

- Christian Rossow's patch which fixes array_dims() example to be
consistent with the data previously shown.

- Fix up text concatenation so that it accepts all the reasonable
cases that were accepted by prior Postgres releases. This takes
care of the loose end left by the preceding patch to downgrade
implicit casts-to-text. To avoid breaking desirable behavior for
array concatenation, introduce a new polymorphic pseudo-type
"anynonarray" --- the added concatenation operators are actually
text || anynonarray and anynonarray || text.

- Minor editorialization: don't flush plan cache without need.

- Downgrade implicit casts to text to be assignment-only, except for
the ones from the other string-category types; this eliminates a lot
of surprising interpretations that the parser could formerly make
when there was no directly applicable operator. Create a general
mechanism that supports casts to and from the standard string types
(text,varchar,bpchar) for *every* datatype, by invoking the
datatype's I/O functions. These new casts are assignment-only in
the to-string direction, explicit-only in the other, and therefore
should create no surprising behavior. Remove a bunch of
thereby-obsoleted datatype-specific casting functions. The "general
mechanism" is a new expression node type CoerceViaIO that can
actually convert between *any* two datatypes if their external text
representations are compatible. This is more general than needed
for the immediate feature, but might be useful in plpgsql or other
places in future. This commit does nothing about the issue that
applying the concatenation operator || to non-text types will now
fail, often with strange error messages due to misinterpreting the
operator as array concatenation. Since it often (not always) worked
before, we should either make it succeed or at least give a more
user-friendly error; but details are still under debate. Peter
Eisentraut and Tom Lane

== Rejected Patches (for now) ==

No one was disappointed this week :-)

== Pending Patches ==

Andrew Dunstan sent in a WIP patch for CSV logs.

Simon Riggs sent in a patch to fix recent PITR bug, with proposals as
discussed on -admin and -hackers. Patch implements option #3, as
proposed here:
http://archives.postgresql.org/pgsql-hackers/2007-06/msg00234.php

Magnus Hagander sent in another revision of Joachim Wieland's patch
which changes some regression tests for ECPG.