On 2014-09-29 10:12:25 -0400, Tom Lane wrote: > Andres Freund <and...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
