== PostgreSQL Weekly News - September 09 2007 ==

== PostgreSQL Weekly News - September 09 2007 ==

== PostgreSQL Product News ==

Another PostgreSQL Diff Tool 1.0.0_beta26 released.
http://pgfoundry.org/projects/apgdiff/

PSQL-Query 0.01 released.
http://search.cpan.org/~ecarroll/PSQL-Query-0.01_01/bin/psql-plus

OpenMFG 2.2 and PostBooks 2.2 released.
http://www.xtuple.com/

== PostgreSQL Jobs for September ==

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

== PostgreSQL Local ==

PostgreSQL Fall Conference will be October 20, 2007 in Portland.
http://www.postgresqlconference.org/

There will be a PostgreSQL booth at Ohio Linuxfest on September 29th,
and Robert Treat will be speaking.
http://www.ohiolinux.org/speakers.html#talk1_06

SFPUG presents Fred Moyer on Big Blue Ball of Mud, September 11, 7:30pm
http://postgresql.meetup.com/1/calendar/6143101/

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

Submit news and announcements by Sunday at 3:00pm Pacific time.
Please send English language ones to david(at)fetter(dot)org, German to
pwn(at)pgug(dot)de(dot)

== Applied Patches ==

Teodor Sigaev committed:

- Improve page split in rtree emulation. Now when a split result has
a big misalignement, it tries to split page basing on distribution
of box's centers. Per report from Tom Dolafi.

- Fix infinite loop in rank_cd for some corner queries. Per Daniele
Varrazzo.

- Improvements from Heikki Linnakangas.

- Improve various checks by Heikki Linnakangas.

- Refactor of tsearch code by Heikki Linnakangas with small
editorization by me.

- Add turkish stopword list. Thanks to Devrim GUNDUZ.

Bruce Momjian committed:

- Add Hash thread URL to TODO:
"http://archives.postgresql.org/pgsql-hackers/2007-09/msg00051.php"

- Mark TODO as DONE: "Reduce XID consumption of read-only queries."

Andrew Dunstan committed:

- Fix typo in regression Makefile.

- Provide for a file specifying non-standard config options for temp
install for pg_regress, via --temp-config option. Pick this up in
the make file via TEMP_CONFIG setting.

- Provide for binary input/output of enums, to fix complaint from
Merlin Moncure. This just provides text values, we're not exposing
the underlying Oid representation. Catalog version bumped.

Michael Meskes committed:

- In pgsql/src/interfaces/ecpg, synced parser and keyword list.

Tom Lane committed:

- Set the correct context (PGC_SIGHUP) for log_autovacuum, per ITAGAKI
Takahiro. Fix grammatical errors in its description.

- Code review for GUC revert-values-if-removed-from-postgresql.conf
patch; and in passing, fix some bogosities dating from the
custom_variable_classes patch. Fix guc-file.l to correctly check
changes in custom_variable_classes that are attempted concurrently
with additions/removals of custom variables, and don't allow the new
setting to be applied in advance of checking it. Clean up messy and
undocumented situation for string variables with NULL boot_val. Fix
DefineCustomVariable functions to initialize boot_val correctly.
Prevent find_option from inserting bogus placeholders for custom
variables that are simply inquired about rather than being set.

- Replace the former method of determining snapshot xmax --- to wit,
calling ReadNewTransactionId from GetSnapshotData --- with a
"latestCompletedXid" variable that is updated during transaction
commit or abort. Since latestCompletedXid is written only in places
that had to lock ProcArrayLock exclusively anyway, and is read only
in places that had to lock ProcArrayLock shared anyway, it adds no
new locking requirements to the system despite being cluster-wide.
Moreover, removing ReadNewTransactionId from snapshot acquisition
eliminates the need to take both XidGenLock and ProcArrayLock at the
same time. Since XidGenLock is sometimes held across I/O this can
be a significant win. Some preliminary benchmarking suggested that
this patch has no effect on average throughput but can significantly
improve the worst-case transaction times seen in pgbench. Concept
by Florian Pflug, implementation by Tom Lane.

- Don't take ProcArrayLock while exiting a transaction that has no
XID; there is no need for serialization against snapshot-taking
because the xact doesn't affect anyone else's snapshot anyway. Per
discussion. Also, move various info about the interlocking of
transactions and snapshots out of code comments and into a
hopefully-more-cohesive discussion in access/transam/README. Also,
remove a couple of now-obsolete comments about having to force some
WAL to be written to persuade RecordTransactionCommit to do its
thing.

- Allow CREATE INDEX CONCURRENTLY to disregard transactions in other
databases, per gripe from hubert depesz lubaczewski. Patch from
Simon Riggs.

- Make eval_const_expressions() preserve typmod when simplifying
something like null::char(3) to a simple Const node. (It already
worked for non-null values, but not when we skipped evaluation of a
strict coercion function.) This prevents loss of typmod knowledge in
situations such as exhibited in bug #3598. Unfortunately there
seems no good way to fix that bug in 8.1 and 8.2, because they
simply don't carry a typmod for a plain Const node. In passing I
made all the other callers of makeNullConst supply "real" typmod
values too, though I think it probably doesn't matter anywhere else.

- Volatile-qualify the ProcArray PGPROC pointer in a bunch of routines
that examine fields that could change under them. This is just to
make really sure that when we are fetching a value 'only once',
that's what actually happens. Possibly this is a bug that should be
back-patched, but in the absence of solid evidence that it's needed,
I won't bother.

- Quick hack to make the VXID of a prepared transaction be -1/XID, so
that different prepared xacts can be told apart in the pg_locks
view. Per suggestion from Florian.

- Implement lazy XID allocation: transactions that do not modify any
database rows will normally never obtain an XID at all. We already
did things this way for subtransactions, but this patch extends the
concept to top-level transactions. In applications where there are
lots of short read-only transactions, this should improve
performance noticeably; not so much from removal of the actual
XID-assignments, as from reduction of overhead that's driven by the
rate of XID consumption. We add a concept of a "virtual transaction
ID" so that active transactions can be uniquely identified even if
they don't have a regular XID. This is a much lighter-weight
concept: uniqueness of VXIDs is only guaranteed over the short term,
and no on-disk record is made about them. Florian Pflug, with some
editorialization by Tom.

- In pgsql/doc/src/sgml/textsearch.sgml, sync examples of psql \dF
output with current CVS HEAD behavior. Random other wordsmithing.

- Support SET FROM CURRENT in CREATE/ALTER FUNCTION, ALTER DATABASE,
ALTER ROLE. (Actually, it works as a plain statement too, but I
didn't document that because it seems a bit useless.) Unify
VariableResetStmt with VariableSetStmt, and clean up some ancient
cruft in the representation of same.

== Rejected Patches (for now) ==

No one was disappointed this week :-)

== Pending Patches ==

Heikki Linnakangas sent in a document update to version 14 of the HOT
patch.

Heikki Linnakangas sent in a refactor for the tsearch2 code.

Pavan Deolasee sent in version 15 of the HOT patch.

Andrew Dunstan sent in a patch which adds temp_config configuration.

Greg Smith sent in a patch which does just-in-time background
writing.

Tom Lane sent in a WIP patch for latestCompletedXid method of
computing snapshot xmax.

Andrew Dunstan sent in a WIP patch to replace MSVC build scripts
build.bat and vcregress.bat with build.pl and vcregress.pl
respectively.

Sibte Abbas sent in a patch which implements the \# TODO for psql.