On Sat, 2007-03-17 at 11:45 -0400, Tom Lane wrote: > "Pavan Deolasee" <[EMAIL PROTECTED]> writes: > > While creating an index, if a HEAP_ONLY tuple is found, > > CREATE INDEX [CONCURRENTLY] fails with an error and the > > user needs to SET HOT OFF and then try again. While turning > > HOT off, the entire table is CHILLed, holding AccessExclusive > > lock on the table. Once the new index is created, user > > can turn HOT on again. > > It hardly seems acceptable to require exclusive lock to chill a table. > In production situations, knowing that you'd have to do that to do > index maintenance on a large table would probably scare you off of > ever enabling the feature at all. Last year we were getting beaten up > about how it wasn't acceptable for CREATE INDEX to lock out writes > for a long time; how is it suddenly acceptable to need to lock out > both reads and writes for a long time before you can even think > about creating an index?
I agree with you: It isn't acceptable for us to contemplate an AccessExclusiveLock before we can build any index. We *must* make CREATE INDEX CONCURRENTLY work with HOT. The good news is I think we can without significant difficulty. The problems are with CREATE INDEX, in some cases. I regret that I did not see those difficulties until recently, which is why I was concerned that we spent time on VACUUM FULL rather than this issue. I'm glad now that you both pressed ahead and solved that though. As a result of the issues, I think Pavan is playing safe, to make sure there is *an* option, so that we can build upwards from there. The proposal is pragmatism only, while we discuss other approaches. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
