
> 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

Thu, 6 May 2004 13:23:37 -0400
"Merlin Moncure" <merlin.moncure@rcsonline.com>
Re: 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.


Thu, 06 May 2004 13:43:58 -0400
Rod Taylor <pg@rbt.ca>
Re: alter table alter columns vs. domains
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.

Thu, 06 May 2004 14:39:29 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: 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.


Thu, 06 May 2004 15:00:12 -0400
Rod Taylor <pg@rbt.ca>
Re: alter table alter columns vs. domains