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 по дате отправления: