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/ ------------