Thread: CREATE RULE ignored, what did I do wrong
OK, below is the dump of the table definition. Several other tables reference this and have ON DELETE CASCADE. In this
tablethere is a rule for ON DELETE. The WHERE clause (NOT old.is_deleted) should always be the case, as the field is
FALSEfor all existing entries (checked).
The cascading deletes are all performed when I delete from this table. The rule is not. The record is NOT retained with
is_deletednow TRUE. I turned on log_statement, and saw only the queries corresponding to the cascading delete, not my
DOINSTEAD queries.
Does the cascade happen first?? If so, how do I get in ahead of it?
Thanks.
*******
smoothed_rank_episode_id | integer | not null default
nextval('base_rank_episode_base_rank_episode_id_seq'::text)base_rank_episode_id | integer | not
nullsmoothing_id | integer | not null default 0smoothing_parameters | double precision[] |
notnull default '{}'::double precision[]is_deleted | boolean | default false
Indexes: "smoothed_rank_episode_pkey" primary key, btree (smoothed_rank_episode_id) "smoothed_rank_episode_ak1"
unique,btree (base_rank_episode_id, smoothing_id, smoothing_parameters)
Foreign-key constraints: "$1" FOREIGN KEY (smoothing_id) REFERENCES smoothing_algorithm(smoothing_id) ON UPDATE
CASCADEON DELETE CASCADE
Rules: del_smoothed_rank_episode AS ON DELETE TO smoothed_rank_episode WHERE (NOT old.is_deleted) DO INSTEAD (DELETE
FROMhistorical_rank WHERE (historical_rank.smoothed_rank_episode_id = old.smoothed_rank_episode_id); DELETE FROM signal
WHERE(signal.signal_episode_id IN (SELECT signal_episode.signal_episode_id FROM signal_episode WHERE
(signal_episode.smoothed_rank_episode_id= old.smoothed_rank_episode_id))); UPDATE smoothed_rank_episode SET is_deleted
=true WHERE (smoothed_rank_episode.smoothed_rank_episode_id = old.smoothed_rank_episode_id); )
andrew@pillette.com writes:
> Foreign-key constraints:
> "$1" FOREIGN KEY (smoothing_id) REFERENCES smoothing_algorithm(smoothing_id) ON UPDATE CASCADE ON DELETE CASCADE
> Rules:
> del_smoothed_rank_episode AS ON DELETE TO smoothed_rank_episode
> WHERE (NOT old.is_deleted) DO INSTEAD ...
The DELETE commands generated by the foreign key ON DELETE CASCADE will
get rewritten by your ON DELETE rule. You probably do not want to do
this; or at least not make it an INSTEAD rule.
There has been some debate in the past about whether rules should be
able to break foreign-key constraints, but I tend to class it as a
"you should know what you're doing" feature. Preventing this kind
of error would inevitably result in a serious reduction of the power
of the rule feature.
regards, tom lane
I was trying to implement a pseudo-delete, where the (millions of) records in several child tables were actually deleted,but a flag was set in the summary table instead of deleting it, as an archiving mechanism. (If the flag was alreadyset, the WHERE clause in the RULE should be false, and the delete happen as usual?!) The FK relation below has thesummary table as the child, and isn't critical. It's the tables for which this is the parent that are the issue. Do you have an idea how to implement this best? Tom Lane <tgl@sss.pgh.pa.us> wrote .. > andrew@pillette.com writes: > > Foreign-key constraints: > > "$1" FOREIGN KEY (smoothing_id) REFERENCES smoothing_algorithm(smoothing_id) > ON UPDATE CASCADE ON DELETE CASCADE > > Rules: > > del_smoothed_rank_episode AS ON DELETE TO smoothed_rank_episode > > WHERE (NOT old.is_deleted) DO INSTEAD ... > > The DELETE commands generated by the foreign key ON DELETE CASCADE will > get rewritten by your ON DELETE rule. You probably do not want to do > this; or at least not make it an INSTEAD rule. > > There has been some debate in the past about whether rules should be > able to break foreign-key constraints, but I tend to class it as a > "you should know what you're doing" feature. Preventing this kind > of error would inevitably result in a serious reduction of the power > of the rule feature. > > regards, tom lane