Hi,
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.
I changed the order of constraints list to delete foreign key
constraints first.
Any comments are welcome.
Regards,
Nakano
*** postgresql-8.1.2-org/src/backend/commands/tablecmds.c 2005-11-23
03:23:07.000000000 +0900
--- postgresql-8.1.2/src/backend/commands/tablecmds.c 2006-01-27
21:46:54.000000000 +0900
***************
*** 4970,4979 ****
Assert(foundObject.objectSubId == 0);
if
(!list_member_oid(tab->changedConstraintOids, foundObject.objectId))
{
! tab->changedConstraintOids =
lappend_oid(tab->changedConstraintOids,
!
foundObject.objectId);
! tab->changedConstraintDefs =
lappend(tab->changedConstraintDefs,
!
pg_get_constraintdef_string(foundObject.objectId));
}
break;
--- 4970,5022 ----
Assert(foundObject.objectSubId == 0);
if
(!list_member_oid(tab->changedConstraintOids, foundObject.objectId))
{
! Oid conOid = foundObject.objectId;
! Relation conRel;
! SysScanDesc conScan;
! ScanKeyData conKey[1];
! HeapTuple conTup;
! Form_pg_constraint conForm;
! char *defstring =
pg_get_constraintdef_string(foundObject.objectId);
!
! /* Look up the target constraint */
! conRel =
heap_open(ConstraintRelationId, AccessShareLock);
!
! ScanKeyInit(&conKey[0],
!
ObjectIdAttributeNumber,
!
BTEqualStrategyNumber, F_OIDEQ,
!
ObjectIdGetDatum(conOid));
!
! conScan = systable_beginscan(conRel,
ConstraintOidIndexId, true,
!
SnapshotNow, 1, conKey);
!
! /* This should not fail, since
pg_get_constraint_string found it. */
! conTup = systable_getnext(conScan);
!
! conForm = (Form_pg_constraint)
GETSTRUCT(conTup);
!
! /*
! * FOREIGN KEY constraints depend on
the indexes which are depend
! * on PRIMARY KEY constraints, so we
need to append FOREIGN KEY
! * constraints ahead of PRIMARY KEY
constraints. Otherwise deletion
! * of a PRIMARY KEY constraint with
RESTRICT in cleanup will fail.
! */
! if (conForm->contype ==
CONSTRAINT_FOREIGN)
! {
! tab->changedConstraintOids =
lcons_oid(conOid,
!
tab->changedConstraintOids);
! tab->changedConstraintDefs =
lcons(defstring,
!
tab->changedConstraintDefs);
! }
! else
! {
! tab->changedConstraintOids =
lappend_oid(tab->changedConstraintOids,
!
conOid);
! tab->changedConstraintDefs =
lappend(tab->changedConstraintDefs,
!
defstring);
! }
!
! systable_endscan(conScan);
! heap_close(conRel, AccessShareLock);
}
break;
***************
*** 5141,5149 ****
/*
* Now we can drop the existing constraints and indexes --- constraints
! * first, since some of them might depend on the indexes. It should be
! * okay to use DROP_RESTRICT here, since nothing else should be
depending
! * on these objects.
*/
foreach(l, tab->changedConstraintOids)
{
--- 5184,5193 ----
/*
* Now we can drop the existing constraints and indexes --- constraints
! * first, since some of them might depend on the indexes. (FOREIGN KEY
! * constraints depend on the indexes, but these are deleted first.)
! * It should be okay to use DROP_RESTRICT here, since nothing else
should
! * be depending on these objects.
*/
foreach(l, tab->changedConstraintOids)
{
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
