Date: Mon, 13 Sep 2004 11:13:08 -0700
From: andrew@pillette.com
Message-Id: <200409131813.i8DID8Y10812@pillette.com>
Subject: Re: CREATE RULE ignored, what did I do wrong 
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: andrew@pillette.com, pgsql-sql@postgresql.org
MIME-Version: 1.0
Content-Type: text/plain
Content-Transfer-Encoding: 7bit

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