Re: alter table alter columns vs. domains

Rod Taylor <pg(at)rbt(dot)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.