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@momjian.us>          http://momjian.us EnterpriseDB
http://www.enterprisedb.com
 + If your life is a hard drive, Christ can be your backup. +