Incomprehensible behaviour of a foreign key.

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