== PostgreSQL Weekly News - March 18 2007 ==

== PostgreSQL Weekly News - March 18 2007 ==

Student applications for Google Summer Of Code are officially open:
http://code.google.com/soc/

Please contact Robert Treat at xzilla(at)users(dot)sourceforge(dot)net if you
want to do a lightning talk at PgCon.

== PostgreSQL Product News ==

pg_bulkload released
http://pgfoundry.org/projects/pgbulkload/

Replicator 8.1.9-1.7 released.
http://www.commandprompt.com/

PL/Proxy 2.0 released:
https://developer.skype.com/SkypeGarage/DbProjects/PlProxy

PgBouncer 1.0 released:
https://developer.skype.com/SkypeGarage/DbProjects/PgBouncer

SkyTools 2.1 released:
https://developer.skype.com/SkypeGarage/DbProjects/SkyTools

Slony-I 1.1.8 and 1.2.8 released:
http://www.slony.info/

== PostgreSQL Jobs for March ==

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

== PostgreSQL Local ==

The Italian PostgreSQL community is looking for sponsors for its
PostgreSQL day in Prato, Italy this summer. Check the link below
to participate.
http://www.pgday.it

Anders Steinlein is starting a PUG in Norway, please contact him at
<anders(at)steinlein(dot)no> to participate.

Josh Berkus, David Fetter, Fernando Ike de Oliveira will be at FISL
8.0 in Porto Alegre, RS, Brazil

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

Neil Conway committed:

- Stefan Huehner's patch to mark several char* variables as const
where they are initialized with constant strings.

- Stefan Huehner's patch to change some function declarations from K&R
style to ANSI C.

Michael Meskes committed:

- Simplified sortby rule in pgsql/src/backend/parser/gram.y.

- Miscellaneous fixes to ecpg, some refactoring, and some new test
cases.

- Added new directory src/interfaces/ecpg/preproc/parser.

Alvaro Herrera committed:

- Fix uninitialized value in pgstatindex leading to invalid values
being reported in some cases. Report and patch from Tatsuhito
Kasahara. Also fix a couple of other bugs I noticed in skimming the
surrounding code.

Teodor Sigaev committed:

- Guillaume Smet's patch to add GIN support for pg_trgm, with minor
editorization by Teodor Sigaev.

Tom Lane committed:

- Fix ecpg/preproc makefile for parallel builds: parser.o must depend
on preproc.h, else make may try to build it before preproc.h is
ready. Per failures seen here and in buildfarm.

- SPI_cursor_open failed to enforce that only read-only queries could
be executed in read_only mode. This could lead to various
relatively-subtle failures, such as an allegedly stable function
returning non-stable results. Bug goes all the way back to the
introduction of read-only mode in 8.0. Per report from Gaetano
Mendola.

- Ooops, got only one of the two ArrayExpr variants correct in first
cut at exprTypmod support. Also, experimentation shows that we need
to label the type of Const nodes that are numeric with a specific
typmod.

- Fix up the remaining places where the expression node structure
would lose available information about the typmod of an expression;
namely, Const, ArrayRef, ArrayExpr, and EXPR and ARRAY SubLinks. In
the ArrayExpr and SubLink cases it wasn't really the data
structure's fault, but exprTypmod() being lazy. This seems like a
good idea in view of the expected increase in typmod usage from
Teodor's work to allow user-defined types to have typmods. In
particular this responds to the concerns we had about eliminating
the special-purpose hack that exprTypmod() used to have for BPCHAR
Consts. We can now tell whether or not such a Const has been cast
to a specific length, and report or display properly if so. initdb
forced due to changes in stored rules.

- Fix race condition in parallel regression tests. The new plancache
test was expecting there to be no regular table named 'foo', but it
turns out the rules test transiently creates one, so that plancache
would sometimes fail. I couldn't reproduce that in quite a few
tries here, but several buildfarm machines have shown the failure.
Fix by renaming plancache's temp table to something nonconflicting.

- Make use of plancache module for SPI plans. In particular, since
plpgsql uses SPI plans, this finally fixes the ancient gotcha that
you can't drop and recreate a temp table used by a plpgsql function.
Along the way, clean up SPI's API a little bit by declaring SPI plan
pointers as "SPIPlanPtr" instead of "void *". This is cosmetic but
helps to forestall simple programming mistakes. (I have changed
some but not all of the callers to match; there are still some "void
*"'s in contrib and the PL's. This is intentional so that we can
see if anyone's compiler complains about it.)

- Fix a longstanding bug in VACUUM FULL's handling of update chains.
The code did not expect that a DEAD tuple could follow a
RECENTLY_DEAD tuple in an update chain, but because the OldestXmin
rule for determining deadness is a simplification of reality, it is
possible for this situation to occur (implying that the
RECENTLY_DEAD tuple is in fact dead to all observers, but this patch
does not attempt to exploit that). The code would follow a chain
forward all the way, but then stop before a DEAD tuple when backing
up, meaning that not all of the chain got moved. This could lead to
copying the chain multiple times (resulting in duplicate copies of
the live tuple at its end), or leaving dangling index entries behind
(which, aside from generating warnings from later vacuums, creates a
risk of wrong query results or bogus duplicate-key errors once the
heap slot the index entry points to is repopulated). The fix is to
recheck HeapTupleSatisfiesVacuum while following a chain forward,
and to stop if a DEAD tuple is reached. Each contiguous group of
RECENTLY_DEAD tuples will therefore be copied as a separate chain.
The patch also adds a couple of extra sanity checks to verify
correct behavior. Per report and test case from Pavan Deolasee.

- Arrange to install a "posixrules" entry in our timezone database, so
that POSIX-style timezone specs that don't exactly match any
database entry will be treated as having correct USA DST rules.
Also, document that this can be changed if you want to use some
other DST rules with a POSIX zone spec. We could consider changing
localtime.c's TZDEFRULESTRING, but since that facility can only deal
with one DST transition rule, it seems fairly useless now; might as
well just plan to override it using a "posixrules" entry. Backpatch
as far as 8.0. There isn't much we can do in 7.x ... either your
libc gets it right, or it doesn't.

- Fix broken markup, strange tab width.

- Regression makefile now needs to make separate lists of what to
clean for input/ and output/ directories, because with the addition
of largeobject_1.source, they're not the same list. Apparently the
current buildfarm process does not exercise whether 'make distclean'
leaves a clean tree behind, else the farm would have been failing
for awhile.

- First phase of plan-invalidation project: create a plan cache
management module and teach PREPARE and protocol-level prepared
statements to use it. In service of this, rearrange
utility-statement processing so that parse analysis does not assume
table schemas can't change before execution for utility statements
(necessary because we don't attempt to re-acquire locks for utility
statements when reusing a stored plan). This requires some
refactoring of the ProcessUtility API, but it ends up cleaner
anyway, for instance we can get rid of the QueryContext global.
Still to do: fix up SPI and related code to use the plan cache; I'm
tempted to try to make SQL functions use it too. Also, there are at
least some aspects of system state that we want to ensure remain the
same during a replan as in the original processing; search_path
certainly ought to behave that way for instance, and perhaps there
are others.

Peter Eisentraut committed:

- Reverted patch below awaiting further fixes.

- Joachim Wieland's patch which makes configuration parameters fall
back to their default values when they are removed from the
configuration file.

Magnus Hagander committed:

- MSVC build actually needs Bison 2.2 or later, not 2.1. Or 1.875 as
before.

- Add note that diff is required for regression tests.

- Add cvs tags to msvc build files, along with a (very short) comment
about what each script does.

- Oops, forgot to remove the old genbki script.

- Turn most vc build scripts into modules instead of scripts, and just
have skeleton scripts calling them. To make it easier for the
buildfarm (or other "outside callers") to use these modules
directly. Per suggestion from Andrew Dunstan.

- Add new columns for tuple statistics on a database level to
pg_stat_database.

- Greg Sabino Mullane's patch which makes psql's \da show the return
type of the aggregate.

- In pgsql/src/tools/msvc/builddoc.bat, remove extra single-quotes
copied from Unix build. Win32 doesn't strip single quotes...

- Rewrite win32 install documentation (it's not client only anymore,
and it's now complete). Update for the MSVC6/Borland support now
being only libpq. Move most of the information about full MSVC
build from README file into documentation.

- Filter out warnings coming from the stylesheets, so that actual
warnings show up properly. Show some minor progress messages.

- Set root of docbook stuff from buildenv.bat and not from
builddoc.bat. Fix calling for file renaming when cd:ed into a
different directory.

- Properly use pg_strcasecmp() instead of strcasecmp(). Un-breaks
win32 build.

- ITAGAKI Takahiro's patch which sets stderr to unbuffered for pgbench
on win32.

- Make a run with perltidy to format the MSVC perl code. Per request
from Andrew Dunstan.

== Rejected Patches (for now) ==

No one was disappointed this week :-)

== Pending Patches ==

Heikki Linnakangas submitted a patch to improve bitmap scanning. A
lively discussion ensued.

Gavin Sherry sent in a different bitmap index scan patch.

Gregory Stark sent in a patch implementing Tom Lane's suggestion of
packing the external toast pointers unaligned and copying them to a
local struct to access the fields.

Pavan Deolasee sent in version 4.4 of his HOT WIP patch.

Gregory Stark sent in another version of his Packed Varlena patch.

Jeff Davis sent in another revision of his WIP synchronized scan
patch, this one with some adjustments to call ss_report_loc() less
often, changes to logging, and some new GUC variables.

Andrew Dunstan sent in a patch which removes the undocumented pre-7.3
flavor of \copy from psql.

Magnus Hagander sent in a patch which adds some new columns to
pg_stat_database.

Jan Wieck sent in three revisions of a patch which extends pg_trigger
and pg_rewrite in a replication-friendly manner.

Magnus Hagander sent in a patch which replaces the pthreads code in
ecpg with native win32 threads, in order to make it threadsafe. The
idea is not to have to download the non-standard pthreads library on
windows. Does it seem like it should be doing the right thing? Does
somebody have a good test-case where ecpg breaks when not built
thread-safe? (which would then also break when built thread-safe with
a broken implementation)

ITAGAKI Takahiro sent in a cancel handler for vacuumdb, reindexdb and
clusterdb.