On Fri, 26 Sep 2003, Kevin Brown wrote:
> Tom Lane wrote:
> > Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> > > I think theoretically in serializable the cases where the difference
> > > between the snapshot from this statement and the standard snapshot for the
> > > transaction are noticable we probably have a serialization failure
> >
> > Hmm, that is a good point. It would be cleaner to throw a "can't
> > serialize" failure than have the RI triggers run under a different
> > snapshot. I am not sure if we can implement that behavior easily,
> > though. Can you think of a way to detect whether there's an RI conflict
> > against a later-started transaction?
>
> Just some thoughts on this that, of course, could be wrong. So please
> don't be too hard on me if I'm full of it. :-)
>
> By "a later-started transaction" I assume you mean a later-started
> transaction that commits before yours does?
...
> But if PG exhibits exactly the same bug this thread refers to regardless
> of whether a row is examined/modified via directly issued SQL or via
> the RI mechanism then the problem lies not within the RI code at all,
> but within the serialization code.
It's actual a different problem from the original one at this point. If
one just switches to using whatever snapshot is in place for the
transaction, you run into the problem that our serializable isolation mode
isn't entirely serializable and therefore isn't sufficient to guarantee
that the constraint is satisfied.
The case at hand (with *'s on the ri queries) assuming pk already
has an id=1 row would be.
T1: begin;
T1: set transaction isolation level serializable;
T1 ... (something that does a select, not necessarily on either pk or fk)
T2: begin;
T2: insert into fk values (1);
T2*:select * from pk where id=1 for update;
T2: commit;
T1: delete from pk where id=1;
T1*:select * from fk where id=1 for update;
T1: commit;
If you want to treat the serial execution as T1 followed by T2. Then
T2* would have to show no rows for pk and T2 rolls back.
If you want to treat the order as T2,T1, then T1* would have to see the
row that T2 inserted and T1 rolls back.
Right now, you won't get that, you'll get T2* showing 1 row and T1*
showing 0 rows.