On Thu, 13 Jun 2002, Jan Wieck wrote:
> Stephan Szabo wrote:
> >
> > On Wed, 12 Jun 2002, Joshua b. Jore wrote:
> >
> > > The following sql demonstrates the problem. What I'm getting at here is
> > > cases where the rows in "b" are being altered instead of being deleted
> > > consequently the delete to "a" shouldn't happen.
> > >
> > > create table a (id int4 primary key);
> > > create table b (id int4 references a on delete cascade);
> > > create rule b0 as on delete to b do instead nothing;
> > >
> > > insert into a values (1);
> > > insert into b values (1);
> > > delete from a;
> >
> > When you do the delete from a, the constraint will no longer
> > be satisifed. It throws the error to prevent the delete
> > from working. Maybe throwing a standard constraint violation
> > would be enough, but I'd worry that there'd be some other
> > return case that we should know about.
> >
> > In general on <x> rules with on <x> action foreign key constraints
> > are a bad idea. I'd almost want to disallow the above entirely.
>
> Trying to protect RI against *every* feature in PostgreSQL
> is dangerous IMHO. It might break useful administrative
> possibilities. Actually, every RI constraint can be violated
> with TRUNCATE. So should we disable TRUNCATE for tables that
> have triggers or rules?
Isn't part of that (with TRUNCATE) on the todo list?
> The referenced columns must be unique, protected by a unique
> index. But yet it's still possible to drop that index later.
> Should we prevent that too? If not, I can show you alot of
> funny stuff possible now!
True. And there are other places that at least at one time (I haven't
tested recently) that somewhat rely on the brokenness of unique
constraints to work properly. I'd like to eventually see the
constraint triggers try to handle as many of those situations
as is feasible though.
> Rewrite rules are in general a mechanism to make views
> updatable. They have originally been an idea to implement
> an alternative to triggers, but that thesis didn't hold true.
> And the instance level rules are long gone anyway.
>
> Let's not get paranoid just because someone with alot of
> PostgreSQL expertise can construct a schema that allows
> RI breakage.
True, but it's not obvious to someone that they are breaking
RI when they do the above in general. I think more people
are doing this and then wondering why it's breaking
than are doing it on purpose given the past couple of times
this has come up. I guess we need to decide at what level
we want to guarantee the constraint and how much work we're
willing to have the computer do to check. For example, a
select after the delete rather than the specific check on
SPI_execp would catch most cases of rule/trigger prevention
unless other after triggers were involved. But that requires another
query. Is it worth the extra query? I'm not sure. Is it
worth coming up with a way that catches other after triggers
on the real query being run in a rewrite case? Probably not,
since I don't see an obvious way of doing so.
