Tom Lane wrote:> "Pavan Deolasee" <pavan.deolasee@enterprisedb.com> writes:>> While creating an index, if a HEAP_ONLY
tupleis 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
indexis 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
probablyscare you off of> ever enabling the feature at all. Last year we were getting beaten up> about how it wasn't
acceptablefor CREATE INDEX to lock out writes> for a long time; how is it suddenly acceptable to need to lock out> both
readsand 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
