Hi there, I have a somewhat peculiar problem.
To begin with, here are links to my schema and rules: my_table and associated rules <http://postgresql.pastebin.com/0eCSuvkU> and my_table_history <http://postgresql.pastebin.com/cGm617Cp> 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): [email protected]
