Re: URGENT pg_xlog full impossible to restart ...

=?iso-8859-15?q?Herv=E9_Piedvache?= <footcow@noos.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