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 по дате отправления:

Предыдущее
От: "Alexandr Sereda"
Дата:
Сообщение: BUG #2536: TInterval: wrong interpretation of "Undefined Range" value.
Следующее
От: "John Lyssy"
Дата:
Сообщение: BUG #2538: Hang on insert/select