Thread: Question about row visibility after locks
I've been wondering (and probably should look through the code, but figured asking would be faster) if there's any guarantee that I'll see rows inserted by a transaction I'm waiting on in the middle of a query. Basically, if I've got a select that's running using HeapTupleSatisfiesDirty so that I can see uncommitted rows and I block in the middle for another transaction (waiting to see if it commits the row I'm looking at) and that transaction inserts another row that meets my search criteria am I guaranteed to see that second row in all cases? Basically for the foreign keys, I'm doing something like the above, but I'm worried that if the waiting for transaction does something like: insert ...-- At this point the select runs delete ... insert ... that I won't see the last row and I'll let through an update or delete that should have failed.
Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> Basically, if I've got a select that's running using
> HeapTupleSatisfiesDirty so that I can see uncommitted rows
> and I block in the middle for another transaction (waiting
> to see if it commits the row I'm looking at) and that
> transaction inserts another row that meets my search criteria
> am I guaranteed to see that second row in all cases?
No; it might get inserted into a page you've already scanned over.
You'd have to restart your scan if you wanted that.
regards, tom lane
On Sat, 9 Nov 2002, Tom Lane wrote: > Stephan Szabo <sszabo@megazone23.bigpanda.com> writes: > > Basically, if I've got a select that's running using > > HeapTupleSatisfiesDirty so that I can see uncommitted rows > > and I block in the middle for another transaction (waiting > > to see if it commits the row I'm looking at) and that > > transaction inserts another row that meets my search criteria > > am I guaranteed to see that second row in all cases? > > No; it might get inserted into a page you've already scanned over. > You'd have to restart your scan if you wanted that. Okay, that's what I figured, but wanted to check before doing something like that.