double insert on inherited table with where constraint based on sequence
От | Andrew Hammond |
---|---|
Тема | double insert on inherited table with where constraint based on sequence |
Дата | |
Msg-id | 1153332853.919063.77280@i42g2000cwa.googlegroups.com обсуждение исходный текст |
Ответы |
Re: double insert on inherited table with where constraint based on sequence
|
Список | 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
В списке pgsql-bugs по дате отправления: