From: andrew@pillette.com
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: andrew@pillette.com
Cc: pgsql-sql@postgresql.org
Subject: Re: CREATE RULE ignored, what did I do wrong
Date: Mon, 13 Sep 2004 11:13:08 -0700
Message-ID: <200409131813.i8DID8Y10812@pillette.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: andrew@pillette.com, tgl@sss.pgh.pa.us
Subject: Re: CREATE RULE ignored, what did I do wrong
In-Reply-To: <200409131813.i8DID8Y10812@pillette.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox