Re: Allowing NOT IN to use ANTI joins

On 11 June 2014 17:52, Greg Stark <stark(at)mit(dot)edu> wrote:
> On Wed, Jun 11, 2014 at 3:26 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> 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.
>
> Obviously not general cases of ALTER COLUMN TYPE but dropping a NULL
> constraint seems like the kind of change targeted by Simon's "reduce
> lock strength" patch that I'm sure he's still interested in. I think
> that patch, while full of dragons to steer around, is something that
> will keep coming up again and again in the future. It's a huge
> operational risk that even these short exclusive locks can cause a
> huge production outage if they happen to get queued up behind a
> reporting query.

The focus of the lock strength reduction was around actions that lock
the table for extended periods. So it was mostly about adding things.
All the DROP actions are still AccessExclusiveLocks and will be for a
while.

Having said that, any join plan that relies upon a constraint will
still be valid even if we drop a constraint while the plan executes
because any new writes will not be visible to the executing join plan.
If we are relaxing a constraint, then a writable query that still
thinks a constraint exists won't cause a problem - it may error out
when it need not, but that's not so bad as to be worth worrying about.

So I think we can remove a NOT NULL constraint without too much problem.

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services