Re: Allowing join removals for more join types

On Wed, Jun 4, 2014 at 11:50 AM, Noah Misch <noah(at)leadboat(dot)com> wrote:

> On Wed, May 28, 2014 at 08:39:32PM +1200, David Rowley wrote:
> > The attached patch allows join removals for both sub queries with left
> > joins and also semi joins where a foreign key can prove the existence of
> > the record.
>
> 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. For example, a query
> running within a VOLATILE function f() in a statement like "UPDATE t SET c
> =
> f(c)" may read data that contradicts FK constraints involving table "t".
> Deferred UNIQUE constraints, which we also do not yet use for deductions in
> the planner, have the same problem; see commit 0f39d50. This project will
> need a design accounting for that hazard.
>
>
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.

For the deferred unique constraints I'm protecting against that the same
way as the left join removal does... It's in the
relation_has_foreign_key_for() function where I'm matching the foreign keys
up to the indexes on the other relation.

As a point of procedure, I recommend separating the semijoin support into
> its
> own patch. Your patch is already not small; delaying non-essential parts
> will
> make the essential parts more accessible to reviewers.
>
>
That's a good idea. I think the left join additions would be realistic to
get in early in the 9.5 cycle, but the semi and anti joins stuff I know
that I'm going to need some more advice for. It makes sense to split them
out and get what I can in sooner rather than delaying it for no good reason.

Regards

David Rowley