Re: [HACKERS] SET NOT NULL [NOT VALID / CONCURRENTLY]?

On 21 December 2016 at 16:48, Joel Jacobson <joel@trustly.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