
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.



Fri, 8 Nov 2002 09:42:57 -0800 (PST)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Question about row visibility after locks
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

Sat, 09 Nov 2002 10:43:27 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Question about row visibility after locks 
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.


Sat, 9 Nov 2002 11:03:05 -0800 (PST)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: Question about row visibility after locks 