Re: CREATE INDEX and HOT - revised design

On 3/23/07, Hannu Krosing <hannu(at)skype(dot)net> 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.
>
> You mean, which were started before CREATE INDEX completes ?
>
> Just wait for all concurrent transactions to complete before marking the
> index as usable in plans.

I thought there will be objections to that approach since it changes
the CREATE INDEX behavior and may also lead to new deadlock
conditions.

> I am
> > proposing to do that by storing an xid in the pg_index row.
>
> I don't think it is a good idea to store xid's anywhere but in xmin/xmax
> columns, as doing so would cause nasty xid wraparound problems.

May be we can use VACUUM to cleanup the xid. If xid is less than
OldtestXmin, the index is visible to all the transactions and can be
marked as InvalidTransactionId.

> >
> >
> > 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.
>
> Xids are unstable and will come back to bite you after 2G transactions.
>
> Why not just use the "isindvalid" flag ?
>
>
Who would set the flag to true ? Unless of course we are waiting in
CREATE INDEX. But that seems to be less acceptable to me.

Thanks,
Pavan

--

EnterpriseDB http://www.enterprisedb.com