Thread: INSERT RULE doesn't allow OLD, so how does one work with serial datatypes?

I'm having a bit of mystery in solving a postgresql puzzle.  I have a
table that when it gets inserted or updated or deleted it is logged
into a log table.  The log table contains who (current_user) did the
insert/update/delete the CURRENT_TIMESTAMP.  Everything works great
except the INSERT because I cannot use OLD and NEW increments the
serial twice!

CREATE TABLE ttest (bpchar, instime abstime, prikey serial PRIMARY
KEY);
CREATE TABLE ttest_log ( value bpchar, user bpchar, instime abstime,
modtime abstime , logprikey int4);

CREATE RULE ri AS ON INSERT TO ttest DO
INSERT INTO ttest_log (NEW.value , current_user, CURRENT_TIMESTAMP,
'infinity', NEW.logprikey);
--on the above NEW.logprikey creates two different primary keys!!  One
pk for the ttest and pk +1 for ttest_log!


CREATE RULE rupd AS ON UPDATE TO ttest DO
INSERT INTO ttest_log (old.value, current_user, old.instime,
CURRENT_TIMESTAMP);

CREATE RULE rdel AS ON DELETE TO ttest DO
INSERT INTO ttest_log (old.value, current_user, old.instime,
CURRENT_TIMESTAMP);

On Sat, May 13, 2006 at 02:11:14PM -0700, Karen Hill wrote:
> I'm having a bit of mystery in solving a postgresql puzzle.  I have a
> table that when it gets inserted or updated or deleted it is logged
> into a log table.  The log table contains who (current_user) did the
> insert/update/delete the CURRENT_TIMESTAMP.  Everything works great
> except the INSERT because I cannot use OLD and NEW increments the
> serial twice!

You cannot use OLD because there is no old row. And rules are like
macros, so the nextval() gets evaluated twice.

You probably want a trigger...
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.