Re: Reduce lock level for ALTER TABLE ... ADD CHECK .. NOT VALID

On Sat, Jul 10, 2021 at 2:50 PM John Naylor
<john(dot)naylor(at)enterprisedb(dot)com> wrote:
> On Thu, Apr 22, 2021 at 8:01 AM Simon Riggs <simon(dot)riggs(at)enterprisedb(dot)com> wrote:
> >
> > 897795240cfaaed724af2f53ed2c50c9862f951f forgot to reduce the lock
> > level for CHECK constraints when allowing them to be NOT VALID.
> >
> > This is simple and safe, since check constraints are not used in
> > planning until validated.
>
> The patch also reduces the lock level when NOT VALID is not specified, which didn't seem to be the intention.

Thank you for reviewing. I agree that the behavior works as you indicated.

My description of this was slightly muddled. The lock level for
CONSTR_FOREIGN applies whether or not NOT VALID is used, but the test
case covers only NOT VALID because it a) isn't tested and b) is more
important. I just followed that earlier pattern and that led me to
adding "NOT VALID" onto the title of the thread.

What is true for CONSTR_FOREIGN is also true for CONSTR_CHECK - the
lock level can be set down to ShareRowExclusiveLock in all cases
because adding a new CHECK does not affect the outcome of currently
executing SELECT statements. (Note that this is not true for Drop
Constraint, which has a different lock level, but we aren't changing
that here). Once the constraint is validated it may influence the
optimization of later SELECTs.

So the patch and included docs are completely correct. Notice that the
name of the patch reflects this better than the title of the thread.

--
Simon Riggs http://www.EnterpriseDB.com/