Re: problems removing foreign-key triggers, postgresql 7.1.1

On Sun, 14 Jul 2002, Andre Schlieper wrote:

> Hi there,
> I've some problems droping tiggers (foreign key-constraint) on
> postgresql 7.1.1,
> example-tables are like this :
>
> CREATE TABLE pers (
>     pid         INT         NOT NULL PRIMARY KEY,
>     pname     TEXT     NOT NULL);
>
> CREATE TABLE tasks (
>     taskid        SERIAL NOT NULL PRIMARY KEY,
>     pid             INT NOT NULL CONSTRAINT tasks__ref_p REFERENCES
> pers,
>     task         TEXT NOT NULL);
>
> This creates two simple tables, linked with one foreign-key
> "tasks_ref_p".
> This constraint is done with three triggers (2 on the pk table, 1 on the
> fk table), wich are named:
> RI_ConstraintTrigger_594265
> RI_ConstraintTrigger_594267
> RI_ConstraintTrigger_594263
>
>
> I tried to delete one of these triggers with
> drop trigger "RI_ConstraintTrigger_594265" on pers;
> that fails with
> ERROR:  DropTrigger: there is no trigger ri_constrainttrigger_594265 on
> relation pers

The first one should be on tasks, so I think you may have been referencing
the wrong table.

> I also tried to delete these triggers with
> DELETE FROM pg_trigger WHERE tgname='RI_ConstraintTrigger_594265';
> wich worked, all trigger are away from pg_trigger.

Doing this is dangerous as you've noticed...
The second error you got was because it couldn't find the appropriate
number of triggers on the relation.

> Q1 : How can I get DROP TRIGGER to work, why does it not work here ?
> Q2:  what's the right strategy to delete directly on pg_trigger ? was it
> only luck that it is working that way; deleting on pg_triggers and
> fixing on pg_class(reltriggers) ?

Doing the delete/update will work (assuming that you correctly set the
number of triggers as opposed to setting it to 0 unconditionally - I
believe pg_dump in data only mode outputs a statement that will do that
for you).  If there are triggers and you set reltriggers to 0, it will
work until you add a trigger at which point I think it starts failing
again.