Re: Concurrent CREATE INDEX, try 2 (was Re: Reducing

Ühel kenal päeval, T, 2005-12-06 kell 15:12, kirjutas Tom Lane:
> Jochem van Dieten <jochemd(at)gmail(dot)com> writes:
> > On 12/5/05, Hannu Krosing wrote:
> >> 3) record the index in pg_class, but mark it as "do not use for lookups"
> >> in a new field. Take snapshot SNAP2. commit transaction.
>
> > What happens if another transaction takes a snapshot between SNAP2 and
> > the commit? Wouldn't you need a lock to guard against that? (Not that
> > I don't know if that is possible or desirable.)
>
> It's worse than that, because an updating command that is already
> running has already made its list of which indexes to update. You can't
> say "commit" and expect transactions already in flight to react
> magically to the presence of the new index. If you take a lock that
> excludes writes, and then release that lock with your commit (lock
> release actually happens after commit btw),

Is it possible to release a lock without commit ?

> then you can be sure that
> subsequent write transactions will see your new index, because they take
> their writer's lock before they inspect pg_index to see what indexes
> they need to update.
>
> Short of taking such a lock, you have a race condition.
>
> There's another little problem: it's not clear that "present in SNAP2
> but not in SNAP1" has anything to do with the condition you need. This
> would exclude rows made by transactions still in progress as of SNAP2,
> but you can't know whether such rows were made before or after your
> commit of the index. It doesn't do the right thing for deleted rows
> either (deleted rows may still need to be entered into the index),
> though perhaps you could fix that with a creative reinterpretation of
> what "present in a snap" means.

It seems that taking SNAP1 also needs to be fitted between any other
transactions (i.e no transaction can be running at the time) which can
hopefully be done as I outlined to my other answer to grandparent.

-----------------
Hannu