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. A further opportunity would be to apply this to any SELECT statement in a DML transaction, provided that there was no prior DML statement or statement containing a volatile function in the same transaction. We already have a redundant outer join optimization, and I've implemented the redundant inner join optimization in two other products before, so adding the additional logic to support the inner join case(s) sounds straightforward to me. Can anyone think of any other problem scenarios? /Jim F -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
