Thread: double insert on inherited table with where constraint based on sequence

Perhaps I'm missing something here, but it looks like I'm getting an
insert into both the parent and child tables when my RULE's where
clause is based on a DEFAULT generated from a sequence. It looks like
nextval on the sequence is called 3 times for every insert.

I don't know if this is properly a bug or just un-expected behaviour.
It seems very counter-intuitive since the rule says DO INSTEAD so ISTM
that either one or the other insert should happen.

ahammond=# \d t2
                         Table "public.t2"
 Column |  Type   |                    Modifiers
--------+---------+-------------------------------------------------
 id     | integer | not null default nextval('t2_id_seq'::regclass)
 name   | text    | not null
Indexes:
    "t2_pkey" PRIMARY KEY, btree (id)
    "t2_name_key" UNIQUE, btree (name)
    "t2_test" btree ((name::integer)) WHERE is_number(name)
Rules:
    t2_part AS
    ON INSERT TO t2
   WHERE new.id > 10 DO INSTEAD  INSERT INTO t2_child (id, name)
  VALUES (new.id, new.name)

ahammond=# SELECT * FROM t2;
 id | name
----+-------
  1 | one
  2 | two
  3 | three
  4 | 4
  5 | 5
(5 rows)

ahammond=# CREATE TABLE t2_child (CHECK (id > 10)) INHERITS (t2);
CREATE TABLE

ahammond=# CREATE RULE t2_part AS ON INSERT TO t2 WHERE id > 10 DO
INSTEAD INSERT INTO t2_child VALUES (NEW.id, NEW.name);
CREATE RULE

ahammond=# INSERT INTO t2 (name) VALUES ('six');
INSERT 0 1
ahammond=# INSERT INTO t2 (name) VALUES ('seven');
INSERT 0 1
ahammond=# INSERT INTO t2 (name) VALUES ('eight');
INSERT 0 0
ahammond=# INSERT INTO t2 (name) VALUES ('9');
INSERT 0 0
ahammond=# INSERT INTO t2 (name) VALUES ('ten');
INSERT 0 0
ahammond=# INSERT INTO t2 (name) VALUES ('11');
INSERT 0 0
ahammond=# INSERT INTO t2 (name) VALUES ('12');
INSERT 0 0
ahammond=# SELECT * FROM t2;
 id | name
----+-------
  1 | one
  2 | two
  3 | three
  4 | 4
  5 | 5
  7 | six
 10 | seven
 12 | seven                     ?
 15 | eight
 18 | 9
 21 | ten
 24 | 11
 27 | 12
(13 rows)

ahammond=# SELECT * FROM ONLY t2 ;
 id | name
----+-------
  1 | one
  2 | two
  3 | three
  4 | 4
  5 | 5
  7 | six
 10 | seven
(7 rows)

ahammond=# SELECT * FROM t2_child ;
 id | name
----+-------
 12 | seven
 15 | eight
 18 | 9
 21 | ten
 24 | 11
 27 | 12
(6 rows)

Note that the "seven" entry appears twice.

Drew

"Andrew Hammond" <andrew.george.hammond@gmail.com> writes:
> Rules:
>     t2_part AS
>     ON INSERT TO t2
>    WHERE new.id > 10 DO INSTEAD  INSERT INTO t2_child (id, name)
>   VALUES (new.id, new.name)

"new.id" is replaced by "nextval('t2_id_seq'::regclass)" each time it
appears ... which will be four separate times, viz the two evaluations
of the WHERE clause and the two VALUES clauses.

Basically, volatile functions and rules do not mix.  Consider using
a trigger on t2 instead to redirect the insert somewhere else.

            regards, tom lane