Re: CREATE INDEX and HOT - revised design

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.

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?

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

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

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

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com