Date: Wed, 27 Oct 2004 07:11:25 -0700 (PDT)
From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: "Dmitry P. Ovechkin" <odp@inpris.ru>
Cc: pgsql-sql@postgresql.org
Subject: Re: RULE and default nextval() column
In-Reply-To: <417FA067.5030506@inpris.ru>
Message-ID: <20041027071014.B35265@megazone.bigpanda.com>
References: <417FA067.5030506@inpris.ru>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII

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.