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 ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings