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
PRIMARYKEY REFERENCES offer_immutable); ALTER TABLE offer_immutable ADD CONSTRAINT offer_immutable_offer_id_fkey
FOREIGNKEY (offer_id) REFERENCES offer_mutable DEFERRABLE INITIALLY DEFERRED; CREATE VIEW offer AS SELECT *
FROMoffer_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 <cjs@cynic.net> +81 90 7737 2974 http://www.NetBSD.org Make up enjoying your city life...produced
byBIC CAMERA