Re: Re: I think I know what I'm doing wrong, but....

On Saturday 06 January 2001 12:04, Tom Lane wrote:
> "Eric Nielsen" <ericnielsen@earthlink.net> writes:
> >> However the insert fails, with a referential integrity problem.  I
> >> _think_ this is because it tries to do the rule's trigger before
> >> actually doing the original select, so the key does not exist.
>
> I think you are right.  Non-INSTEAD rule actions are executed before the
> original query is carried out.  I have argued in the past that this is
> stupid for ON INSERT rules, which would be better done after the
> original query, but I haven't had any luck getting agreement to change
> it.
>
> >> Is there a solution using rules,
>
> Only if you can use a delayed referential integrity check that isn't
> checked till end of transaction.  I don't know offhand if we even
> implement such a feature, let alone whether it'd be OK for your
> application logic.
>
>             regards, tom lane


The man-page for create_table has all the info in it about

CONSTRAINT [name]
    [NOT] DEFERRABLE
    INITIALLY DEFERRED|IMMEDIATE

NOT DEFERRABLE is the default.

I've used INITIALLY DEFERRED before to avoid these problems. If just
DEFERRABLE is used, then it is implicitly INITIALLY IMMEDIATE.  To make a
deferrable constraint deferred, SET CONSTRAINTS {ALL|name,...} DEFERRED has
to be done first.

--
-------- Robert B. Easter  reaster@comptechnews.com ---------
-- CompTechNews Message Board http://www.comptechnews.com/ --
-- CompTechServ Tech Services http://www.comptechserv.com/ --
---------- http://www.comptechnews.com/~reaster/ ------------