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].
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
--- 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
- validate_index(relationId, indexRelationId, snapshot);
* The index is now valid in the sense that it contains all
* interesting tuples. But since it might not contain tuples
* 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++)
+ * Scan the index and the heap, insert any missing index
+ validate_index(relationId, indexRelationId, snapshot);
/* Index can now be marked valid -- update its pg_index entry */
pg_index = heap_open(IndexRelationId, RowExclusiveLock);
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend