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@megazone.bigpanda.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.