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

From: "Simon Riggs" <simon(at)2ndquadrant(dot)com>
To: "Pavan Deolasee" <pavan(dot)deolasee(at)enterprisedb(dot)com>
Cc: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>, <pgsql-hackers(at)postgresql(dot)org>, "Heikki Linnakangas" <heikki(at)enterprisedb(dot)com>
Subject: Re: CREATE INDEX and HOT (was Question:pg_classattributes and race conditions ?)
Date: 2007-03-19 09:51:09
Message-ID: 1174297869.4160.685.camel@silverbirch.site
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Guillaume Smet 2007-03-19 10:01:08 Re: ILIKE and indexes
Previous Message Simon Riggs 2007-03-19 09:28:13 Re: CREATE INDEX and HOT (was Question:pg_classattributes and race conditions ?)