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, so maybe 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)

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

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?


---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

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.

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings