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 01:09:48
Message-ID: 20080122170353.K27580@megazone.bigpanda.com (view raw or flat)
Thread:
Lists: pgsql-bugs
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.

In response to

pgsql-bugs by date

Next:From: Kris JurkaDate: 2008-01-23 08:51:39
Subject: Re: BUG #3894: JDBC DatabaseMetaData.getTables is inconsistently case-sensitive with schema name
Previous:From: Tom LaneDate: 2008-01-23 00:48:31
Subject: Re: bad message or bad privilege check in foreign key constraint

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