Skip site navigation (1) Skip section navigation (2)

Re: lack of consequence with domains and types

From: Erik Jones <ejones(at)engineyard(dot)com>
To: Grzegorz Jaśkiewicz <gryzman(at)gmail(dot)com>
Cc: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Re: lack of consequence with domains and types
Date: 2008-12-22 17:10:58
Message-ID: A6212C59-6A3D-4756-8B1A-A8642042F2C5@engineyard.com (view raw or flat)
Thread:
Lists: 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






In response to

Responses

pgsql-general by date

Next:From: Sebastian TennantDate: 2008-12-22 17:20:10
Subject: Ordering returned rows according to a list
Previous:From: David FetterDate: 2008-12-22 16:56:23
Subject: Re: Backup Policy & Disk Space Issues

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group