Domain check taking place unnecessarily?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



I'm used to adding an empty column being instant in most cases, so my 
attention was drawn when it took a long lock.

The timings below imply that each row is running the CHECK?

I've come to expect addition of a NULL column to be fast, and what I'm 
seeing seems to contradict the docs [1]:

> PostgreSQL assumes that CHECK constraints' conditions are immutable, 
> that is, they will always give the same result for the same input value. 
> This assumption is what justifies examining CHECK constraints only when 
> a value is first converted to be of a domain type, and not at other 
> times.

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):

I don't think this is another index or constraint, as removing them does 
not affect performance. Also the "text" case below seems to prove this. 
Results are fully reproducable by repeatedly dropping and adding these 
columns.

Reporting in case something is not as expected. I can't even think of a 
workaround here...

This is PostgreSQL 14.5 on Alpine Linux. Thanks.

[1] https://www.postgresql.org/docs/current/sql-createdomain.html

---

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)

-- 
Mark





[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux