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


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

"Andrew Hammond" <andrew.george.hammond (AT) gmail (DOT) com> writes:
"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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq