pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedCREATE RULE ignored, what did I do wrong
3+ messages / 2 participants
[nested] [flat]
* CREATE RULE ignored, what did I do wrong
@ 2004-09-10 21:36 andrew@pillette.com
0 siblings, 1 reply; 3+ messages in thread
From: andrew@pillette.com @ 2004-09-10 21:36 UTC (permalink / raw)
To: pgsql-sql
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); )
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: CREATE RULE ignored, what did I do wrong
@ 2004-09-11 06:42 Tom Lane <tgl@sss.pgh.pa.us>
parent: andrew@pillette.com
0 siblings, 0 replies; 3+ messages in thread
From: Tom Lane @ 2004-09-11 06:42 UTC (permalink / raw)
To: andrew@pillette.com; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: CREATE RULE ignored, what did I do wrong
@ 2004-09-13 18:13 andrew@pillette.com
0 siblings, 0 replies; 3+ messages in thread
From: andrew@pillette.com @ 2004-09-13 18:13 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: andrew@pillette.com; pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2004-09-13 18:13 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-09-10 21:36 CREATE RULE ignored, what did I do wrong andrew@pillette.com
2004-09-11 06:42 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-09-13 18:13 Re: CREATE RULE ignored, what did I do wrong andrew@pillette.com
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