Thank you Justin for all that useful info! A couple nitpicky questions, so I
can get my recipe right.
On Mon, Jun 1, 2020, at 10:04 PM, Justin Pryzby wrote:
> On Mon, Jun 01, 2020 at 10:49:25AM -0400, John Bachir wrote:
> > Thanks! I'll add that to my recipe for the future. Althoug
> 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.
Just checking - I think you mean lock_timeout? (although setting
deadlock_timeout is also not a bad idea jus
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
Hi Sergei - I just used the recipe on my production database. I didn't observe
all the expected benefits, I wonder if there were confounding factors or if I
did something wrong. If you have time, I'd love to get your feedback. Let me
know if you need more info. I'd love to write a blog post info
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
S
There's the age-old problem of SET NOT NULL being impossible on large actively
used tables, because it needs to lock the table and do a table scan to check if
there are any existing NULL values. I currently have a table that's not
particularly huge but a scan takes 70 seconds, which causes unacc
