Thread: BUG #3424: Not able to drop trigger(RI_trigger*) on specific table.

The following bug has been logged online:

Bug reference:      3424
Logged by:          S R Madhu
Email address:      sraj_madhusr@yahoo.com
PostgreSQL version: 8.0.3
Operating system:   Linux RH6.2
Description:        Not able to drop trigger(RI_trigger*) on specific table.
Details:

We have created a table "student".
Foreign key and triggers are created in student. We are able to map triggers
and constraints from pg_class table.

We have used following mapping mechanism to get the trigger name.
pg_class.relfilenode -> pg_trigger.tgrelid.

We have used following mapping mechanism to get the constraint name.
pg_class.relfilenode -> pg_constraint.conrelid.

Problem : After ALTER TABLE command to this table, We are not able to map
triggers/constraints and tables.
relfilenode in pg_class is changed after ALTER TABLE.
but this is not reflected in pg_constraint/pg_triggers.

Please do the needful.

S R Madhu wrote:
> We have created a table "student".
> Foreign key and triggers are created in student. We are able to map triggers
> and constraints from pg_class table.
>
> We have used following mapping mechanism to get the trigger name.
> pg_class.relfilenode -> pg_trigger.tgrelid.
>
> We have used following mapping mechanism to get the constraint name.
> pg_class.relfilenode -> pg_constraint.conrelid.
>
> Problem : After ALTER TABLE command to this table, We are not able to map
> triggers/constraints and tables.
> relfilenode in pg_class is changed after ALTER TABLE.
> but this is not reflected in pg_constraint/pg_triggers.

pg_trigger.tgrelid and pg_constraint.conrelid reference the oid of the
relation, not relfilenode. Initially, relfilenode happens to have the
same value, but as you noticed, some commands like ALTER TABLE change it.

--
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com