Thread: rules or trigers?

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?

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

it happens nothing when I update one of field in tab. The same happens
with ON DELETE RULE.
Am I missing something or it's not yet implemented?

Do You think I should use triggers to log information about changing data?


btw. Did You know there will be conference about postgresql in Poland?
        Anyone going to be there?:)

Marcin Mazurek

--
Kierownik Działu Systemowego
MULTINET SA o/Poznan
http://www.multinet.pl/

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

Tom Lane (tgl@sss.pgh.pa.us) napisał:
> 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.
and thats what I did a minute ago, works just fine, at least it looks like:)

> 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...
maybe somebody needs it that way, at least I have an option.
thx for Your help.

Marcin Mazurek

--
Kierownik Działu Systemowego
MULTINET SA o/Poznan
http://www.multinet.pl/