Wow! Thank you Sergei for working on this patch, for working for months/years to get it in, and for replying to my email!
For others reading this later: - the feature was introduced in 12 - the commit is here https://github.com/postgres/postgres/commit/bbb96c3704c041d139181c6601e5bc770e045d26 Sergei, a few questions: - Just to be clear, your recipe does not require any indexes, right? Because the constraint check table scan is inherently concurrent? - Was this new behavior mentioned in the release nose? - Do you know if there are any blog posts etc. discussing this? (I'm definitely going to write one!!) John > > But the answer in SO is a bit incomplete for recent postgresql > releases. Seqscan is not the only possible way to set not null in > pg12+. My patch was commited ( > https://commitfest.postgresql.org/22/1389/ ) and now it's possible to > do this way: > > alter table foos > add constraint foos_not_null > check (bar1 is not null) not valid; -- short-time exclusive lock > > alter table foos validate constraint foos_not_null; -- still seqscan > entire table but without exclusive lock > > An then another short lock: > alter table foos alter column bar1 set not null; > alter table foos drop constraint foos_not_null; > > regards, Sergei >
