Re: Patch for ALTER TABLE / TYPE

NAKANO Yoshihisa <nakano(dot)yosihisa(at)jp(dot)fujitsu(dot)com> writes:
> Please find the patch attached. This is for the bug which is posted to
> hackers before.
> http://archives.postgresql.org/pgsql-hackers/2005-06/msg01442.php

> We can see a problem by this bug in following way.

> CREATE TABLE pktable (a int primary key);
> CREATE TABLE fktable (b int references pktable);
> ALTER TABLE pktable ALTER COLUMN a TYPE bigint; -- succeed
> REINDEX TABLE pg_depend;
> ALTER TABLE pktable ALTER COLUMN a TYPE int; -- fail
> NOTICE: constraint fktable_b_fkey on table fktable depends on index
> pktable_pkey
> ERROR: cannot drop constraint pktable_pkey on table pktable because
> other objects depend on it
> HINT: Use DROP ... CASCADE to drop the dependent objects too.

Ah, thanks for providing the simple test case. Much nicer than
Neil's way...

I don't much like the patch though :-(. It seems like a brute force
solution, and it's lacking error checking.

After looking at the test case a bit, I have an alternate approach:
constraints on the column will have DEPENDENCY_AUTO type, while
constraints using the column will have DEPENDENCY_NORMAL type.
Therefore, if we drop the NORMAL ones before the AUTO ones, that
should be enough to fix it. This doesn't require much extra code,
or any extra catalog searches, since the pg_depend record is already
available in ATExecAlterColumnType where we need to decide whether
to stick the item on the front or back of the list.

regards, tom lane