On Wed, Jun 4, 2014 at 11:50 AM, Noah Misch <noah@leadboat.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:
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.
