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

On Tue, 22 Jan 2008, Tom Lane wrote:

> hubert depesz lubaczewski <depesz(at)depesz(dot)com> writes:
> > apparently revoking update rights on referencing table blocks deletes on master table:
>
> >> revoke update on b from test;
> > REVOKE
>
> >> delete from a where id = 1;
> > ERROR: permission denied for relation b
> > CONTEXT: SQL statement "SELECT 1 FROM ONLY "public"."b" x WHERE $1 OPERATOR(pg_catalog.=) "a_id" FOR SHARE OF x"
>
> 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?