CREATE INDEX and HOT (was Question: pg_class attributes and race conditions ?) - Mailing list pgsql-hackers

How do we move forward with the CREATE INDEX issue with
HOT ? There are quite a few suggestions and objections.
Can we please discuss and decide on the plan ? I am very
comfortable with the current state of HOT, the results
are encouraging and I hope this issue does not become
a showstopper.

Here is what different people have suggested:

1. Simon and I posted couple of designs which include prior
suggestions from Heikki, Tom, Hannu and others. I believe
these designs are not very distinct and if done correctly
would help us keep the current behavior of CREATE INDEX
and CREATE INDEX CONCURRENTLY same. There are concerns
about race conditions and deadlock issues though. What
is a general feeling at this point ? Are these issues very
hard to address ? At a high level, IMO we need some or all
of these things to make either of these designs work:
   - ability to enable/disable HOT-updates on the table   - ability to enable/disable HOT-fetches on the table   -
abilityto wait for concurrent index scans to finish   - ability to allow only one backend to CHILL the table
 

How hard would these be without causing any race conditions
and deadlocks ?


2. Heikki suggested an approach where we add a byte
to tuple header and track HOT-ness of different indexes.
The idea looks good but had a downside of increasing tuple
header and complexity.


3. Alvaro suggested to have a ALTER TABLE .. SET CHILL kind
of syntax to chill the table. I would like to extend that
a bit further:

Can we have a mechanism to explicitely ON/OFF HOT-updates
on tables ? By default, HOT is disabled. If user wants to
take advantage of HOT, he/she should do something like:

ALTER TABLE test SET HOT ON;

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.

This infrastructure would ensure that the current
behavioral expectations of CREATE INDEX [CONCURRENTLY]
don't suddenly change unless user explicitely turns
HOT on. In that case, we can very well assume that the
user is aware of the advantages/disadvantages of HOT
and also cost associated with creating a new index on
HOT-updated tables. This would also allow users
experiment with HOT and turn it off if there is no gain
in their environment.

Any thoughts on the these approaches ? What is feasible
and acceptable ?

I am more inclined towards  the third approach for this
release and improve things later on. Is this acceptable ?


Thanks,
Pavan


-- 


EnterpriseDB        http://www.enterprisedb.com