Обсуждение: INFINITE RECURSION with rules...

Hi to everyone.

I've got some problem with rules.

-- I have 2 tables

CREATE TABLE a (email varchar(20), name varchar(10), num1 smallint, num2 smallint, PRIMARY KEY (email, name));

CREATE TABLE b (id smallint PRIMARY KEY, email_a varchar(20), name_a varchar(10), tot smallint, FOREIGN KEY (email_a, name_a) REFERENCES a(email, name));

/*

My goal is to calculate and insert automatically the value of “tot” when I insert a row into table b.

*/

-- Some samples

INSERT INTO a VALUES ('mail1@email.com','bill',3,5);

INSERT INTO a VALUES ('2mail@email.com','paul',4,7);

-- Then I created a simple function

CREATE OR REPLACE FUNCTION calc(varchar(10), varchar(20)) RETURNS smallint AS $$

DECLARE

rowww a%ROWTYPE;

BEGIN

SELECT * INTO rowww FROM a WHERE email = $1 AND name = $2;

IF FOUND

THEN RETURN rowww.num1 * rowww.num2;

ELSE

RAISE WARNING 'Error: values non found!';

END IF;

END;

$$ LANGUAGE plpgsql;

-- And this easy rule

CREATE RULE rrr_a_b AS ON INSERT TO b

DO INSTEAD

INSERT INTO b VALUES

(NEW.id,

NEW.email_a,

NEW.name_a,

(SELECT calc(NEW.email_a, NEW.name_a))

);

-- Sample for insert into b

INSERT INTO b VALUES (33,'mail1@email.com','bill');

Trying to insert into b (and using the new rule defined by myself, i receive this message:

ERROR: infinite recursion detected in rules for relation "b"

How I could solve this problem?

On Sun, Mar 23, 2008 at 10:30 AM, srdjan <srdjan.matic@anche.no> wrote:
[...]
> CREATE TABLE b (id smallint PRIMARY KEY, email_a varchar(20), name_a
> varchar(10), tot smallint, FOREIGN KEY (email_a, name_a) REFERENCES a(email,
> name));
>
[...]
>
> -- And this easy rule
>
> CREATE RULE rrr_a_b AS ON INSERT TO b
> DO INSTEAD
> INSERT INTO b VALUES
> (NEW.id,
> NEW.email_a,
> NEW.name_a,
> (SELECT calc(NEW.email_a, NEW.name_a))
> );
>
> -- Sample for insert into b
>
> INSERT INTO b VALUES (33,'mail1@email.com','bill');
>
[...]
> Trying to insert into b (and using the new rule defined by myself, i receive
> this message:
>
> ERROR: infinite recursion detected in rules for relation "b"
>

when you insert into b the rule rewrites your query into an insert
into b... ah... another insert into b, the rule rewrites *again* the
query into (guess what?) another insert into b... and the rule system
will continue rewriting your query until it get something different to
an insert into b... hope i was clear...

now, why the rule? isn't enough to simply do this?

INSERT INTO b VALUES (33,'mail1@email.com','bill',
calc('mail1@email.com', 'bill'));

or maybe using a trigger before insert but you're insert should look like:

INSERT INTO b(id, email_a, name_a) VALUES (33,'mail1@email.com','bill');

and in the trigger fill the tot column

--
regards,
Jaime Casanova

srdjan wrote:

> /*
> *My goal is to calculate and insert automatically the value of "tot"
> when I insert a row into table b.*
> */

Use a trigger.  It's a lot simpler.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support