Ühel kenal päeval, T, 2005-12-06 kell 15:12, kirjutas Tom Lane: > 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),
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 ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
