Jim Finnerty <jfinnert@amazon.com> writes:
> Great example, David. The planner can detect whether a SELECT statement
> contains a volatile function, and can disable the proposed redundant
> inner-join optimization in that case.
> If necessary, the planner could also check that the FK constraint is not
> DEFERRED, but if there are no volatile functions and the SELECT statement
> can't see an inconsistent state created by any other transaction, I think
> that just checking for volatile functions and not being inside a DML
> transaction would be sufficient.
I don't think you're thinking nearly hard enough about what would break
this. The planner does not have much insight into the context a
statement is being used in (e.g. whether we're inside some kind of
PL function). Nor does it get to make assumptions about whether the plan
will be used inside a transaction block or not.
regards, tom lane