problems removing foreign-key triggers, postgresql 7.1.1

Поиск
Список
Период
Сортировка
От Andre Schlieper
Тема problems removing foreign-key triggers, postgresql 7.1.1
Дата
Msg-id 3D31C999.99790392@gmx.de
обсуждение исходный текст
Ответы Re: problems removing foreign-key triggers, postgresql 7.1.1
Список 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






В списке pgsql-admin по дате отправления:

Предыдущее
От: Orion Orion
Дата:
Сообщение: Help
Следующее
От: "Martin Kuria"
Дата:
Сообщение: Corrupted Database