Re: CREATE INDEX and HOT - revised design - Mailing list pgsql-hackers

Heikki Linnakangas wrote:
> 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.
> 
> When exactly would all HOT chains be dead? AFAICS, that would be after 
> the xid of CREATE INDEX gets older than oldest xmin, and VACUUM is run 
> to prune and pointer-swing all HOT chains.

Yes, I think so.  We might need to have pg_autovacuum involved in
checking pg_index and doing a vacuum when needed, but that is only for
the boolean idea;  the xid idea doesn't need it, I think.

> Would we have to wait after setting the new forbid_hot_updates-flag in 
> pg_class, to make sure everyone sees the change? What if CREATE INDEX 
> crashes, would we need a vacuum to reset the flag?

Uh, I am thinking we would just set it at the start of CREATE INDEX, but
actually if we do the pg_index xid idea, we don't need to tell them
anything --- once they see the new index in pg_index, they will
automatically know whether to include the new index in the HOT tests
based on its own xid.  Right now, if a new index is created, existing
backends know they need to insert into it, so it would be the same
mechanism, except they have to check the pg_index xid field.

If we use the xid idea, and CREATE INDEX crashes, there is no problem
because all the backend just keep going and never see the new pg_index
xid entry.

> > A more sophisticated idea would be to place an xid, rather than a
> > boolean, in pg_index to indicate which chains were created after the
> > index was created to control whether the index should follow that HOT
> > chain, or ignore it.  The xmax of the head of the HOT chain can be used
> > as an indicator of when the chain was created.  Transactions started
> > before the pg_index xid could continue following the old rules and
> > insert into the _new_ index for HOT chain additions, and new
> > transactions would create HOT chains that could skip adding to the new
> > index.  Cleanup of the hybrid HOT chains (some indexes take part, some
> > do not) would be more complex.  
> 
> What xid would you place in pg_index? Xid of the transaction running 
> CREATE INDEX, ReadNewTransactionId() or what?

I would say the current xid counter value at time of index creation
completion, so currently running transactions are unaffected, but newly
started transactions create HOT chains that include the new index, and
we just cleanup the old hot chains as the currently-running transactions
complete.

> How does that work if you have a transaction that begins before CREATE 
> INDEX, and updates something after CREATE INDEX?

OK, uses the old rules of always inserting into the new index, even if
it is creating a HOT chain.  The bottom line is that we would now have
chains with different indexes involved, and the HOT head xmax compared
to pg_index tells us which indexes are involved in the chain.

> > I know we have xid wrap-around, but I think the VACUUM FREEZE could
> > handle it by freezing the pg_index xid column value when it does the
> > table.
> 
> I don't think you can freeze the xid-column, we went through a similar 
> discussion on pg_class.relfrozenxid. But you can move it forward to 
> oldest xmin.

Sounds good.

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