Thread: Drop Column with Foreign Key Problem

Hello!

I have a Problem.

A table with a ForeignKeyRef exits. The Name of the field with this FK-Constraint is 'id_employee'
because it is referencing the field 'id_employee' in 'public.sd_employee'. I drop this column of the
table with cascade in a script:

ALTER TABLE public.sd_messaging_participant DROP COLUMN id_employee CASCADE;

In one script later i try to make an update on the referenced Table:

UPDATE sd_employee SET leave = 1.5;

But it doesent works. I get always this Error:

ERROR:  constraint participant_employee: table sd_messaging_participant does not have an attribute
id_employee

The constraint 'participant_employee' should be droped too, due the use of CASCADE, but it seems
that he is alive.

Also explizit dropping the constraint 'participant_employee' before dropping the field will not
solve the problem:

ALTER TABLE public.sd_messaging_participant DROP CONSTRAINT participant_employee CASCADE;

If i try to drop the constraint after dropping the field, postgres means the constraint is not
existing anymore. But if i try to do the update it produces still this error.

If i dump the DB i can not found the constraint 'participant_employee' anymore but 3 triggers
belonging to this constraint are still in the DB:

...
CREATE CONSTRAINT TRIGGER participant_employee
    AFTER INSERT OR UPDATE ON sd_messaging_participant
    FROM sd_employee
    NOT DEFERRABLE INITIALLY IMMEDIATE
    FOR EACH ROW
    EXECUTE PROCEDURE "RI_FKey_check_ins" ('participant_employee', 'sd_messaging_participant',
'sd_employee', 'UNSPECIFIED', 'id_employee', 'id_employee');
...

That is for Insert and there are one for Update and one for Deleting too. I have absolutly no idea
how can this happens and i think i could solve this problem by dropping these 3 Triggers. But i dont
know how is the syntax to drop such triggers?

And know anybody out there how it could happen?

Thanks for your help,
Thomas!

"Thomas Chille (spoon)" <t.chille@spoon.de> writes:
> If i dump the DB i can not found the constraint 'participant_employee' anymore but 3 triggers
> belonging to this constraint are still in the DB:

> CREATE CONSTRAINT TRIGGER participant_employee

It looks to me like you created this database by loading a dump from an
older (pre-7.3) Postgres?  If so, there are not any dependency links
from those triggers to the columns involved, and so dropping the columns
doesn't cause the triggers to go away.  You'll need to drop the triggers
by hand.

There is a contrib script (contrib/adddepend) that tries to add the
missing dependencies in an upgraded database.  I've never used it and
am not sure how reliable it is, but it probably beats trying to identify
the trouble spots by hand.

See the 7.3 release notes for more about this issue.

            regards, tom lane

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