Re: rules or trigers?

Marcin Mazurek <M.Mazurek@poznan.multinet.pl> writes:
> Simple example to make things clearer.
> CREATE TABLE tab (id INT SERIAL PRIMARY KEY, sth TEXT); --main table
> CREATE TABLE log_tab(id INT, sth TEXT);    --table to maintain logs in it

> CREATE RULE tab_log_ins AS ON INSERT TO tab DO
>         INSERT INTO log_tab  VALUES (new.id, new.sth);

> INSERT INTO tab (sth) VALUES ('something');
> when I insert new raw in tab, id field differs (rises by one) from id in
> log_tab, how can i avoid it?

At least at the moment, the only way is to use a trigger.

The problem is this.  Your insert is transformed by the parser to include
the defaults for the missing columns:

INSERT INTO tab (id, sth) VALUES (nextval('id_seq'), 'something');

Then the rule is applied.  That's also fundamentally a textual
transformation, so what actually gets executed is equivalent to

INSERT INTO log_tab  VALUES (nextval('id_seq'), 'something');
INSERT INTO tab (id, sth) VALUES (nextval('id_seq'), 'something');

See the problem?  What you want is to lay your hands on the actual
values that are getting inserted into "tab", and a rule cannot do that.
But a trigger does exactly that.

I am not sure whether this behavior of rules is a bug or a feature.
I am sure that it would be difficult to change...

            regards, tom lane