Skip site navigation (1) Skip section navigation (2)

Re: RULE and default nextval() column

From: Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com>
To: "Dmitry P(dot) Ovechkin" <odp(at)inpris(dot)ru>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: RULE and default nextval() column
Date: 2004-10-27 14:11:25
Message-ID: 20041027071014.B35265@megazone.bigpanda.com (view raw or flat)
Thread:
Lists: pgsql-sql
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.

In response to

pgsql-sql by date

Next:From: Jon UhalDate: 2004-10-27 15:09:39
Subject: Foreign Key Non-Null Problem in 8.0
Previous:From: Dmitry P. OvechkinDate: 2004-10-27 13:19:35
Subject: RULE and default nextval() column

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group