Re: Allowing join removals for more join types

On Wed, Jun 04, 2014 at 10:14:42AM -0400, Tom Lane wrote:
> David Rowley <dgrowleyml(at)gmail(dot)com> writes:
> > On Wed, Jun 4, 2014 at 11:50 AM, Noah Misch <noah(at)leadboat(dot)com> wrote:
> >> When a snapshot can see modifications that queued referential integrity
> >> triggers for some FK constraint, that constraint is not guaranteed to hold
> >> within the snapshot until those triggers have fired.
>
> > I remember reading about some concerns with that here:
> > http://www.postgresql.org/message-id/51E2D505.5010705@2ndQuadrant.com
> > But I didn't quite understand the situation where the triggers are delayed.
> > I just imagined that the triggers would have fired by the time the command
> > had completed. If that's not the case, when do the triggers fire? on

Normally, they fire in AfterTriggerEndQuery(), which falls at the end of a
command. The trouble arises there when commands nest. (If the constraint is
deferred, they fire just before COMMIT.)

> > commit? Right now I've no idea how to check for this in order to disable
> > the join removal.
>
> I'm afraid that this point destroys your entire project :-( ... even
> without deferred constraints, there's no good way to be sure that you're
> not planning a query that will be run inside some function that can see
> the results of partially-completed updates. The equivalent problem for
> unique indexes is tolerable because the default choice is immediate
> uniqueness enforcement, but there's no similar behavior for FKs.

Let's not give up just yet. There's considerable middle ground between
ignoring the hazard and ignoring all FK constraints in the planner, ...

> It's possible that we could apply the optimization only to queries that
> have been issued directly by a client, but that seems rather ugly and
> surprise-filled.

... such as this idea. It's a good start to a fairly-hard problem. FKs are
also always valid when afterTriggers->query_depth == -1, such as when all
ongoing queries qualified for EXEC_FLAG_SKIP_TRIGGERS. What else? We could
teach trigger.c to efficiently report whether a given table has a queued RI
trigger. Having done that, when plancache.c is building a custom plan, the
planner could ignore FKs with pending RI checks and use the rest. At that
point, the surprises will be reasonably-isolated.

--
Noah Misch
EnterpriseDB http://www.enterprisedb.com