ALTER composite type does not work, but ALTER TABLE which ROWTYPE is used as a type - works fine

Hello.

I can successfully ALTER a NON-EMPTY table (ct) which ROWTYPE is used as a
column type for another table (dt):

CREATE TABLE ct (id INTEGER);
CREATE TABLE dt (id INTEGER, c ct);

INSERT INTO dt VALUES(1, '(666)');
SELECT * FROM dt;
-- (1, '(666)')

ALTER TABLE ct ADD COLUMN n INTEGER;
SELECT * FROM dt;
-- (1, '(666,)')

You see, '(666,)' means that the new field is added successfully.

But, if I declare ct as a COMPOSITE type (not a table), it is not permitted
to ALTER this type (Postgres says that there are dependensies on ct).
Why?

On Thu, Dec 4, 2008 at 9:53 AM, Dmitry Koterov <dmitry(at)koterov(dot)ru> wrote:
> Hello.
>
> I can successfully ALTER a NON-EMPTY table (ct) which ROWTYPE is used as a
> column type for another table (dt):
>
> CREATE TABLE ct (id INTEGER);
> CREATE TABLE dt (id INTEGER, c ct);
>
> INSERT INTO dt VALUES(1, '(666)');
> SELECT * FROM dt;
> -- (1, '(666)')
>
> ALTER TABLE ct ADD COLUMN n INTEGER;
> SELECT * FROM dt;
> -- (1, '(666,)')
>
> You see, '(666,)' means that the new field is added successfully.
>
>
> But, if I declare ct as a COMPOSITE type (not a table), it is not permitted
> to ALTER this type (Postgres says that there are dependensies on ct).
> Why?

Because of this there is no reason to ever use 'create type'....always
use 'create table'. 'alter type' can't add/remove columns anyways.

merlin

Could you please say, if ALTER TYPE ... ADD COLUMN is planned for a future
PostgreSQL version?

On Fri, Dec 5, 2008 at 4:08 PM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:

> On Thu, Dec 4, 2008 at 9:53 AM, Dmitry Koterov <dmitry(at)koterov(dot)ru> wrote:
> > Hello.
> >
> > I can successfully ALTER a NON-EMPTY table (ct) which ROWTYPE is used as
> a
> > column type for another table (dt):
> >
> > CREATE TABLE ct (id INTEGER);
> > CREATE TABLE dt (id INTEGER, c ct);
> >
> > INSERT INTO dt VALUES(1, '(666)');
> > SELECT * FROM dt;
> > -- (1, '(666)')
> >
> > ALTER TABLE ct ADD COLUMN n INTEGER;
> > SELECT * FROM dt;
> > -- (1, '(666,)')
> >
> > You see, '(666,)' means that the new field is added successfully.
> >
> >
> > But, if I declare ct as a COMPOSITE type (not a table), it is not
> permitted
> > to ALTER this type (Postgres says that there are dependensies on ct).
> > Why?
>
> Because of this there is no reason to ever use 'create type'....always
> use 'create table'. 'alter type' can't add/remove columns anyways.
>
> merlin
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

Dmitry Koterov wrote:
> Could you please say, if ALTER TYPE ... ADD COLUMN is planned for a future
> PostgreSQL version?

It is not currently on the TODO list.

---------------------------------------------------------------------------

>
>
> On Fri, Dec 5, 2008 at 4:08 PM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
>
> > On Thu, Dec 4, 2008 at 9:53 AM, Dmitry Koterov <dmitry(at)koterov(dot)ru> wrote:
> > > Hello.
> > >
> > > I can successfully ALTER a NON-EMPTY table (ct) which ROWTYPE is used as
> > a
> > > column type for another table (dt):
> > >
> > > CREATE TABLE ct (id INTEGER);
> > > CREATE TABLE dt (id INTEGER, c ct);
> > >
> > > INSERT INTO dt VALUES(1, '(666)');
> > > SELECT * FROM dt;
> > > -- (1, '(666)')
> > >
> > > ALTER TABLE ct ADD COLUMN n INTEGER;
> > > SELECT * FROM dt;
> > > -- (1, '(666,)')
> > >
> > > You see, '(666,)' means that the new field is added successfully.
> > >
> > >
> > > But, if I declare ct as a COMPOSITE type (not a table), it is not
> > permitted
> > > to ALTER this type (Postgres says that there are dependensies on ct).
> > > Why?
> >
> > Because of this there is no reason to ever use 'create type'....always
> > use 'create table'. 'alter type' can't add/remove columns anyways.
> >
> > merlin
> >
> > --
> > Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-hackers
> >

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

On Sun, Dec 7, 2008 at 7:57 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> Dmitry Koterov wrote:
>> Could you please say, if ALTER TYPE ... ADD COLUMN is planned for a future
>> PostgreSQL version?
>
> It is not currently on the TODO list.

Perhaps we could add it? It's been complained about more than once in
this space.

...Robert

On Sun, Dec 7, 2008 at 10:17 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Sun, Dec 7, 2008 at 7:57 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
>> Dmitry Koterov wrote:
>>> Could you please say, if ALTER TYPE ... ADD COLUMN is planned for a future
>>> PostgreSQL version?
>>
>> It is not currently on the TODO list.
>
> Perhaps we could add it? It's been complained about more than once in
> this space.

Well, new features that have a perfectly acceptable and usable
workaround typically have a fairly low priority of fixing :-)

Since tables are basically types, I'm not sure what the difference is
between tables and composite types (meaning, why do we have the
composite type syntax at all?) I'm not sure if this came up during
the design discussion or not.

merlin

Merlin Moncure wrote:
> On Sun, Dec 7, 2008 at 10:17 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>
>> On Sun, Dec 7, 2008 at 7:57 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
>>
>>> Dmitry Koterov wrote:
>>>
>>>> Could you please say, if ALTER TYPE ... ADD COLUMN is planned for a future
>>>> PostgreSQL version?
>>>>
>>> It is not currently on the TODO list.
>>>
>> Perhaps we could add it? It's been complained about more than once in
>> this space.
>>
>
> Well, new features that have a perfectly acceptable and usable
> workaround typically have a fairly low priority of fixing :-)
>
> Since tables are basically types, I'm not sure what the difference is
> between tables and composite types (meaning, why do we have the
> composite type syntax at all?) I'm not sure if this came up during
> the design discussion or not.
>
>
>

Your "workaround" involves have a redundant table that you don't ever
intend to populate.

cheers

andrew

On Mon, Dec 8, 2008 at 8:01 AM, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
> Merlin Moncure wrote:
>> Well, new features that have a perfectly acceptable and usable
>> workaround typically have a fairly low priority of fixing :-)
>>
>> Since tables are basically types, I'm not sure what the difference is
>> between tables and composite types (meaning, why do we have the
>> composite type syntax at all?) I'm not sure if this came up during
>> the design discussion or not.
>
> Your "workaround" involves have a redundant table that you don't ever intend
> to populate.

Redundant how? Since tables and types exist in the same namespace
(can't have table and type in the same schema with the same name), a
table is just a type with storage. If that's a big deal, remove the
insert priv...

I like to keep the table based types I use in a special schema, like
'types' anyways.

merlin

> Well, new features that have a perfectly acceptable and usable
> workaround typically have a fairly low priority of fixing :-)

Putting something in the TODO list doesn't make it a priority. But it
indicates that it's something that the community would like to see
fixed, if anyone is interested in doing the work. There is a lot more
consensus for this item than many that have been added to the TODO
list in the past.

...Robert

Robert Haas wrote:
> > Well, new features that have a perfectly acceptable and usable
> > workaround typically have a fairly low priority of fixing :-)
>
> Putting something in the TODO list doesn't make it a priority. But it
> indicates that it's something that the community would like to see
> fixed, if anyone is interested in doing the work. There is a lot more
> consensus for this item than many that have been added to the TODO
> list in the past.

OK, so what should the TODO item be?

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Allow ALTER TYPE to add, rename, change the type of, and drop columns?

...Robert

On Wed, Dec 10, 2008 at 6:36 AM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> Robert Haas wrote:
>> > Well, new features that have a perfectly acceptable and usable
>> > workaround typically have a fairly low priority of fixing :-)
>>
>> Putting something in the TODO list doesn't make it a priority. But it
>> indicates that it's something that the community would like to see
>> fixed, if anyone is interested in doing the work. There is a lot more
>> consensus for this item than many that have been added to the TODO
>> list in the past.
>
> OK, so what should the TODO item be?
>
> --
> Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
> EnterpriseDB http://enterprisedb.com
>
> + If your life is a hard drive, Christ can be your backup. +
>

>> Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
>> OK, so what should the TODO item be?
On Wed, Dec 10, 2008 at 7:44 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> Allow ALTER TYPE to add, rename, change the type of, and drop columns?

That's probably the consensus view. Personally, I think creating
composite types through 'create type as' was a mistake...we probably
should have gone through create table instead with some special syntax
for storage-less tables aka composite types.

Even if I'm right though, I don't think that mistake can be taken
back, so all that functionality needs to be reimplemented in create
type somehow.

merlin

Robert Haas wrote:
> Allow ALTER TYPE to add, rename, change the type of, and drop columns?

That seems kind of vague because my first reaction is that a type
doesn't have columns, but you are talking about composite types, right?

I have added this TODO item:

Allow ALTER TYPE on composite types to perform operations similar to
ALTER TABLE

* http://archives.postgresql.org/pgsql-hackers/2008-12/msg00245.php

---------------------------------------------------------------------------

>
> ...Robert
>
> On Wed, Dec 10, 2008 at 6:36 AM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> > Robert Haas wrote:
> >> > Well, new features that have a perfectly acceptable and usable
> >> > workaround typically have a fairly low priority of fixing :-)
> >>
> >> Putting something in the TODO list doesn't make it a priority. But it
> >> indicates that it's something that the community would like to see
> >> fixed, if anyone is interested in doing the work. There is a lot more
> >> consensus for this item than many that have been added to the TODO
> >> list in the past.
> >
> > OK, so what should the TODO item be?
> >
> > --
> > Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
> > EnterpriseDB http://enterprisedb.com
> >
> > + If your life is a hard drive, Christ can be your backup. +
> >

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

On Wed, Dec 10, 2008 at 9:05 AM, Alvaro Herrera
<alvherre(at)commandprompt(dot)com> wrote:
> Merlin Moncure escribió:
>> >> Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
>> >> OK, so what should the TODO item be?
>> On Wed, Dec 10, 2008 at 7:44 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>> > Allow ALTER TYPE to add, rename, change the type of, and drop columns?
>>
>> That's probably the consensus view. Personally, I think creating
>> composite types through 'create type as' was a mistake...we probably
>> should have gone through create table instead with some special syntax
>> for storage-less tables aka composite types.
>
> I disagree that CREATE TABLE should be (or should have been) used to
> create types. Someday we might need to expand the work we do for that
> case in a different direction than tables, and we would be stuck.

But, tables _are_ types, particularly in relational parlance. In
fact, postgresql's older, more relational terms (tuples and such) are
coming from that perspective, although I admit that's mostly
irrelevant now. I think we are more stuck now, having to re-implement
many things alter table does in 'alter type (as)???'. It's a mess.
What if we want to add check constraints to composite types?

> Also, for tables we create files, we generate statistics, we compute
> relfrozenxid, we call vacuum on, and so on and so forth. We do none of
> these things on types.

Those things are what come with 'storage' so if you are defining a
type with no storage mechanism you could possibly skip those things.

> In fact, types are not in pg_class at all.

incorrect!! composite types are in pg_class (relkind='c'). That
actually knida confirms what I'm saying, composite types were added in
a confusing overlay over the 'create type' command, which is something
completely different. create type means two completely different
things depending on a minor grammar change...gah! :-)

I still stand by by statement...create table should have allowed you
to create a composite type as we do it with create type as today...and
(perhaps) storage (relfrozenxid etc.) could be added or removed with
alter table.

merlin

On Wed, Dec 10, 2008 at 8:00 AM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> Robert Haas wrote:
>> Allow ALTER TYPE to add, rename, change the type of, and drop columns?
>
> That seems kind of vague because my first reaction is that a type
> doesn't have columns, but you are talking about composite types, right?

Right.

> I have added this TODO item:
>
> Allow ALTER TYPE on composite types to perform operations similar to
> ALTER TABLE
>
> * http://archives.postgresql.org/pgsql-hackers/2008-12/msg00245.php

Sounds good.

...Robert

Merlin Moncure wrote:
> On Wed, Dec 10, 2008 at 9:05 AM, Alvaro Herrera
> <alvherre(at)commandprompt(dot)com> wrote:
>
>> Merlin Moncure escribió:
>>
>>>>> Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
>>>>> OK, so what should the TODO item be?
>>>>>
>>> On Wed, Dec 10, 2008 at 7:44 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>>>
>>>> Allow ALTER TYPE to add, rename, change the type of, and drop columns?
>>>>
>>> That's probably the consensus view. Personally, I think creating
>>> composite types through 'create type as' was a mistake...we probably
>>> should have gone through create table instead with some special syntax
>>> for storage-less tables aka composite types.
>>>
>> I disagree that CREATE TABLE should be (or should have been) used to
>> create types. Someday we might need to expand the work we do for that
>> case in a different direction than tables, and we would be stuck.
>>
>
> But, tables _are_ types, particularly in relational parlance. In
> fact, postgresql's older, more relational terms (tuples and such) are
> coming from that perspective, although I admit that's mostly
> irrelevant now. I think we are more stuck now, having to re-implement
> many things alter table does in 'alter type (as)???'. It's a mess.
> What if we want to add check constraints to composite types?
>
>
>> Also, for tables we create files, we generate statistics, we compute
>> relfrozenxid, we call vacuum on, and so on and so forth. We do none of
>> these things on types.
>>
>
> Those things are what come with 'storage' so if you are defining a
> type with no storage mechanism you could possibly skip those things.
>
>
>> In fact, types are not in pg_class at all.
>>
>
> incorrect!! composite types are in pg_class (relkind='c'). That
> actually knida confirms what I'm saying, composite types were added in
> a confusing overlay over the 'create type' command, which is something
> completely different. create type means two completely different
> things depending on a minor grammar change...gah! :-)
>
> I still stand by by statement...create table should have allowed you
> to create a composite type as we do it with create type as today...and
> (perhaps) storage (relfrozenxid etc.) could be added or removed with
> alter table.
>
>
>

This whole debate seems moot. We're not going to remove composite types
created with CREATE TYPE, so the rest is irrelevant. We don't have the
luxury of revisiting such decisions made many years ago, whether or not
you think they were good.

cheers

andrew

Merlin Moncure escribió:
> On Wed, Dec 10, 2008 at 9:05 AM, Alvaro Herrera
> <alvherre(at)commandprompt(dot)com> wrote:

> > I disagree that CREATE TABLE should be (or should have been) used to
> > create types. Someday we might need to expand the work we do for that
> > case in a different direction than tables, and we would be stuck.
>
> But, tables _are_ types, particularly in relational parlance.

Well, yeah, they are on relational. But here on Postgres, "tables are
types" is correct, but not the other way around. (And this is expressed
by "tables are in pg_class, types are on pg_type"; and tables "have" an
homonymous type.) The distinction is blurred by having composites in
pg_class too, of course. (I don't know the reason that they are. Maybe
they don't really need to. Maybe we could get away with having
pg_attribute entries with no corresponding pg_class entry.)

I'm not too sure about ALTER TYPE duplicating stuff. Perhaps they could
both be implemented by the same code underneath. Up to now, I haven't
seen much request for these features such as check constraints on
composites; perhaps that's the reason we don't have them.

> > In fact, types are not in pg_class at all.
>
> incorrect!! composite types are in pg_class (relkind='c'). That
> actually knida confirms what I'm saying, composite types were added in
> a confusing overlay over the 'create type' command, which is something
> completely different. create type means two completely different
> things depending on a minor grammar change...gah! :-)

Maybe the problem is not that they are created with CREATE TYPE, but
that they have a pg_class entry ;-)

> I still stand by by statement...create table should have allowed you
> to create a composite type as we do it with create type as today...and
> (perhaps) storage (relfrozenxid etc.) could be added or removed with
> alter table.

Well, these days we could probably have implemented this as CREATE TABLE
with a specific storage option. We didn't have that at the time.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Andrew Dunstan wrote:
>
> This whole debate seems moot. We're not going to remove composite types
> created with CREATE TYPE, so the rest is irrelevant. We don't have the
> luxury of revisiting such decisions made many years ago, whether or not
> you think they were good.
>
>

You can always fix something. It can be deprecated in favor of a
cleaner and more elagant method.

My two cents ... I never use CREATE TYPE AS, seems redundant and
limited. I use CREATE TABLE and just never insert any records.

--
Andrew Chernow
eSilo, LLC
every bit counts
http://www.esilo.com/