Re: Allowing NOT IN to use ANTI joins

Marti Raudsepp <marti(at)juffo(dot)org> writes:
> On Wed, Jun 11, 2014 at 11:53 AM, David Rowley <dgrowleyml(at)gmail(dot)com> wrote:
>> as long as the transaction id
>> is taken before we start planning in session 1 then it should not matter if
>> another session drops the constraint and inserts a NULL value as we won't
>> see that NULL value in our transaction... I'd assume that the transaction
>> has to start before it grabs the table defs that are required for planning.
>> Or have I got something wrong?

> 1. You're assuming that query plans can only survive for the length of
> a transaction. That's not true, prepared query plans can span many
> transactions.

> 2. Also a FOR UPDATE clause can return values "from the future", if
> another transaction has modified the value and already committed.

> But this whole issue is moot anyway, the plan will get invalidated
> when the nullability changes.

Right. The key point for David's concern is that we always hold (at
least) AccessShareLock on every relation used in a query, continuously
from rewrite through to the end of execution. This will block any attempt
by other transactions to make schema changes in the relation(s).
In the case of re-using a prepared plan, we re-acquire all these locks
and then check to see if we received any invalidation messages that
render the plan invalid; if not, we can proceed to execution with the same
safety guarantees as originally. (If we did, we replan starting from the
raw parse tree.)

If we didn't have mechanisms like this, we'd have far worse hazards from
ALTER TABLE than whether the planner made an incorrect join optimization.
Consider ALTER COLUMN TYPE for instance.

regards, tom lane