Re: Concurrent CREATE INDEX, try 2 (was Re: Reducing - Mailing list pgsql-hackers

From Jochem van Dieten
Subject Re: Concurrent CREATE INDEX, try 2 (was Re: Reducing
Date
Msg-id f96a9b830512071001k18646698kcc7adc4e242162b8@mail.gmail.com
Whole thread Raw
In response to Re: Concurrent CREATE INDEX, try 2 (was Re: Reducing  (Hannu Krosing <hannu@skype.net>)
List pgsql-hackers
On 12/6/05, Hannu Krosing wrote:
>
> 1) run a transaction repeatedly, trying to hit a point of no concurrent
> transactions, encance the odds by locking out starting other
> transactions for a few (tenths or hundredths of) seconds, if it
> succeeds, record SNAP1, commit and and continue, else rollback, then
> sleep a little and retry.

Which locks can be released by committing here?


> 2) build index on all rows inserted before SNAP1
>
> 3) run a transaction repeatedly, trying to hit a point of no concurrent
> transactions by locking out other transactions for a few (tenths or
> hundredths of) seconds, if it succeeds, record SNAP2, mark index as
> visible for inserts, commit. now all new transactions see the index and
> use it when inserting new tuples.
>
> 4) scan over table, add all tuples between SNAP1 and SNAP2 to index

You can not guarantee that every tuple inserted in the table will be
visible to SNAP 2 if you take SNAP2 before the commit of the
insert-only index has dropped below the global XMIN-horizon.


> 5) mark index as usable for query plans

How about:

- begin transaction X1  - insert all visible tuples in an index  - mark index incomplete
- commit

- wait for X1 to become visible to all running transactions (X1 is
known from the XMIN in pg_class / pg_index)

- begin transaction X2  - insert all missing tuples in index  - mark index complete
- commit

Jochem

pgsql-hackers by date:

Previous
From: Bruce Momjian
Date:
Subject: Foreign key trigger timing bug?
Next
From: Greg Stark
Date:
Subject: Re: Concurrent CREATE INDEX, try 2 (was Re: Reducing