Re: Domain check taking place unnecessarily?

On Wed, 2023-02-08 at 18:01 +0000, Mark Hills wrote:
> I've ruled out waiting on a lock; nothing is reported with
> log_lock_waits=on. This is a test database with exclusive access (2.5
> million rows):
>
> This is PostgreSQL 14.5 on Alpine Linux. Thanks.
>
> CREATE DOMAIN hash AS text
>     CHECK (VALUE ~ E'^[a-zA-Z0-9]{8,32}$');
>  
> devstats=> ALTER TABLE invite ADD COLUMN test text;
> ALTER TABLE
> Time: 8.988 ms
>  
> devstats=> ALTER TABLE invite ADD COLUMN test hash;
> ALTER TABLE
> Time: 30923.380 ms (00:30.923)
>  
> devstats=> ALTER TABLE invite ADD COLUMN test hash DEFAULT NULL;
> ALTER TABLE
> Time: 30344.272 ms (00:30.344)
>  
> devstats=> ALTER TABLE invite ADD COLUMN test hash DEFAULT '123abc123'::hash;
> ALTER TABLE
> Time: 67439.232 ms (01:07.439)

It takes 30 seconds to schan the table and determine that all existing rows
satisky the constraint.

The last example is slower, because there is actually a non-NULL value to check.

If that were not a domain, but a normal check constraint, you could first add
the constraint as NOT VALID and later run ALTER TABLE ... VALIDATE CONSTRAINT ...,
which takes a while too, but does not lock the table quite that much.
But I don't think there is a way to do that with a domain.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com