From: | Craig Ringer <craig(at)2ndquadrant(dot)com> |
---|---|
To: | Joel Jacobson <joel(at)trustly(dot)com> |
Cc: | Pg Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: SET NOT NULL [NOT VALID / CONCURRENTLY]? |
Date: | 2016-12-21 09:24:35 |
Message-ID: | CAMsr+YHe1mG4rCV+7g+o1WB44+uo_+O8DXB3Jj6UWCRn-kqqUw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On 21 December 2016 at 16:48, Joel Jacobson <joel(at)trustly(dot)com> wrote:
> Hi hackers,
>
> I would be good if it would be possible to quickly set NOT NULL for an
> existing column in a table
> that have no rows where the column IS NULL and where there is a full
> index on the column
> allowing the logics to quickly understand there are no NULL values,
> and just have to take a quick
> lock on the table to prevent any modifications during the short time
> when the NOT NULL
> is set for the column.
>
> Currently if you want to set NOT NULL for a column in a huge table
> that's not doable without blocking all writes to the table for quite
> some time.
> Setting NOT NULL for a 100 million row table took 28 seconds locally
> on my machine.
>
> Is anyone working on fixing this for PostgreSQL 10?
Not as far as I know.
IMO this and other similar cases should all be handled the same way:
create the constraint NOT VALID, then VALIDATE it while holding a weak
lock that only blocks concurrent schema changes.
--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
From | Date | Subject | |
---|---|---|---|
Next Message | Andres Freund | 2016-12-21 09:28:05 | Re: simplehash vs. pgindent |
Previous Message | Joel Jacobson | 2016-12-21 08:48:00 | SET NOT NULL [NOT VALID / CONCURRENTLY]? |