Hi there,
I have a somewhat peculiar problem.
Scene: I'm implementing a pretty standard history keeping mechanism for some tables by attaching the following Rule to them.
--INSERT
CREATE OR REPLACE RULE on_insert AS
ON INSERT TO my_table DO
INSERT INTO history.my_table_history select new.*, now(), CURRENT_USER, 'INSERT'::character varying;
--UPDATE
CREATE OR REPLACE RULE on_update AS
ON UPDATE TO my_table DO
INSERT INTO history.my_table_history select new.*, now(), CURRENT_USER, 'UPDATE'::character varying;
The table in the history schema is a duplicate of the original table except that my serial is now just a plain integer and I've added 4 columns (timestamp, username and change_type and a history_id serial ).
The issue that I'm having is the following:
I have a serial field named my_table_id in my_table that is tied to a sequence, while the history table has the same column - but as a simple integer without the nextval.
Let's, for argument's sake, say that my sequence has a currval() of 2000 and that I run an INSERT on my_table and my serial (correctly) gets set to 2001.
This prompts the on_insert RULE to fire off its own INSERT command, EXCEPT that when I look into history.my_table_history I see that the my_table_history.my_table_id is set to 2002.
Going back to my_table, I can verify that its my_table_id is still set to 2001.
Taking a look at the sequence I can verify that curval() is set to 2002
Being utterly perplexed I try and run an UPDATE command on the same row - and much to my relief (or distress) that rule seems to work just as it should, with the correct my_table_id propagating down into the history table.
Am I wrong in my thinking? Is there something faulty with the above RULE?
I've been over and over my schemas and can't for the life of me figure out what the issue is.
Does anybody have any idea what is going on?
Kind regards from Iceland,
Gissur Þórhallsson
Loftmyndir ehf.
Laugavegur 13
IS 101 Reykjavík - Iceland
sími (tel): (+354) 540 2500
tölvupóstur (email):
gissur@loftmyndir.is