Re: Re: Referential Integrity problem...

On Thu, 30 Aug 2001, Joel Burton wrote:

> On Thu, 30 Aug 2001, Justin wrote:
>
> (cc'd to PG list in hopes that someone else might find this useful,
> or that someone else might have a better solution.)
>
> > Have you ever come across the problem of having multiple fields in a
> > table referring to one key field in another table?
> >
> > i.e.
> > table job (level integer, descript varchar(20))
> > table area_access (person_name varchar(40), left_wing integer references
> > job(level) on update cascade, right_wing integer references job(level)
> > on update cascade, foyer integer references job(level) on update
> > cascade)
> >
> > The problem with this is that whenever one of the fields in job.level is
> > updated, the RI trigger fires and tries to update the first field in
> > table area_access, the second field is now in violation of the RI
> > constraint though, so it errors out and rolls the transaction back.
> > Creating the table with DEFERRABLE and INITIALLY DEFERRED however
> > doesn't help, the RI triggers don't seem to fire until COMMIT is done,
> > at which time the second field to be checked violates the RI constraint
> > and the transaction is still rolled back. :(
> >
> > Any ideas?
>
> I've had this problem w/other DBs, but not w/PG. I'm not sure if this
> behavior is meant to be bug-for-bug compatible w/other products, or if
> (even worse) the standards demand it. Might want to send you post to
> hackers and see if they have any ideas on better fixes.

If I'm reading the original message correctly, he should be able to do
what he wants, but it is currently broken because only the final state
should be being checked, but we currently don't stop the checks on the
intermediate states.

I've got an patch around here somewhere that might help the situation,
although the patch was made against recent sources and needs to be cleaned
up alot before I'd submit it for inclusion (including the fact I think
there's a better way to do this than the first one I chose, and I haven't
had a chance to pursue it)