Re: Patch to support SEMI and ANTI join removal

On 2014-09-29 10:12:25 -0400, Tom Lane wrote:
> Andres Freund <andres(at)2ndquadrant(dot)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