Unexpected behaviour (7.13, FreeBSD) - Mailing list pgsql-bugs

I'm building a 3-dimensional array using 5 tables; 3 of them define the
headings for the 3 dimensions, a fourth states the valid combinations
of labels, and the 5th table matches combinations to values.

I want to populate the first 4 tables all at the same time; by providing
the grid which defines the 4th table, I want it to automatically back-
populate the first three tables. This should be do-able by creating a
RULE which says

 CREATE RULE sometable_insert AS
  ON INSERT TO sometable
  WHERE EXISTS (SELECT sometable.uid WHERE sometable.field = new.field)
  DO INSTEAD NOTHING ;

Then I can simply create an insert rule for my 4th table which inserts
a value into each of the first 3 tables.

Here is the example SQL. This all works, and if I insert data into
tailoring_combos_view one insert at a time, it works. The only time it
doesn't work is when I try to import multiple values at a time. It then
appears that the WHERE EXISTS statement in the above rule example is
not noticing that a value HAS been added.


DROP DATABASE daoc ;
CREATE DATABASE daoc ;
\c daoc

BEGIN ;

CREATE TABLE tailoring_types (
 typeid        SERIAL,
 type        VARCHAR(32)    NOT NULL    UNIQUE
) ;

CREATE TABLE tailoring_classes (
 classid    SERIAL,
 class        VARCHAR(32)    NOT NULL    UNIQUE
) ;

CREATE TABLE tailoring_places (
 placeid    SERIAL,
 place    VARCHAR(32)    NOT NULL    UNIQUE
) ;

CREATE TABLE tailoring_combos (
 comboid    SERIAL,
 typeid        INT        NOT NULL
    REFERENCES tailoring_types(typeid)
    ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED,
 classid    INT        NOT NULL
    REFERENCES tailoring_classes(classid)
    ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED,
 placeid    INT        NOT NULL
    REFERENCES tailoring_places(placeid)
    ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED,
 UNIQUE ( typeid, classid, placeid )
) ;

CREATE TABLE tailoring_prices (
 comboid    INT        NOT NULL    PRIMARY KEY
    REFERENCES tailoring_combos(comboid)
    ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED,
 cost        INT        DEFAULT 0,
 value        INT         DEFAULT 0
) ;

CREATE TABLE wishlist (
 wishid        SERIAL,
 poster        VARCHAR(16)    NOT NULL,
 posted        DATETIME    NOT NULL    DEFAULT 'now',
 wishtext    TEXT        NOT NULL,
 votesaye    INT                DEFAULT 0,
 votesnay    INT                DEFAULT 0
) ;


CREATE VIEW tailoring_combos_view AS
 SELECT
    t.type            AS type,
    cl.class        AS class,
    p.place            AS place
 FROM
    tailoring_combos    AS c,
    tailoring_types        AS t,
    tailoring_classes    AS cl,
    tailoring_places    AS p
 WHERE
    c.typeid = t.typeid    AND
    c.classid = cl.classid    AND
    c.placeid = p.placeid
 ORDER BY
    c.typeid, c.classid, c.placeid
 ;


CREATE RULE tailoring_types_insert AS
 ON INSERT TO tailoring_types
 WHERE EXISTS ( SELECT typeid FROM tailoring_types WHERE type = new.type )
 DO INSTEAD NOTHING ;

CREATE RULE tailoring_classes_insert AS
 ON INSERT TO tailoring_classes
 WHERE EXISTS ( SELECT classid FROM tailoring_classes WHERE class = new.class )
 DO INSTEAD NOTHING ;

CREATE RULE tailoring_places_insert AS
 ON INSERT TO tailoring_places
 WHERE EXISTS ( SELECT placeid FROM tailoring_places WHERE place = new.place )
 DO INSTEAD NOTHING ;

CREATE RULE tailoring_combos_view_insert AS
 ON INSERT
 TO tailoring_combos_view
 DO INSTEAD (
  INSERT INTO tailoring_types (type) VALUES (new.type) ;
  INSERT INTO tailoring_classes (class) VALUES (new.class) ;
  INSERT INTO tailoring_places (place) VALUES (new.place) ;
  INSERT INTO tailoring_combos (typeid, classid, placeid)
    SELECT
        t.typeid as typeid,
        c.classid as classid,
        p.placeid as placeid
     FROM
        tailoring_types AS t,
        tailoring_classes AS c,
        tailoring_places AS p
    WHERE
        t.type = new.type        AND
        c.class = new.class        AND
        p.place = new.place ;
 ) ;

END ;

BEGIN ;

CREATE TEMP TABLE combos ( type varchar(32), class varchar(32), place varchar(32) ) ;

COPY combos FROM stdin USING DELIMITERS ' ';
Cloth Woolen Hands
Cloth Linen Hands
Cloth Brocade Hands
Cloth Silk Hands
Robe Woolen Plain
Robe Woolen Dress
Robe Woolen Fancy
Robe Linen Plain
\.

INSERT INTO tailoring_combos_view (type,class,place) SELECT * FROM combos ;

END ;

BEGIN ;
/* The above fails with a duplicate key value. However, the next lines work */

INSERT INTO tailoring_combos_view VALUES ('Cloth', 'Woolen', 'Hands') ;
INSERT INTO tailoring_combos_view VALUES ('Cloth', 'Linen', 'Hands') ;
INSERT INTO tailoring_combos_view VALUES ('Cloth', 'Brocade', 'Hands') ;
INSERT INTO tailoring_combos_view VALUES ('Cloth', 'Woolen', 'Head') ;
INSERT INTO tailoring_combos_view VALUES ('Cloth', 'Linen', 'Head') ;
INSERT INTO tailoring_combos_view VALUES ('Cloth', 'Brocade', 'Head') ;
INSERT INTO tailoring_combos_view VALUES ('Roman', 'Rawhide', 'Hands') ;
INSERT INTO tailoring_combos_view VALUES ('Roman', 'Tanned', 'Hands') ;
INSERT INTO tailoring_combos_view VALUES ('Roman', 'Cured', 'Hands') ;

END ;

--
You think Oedipus had problems? Adam was Eve's mother!