On Wed, 8 Feb 2023, Laurenz Albe wrote:
> 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.
But there's no existing data (note this is adding column, not constraint)
Existing rows are guaranteed to satisfy the domain check, because the
domain check is guaranteed to be immutable (per [1] in my original mail)
Of course, if it were a table constraint it may involve multiple columns,
requiring it to be evaluated per-row.
But the docs make it clear the domain check is expected to be evaluated on
input, precisely for this purpose.
So I wondered if this was a shortcoming or even a bug.
It seems that adding a column of NULL (or even default) values for a
domain can (should?) be as quick as a basic data type like text or
integer...?
--
Mark