Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> On Wed, 24 Sep 2003, Tom Lane wrote:
>> But I'm wondering why we have to do this at all.)
> I think if you have something like:
> create table test1 (id int primary key, otherid int references test1);
> insert into test1 values (4,4);
> T1: begin;
> T1: set transaction isolation level serializable;
> T1: select * from test1;
> T2: begin;
> T2: insert into test1 values (5,4);
> T2: end;
> T1: delete from test1 where id=4;
> -- I think the standard snapshot rules would mean that the row 5,4 would
> be hidden from the select in the trigger, which means that the delete
> would be allowed, where it should fail since that'd leave an orphaned
> child row.
Ah, I see. And the reason there's no race condition with SnapshotNow is
that T2 took a SELECT FOR UPDATE lock on the id=4 row, so the DELETE
couldn't succeed until T2 commits.
Okay, I'll work out some extension of the APIs to let us propagate the
snapshot request down through SPI and into the Executor, rather than
using a global variable for it. (Unless someone has a better idea...)
regards, tom lane
