From: | Peter Gebauer <peter(dot)gebauer(at)bluefront(dot)se> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Strange constraint violation when applying rules to a view. |
Date: | 2004-07-28 13:45:14 |
Message-ID: | 20040728134514.GA2669@bluefront.se |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hello.
This section works fine:
CREATE TABLE torder (
id INT8 NOT NULL PRIMARY KEY
);
CREATE TABLE torder_row (
id INT8 NOT NULL PRIMARY KEY,
torder_id INT8 NOT NULL REFERENCES torder (id) ON DELETE RESTRICT
);
CREATE VIEW vorder(orderID, rowID) AS SELECT 1, 1 FROM torder;
CREATE RULE vorder_ins AS ON INSERT TO vorder
DO INSTEAD
(INSERT INTO torder VALUES (NEW.orderID);
INSERT INTO torder_row VALUES (NEW.rowID, NEW.orderID);
);
CREATE RULE vorder_upd AS ON UPDATE TO vorder
DO INSTEAD
(INSERT INTO torder (id) VALUES (NEW.orderID);
UPDATE torder_row SET torder_id = NEW.orderID;
DELETE FROM torder WHERE id = OLD.orderID;
);
INSERT INTO vorder VALUES (1, 1);
UPDATE vorder SET orderID = 2;
SELECT * FROM vorder;
But when I change the view to the following:
CREATE VIEW vorder(orderID, rowID) AS SELECT 1, 1 FROM torder;
becomes
CREATE VIEW vorder(orderID, rowID) AS SELECT id, 1 FROM torder;
I get the following error:
ERROR: insert or update on table "torder_row" violates foreign key
constraint "$1"
This was just a small test so the table definitions and view makes
little sense, but generally, any time the view actualy selects columns
it seems the update rule fails with a constraint violation even though
it shouldn't.
Thank's for reading!
/Peter
From | Date | Subject | |
---|---|---|---|
Next Message | Prabu Subroto | 2004-07-28 14:13:50 | Re: altering a starting value of "serial" macro |
Previous Message | John Sidney-Woollett | 2004-07-28 13:43:03 | Re: altering a starting value of "serial" macro |