Re: Troubleshooting SPI_execp() failed in

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?

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!

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.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #