Re: CREATE INDEX and HOT - revised design

Pavan Deolasee wrote:
> My argument is that its enough to index only the LIVE tuple which
> is at the end of the chain if we don't use the new index for queries
> in transactions which were started before CREATE INDEX. I am
> proposing to do that by storing an xid in the pg_index row. A
> special case is where a tuple is UPDATEd multiple times by
> the same transaction which is also creating the index, in which case
> there are more than one LIVE versions of the tuple. But again
> we are safe by indexing only the latest version because all other
> versions would be invisible (even to us) once CREATE INDEX commits.

What if CREATE INDEX is run in a SERIALIZABLE transaction?

> > In fact, the serializable transactions started before CREATE INDEX
> > > can not anyway see the index so all this is done to handle
> > > read-committed transactions.
> >
> > You are laboring under an illusion that system catalog accesses are MVCC.
> > SnapshotNow does not behave that way: the system can see the new index
> > as soon as it's committed. (It had better, since it has to start
> > updating the index immediately, whether it's safe to scan it or not.)
> > I'm not sure whether that's fundamental to your argument or not, but
> > it's certainly wrong.
>
>
>
> Oh, thanks for pointing that out. But thats certainly not fundamental
> to the argument as you probably already guessed. The xid still controls
> the usage of index for query planning, somewhat similar to "isindvalid"
> flag for CREATE INDEX CONCURRENTLY.

I am glad you found the pg_index xid actually helps in other ways.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +