Re: PostgreSQL 7.4.2 allows foreign key violation

On Sun, 8 Aug 2004, Jan Wieck wrote:

> On 8/6/2004 1:23 PM, Tom Lane wrote:
> > Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com> writes:
> >> On Fri, 6 Aug 2004, Tom Lane wrote:
> >>> Already does what? I see nothing in there that would override either
> >>> triggers or rules...
> >
> >> It's not for overriding the triggers or rules, but instead checking that
> >> the post action state is valid (by running the no action code which
> >> makes sure that either another row now has the pk value or that there are
> >> no longer any matching rows).
> >
> > Oh, I see. Seems an awfully expensive solution though :-(
>
> IMHO it is one of the cases that are on the line of "doctor, when I ...
> then don't do it". As you said, there is no perfect solution. Triggers
> and rules can conflict in several ways, but we don't want to sacrifice
> one for making the other failsafe.

True, but I don't think we're sacrificing one for the other. We'd be
sacrificing speed AFAICS.

As I see it we've got the following:
a) Do no code changes. The constraints can be fooled by some situations,
make sure that it's documented and point people to the documentation.
Upsides: No code changes, no further slowdown of functioning of
constraint.
Downside: Constraint can be violated

b) Always run the no action check code. We always run the no action code
after the action to check to make sure that the dependent rows are
no longer there (or are supported by some other value). This is the
theoretical model of the constraint in the spec, I believe.
Upsides: Constraint should be theoretically difficult to break with
this form of violation, barring bugs. Minor code change.
Downsides: Everyone pays the (non-trivial) cost to do the check to
fix this case. The extra check also potentially grabs yet more
locks.

c) Run the no action check code when we think there's some chance of this
situation occuring. The "some chance" could be always in which case
this is the same as b, if there are any instead rules or before
triggers on the acted upon table for the action being run (update or
delete), or something more complicated.
Upsides: Compared to a, we would get a constraint that's harder to
break. Compared to b, we hopefully lessen the cost to people not
using the combination.
Downsides: More involved code changes and testing to make sure it's
right. We still add a cost to everyone to check the state. The
constraint now "acts differently" for people using instead rules
or before triggers which means it's an additional variable to
deal with when debugging problems.