Re: Incomprehensible behaviour of a foreign key. - Mailing list pgsql-general

As usual I forgot to include the version number. It's 7.3.3


On Sun, 20 Jul 2003, Nigel J. Andrews wrote:

>
>
> I'm completely baffled by this thing, the work it is for is extremely urgent
> and this is currently a show stopper. My minimal test script showing the
> problem is attached and the output is shown below.
>
> There is no other connection to the db, indeed I have been stopping and
> starting the backend itself before each of my test runs this morning and once
> again the shown output is obtained after doing that and opening this one and
> only this one connection to the db.
>
> I'd really appreciate an explanation, since this test is based on queries
> extracted from the db log, is only one specific example of this sort of
> operation from many in the driving program and most significantly it seems I
> can't even write sql statments hardcoding these values as the test script shows
> they still get the ref. int. error.
>
> If necessary I will absolutely turn on auto commit after each statement in
> order to get this block of code to run but once again the test script shows
> that this will make sod all difference since it's the completely empty table
> before the transaction even starts that is causing the problem.
>
>
>
> **** Start by showing the problem table is empt _before_ the transaction starts
> select * from site_membership;
>  id | site_id | group_id
> ----+---------+----------
> (0 rows)
>
> begin;
> BEGIN
> **** Move some other references out of the way
> update sections set group_id = 207
> where
> exists (select 1
> from groups g
> where
> (g.principal_user_id = 144 or g.name = Press Office )
> and
> g.id <> 207
> and
> group_id = g.id
> )
> ;
> UPDATE 12
> **** Show what we will be trying to delete
> select * from groups
> where
> exists (select 1
> from groups g
> where
> (g.principal_user_id = 144 or g.name = Press Office )
> and
> g.id <> 207
> and
> groups.id = g.id
> )
> ;
>  id  | active | site_id | principal_user_id |     name     | summary
> -----+--------+---------+-------------------+--------------+---------
>  173 | t      |         |               113 | Press Office |
>  206 | t      |         |               140 | Press Office |
>  211 | t      |         |               153 | Press Office |
> (3 rows)
>
> **** Attempt the delete ...
> **** ...and watch the empty table from the start cause a ref. int. failure!
> delete from groups
> where
> exists (select 1
> from groups g
> where
> (g.principal_user_id = 144 or g.name = Press Office )
> and
> g.id <> 207
> and
> groups.id = g.id
> )
> ;
> psql:/tmp/aa2.sql:101: ERROR:  $2 referential integrity violation - key in groups still referenced from
site_membership
>
>
>

--
Nigel J. Andrews
Telephone: +44 (0) 208 941 1136