Jochem van Dieten <[EMAIL PROTECTED]> 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 ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings