double insert on inherited table with where constraint based on sequence

From: "Andrew Hammond" <andrew(dot)george(dot)hammond(at)gmail(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: double insert on inherited table with where constraint based on sequence
Date: 2006-07-19 18:14:13
Message-ID: 1153332853.919063.77280@i42g2000cwa.googlegroups.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2006-07-19 19:22:01 Re: double insert on inherited table with where constraint based on sequence
Previous Message Thiago Silva 2006-07-19 17:03:26 XML2 module: odd query results