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 table there is a rule for ON DELETE. The WHERE clause (NOT old.is_deleted) should always be the case, as the field is FALSE for 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_deleted now TRUE. I turned on log_statement, and saw only the queries corresponding to the cascading delete, not my DO INSTEAD 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 null
smoothing_id | integer | not null default 0
smoothing_parameters | double precision[] | not null 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 CASCADE ON DELETE CASCADE
Rules:
del_smoothed_rank_episode AS ON DELETE TO smoothed_rank_episode WHERE (NOT old.is_deleted) DO INSTEAD (DELETE FROM historical_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); )

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Re: CREATE RULE ignored, what did I do wrong

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

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Re: CREATE RULE ignored, what did I do wrong

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 already set, the WHERE clause in the RULE should be false, and the delete happen as usual?!) The FK relation below has the summary 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 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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)