Re: CREATE INDEX and HOT (was Question:pg_classattributes and race conditions ?)

On Mon, 2007-03-19 at 14:33 +0530, Pavan Deolasee wrote:
> Simon Riggs wrote:
> >
> >
> > We *must* make CREATE INDEX CONCURRENTLY work with HOT. The good news is
> > I think we can without significant difficulty.
> >
>
> Yeah, I think CREATE INDEX CONCURRENTLY is much easier to solve. Though
> I am not completely convinced that we can do that without much changes
> to CREATE INDEX CONCURRENTLY logic. For example, I believe we still
> need to lock out HOT-updates before we start CREATE INDEX CONCURRENTLY.
> Otherwise we might end up creating two paths to the same tuple in
> the new index.
>
> Say, we have a table with two columns (int a, int b). We have an
> index on 'a' and building another index on 'b'. We got a tuple
> (10, 20) in the heap. In the first phase of CREATE INDEX CONCURRENTLY,
> this tuple would be indexed. If the tuple is HOT-updated to (10, 30)
> before the first phase ends, the updated tuple would again get
> indexed in the second phase. This would lead to two paths to the
> latest visible tuple from the new index.
>
> Am I missing something in your design that stops this from
> happening ?

This problem is solved by moving the wait (for all transactions in
reference snapshot to finish) so that it is now between the first and
second scans, as described.

During the second scan we would prune each block, so the only remaining
tuple in the block when the second scan sees it would be (10,30) and it
would no longer be a HOT tuple - the index would have a pointer to it,
so no new index pointer would be added. The pointer to (10,30) is the
same pointer that was added in the first phase for the tuple (10,20).

The wait and subsequent prune ensures that all HOT tuples are now the
root of their HOT chain. The index created in the fist phase ensures
that the HOT chains are never added to.

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com