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
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 ?) |