Thread: Rules & sequences

Hi, I wonder if anyone can help?

I'm working on an audit trail, and trying to do it with rules.  update &
delete are ok, I'm having a problem with my insert rule -

create rule address_ins as on insert to address DO
insert into address_hist values (new.*,current_timestamp,'insert');

The problem is, my address table has default nextval('address_id_seq') as
the default for the Primary key.  When I insert into the address table, the
rule seems to take the nextval of the sequence for the audit table, and then
the actual insert into the address table takes another nextval, so the
record in the audit table has a different ID to the main table, which isn;t
really very good for auditing!  Is there anything I can do about this, or
will I have to do an after insert trigger instead?

Thanks,
Tamsin

"Tamsin" <tg_mail@bryncadfan.co.uk> writes:
> I'm having a problem with my insert rule -

> create rule address_ins as on insert to address DO
> insert into address_hist values (new.*,current_timestamp,'insert');

> The problem is, my address table has default nextval('address_id_seq') as
> the default for the Primary key.  When I insert into the address table, the
> rule seems to take the nextval of the sequence for the audit table, and then
> the actual insert into the address table takes another nextval,

I think this may be an inherent problem in the way rules are handled
(unless Jan has an idea how to fix it).  You'd probably have better luck
using a trigger.

            regards, tom lane