value

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): gissur(at)loftmyndir(dot)is