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); ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
