On 2014-09-29 10:12:25 -0400, Tom Lane wrote:
> Andres Freund <andres@2ndquadrant.com> writes:
> > On 2014-09-28 10:41:56 -0400, Tom Lane wrote:
> >> If this optimization only works in that scenario, it's dead in the water,
> >> because that assumption is unsupportable. The planner does not in general
> >> use the same query snapshot as the executor, so even in an immediate-
> >> execution workflow there could have been data changes (caused by other
> >> transactions) between planning and execution.
>
> > I don't think the effects of other queries are the problem here. The
> > effect of other backend's deferred FK checks shouldn't matter for other
> > backends for normal query purposes. It's the planning backend that might
> > have deferred checks and thus temporarily violated foreign keys.
>
> 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.
Greetings,
Andres Freund
-- Andres Freund http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training &
Services