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
inherentlyconcurrent?
- 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
>