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

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

From: Robert Haas <robertmhaas(at)gmail(dot)com>
To: Merlin Moncure <mmoncure(at)gmail(dot)com>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, 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-20 17:16:36
Message-ID: CA+TgmobFiwU8ny1Sy8tHR_XyHsy3pgCMJJE36e68fBP6bTKQJg@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-bugspgsql-general
On Wed, Mar 7, 2012 at 3:49 PM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
> 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.

I think Tom's correct about what the right behavior would be if
composite types supported defaults, but they don't, never have, and
maybe never will.  I had a previous argument about this with Tom, and
lost, though I am not sure that anyone other than Tom thinks that the
current behavior is for the best.  But see commits
a06e41deebdf74b8b5109329dc75b2e9d9057962 and
a40b1e0bf32b1da46c1baa9bc7da87f207cd37d8.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

In response to

Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2012-03-20 18:48:46
Subject: Re: [GENERAL] Altering a table with a rowtype column
Previous:From: kadasDate: 2012-03-20 06:59:03
Subject: BUG #6545: le telechargement ne s acheve pas

pgsql-general by date

Next:From: Reid ThompsonDate: 2012-03-20 17:45:40
Subject: Re: Is it even possible?
Previous:From: Gary ChambersDate: 2012-03-20 17:07:57
Subject: Re: Is it even possible?

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