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

Re: [GENERAL] Altering a table with a rowtype column

From: Merlin Moncure <mmoncure(at)gmail(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Mike Blackwell <mike(dot)blackwell(at)rrd(dot)com>, pgsql-bugs <pgsql-bugs(at)postgresql(dot)org>, PostgreSQL General <pgsql-general(at)postgresql(dot)org>
Subject: Re: [GENERAL] Altering a table with a rowtype column
Date: 2012-03-07 20:49:39
Message-ID: CAHyXU0xL970eAwXNwSRCMoMQr=WebLcMV+H4zBUAstEjohx8yg@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-bugspgsql-general
On Wed, Mar 7, 2012 at 2:31 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Merlin Moncure <mmoncure(at)gmail(dot)com> writes:
>> On Wed, Mar 7, 2012 at 11:45 AM, Mike Blackwell <mike(dot)blackwell(at)rrd(dot)com> wrote:
>>> alter table a add column even_more_stuff boolean not null default false;
>
>> aha! that's not what you posted last time.  you appended 'not null
>> default false'; which inexplicably breaks the ALTER.
>
>> try this:
>> ALTER TABLE a ADD COLUMN even_more_stuff text not null;
>> ALTER TABLE a ALTER even_more_stuff set default false;
>> ALTER TABLE a DROP COLUMN even_more_stuff;
>> ALTER TABLE a ADD COLUMN even_more_stuff boolean not null default false;
>
>> (this really looks like a bug in postgres, cc-ing to bugs)
>
> It is not a bug.  The ALTER ADD ... DEFAULT ... form implies rewriting
> every existing tuple of the rowtype to insert a non-null value in the
> added column, and we don't have support for doing that to rowtype
> columns, only to the target table and descendants.

I'm not buying that..it implies no such thing.  In particular, for
table-as-rowtype columns, there's no way that I can see to have
default values be generated.  So why does it follow that the dependent
table has to be rewritten?  Column constraints are not enforced on the
rowtype, so it follows that default shouldn't be either considering
there's no way to get the default to fire.  Composite type (or table
based composite) defaults are applied to the composite as a whole, not
to specific fields.

On a practical level, the error blocks nothing -- you can bypass it
trivially.   It's just an annoyance that prevents things that users
would like to be able to do with table row types.  So I'd argue to
remove the check, although I can kinda see the argument that it's not
a bug unless the check was recently introduced so that it broke older
code.

merlin

In response to

Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2012-03-07 22:08:25
Subject: Re: Extension tracking temp table and causing update failure
Previous:From: Alvaro HerreraDate: 2012-03-07 20:49:22
Subject: Re: [GENERAL] Altering a table with a rowtype column

pgsql-general by date

Next:From: Bret SternDate: 2012-03-07 21:00:32
Subject: Re: Show Databases via ODBC
Previous:From: Alvaro HerreraDate: 2012-03-07 20:49:22
Subject: Re: [GENERAL] Altering a table with a rowtype column

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