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. Although by that time
On Mon, Jun 01, 2020 at 10:49:25AM -0400, John Bachir wrote:
> 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
> 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
On Fri, May 29, 2020 at 09:53:14PM -0400, John Bachir wrote:
> 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
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
Hi
> 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?
Right. "alter table validate constraint" can not use indexes, but does not
block concurrent read/write queries. Other querie
>
>
>
> John, I think it's worth pointing out that Postgres most likely does a
> full table scan to validate a constraint by design and not in optimization
> oversight. Think of what's gonna happen if the index used for checking is
> corrupted?
>
>
This can't be true: a corrupted index is a failur
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
On Fri, May 29, 2020 at 8:56 AM Sergei Kornilov wrote:
> Hello
>
> Correct index lookup is a difficult task. I tried to implement this
> previously...
>
> 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 p
Hello
Correct index lookup is a difficult task. I tried to implement this
previously...
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/ ) a
11 matches
Mail list logo
