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 - ability to 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 ---------------------------(end of broadcast)--------------------------- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate