Обсуждение: bad message or bad privilege check in foreign key constraint
apparently revoking update rights on referencing table blocks deletes on master table: (user test, database test, user test is not superuser) > create table a (id serial primary key, x text); NOTICE: CREATE TABLE will create implicit sequence "a_id_seq" for serial column "a.id" NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "a_pkey" for table "a" CREATE TABLE > create table b (id serial primary key, a_id int4 references a(id) ); NOTICE: CREATE TABLE will create implicit sequence "b_id_seq" for serial column "b.id" NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "b_pkey" for table "b" CREATE TABLE > insert into a (id) values (1); INSERT 0 1 > insert into b (a_id) values (1); INSERT 0 1 > 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" now, i know that the sql shown in context comes from foreign key trigger, but is definitely not obvious for everybody, somaybe the message should be cleared a bit in this case. on the other hand - i'm not really sure that update rights should be neccessary in such case. best regards, depesz -- quicksil1er: "postgres is excellent, but like any DB it requires a highly paid DBA. here's my CV!" :) http://www.depesz.com/ - blog dla ciebie (i moje CV)
hubert depesz lubaczewski <depesz@depesz.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?
regards, tom lane
On Tue, 22 Jan 2008, Tom Lane wrote: > hubert depesz lubaczewski <depesz@depesz.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?
Stephan Szabo <sszabo@megazone.bigpanda.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.
regards, tom lane
On Tue, 22 Jan 2008, Tom Lane wrote: > Stephan Szabo <sszabo@megazone.bigpanda.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.