Thread: RULE and default nextval() column

Hello.
I'mtrying to implement history tables using rules.
I have
test_table
----------
create sequence history_seq start 1;
create sequence test_sequence;
# source table
drop table test_table;
create table test_table (
i integer default nextval('test_sequence'),
c character(10)
);
# history table
create table test_table_history (
hist integer default nextval('history_seq'),
i integer,
c character(10)
);
# rule to save history
create rule test_table_history_insert as on insert to test_table do
insert into test_table_history values ( nextval('history_seq'),        new.i,        new.c        );
#
Then I try to insert into test_table;
test=> insert into test_table values( nextval('test_sequence'), 'a');
INSERT 3299176 1
test=> insert into test_table (c) values('a');
INSERT 3299178 1
drweb=> select * from test_table; i |     c
---+------------ 1 | a 3 | a
(2 rows)

test=> select * from test_table_history; hist | i |     c
------+---+------------    1 | 2 | a    2 | 4 | a
(2 rows)

==========
Problem is : value of "i" field increments during insertion into both 
test_table and test_table history.
This also happens if I omit "i" in insert statement and let it be filled 
by default.
Can something be done to avoid it?

Sincerely, Dmitry Ovechkin.

On Wed, 27 Oct 2004, Dmitry P. Ovechkin wrote:

> Hello.
> I'mtrying to implement history tables using rules.
> I have
> test_table
> ----------
> create sequence history_seq start 1;
> create sequence test_sequence;
> # source table
> drop table test_table;
> create table test_table (
> i integer default nextval('test_sequence'),
> c character(10)
> );
> # history table
> create table test_table_history (
> hist integer default nextval('history_seq'),
> i integer,
> c character(10)
> );
> # rule to save history
> create rule test_table_history_insert as on insert to test_table do
> insert into test_table_history values ( nextval('history_seq'),
>          new.i,
>          new.c
>          );
> #
> Then I try to insert into test_table;
> test=> insert into test_table values( nextval('test_sequence'), 'a');
> INSERT 3299176 1
> test=> insert into test_table (c) values('a');
> INSERT 3299178 1
> drweb=> select * from test_table;
>   i |     c
> ---+------------
>   1 | a
>   3 | a
> (2 rows)
>
> test=> select * from test_table_history;
>   hist | i |     c
> ------+---+------------
>      1 | 2 | a
>      2 | 4 | a
> (2 rows)
>
> ==========
> Problem is : value of "i" field increments during insertion into both
> test_table and test_table history.
> This also happens if I omit "i" in insert statement and let it be filled
> by default.
> Can something be done to avoid it?

Pretty much the only real answer is to use a trigger rather than a rule.
Rules are basically like macro expansions and have many of the same
problem with side effects that macros do.