Re: Combination of Triggers and self-FKs produces inconsistent data

Tom,

> You can't have your cake and eat it too, Josh. If we make the RI
> mechanism operate at a level underneath triggers, then we'll lose all
> sorts of useful capability that people are depending on. A couple of
> examples:
>
> * the ability to log table changes caused by RI cascades
>
> * the ability to maintain row update timestamps when the update is
> caused by an RI cascade

Yeah, I can see that there isn't an obvious fix. However, at the end of
the day it means that RI in Postgres can be accidentally broken by user
action without removing or disabling the constraint. This isn't a
comfortable thought; it sounds an awful lot like another OSS-DB.

Or to put it another way, we don't allow triggers to break UNIQUE
constraints or CHECK constraints. All of the other constraints operate
at a level below triggers. Why are FKs different?

> It doesn't say that, because it isn't true. What is true is that if you
> make a trigger that prevents updates from happening, it breaks RI
> updates as well as directly-user-initiated updates.

Again, if we're going to retain this issue, then it needs to be in the
documentation that RI isn't enforced on the results of triggers.
Because, polling 5 people on IRC who each have more than 3 years of
PostgreSQL experience ... and two of whom are code contributors ... this
issue surprised *all* of them.

> Either way, you're
> going to need to fix the trigger.

If you read to the end of the example, you'd see that I'm saying that
the trigger should *fail*, with an error. Not work.

Throughout the history of the project, no functionality which ends in a
inconsistent data state has ever been acceptable which I can recall.
When did we change our policy?

--Josh Berkus