I've got a table I've split into two, a portion that can be modified
under normal circumstances, and a portion that can't be. (In a testing
mode they both can be, but in production the user IDs doing the work
don't have update or delete access on the immutable portion. BTW, I'm
open to better ways of doing this.)
So it's along the lines of
CREATE TABLE offer_immutable (offer_id serial PRIMARY KEY );
CREATE TABLE offer_mutable (
offer_id int PRIMARY KEY REFERENCES offer_immutable);
ALTER TABLE offer_immutable ADD CONSTRAINT offer_immutable_offer_id_fkey
FOREIGN KEY (offer_id) REFERENCES offer_mutable
DEFERRABLE INITIALLY DEFERRED;
CREATE VIEW offer AS
SELECT * FROM offer_immutable NATURAL JOIN offer_mutable;
In a transaction, when I try to commit, this does not work:
CREATE OR REPLACE RULE offer_delete AS
ON DELETE TO offer DO INSTEAD (
DELETE FROM offer_mutable WHERE offer_id = OLD.offer_id;
DELETE FROM offer_immutable WHERE offer_id = OLD.offer_id;
);
It gives me:
ERROR: insert or update on table "offer_immutable" violates foreign
key constraint "offer_immutable_offer_id_fkey"
DETAIL: Key (offer_id)=(77) is not present in table "offer_mutable".
On the other hand, if I use this instead:
CREATE OR REPLACE FUNCTION offer_delete(int) RETURNS VOID AS $$
DELETE FROM offer_mutable WHERE offer_id = $1;
DELETE FROM offer_immutable WHERE offer_id = $1;
$$ LANGUAGE 'SQL' VOLATILE SECURITY INVOKER;
CREATE OR REPLACE RULE offer_delete AS
ON DELETE TO offer DO INSTEAD SELECT offer_delete(OLD.offer_id);
It works fine.
I can also just do the two separate deletes in a transaction, and it
works ok.
BEGIN;
DELETE FROM offer_mutable WHERE offer_id = 123;
DELETE FROM offer_immutable WHERE offer_id = 123;
COMMIT;
Bug?
cjs
--
Curt Sampson <[EMAIL PROTECTED]> +81 90 7737 2974 http://www.NetBSD.org
Make up enjoying your city life...produced by BIC CAMERA
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
