Andres Freund <andres@2ndquadrant.com> writes:
> On 2014-09-29 10:12:25 -0400, Tom Lane wrote:
>> I see. So why aren't we simply ignoring deferrable FKs when making the
>> optimization? That pushes it back from depending on execution-time state
>> (unsafe) to depending on table DDL (safe).
> IIRC there's some scenarios where violated FKs are visible to client
> code for nondeferrable ones as well. Consider e.g. cascading foreign
> keys + triggers. Or, somewhat insane, operators used in fkey triggers
> that execute queries themselves.
Yeah, I had just thought of the query-in-function-called-from-violating-
query case myself. I plead insufficient caffeine :-(. I'd been making
a mental analogy to non-deferred uniqueness constraints, but actually
what we will optimize outer joins on is "immediate" unique indexes,
wherein there's no delay at all before the constraint is checked.
Too bad there's no equivalent in foreign key land.
These are certainly corner cases, but it doesn't seem up to project
quality standards to just ignore them. So I'm thinking you're right
that a run-time short-circuit would be the only way to deal with the
case safely.
On the whole I'm feeling that the scope of applicability of this
optimization is going to be too narrow to justify the maintenance
effort and extra planning/runtime overhead.
regards, tom lane