Обсуждение: lack of consequence with domains and types
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.
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.
Thanks :)
--
GJ
On Mon, Dec 22, 2008 at 1:49 PM, Grzegorz Jaśkiewicz <gryzman@gmail.com> wrote:
> 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....
Well I can't, it doesn't work :(
create domain xyz as footype default(nextval('seq1'), now()) not null;
ERROR: "xyz" is not a valid base type for a domain
huh....
--
GJ
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
On Mon, Dec 22, 2008 at 6:10 PM, Erik Jones <ejones@engineyard.com> wrote: > As mentioned above, by "fixing" the behavior to be what you're expecting > you'd be breaking the defined behavior of ALTER TABLE. I don't understand. The domain's have default values, how will it break alter table ? Please explain. -- GJ
On Dec 22, 2008, at 1:08 PM, Grzegorz Jaśkiewicz wrote: > On Mon, Dec 22, 2008 at 6:10 PM, Erik Jones <ejones@engineyard.com> > wrote: >> As mentioned above, by "fixing" the behavior to be what you're >> expecting >> you'd be breaking the defined behavior of ALTER TABLE. > > I don't understand. The domain's have default values, how will it > break alter table ? Please explain. Yes, and columns have default values, too, which are not tied to their datatype's default value (if it even has one). ALTER TABLE initializes rows to have the new *column's* default. A column of some domain type could easily have some default other than the domain's default and, in fact, if you don't specify a default for the column then it's default is NULL. Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability 866.518.9273 x 260 Location: US/Pacific IRC: mage2k
On Wed, Dec 24, 2008 at 6:12 PM, Erik Jones <ejones@engineyard.com> wrote: > Yes, and columns have default values, too, which are not tied to their > datatype's default value (if it even has one). ALTER TABLE initializes rows > to have the new *column's* default. A column of some domain type could > easily have some default other than the domain's default and, in fact, if > you don't specify a default for the column then it's default is NULL. the whole thing about domains, is that you specify type and default, and even check constraint. And I did specify default - hence I would expect it to be set to that value!! -- GJ
On Dec 24, 2008, at 12:04 PM, Grzegorz Jaśkiewicz wrote: > On Wed, Dec 24, 2008 at 6:12 PM, Erik Jones <ejones@engineyard.com> > wrote: >> Yes, and columns have default values, too, which are not tied to >> their >> datatype's default value (if it even has one). ALTER TABLE >> initializes rows >> to have the new *column's* default. A column of some domain type >> could >> easily have some default other than the domain's default and, in >> fact, if >> you don't specify a default for the column then it's default is NULL. > > the whole thing about domains, is that you specify type and default, > and even check constraint. And I did specify default - hence I would > expect it to be set to that value!! You really need to understand the difference between a domain's default and a column's default. The ALTER TABLE docs specifically say that if you don't specify a default for the new *column* then that column is set to NULL for all rows. That is not the same as not providing a value for a column of some domain type with a default in an INSERT statement. A domain with a default does not specify that it can not be set to null: pagila=# create domain foo_domain as integer default 5; CREATE DOMAIN Time: 289.269 ms pagila=# create table foo_table (a int, b foo_domain); CREATE TABLE Time: 22.784 ms pagila=# insert into foo_table (a, b) values (1, null); INSERT 0 1 Time: 1.489 ms pagila=# insert into foo_table (a) values (1); INSERT 0 1 Time: 1.582 ms pagila=# select * from foo_table; a | b ---+---- 1 | \N 1 | 5 (2 rows) Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability 866.518.9273 x 260 Location: US/Pacific IRC: mage2k
gj=# create domain dfoo as varchar(20) default 'bollocks' not null; CREATE DOMAIN Time: 1680,908 ms gj=# create table foo( a bigserial not null, b int default (random()*100)::int not null ); NOTICE: CREATE TABLE will create implicit sequence "foo_a_seq" for serial column "foo.a" CREATE TABLE Time: 899,848 ms gj=# insert into foo(b) select generate_series(1, 10); INSERT 0 10 Time: 138,247 ms gj=# alter table foo add column c dfoo not null; ALTER TABLE Time: 351,059 ms gj=# select * from foo order by random() limit 1; a | b | c ---+---+---------- 3 | 3 | bollocks (1 row) Time: 114,236 ms I _Do_ understand domains very well. Thing is, why the same thing won't happen if I include that domain in type!!!
I hope Tom can hear my prayers. This basically means, I won't be able to use domains+type in my designs. :/
On Wed, Dec 24, 2008 at 6:41 PM, Erik Jones <ejones@engineyard.com> wrote: > > On Dec 24, 2008, at 12:04 PM, Grzegorz Jaśkiewicz wrote: > >> On Wed, Dec 24, 2008 at 6:12 PM, Erik Jones <ejones@engineyard.com> wrote: >>> >>> Yes, and columns have default values, too, which are not tied to their >>> datatype's default value (if it even has one). ALTER TABLE initializes >>> rows >>> to have the new *column's* default. A column of some domain type could >>> easily have some default other than the domain's default and, in fact, if >>> you don't specify a default for the column then it's default is NULL. >> >> the whole thing about domains, is that you specify type and default, >> and even check constraint. And I did specify default - hence I would >> expect it to be set to that value!! > > You really need to understand the difference between a domain's default and > a column's default. The ALTER TABLE docs specifically say that if you don't > specify a default for the new *column* then that column is set to NULL for > all rows. That is not the same as not providing a value for a column of > some domain type with a default in an INSERT statement. A domain with a > default does not specify that it can not be set to null: I disagree. It's quite natural and reasonable to have defaults passed through the composite type as the OP expects. This is a possible improvement (TODO?) in the way composite types are handled. There are a couple of other loopholes in domans/composite types: * domains can't be stacked in an array (but you can if they are wrapped in a composite type) * check constraints not enforced for composite type on cast (but are for domains) * alter type should be expanded to allow things that are currently possible via alter table (currently a TODO, IIRC), or create table/alter table should be adjusted for better handling of types, and 'create type as' should be deprecated. The latter is what I think should happen, but it's controversial :-). In the meantime the OP has to decide what he wants to use more, composite types or default values on domains. merlin
another glance at source code, and docs tells me - that there's not such thing as default value for custom type - unless that type is defined as new base scalar type. So probably, that would require postgresql to allow users to define default values for composite types as well, like that: create type foo AS ( a int default 1, b foodomain default 'foo', .... ); Going through source code, I have no idea where that would go - because I got only experience in creating types + custom indices, not hacking postgresql guts. More help required here, please .. (I don't know, should that go to -hackers [too]. My recent history there probably makes majority of folks to ignore my posts straight away). Thanks.
On Fri, Dec 26, 2008 at 3:57 PM, Grzegorz Jaśkiewicz <gryzman@gmail.com> wrote: > another glance at source code, and docs tells me - that there's not > such thing as default value for custom type - unless that type is > defined as new base scalar type. So probably, that would require > postgresql to allow users to define default values for composite types > as well, like that: > create type foo AS > ( > a int default 1, > b foodomain default 'foo', > .... > ); don't forget, you can create types via create table: create table foo as ( a int default 1, ... check (a<5) ); create table bar(f foo); insert into bar default values; -- should foo defaults fire?? I say probably, but check constraints should definately be enforced (currently they are not). (since you can alter the table later, there is very little reason not to create types with create table always). merlin