Re: URGENT pg_xlog full impossible to restart ...

=?iso-8859-15?q?Herv=E9_Piedvache?= <footcow(at)noos(dot)fr> writes:
> But Tom ... could you clearly explain me what is the parameter to set in the
> postgresql.conf to never have my pg_xlog partition's going full ??

> And I was only creating an index ...

This is a known issue in 7.3 and 7.4: a large CREATE INDEX holds shared
buffer locks for unreasonable amounts of time, which can block
CHECKPOINT and thereby delay recycling of WAL files. It's fixed for
7.5, but I don't know of any good way to avoid the problem in the
earlier releases. See discussions back in May --- the fix went in here:

2004-06-02 13:28 tgl

* src/: backend/access/nbtree/nbtpage.c,
backend/access/nbtree/nbtree.c, backend/access/nbtree/nbtsort.c,
backend/access/nbtree/nbtxlog.c, backend/storage/smgr/md.c,
backend/storage/smgr/smgr.c, include/access/nbtree.h,
include/storage/smgr.h: Adjust btree index build to not use shared
buffers, thereby avoiding the locking conflict against concurrent
CHECKPOINT that was discussed a few weeks ago. Also, if not using
WAL archiving (which is always true ATM but won't be if PITR makes
it into this release), there's no need to WAL-log the index build
process; it's sufficient to force-fsync the completed index before
commit. This seems to gain about a factor of 2 in my tests, which
is consistent with writing half as much data. I did not try it
with WAL on a separate drive though --- probably the gain would be
a lot less in that scenario.

regards, tom lane