Re: Allowing join removals for more join types

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
> 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.

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.

regards, tom lane