Re: Drop Column with Foreign Key Problem

Hi Tom!

Thanx again for your fast help. And its helps indeed. You pointed out the problem correct! I could
delete these triggers after scanning the system-tables by hand. Unfortunately this can happen in the
futrue again and thatswhy i tried applying the adddepend script on a fresh restored db before. But
it wont work(output is at the end of this msg). I think its conflicts with the differnt Foreign Key
Styles on the
same relation?

I wrote a small function wich can delete these zombie-triggers by constraint-name automatically and
have to be called after dropping an Oldstyle Foreign Key. Maybe it will help someone too:

CREATE OR REPLACE FUNCTION "public"."drop_fk_trigger" (name) RETURNS "pg_catalog"."void" AS'
DECLARE
_FK ALIAS FOR $1;
_ROW record;
BEGIN

FOR _ROW IN

SELECT tgname, relname
FROM pg_trigger JOIN pg_class ON tgrelid = pg_class.oid
WHERE tgconstrname = _FK

LOOP

EXECUTE ''DROP TRIGGER "'' || _ROW.tgname || ''" ON '' || _ROW.relname;

END LOOP;

RETURN;
END;
'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

Regards
Thomas!

-------------------------------------------------------------------
The Error Output of contrib/adddepend:

...
Upgrade the Unique Constraint style via:

DROP INDEX itm_prc_pk RESTRICT;
ALTER TABLE sd_item_price ADD CONSTRAINT itm_prc_pk UNIQUE (id_item_price);

DBD::Pg::st execute failed: ERROR: Cannot drop index tax_pk because other objects depend on it at
adddepend line 351.
...
...
The below commands will upgrade the foreign key style. Shall I execute them?

DROP TRIGGER "RI_ConstraintTrigger_76044427" ON sd_printer;
DROP TRIGGER "RI_ConstraintTrigger_76044426" ON sd_printer;
DROP TRIGGER "RI_ConstraintTrigger_76043914" ON sd_printer;
DROP TRIGGER "RI_ConstraintTrigger_76043913" ON sd_printer;
DROP TRIGGER "RI_ConstraintTrigger_76044425" ON sd_printer_of_production;
DROP TRIGGER "RI_ConstraintTrigger_76043912" ON sd_printer_of_production;

ALTER TABLE sd_printer_of_production ADD CONSTRAINT "fk_sd_print_fk_sd_pri_sd_print" FOREIGN KEY
(id_printer)
REFERENCES sd_printer(id_printer) MATCH SIMPLE ON UPDATE RESTRICT ON DELETE RESTRICT;

DBD::Pg::st execute failed: ERROR: Cannot drop trigger RI_ConstraintTrigger_76043914 on table
sd_printer because constraint fk_sd_print_fk_sd_pri_sd_print on table sd_printer_of_production
requires it at adddepend line 287.
...