Re: CREATE INDEX and HOT - revised design

Simon Riggs wrote:
> On Wed, 2007-03-21 at 10:47 -0400, Bruce Momjian wrote:
>
> > A different idea is to flag the _index_ as using HOT for the table or
> > not, using a boolean in pg_index. The idea is that when a new index is
> > created, it has its HOT boolean set to false and indexes all tuples and
> > ignores HOT chains. Then doing lookups using that index, the new index
> > does not follow HOT chains. We also add a boolean to pg_class to
> > indicate no new HOT chains should be created and set that to false once
> > the new index is created. Then, at some later time when all HOT chains
> > are dead, we can enable HOT chain following for the new index and allow
> > new HOT chains to be created.
>
> "enable HOT chain following" would require us to hold an
> AccessExclusiveLock on the index.

OK.

> We know that solution exists, the question is: at what point would we
> ever request that lock? Or would we just wait until that lock is next
> taken before enabling it, giving the user no control over when its
> taken? A separate DDL command would be effectively the same as what
> Pavan has recently suggested.

We could just request a lock and if it fails, try again later; it just
delays HOT updates, which is not a big problem. Allowing the user to do
it via a separate command seems to add nothing. The only thing I could
see would be adding an option to CREATE INDEX which waits and then does
it.

However, given recent comments, I think the xid idea, while more
complicated, is better because it has fewer restrictions on when things
happen.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +