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
Date: Wed, 27 Oct 2004 07:11:25 -0700 (PDT)
Message-ID: <20041027071014.B35265@megazone.bigpanda.com> (raw)
In-Reply-To: <417FA067.5030506@inpris.ru>
References: <417FA067.5030506@inpris.ru>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: sszabo@megazone.bigpanda.com, odp@inpris.ru
Subject: Re: RULE and default nextval() column
In-Reply-To: <20041027071014.B35265@megazone.bigpanda.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox