On 3/21/07, Bruce Momjian <[EMAIL PROTECTED]> wrote:

Bruce Momjian wrote:
> I have read the HOT discussion and wanted to give my input.  The major
> issue is that CREATE INDEX might require a HOT chain to be split apart
> if one of the new indexed columns changed in the HOT chain.

To expand a little more, the problem is that when you split those HOT
chains, you have to insert new entries into the _existing_ indexes,
causing problems for concurrent sequential scans.

You mean index scans, right ? Sequential scans don't change with HOT.

I have a new idea.  There has been a lot of focus on trying to tag each
tuple to indicate whether it is part of a HOT chain for individual
indexes, the idea being that some indexes will follow the HOT chain, and
some will not.

In general I would like to preserve the HOT properties at the end of
creation of new index. All index should point to the same root tuple
if pointing to a HOT-chain. The things might become otherwise messy
with the line pointer redirection, reuse of LP_DELETEd tuples and
other things that we have put in.

Disabling HOT-updates using pg_class attribute has same issue
with my earlier proposal. What do we do if the backend crashes before
it can enable it again ? Who would reset the flag ? We could have lived
without DDL and CHILL command if we would have answers for
these questions.

A different idea here:

Would it be acceptable to force CREATE INDEX to be run outside
a transaction block just like CREATE INDEX CONCURRENTLY ? If thats
acceptable, we can do the following:


Since CREATE INDEX locks out table for UPDATEs, we just need
to wait enough before we start the heap scan so that when we do
heap scan, all HOT-chains can be pruned to a single tuple (with or
without the redirected line pointer). So when the new index is built,
we first prune the entire page of HOT-chains and insert the TID of
the root tuple into the new index. IOW we need to wait for all
transactions in the snapshot after acquiring ShareLock but before
we start heap scan.


Simon has already posted a design that would work with CREATE
INDEX CONCURRENTLY. I think we need to tweak it a bit so
that we can handle the HOT-updated tuples after then first heap
scan, but before the index is visible to all transactions. Once the
new index is visible, the heap_update() code path would take care
of avoiding HOT-updates if the column on which new index is being
built is updated.

It seems much simpler to me do something like this. But important
question is whether the restriction that CREATE INDEX can not
be run in a transaction block is acceptable ?

Comments ?



EnterpriseDB     http://www.enterprisedb.com

Reply via email to