Re: bad message or bad privilege check in foreign key constraint

On Tue, 22 Jan 2008, Tom Lane wrote:

> Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com> writes:
> > On Tue, 22 Jan 2008, Tom Lane wrote:
> >> Hmm. I wonder why we are bothering with FOR SHARE locks on the
> >> referencing table, when we don't have any intention to change
> >> those rows. Is there some race condition that's needed to prevent?
>
> > I think it may be if you've done something like updated the row in another
> > transaction it waits for the final state of that transaction rather than
> > erroring immediately.
>
> > Given something like:
> > create table t1(a int primary key);
> > create table t2(b int references t1);
> > insert into t1 values (1);
> > insert into t1 values (2);
> > insert into t2 values (1);
> > T1: begin;
> > T2: begin;
> > T1: update t2 set b=2;
> > T2: delete from t1 where a=1;
> > -- I think here, if we don't use something that tries to get a row lock
> > -- the delete will fail because it still sees the t2 row having b=1
> > -- while with the lock, it'll succeed if T1 commits and fail if T1
> > -- aborts?
>
> But how much do we care about that? The case that's actually necessary
> for correctness, I think, is to block if we are trying to delete a=2
> --- but that happens because T1 took a shared row lock on that row.
> Doing it in the other direction too seems like it'll introduce
> performance and deadlock issues.

Well, from an end user standpoint, I think it's basically similar to the
case with unique where if you delete a row in T1 and try inserting a row
that would conflict in T2 before T1 commits, T2 waits rather than
immediately erroring.