"Sailer, Denis (YBUSA-CDR)" <Denis.Sailer@xxxxxxxxxxxxxx> writes: > The following errors also showed up. So my question is why does the > failure of CREATE INDEX cause all my connections to be terminated and > the database to go into recovery mode? Sounds kind of drastic. Not having any xlog space is fatal across-the-board: there is no place to log anybody's transactions, not just the CREATE INDEX. However, with reasonable checkpointing parameters this shouldn't happen because checkpoints will occur frequently enough to reclaim the xlog space. I believe you got bit by the 7.4 problem that allows checkpoints to get blocked by CREATE INDEX operations. This is fixed in 8.0: 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. > Finally can someone give me a calculation that would tell me how much > log space the CREATE INDEX command is going to take? About as much as the finished index will occupy, which you did not give enough information to estimate. > I still need to > get the index created with the system I have. You might consider updating to 8.0 instead... regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
