Date: Fri, 10 Sep 2004 14:36:45 -0700
From: andrew@pillette.com
Message-Id: <200409102136.i8ALajP19402@pillette.com>
Subject: CREATE RULE ignored, what did I do wrong
To: pgsql-sql@postgresql.org
Cc: 
MIME-Version: 1.0
Content-Type: text/plain
Content-Transfer-Encoding: 7bit

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); )