Unexpected behaviour (7.13, FreeBSD)

From: Oliver Smith <oliver(at)kfs(dot)org>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Unexpected behaviour (7.13, FreeBSD)
Date: 2001-09-06 06:05:31
Message-ID: 20010906070530.A4425@kfs.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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!

Browse pgsql-bugs by date

  From Date Subject
Next Message Reinhard Max 2001-09-06 09:43:12 Re: libpgtcl doesn't use UTF encoding of TCL
Previous Message pgsql-bugs 2001-09-06 05:04:50 Bug #438: New users inherit permissions from dropped users