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
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
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend