Jochem van Dieten <jochemd@gmail.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), 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.
regards, tom lane