Re: adding a nullable column of type domain w/ check constraint runs checks?

I have a large table that I don't want to lock for more than couple
seconds. I want to add a nullable column to the table, the type of the
column is a domain with a check constraint.

It appears that the check constraint is being checked for each row, even
though the column can be nullable? Is there a way around this?

BEGIN
Timing is on.

create domain test_enum numeric check (value > 0);
CREATE DOMAIN
Time: 1.817 ms

create table test_enum_table (id serial primary key);
CREATE TABLE
Time: 2.213 ms

insert into test_enum_table select * from generate_series(1, 2000000);
INSERT 0 2000000
Time: 4299.000 ms

alter table test_enum_table add column t test_enum;
ALTER TABLE
Time: 3165.869 ms -- Takes 3 seconds in this test example

On Tue, Aug 19, 2014 at 3:10 PM, Joe Van Dyk <joe(at)tanga(dot)com> wrote:

> I have a large table that I don't want to lock for more than couple
> seconds. I want to add a nullable column to the table, the type of the
> column is a domain with a check constraint.
>
> It appears that the check constraint is being checked for each row, even
> though the column can be nullable? Is there a way around this?
>
> BEGIN
> Timing is on.
>
> create domain test_enum numeric check (value > 0);
> CREATE DOMAIN
> Time: 1.817 ms
>
> create table test_enum_table (id serial primary key);
> CREATE TABLE
> Time: 2.213 ms
>
> insert into test_enum_table select * from generate_series(1, 2000000);
> INSERT 0 2000000
> Time: 4299.000 ms
>
> alter table test_enum_table add column t test_enum;
> ALTER TABLE
> Time: 3165.869 ms -- Takes 3 seconds in this test example
>
> Also:

alter table test_enum_table add column t1 numeric check (t1 > 0);
ALTER TABLE
Time: 140.185 ms

which is much more reasonable.

On Tue, Aug 19, 2014 at 3:16 PM, Joe Van Dyk <joe(at)tanga(dot)com> wrote:

> On Tue, Aug 19, 2014 at 3:10 PM, Joe Van Dyk <joe(at)tanga(dot)com> wrote:
>
>> I have a large table that I don't want to lock for more than couple
>> seconds. I want to add a nullable column to the table, the type of the
>> column is a domain with a check constraint.
>>
>> It appears that the check constraint is being checked for each row, even
>> though the column can be nullable? Is there a way around this?
>>
>> BEGIN
>> Timing is on.
>>
>> create domain test_enum numeric check (value > 0);
>> CREATE DOMAIN
>> Time: 1.817 ms
>>
>> create table test_enum_table (id serial primary key);
>> CREATE TABLE
>> Time: 2.213 ms
>>
>> insert into test_enum_table select * from generate_series(1, 2000000);
>> INSERT 0 2000000
>> Time: 4299.000 ms
>>
>> alter table test_enum_table add column t test_enum;
>> ALTER TABLE
>> Time: 3165.869 ms -- Takes 3 seconds in this test example
>>
>> Also:
>
> alter table test_enum_table add column t1 numeric check (t1 > 0);
> ALTER TABLE
> Time: 140.185 ms
>
> which is much more reasonable.
>

johnto on irc says:

"I'm not sure why it's done this way. it seems like it could test the
domain once against NULL and see whether that's rejected or not. instead
it just forces a rewrite :-("

On Tue, Aug 19, 2014 at 3:20 PM, Joe Van Dyk <joe(at)tanga(dot)com> wrote:

> On Tue, Aug 19, 2014 at 3:16 PM, Joe Van Dyk <joe(at)tanga(dot)com> wrote:
>
>> On Tue, Aug 19, 2014 at 3:10 PM, Joe Van Dyk <joe(at)tanga(dot)com> wrote:
>>
>>> I have a large table that I don't want to lock for more than couple
>>> seconds. I want to add a nullable column to the table, the type of the
>>> column is a domain with a check constraint.
>>>
>>> It appears that the check constraint is being checked for each row, even
>>> though the column can be nullable? Is there a way around this?
>>>
>>> BEGIN
>>> Timing is on.
>>>
>>> create domain test_enum numeric check (value > 0);
>>> CREATE DOMAIN
>>> Time: 1.817 ms
>>>
>>> create table test_enum_table (id serial primary key);
>>> CREATE TABLE
>>> Time: 2.213 ms
>>>
>>> insert into test_enum_table select * from generate_series(1, 2000000);
>>> INSERT 0 2000000
>>> Time: 4299.000 ms
>>>
>>> alter table test_enum_table add column t test_enum;
>>> ALTER TABLE
>>> Time: 3165.869 ms -- Takes 3 seconds in this test example
>>>
>>> Also:
>>
>> alter table test_enum_table add column t1 numeric check (t1 > 0);
>> ALTER TABLE
>> Time: 140.185 ms
>>
>> which is much more reasonable.
>>
>
> johnto on irc says:
>
> "I'm not sure why it's done this way. it seems like it could test the
> domain once against NULL and see whether that's rejected or not. instead
> it just forces a rewrite :-("
>

Would it be possible to check the domain against null and if that works,
then don't check any of the rows?

Joe