Re: [GENERAL] Drop Column with Foreign Key Problem

[Thomas Chille]

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


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])

[GENERAL] Drop Column with Foreign Key Problem

[Thomas Chille (spoon)]

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!


---(end of broadcast)---
TIP 8: explain analyze is your friend