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.
...