Обсуждение: problems removing foreign-key triggers, postgresql 7.1.1

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

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.

But now if i insert one in tasks with
INSERT INTO tasks (pid, task) VALUES (3, 'Install Linux');
still fails with :
ERROR:  tasks__ref_p referential integrity violation - key referenced
from tasks not found in pers
so the foreign key is not away ?!

After restart of psql there's another message if i insert with
INSERT INTO tasks (pid, task) VALUES (3, 'Install Linux');
ERROR:  RelationBuildTriggers: 1 record(s) not found for rel tasks

Then I tried to fix pg_class(reltriggers) with
 update pg_class set reltriggers=0 where relname='tasks';
 update pg_class set reltriggers=0 where relname='pers';
And did
INSERT INTO tasks (pid, task) VALUES (3, 'Install Linux');
wich finaly worked, but I don't know why.


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) ?

Thanx! Andre

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.