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

On Sat, 2007-03-17 at 12:43 +0530, Pavan Deolasee wrote:
> 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.

My suggested plan would be:

First, we should make CREATE INDEX CONCURRENTLY work, since we have a
design to do that. Many other things are acceptable if that works fully
and correctly with HOT, if it cannot, we have problems and that is a
showstopper. 

Second, I can't put my finger on it exactly, but I think having a DDL
command to chill a table, as Alvaro suggests, sounds like a very wise
utility to include, whatever we do later. That does have the useful
by-product of making CREATE INDEX work without change, but I would not
want to rely on it in the longer term.

While those are happening we can explore the other possibilities in more
detail, but they do seem like safe bets to me.

> 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. 

Agreed. Apologies for confusing things by posting the second design,
that wasn't very helpful or polite.

> 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

That seems like a good summary of what we need. I would add only that
these need not be explicit user-level commands, just internal
capabilities.

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

We should be able to take advantage of two things:

- We only need to chill some of the tuples, in some cases.

- Chilling only requires us to lock the indexes, which would be more
practical if the index locking were more transitory.

I note that IndexScans hold the locks they have on an index until
transaction end, even after the IndexScans and BitmapIndexScans
complete. ISTM we could relax that locking; that seems OK since we
earlier discussed removing locks completely on indexes. The lock on the
heap would not be upgraded or changed.

> 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.

I'm very comfortable with the idea that HOT can be turned on/off for a
table. That gives us a workaround to bugs. Previously, changing things
like WITHOUT OIDS was done over two releases, so I'd suggest the same
thing here. Add the option now, disabled, then look to make it the
default option in the next release. We can override that with the
default_use_hot parameter for those that feel bold, at least initially.
I know Bruce has been long opposed to the idea of a table-level switch,
which is why we've been trying so hard to avoid it. So we should add his
-1 to this idea from the start.

Right now, ideas around (2) sound like too much complexity. Maybe
there's a good idea there waiting to break out, so we should pursue that
also - but I'm not sure you can wait for that to happen.

--  Simon Riggs              EnterpriseDB   http://www.enterprisedb.com