Skip site navigation (1) Skip section navigation (2)

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

From: Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: depesz(at)depesz(dot)com, pgsql-bugs(at)postgresql(dot)org
Subject: Re: bad message or bad privilege check in foreign key constraint
Date: 2008-01-23 00:36:28
Message-ID: 20080122161131.U24490@megazone.bigpanda.com (view raw or flat)
Thread:
Lists: pgsql-bugs
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?


In response to

Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2008-01-23 00:48:31
Subject: Re: bad message or bad privilege check in foreign key constraint
Previous:From: Adam HardyDate: 2008-01-22 23:42:17
Subject: BUG #3894: JDBC DatabaseMetaData.getTables is inconsistently case-sensitive with schema name

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group