Re: CREATE INDEX and HOT (was Question:pg_classattributes and race conditions ?) - Mailing list pgsql-hackers

On Mon, 2007-03-19 at 09:28 +0000, Simon Riggs wrote:
> 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.

AFAICS this is all you need to make CREATE INDEX CONCURRENTLY work with
HOT, which is even simpler than my original post. [This presumes that we
do pruning automatically on a heap scan, not sure what the current state
of that is, but it could be a scan option].

Index: src/backend/commands/indexcmds.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/commands/indexcmds.c,v
retrieving revision 1.157
diff -c -r1.157 indexcmds.c
*** src/backend/commands/indexcmds.c    13 Mar 2007 00:33:39 -0000
1.157
--- src/backend/commands/indexcmds.c    19 Mar 2007 09:49:56 -0000
***************
*** 497,507 ****       ActiveSnapshot = snapshot;
       /*
-        * Scan the index and the heap, insert any missing index
entries.
-        */
-       validate_index(relationId, indexRelationId, snapshot);
-
-       /*        * The index is now valid in the sense that it contains all
currently        * interesting tuples.  But since it might not contain tuples
deleted just        * before the reference snap was taken, we have to wait out any
--- 497,502 ----
***************
*** 514,519 ****
--- 509,519 ----       for (ixcnt = 0; ixcnt < snapshot->xcnt; ixcnt++)
XactLockTableWait(snapshot->xip[ixcnt]);

+       /*
+        * Scan the index and the heap, insert any missing index
entries.
+        */
+       validate_index(relationId, indexRelationId, snapshot);
+       /* Index can now be marked valid -- update its pg_index entry */       pg_index = heap_open(IndexRelationId,
RowExclusiveLock);



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