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

My problem arises in a subset of three tables of my database, foo, bar, and
baz.
Foo hold high level information about an activity
Bar holds a many to many mapping of people to activities
baz holds some predicted information about the size of the mappings and the
current registrations and is keyed by the same values as Foo (ie its primary
key is also declared "references foo on delete cascade on update cascade)

(Yes I am aware that holding the current registrations is redundant and
could be generate using select count(*) from bar where activityid="baf", but
for performance reasons I was advised to keep it current with rules.)

So I made the tables, and the rules for insert,update, and deletes so that
bar and baz are kept consistent.  Now I want to automate the initialization
of baz on the creation of foo.  I thought I could continue to use rules,
such as
create rule foo_insert on insert to foo as insert into baz values
(new.activityid,0,0);

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.

Can anyone confirm my hypothesis?  Is there a solution using rules, or do I
need to use triggers, which seem to have a notion of before and after? (I
have not examined triggers in detail yet)

Thank you
Eric Nielsen

So I solved my problem using a trigger/function combo, but I would still
like to know if what I was trying is possible using rules (see below).

Thank you.
Eric
----- Original Message -----
From: Eric Nielsen <ericnielsen@earthlink.net>
To: <pgsql-novice@postgresql.org>
Sent: Thursday, January 04, 2001 11:13 PM
Subject: I think I know what I'm doing wrong, but....


> My problem arises in a subset of three tables of my database, foo, bar,
and
> baz.
> Foo hold high level information about an activity
> Bar holds a many to many mapping of people to activities
> baz holds some predicted information about the size of the mappings and
the
> current registrations and is keyed by the same values as Foo (ie its
primary
> key is also declared "references foo on delete cascade on update cascade)
>
> (Yes I am aware that holding the current registrations is redundant and
> could be generate using select count(*) from bar where activityid="baf",
but
> for performance reasons I was advised to keep it current with rules.)
>
> So I made the tables, and the rules for insert,update, and deletes so that
> bar and baz are kept consistent.  Now I want to automate the
initialization
> of baz on the creation of foo.  I thought I could continue to use rules,
> such as
> create rule foo_insert on insert to foo as insert into baz values
> (new.activityid,0,0);
>
> 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.
>
> Can anyone confirm my hypothesis?  Is there a solution using rules, or do
I
> need to use triggers, which seem to have a notion of before and after? (I
> have not examined triggers in detail yet)
>
> Thank you
> Eric Nielsen
>
>
>
>

"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

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