Thread: alter table alter columns vs. domains
Is it feasible or practical to consider adding ALTER DOMAIN TYPE type? (basically following the same rules as ALTER TABLE). I don't mind bringing down all the views before I do this, it would be just great if you could change domains from a centralized location. Oracle offers this feature... Merlin
Merlin Moncure wrote: > Is it feasible or practical to consider adding ALTER DOMAIN TYPE type? > (basically following the same rules as ALTER TABLE). > > I don't mind bringing down all the views before I do this, it would be > just great if you could change domains from a centralized location. > Oracle offers this feature... Interesting --- you would have to rebuild every table that uses the domain, and map from-to for all stored values of the domain. TODO item? -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
> > Is it feasible or practical to consider adding ALTER DOMAIN TYPE type? > > (basically following the same rules as ALTER TABLE). > > Interesting --- you would have to rebuild every table that uses the > domain, and map from-to for all stored values of the domain. > > TODO item? Yes. This is something I was going to look at doing in the next release.
> Yes. This is something I was going to look at doing in the next release. Quick question: With your potential changes, you would then be able to alter a domain that is involved in RI constraints between 2 or more tables without bringing down the constraints, yes? This would be great :) Merlin
On Thu, 2004-05-06 at 13:23, Merlin Moncure wrote: > > Yes. This is something I was going to look at doing in the next > release. > > Quick question: > With your potential changes, you would then be able to alter a domain > that is involved in RI constraints between 2 or more tables without > bringing down the constraints, yes? This would be great :) I had been hoping to get away without actually rechecking foreign key constraints, as that makes it significantly more complicated -- but if you set the value to NULL then problems could arise (otherwise fkeys would still match so long as UNIQUE wasn't violated). We would need to apply phases 1 to 3 on all of the tables and move foreign key checks to a phase 4 (intertable work phase?) In the mean time, you can create a new domain then modify the type of all the tables.
Bruce Momjian wrote: >Merlin Moncure wrote: > > >>Is it feasible or practical to consider adding ALTER DOMAIN TYPE type? >>(basically following the same rules as ALTER TABLE). >> >>I don't mind bringing down all the views before I do this, it would be >>just great if you could change domains from a centralized location. >>Oracle offers this feature... >> >> > >Interesting --- you would have to rebuild every table that uses the >domain, and map from-to for all stored values of the domain. > >TODO item? > > Hm, how about ALTER TYPE then? IMHO domains as well as types are a bit too basic to change later on. Regards, Andreas
Rod Taylor <pg@rbt.ca> writes:
>> With your potential changes, you would then be able to alter a domain
>> that is involved in RI constraints between 2 or more tables without
>> bringing down the constraints, yes? This would be great :)
> I had been hoping to get away without actually rechecking foreign key
> constraints,
I don't believe you can, since an ALTER TYPE operation isn't necessarily
a one-to-one transformation. Consider this example:
regression=# create table t1 (f1 numeric primary key);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t1_pkey" for table "t1"
CREATE TABLE
regression=# create table t2 (f2 numeric references t1);
CREATE TABLE
regression=# insert into t1 values(1.1);
INSERT 430598 1
regression=# insert into t1 values(2.1);
INSERT 430599 1
regression=# insert into t2 values(1.1);
INSERT 430600 1
regression=# insert into t2 values(2.1);
INSERT 430601 1
regression=# alter table t1 alter f1 type int8;
WARNING: foreign key constraint "$1" will require costly sequential scans
DETAIL: Key columns "f2" and "f1" are of different types: numeric and bigint.
ERROR: insert or update on table "t2" violates foreign key constraint "$1"
DETAIL: Key (f2)=(1.1) is not present in table "t1".
regression=#
If we were willing to abuse the ALTER TABLE syntax some more, it would
be possible to support changing the datatypes of f1 and f2
simultaneously, thereby allowing the above to work. The infrastructure
for hacking multiple tables in parallel is already there in CVS tip,
but it only gets exercised in cases involving inheritance.
regards, tom lane
PS: The error message is a bit out of whack, since it's not an "insert or
update", and certainly not one on t2. Not sure how hard that is to fix.
Tom Lane wrote: > If we were willing to abuse the ALTER TABLE syntax some more, it would > be possible to support changing the datatypes of f1 and f2 > simultaneously, thereby allowing the above to work. The infrastructure > for hacking multiple tables in parallel is already there in CVS tip, > but it only gets exercised in cases involving inheritance. Just a clarification: isn't ALTER DOMAIN the best place to do this? IMHO, this is one of those rare cases were extending PostgreSQL beyond the SQL spec is justified. Right now, as I understand it, the only way to do these types of things is to bring down the RI rules for a short time until the table manipulation is completed, which seems inelegant, not to mention the convenience factor. Plus, I see anything that encourages usage of domains as good thing, as domains themselves are very good things (and quite underutilized by the unwashed masses, I expect). Merlin
> If we were willing to abuse the ALTER TABLE syntax some more, it would > be possible to support changing the datatypes of f1 and f2 > simultaneously, thereby allowing the above to work. The infrastructure > for hacking multiple tables in parallel is already there in CVS tip, > but it only gets exercised in cases involving inheritance. I don't think we need to allow end users to do it via the grammar. Allowing an ALTER TYPE or ALTER DOMAIN to set up the multiple table transform is enough.
"Merlin Moncure" <merlin.moncure@rcsonline.com> writes:
> Tom Lane wrote:
>> If we were willing to abuse the ALTER TABLE syntax some more, it would
>> be possible to support changing the datatypes of f1 and f2
>> simultaneously, thereby allowing the above to work.
> Just a clarification: isn't ALTER DOMAIN the best place to do this?
It's one way to do it, but not necessarily the only way.
A person who's needing to change their column datatypes has likely not
had the foresight to use domains either, so I don't feel entirely secure
saying that ALTER DOMAIN would solve all problems in this line.
regards, tom lane
> Is it feasible or practical to consider adding ALTER DOMAIN TYPE type? > (basically following the same rules as ALTER TABLE). I was _just_ about to ask that! Chris
Added to TODO:
o Add ALTER DOMAIN TYPE
---------------------------------------------------------------------------
Rod Taylor wrote:
> > > Is it feasible or practical to consider adding ALTER DOMAIN TYPE type?
> > > (basically following the same rules as ALTER TABLE).
> >
> > Interesting --- you would have to rebuild every table that uses the
> > domain, and map from-to for all stored values of the domain.
> >
> > TODO item?
>
> Yes. This is something I was going to look at doing in the next release.
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
-- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610)
359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square,
Pennsylvania19073