Re: lack of consequence with domains and types - Mailing list pgsql-general

On Dec 22, 2008, at 4:49 AM, Grzegorz Jaśkiewicz wrote:

> so, consider this one:
>
> create sequence seq1;
>
> create domain foo1 as bigint default nextval('seq1') not null;
> create domain foo2 as timestamp without time zone default now() not
> null;
> create type footype as
> (
>  a foo1,
>  b foo2
> ) ;
>
> create table bar(a bigint not null, b varchar(20));
> insert into bar(a) select generate_series(1,100);
> alter table bar add column blah footype not null;
>
>
> ERROR:  column "blah" contains null values
>
>
> :/
>
>
> I was expecting domains to kick in with their default values again. I
> presume this is somehow similar to problem with enums I raised before.

Hmm...  While I see what you mean I don't think that would be
desirable as it would conflict with the expected behavior of ALTER
TABLE when adding columns without explicit defaults set in the ALTER
TABLE command:

" When a column is added with ADD COLUMN, all existing rows in the
table are initialized with the column's default value (NULL if no
DEFAULT clause is specified).

Adding a column with a non-null default or changing the type of an
existing column will require the entire table to be rewritten. This
may take a significant amount of time for a large table; and it will
temporarily require double the disk space. "

This says that it you add a column without an explicit default in the
ALTER TABLE statement it definitely will *not* write any values and
will be a quick operation.  Since it doesn't look to write any values
the domain's default is never looked at.  If it did then it would
contradict the defined behavior of adding a column without a default
value in the ALTER TABLE statement.

> Obviously I can work around that thing with:
>
> create sequence seq1;
>
> create type footype as
> (
>  a bigint,
>  b timestamp without time zone
> );
>
> create table bar(a bigint not null, b varchar(20));
> insert into bar(a) select generate_series(1,100);
>
> alter table bar add column blah footype not null default (
> nextval('seq1'), now()) ;
>
>
> but that defeats whole purpose of domains, doesn't it ?
>
> well, on top of that - I could create another domain with default
> (nextval, now), but still....
> The feature of domains and types is really great, but I see a lack of
> consequence here. It would be great to see that fixed in future
> versions of pg.

As mentioned above, by "fixing" the behavior to be what you're
expecting you'd be breaking the defined behavior of ALTER TABLE.

Erik Jones, Database Administrator
Engine Yard
Support, Scalability, Reliability
866.518.9273 x 260
Location: US/Pacific
IRC: mage2k