Strange constraint violation when applying rules to a view.

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

Responses

Browse pgsql-general by date

  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