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? >
Yeah, I agree. I was proposing this as a stop-gap solution though. Something which would help us solve the problem without changing the current behavior for non-HOT tables. So what do you suggest ? Do you feel that there is no way we can solve the problem ? ISTM that if we run CHILL as a seperate transaction (just like VACUUM), we should be able to CHILL the table with ShareUpdateExclusiveLock. Running it as a seperate transaction would reduce the risk of causing deadlocks. Is that a fair assessment ? If we need to CHILL with ShareUpdateExclusiveLock, IMHO we would again be back to something similar to the first approach. I know you said its fragile and full of race conditions, but do you think we can handle it better if we have a seperate DDL command, running within its own transaction ? The algorithm would look like: 1. Disable HOT-updates 2. CHILL the table by inserting appropriate index entries and marking tuples CHILL_IN_PROGRESS 3. Establish a point when there are no open index scans 4. Disable HOT-fetches 5. Scan the heap again, reset CHILL_IN_PROGRESS, HEAP_ONLY and HOT_UPDATED flags 6. Establish a point when there are no open index scans 7. Enable HOT-fetches 8. Enable HOT-updates I need help to do the step 1,3,4,6,7 and 8 (well all :-)) in a deadlock and race condition free mannner. Any suggestions ? Where do we keep the global state about HOT-updates/HOT-fetches ? If we keep it in pg_class, a crash of the CHILL command or the server may leave the pg_class row in a stale state. That does not look like a problem though. In the worst case, we might not be able to do HOT-updates without manual intervention. Again comments, suggestions ? I really appreciate everyone's time and patience. Help is what I need to solve this problem. Thanks, Pavan -- EnterpriseDB http://www.enterprisedb.com ---------------------------(end of broadcast)--------------------------- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
