Re: Referencing Problem/bug?

On Fri, 2003-03-28 at 22:46, J A Stride wrote:

> However if I do an insert into the person resource (which means that there is
> a value in the resource table) and the resource group I cannot put the id's
> from the insert's into the cross reference table as it fails with:
>
> ERROR: <unnamed> referential integrity violation - key referenced from
> resourcegroupxref not found in resource

I had precisely this problem. I am working around it by using a couple
of triggers written in plpython that look in a referential_constraint
table that has rows like:

id | primary_key | foreign_key
----+-------------+----------------------------
1 | domain.id | domain_orderitem.domain_id

Now, atm I'm leaving as much as I can as "real" postgres references. I
believe that it will be faster this way.

The trigger executes before updates, deletes and inserts and just throws
an exception if anything goes wrong. I'll probably release it but not
just yet - it's a bit of a mess since it was the first thing I used
plpython for (it's also largely untested)

It needs some other things, like preventing inserts on base tables, and
some triggers on the referential constraint table to go along and add
the triggers to the appropriate tables.

It can be done, but it's the hackiest thing EVER.

HINT: you won't get it to work in plpgsql if you are inclined to try.

HTH,

James.