On Thu, 15 Jul 2021 at 07:47, Simon Riggs <simon.ri...@enterprisedb.com> wrote: > > On Sat, Jul 10, 2021 at 2:50 PM John Naylor > <john.nay...@enterprisedb.com> wrote: > > On Thu, Apr 22, 2021 at 8:01 AM Simon Riggs <simon.ri...@enterprisedb.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/
alter_table_reduce_more_lock_levels.v1.patch
Description: Binary data
