From: | "Nigel J(dot) Andrews" <nandrews(at)investsystems(dot)co(dot)uk> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Incomprehensible behaviour of a foreign key. |
Date: | 2003-07-20 13:15:30 |
Message-ID: | Pine.LNX.4.21.0307201415050.16690-100000@ponder.fairway2k.co.uk |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Markus Bertheau | 2003-07-20 13:23:16 | Re: Incomprehensible behaviour of a foreign key. |
Previous Message | Nigel J. Andrews | 2003-07-20 12:34:22 | Incomprehensible behaviour of a foreign key. |