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

On Thu, 15 Jul 2021 at 07:47, Simon Riggs <simon(dot)riggs(at)enterprisedb(dot)com> wrote:
>
> 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.

An additional patch covering other types of ALTER TABLE attached. Both
can be applied independently.

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