Thread: ALTER composite type does not work, but ALTER TABLE which ROWTYPE is used as a type - works fine
ALTER composite type does not work, but ALTER TABLE which ROWTYPE is used as a type - works fine
From
"Dmitry Koterov"
Date:
Hello.<br /><br />I can successfully ALTER a NON-EMPTY table (ct) which ROWTYPE is used as a column type for another table(dt):<br /><br />CREATE TABLE ct (id INTEGER);<br />CREATE TABLE dt (id INTEGER, c ct);<br /><br />INSERT INTO dt VALUES(1,'(666)');<br /> SELECT * FROM dt;<br />-- (1, '(666)')<br /><br />ALTER TABLE ct ADD COLUMN n INTEGER;<br />SELECT* FROM dt;<br /> -- (1, '(666,)')<br /><br />You see, '(666,)' means that the new field is added successfully.<br/><br /><br />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).<br /> Why?<br />
Re: ALTER composite type does not work, but ALTER TABLE which ROWTYPE is used as a type - works fine
From
"Merlin Moncure"
Date:
On Thu, Dec 4, 2008 at 9:53 AM, Dmitry Koterov <dmitry@koterov.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
Re: ALTER composite type does not work, but ALTER TABLE which ROWTYPE is used as a type - works fine
From
"Dmitry Koterov"
Date:
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@gmail.com> wrote:
Because of this there is no reason to ever use 'create type'....alwaysOn Thu, Dec 4, 2008 at 9:53 AM, Dmitry Koterov <dmitry@koterov.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?
use 'create table'. 'alter type' can't add/remove columns anyways.
merlin
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: ALTER composite type does not work, but ALTER TABLE which ROWTYPE is used as a type - works fine
From
Bruce Momjian
Date:
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@gmail.com> wrote: > > > On Thu, Dec 4, 2008 at 9:53 AM, Dmitry Koterov <dmitry@koterov.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@postgresql.org) > > To make changes to your subscription: > > http://www.postgresql.org/mailpref/pgsql-hackers > > -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Re: ALTER composite type does not work, but ALTER TABLE which ROWTYPE is used as a type - works fine
From
"Robert Haas"
Date:
On Sun, Dec 7, 2008 at 7:57 PM, Bruce Momjian <bruce@momjian.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
Re: ALTER composite type does not work, but ALTER TABLE which ROWTYPE is used as a type - works fine
From
"Merlin Moncure"
Date:
On Sun, Dec 7, 2008 at 10:17 PM, Robert Haas <robertmhaas@gmail.com> wrote: > On Sun, Dec 7, 2008 at 7:57 PM, Bruce Momjian <bruce@momjian.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
Re: ALTER composite type does not work, but ALTER TABLE which ROWTYPE is used as a type - works fine
From
Andrew Dunstan
Date:
Merlin Moncure wrote: > On Sun, Dec 7, 2008 at 10:17 PM, Robert Haas <robertmhaas@gmail.com> wrote: > >> On Sun, Dec 7, 2008 at 7:57 PM, Bruce Momjian <bruce@momjian.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
Re: ALTER composite type does not work, but ALTER TABLE which ROWTYPE is used as a type - works fine
From
"Merlin Moncure"
Date:
On Mon, Dec 8, 2008 at 8:01 AM, Andrew Dunstan <andrew@dunslane.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
Re: ALTER composite type does not work, but ALTER TABLE which ROWTYPE is used as a type - works fine
From
"Robert Haas"
Date:
> 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
Re: ALTER composite type does not work, but ALTER TABLE which ROWTYPE is used as a type - works fine
From
Bruce Momjian
Date:
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@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Re: ALTER composite type does not work, but ALTER TABLE which ROWTYPE is used as a type - works fine
From
"Robert Haas"
Date:
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@momjian.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@momjian.us> http://momjian.us > EnterpriseDB http://enterprisedb.com > > + If your life is a hard drive, Christ can be your backup. + >
Re: ALTER composite type does not work, but ALTER TABLE which ROWTYPE is used as a type - works fine
From
Bruce Momjian
Date:
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 toALTER TABLE * http://archives.postgresql.org/pgsql-hackers/2008-12/msg00245.php --------------------------------------------------------------------------- > > ...Robert > > On Wed, Dec 10, 2008 at 6:36 AM, Bruce Momjian <bruce@momjian.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@momjian.us> http://momjian.us > > EnterpriseDB http://enterprisedb.com > > > > + If your life is a hard drive, Christ can be your backup. + > > -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Re: ALTER composite type does not work, but ALTER TABLE which ROWTYPE is used as a type - works fine
From
"Merlin Moncure"
Date:
>> Bruce Momjian <bruce@momjian.us> http://momjian.us >> OK, so what should the TODO item be? On Wed, Dec 10, 2008 at 7:44 AM, Robert Haas <robertmhaas@gmail.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
Re: ALTER composite type does not work, but ALTER TABLE which ROWTYPE is used as a type - works fine
From
Alvaro Herrera
Date:
Merlin Moncure escribió: > >> Bruce Momjian <bruce@momjian.us> http://momjian.us > >> OK, so what should the TODO item be? > On Wed, Dec 10, 2008 at 7:44 AM, Robert Haas <robertmhaas@gmail.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. 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. In fact, types are not in pg_class at all. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Re: ALTER composite type does not work, but ALTER TABLE which ROWTYPE is used as a type - works fine
From
"Robert Haas"
Date:
On Wed, Dec 10, 2008 at 8:00 AM, Bruce Momjian <bruce@momjian.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
Re: ALTER composite type does not work, but ALTER TABLE which ROWTYPE is used as a type - works fine
From
"Merlin Moncure"
Date:
On Wed, Dec 10, 2008 at 9:05 AM, Alvaro Herrera <alvherre@commandprompt.com> wrote: > Merlin Moncure escribió: >> >> Bruce Momjian <bruce@momjian.us> http://momjian.us >> >> OK, so what should the TODO item be? >> On Wed, Dec 10, 2008 at 7:44 AM, Robert Haas <robertmhaas@gmail.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
Re: ALTER composite type does not work, but ALTER TABLE which ROWTYPE is used as a type - works fine
From
Andrew Dunstan
Date:
Merlin Moncure wrote: > On Wed, Dec 10, 2008 at 9:05 AM, Alvaro Herrera > <alvherre@commandprompt.com> wrote: > >> Merlin Moncure escribió: >> >>>>> Bruce Momjian <bruce@momjian.us> http://momjian.us >>>>> OK, so what should the TODO item be? >>>>> >>> On Wed, Dec 10, 2008 at 7:44 AM, Robert Haas <robertmhaas@gmail.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
Re: ALTER composite type does not work, but ALTER TABLE which ROWTYPE is used as a type - works fine
From
Alvaro Herrera
Date:
Merlin Moncure escribió: > On Wed, Dec 10, 2008 at 9:05 AM, Alvaro Herrera > <alvherre@commandprompt.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
Re: ALTER composite type does not work, but ALTER TABLE which ROWTYPE is used as a type - works fine
From
Andrew Chernow
Date:
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/