Re: [HACKERS] alter table alter columns vs. domains
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 [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] 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 was _just_ about to ask that! Chris ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] alter table alter columns vs. domains
"Merlin Moncure" <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] alter table alter columns vs. domains
> 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. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] alter table alter columns vs. domains
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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] alter table alter columns vs. domains
Rod Taylor <[EMAIL PROTECTED]> 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. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] alter table alter columns vs. domains
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 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] alter table alter columns vs. domains
> 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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] alter table alter columns vs. domains
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. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] 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). > > 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
Re: [HACKERS] alter table alter columns vs. domains
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 [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[HACKERS] 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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
