FAQ
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

Search Discussions

  • Tom Lane at Jul 19, 2006 at 7:22 pm

    "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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-bugs @
categoriespostgresql
postedJul 19, '06 at 6:14p
activeJul 19, '06 at 7:22p
posts2
users2
websitepostgresql.org
irc#postgresql

2 users in discussion

Andrew Hammond: 1 post Tom Lane: 1 post

People

Translate

site design / logo © 2021 Grokbase