Re: CREATE INDEX and HOT (was Question: pg_class attributes and race conditions ?)

Tom Lane wrote:
> "Pavan Deolasee" <pavan(dot)deolasee(at)enterprisedb(dot)com> 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