RULE and default nextval() column - Mailing list pgsql-sql

From Dmitry P. Ovechkin
Subject RULE and default nextval() column
Date
Msg-id 417FA067.5030506@inpris.ru
Whole thread Raw
Responses Re: RULE and default nextval() column
List pgsql-sql
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.




pgsql-sql by date:

Previous
From: Oliver Elphick
Date:
Subject: Re: How to re-sort a sorted query?
Next
From: Stephan Szabo
Date:
Subject: Re: RULE and default nextval() column