Re: Allowing NOT IN to use ANTI joins

On Wed, Jun 11, 2014 at 11:53 AM, David Rowley <dgrowleyml(at)gmail(dot)com> wrote:
>> The only way to consistently guarantee nullability is through primary
>> key constraints. Fortunately that addresses most of the use cases of
>> NOT IN(), in my experience.

>> See the comment in check_functional_grouping:

> I saw that, but I have to say I've not fully got my head around why that's
> needed just yet.

I was wrong, see Tom's reply to my email. It's OK to rely on
attnotnull for optimization decisions. The plan will be invalidated
automatically when the nullability of a referenced column changes.

check_functional_grouping needs special treatment because it decides
whether to accept/reject views; and if it has allowed creating a view,
it needs to guarantee that the dependent constraint isn't dropped for
a longer term.

> 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.

Regards,
Marti