On Fri, May 29, 2020, at 10:10 PM, Justin Pryzby wrote:
> If you do it right, you can see a DEBUG:
> postgres=# SET client_min_messages=debug;
> postgres=# ALTER TABLE tn ALTER i SET NOT NULL ;
> DEBUG: existing constraints on column "tn"."i" are sufficient to prove
> that it does not contain nulls
Thanks! I'll add that to my recipe for the future. Although by that time it would be too late, so to make use of this I
wouldhave to set up a cloned test environment and hope that all conditions are correctly cloned. Is there a way to
checksufficiency before running the command?
> That the duration decreased every time may have been due to caching?
> How big is the table vs RAM ?
Table is about 10 gigs, machine has 16gigs, I'm hoping OS & PG did not decided to kick out everything else from ram
whendoing the operation. But even with caching, the final command being 20ms, and the first 2 commands being the same
timeas a table scan, seems like something other than caching is at play here? IDK!
> Do you know if the SET NOT NULL blocked or not ?
> Maybe something else had a nontrivial lock on the table, and those commands
> were waiting on lock. If you "SET deadlock_timeout='1'; SET
> log_lock_waits=on;", then you could see that.
I don't know if it blocked. Great idea! I'll add that to my recipe as well.
John
p.s. current recipe: https://gist.github.com/jjb/fab5cc5f0e1b23af28694db4fc01c55a
p.p.s I think one of the biggest surprises was that setting the NOT NULL condition was slow. That's totally unrelated
tothis feature though and out of scope for this list though, I asked about it here
https://dba.stackexchange.com/questions/268301/why-is-add-constraint-not-valid-taking-a-long-time