problems removing foreign-key triggers, postgresql 7.1.1

From: Andre Schlieper <Andre(dot)Schlieper(at)gmx(dot)de>
To: pgsql-admin(at)postgresql(dot)org
Subject: problems removing foreign-key triggers, postgresql 7.1.1
Date: 2002-07-14 18:57:29
Message-ID: 3D31C999.99790392@gmx.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message ventas 2002-07-14 20:53:55
Previous Message Jefim Matskin 2002-07-14 09:50:57 Multilingual issues