Обсуждение: How do I drop a Complicated Constraint Trigger After Delete Execute Procedure?
How do I drop a Complicated Constraint Trigger After Delete Execute Procedure?
От
"Wang, Mary Y"
Дата:
Hi, Hmm. Things are still getting interesting around here. Here is my complicated problem. I tried to delete a user from my users table, but it said "ERROR: bug_assigned_to_fk referential integrity violation - key in users still referenced from bug" Ok. Then I saw this statement in the .sql file. "CREATE CONSTRAINT TRIGGER "bug_assigned_to_fk" AFTER DELETE ON "users" FROM "bug" NOT DEFERRABLE INITIALLY IMMEDIATE FOREACH ROW EXECUTE PROCEDURE "RI_FKey_noaction_del" ('bug_assigned_to_fk', 'bug', 'users', 'FULL', 'assigned_to', 'user_id');" Then I used this command to delete the constraint trigger: " drop trigger bug_assign_to_fk on bug;" I received error: "ERROR: DropTrigger: there is no trigger bug_assign_to_fk on relation bug" I also tried "drop trigger bug_assign_to_fk on user;" I received this error: "ERROR: DropTrigger: there is no trigger bug_assign_to_fk on relation users" Here is the bug table. Please NOTE there is no constraint listed in the bug table. \d bug Table "bug" Attribute | Type | Modifier ---------------+---------+---------------------------------------------- bug_id | integer | not null default nextval('bug_pk_seq'::text) group_id | integer | not null default '0' status_id | integer | not null default '0' priority | integer | not null default '0' category_id | integer | not null default '0' submitted_by | integer | not null default '0' assigned_to | integer | not null default '0' date | integer | not null default '0' summary | text | details | text | close_date | integer | bug_group_id | integer | not null default '0' resolution_id | integer | not null default '0' Indices: bug_group_id, bug_groupid_assignedto_statusid, bug_groupid_statusid, bug_pkey Any ideas on how can I drop the bug_assigned_to_fk trigger so that I can remove an user? Any help is appreciated. Mary
Re: How do I drop a Complicated Constraint Trigger After Delete Execute Procedure?
От
Adrian Klaver
Дата:
On Wednesday 10 February 2010 4:56:21 pm Wang, Mary Y wrote: > Hi, > > Hmm. Things are still getting interesting around here. > > Here is my complicated problem. I tried to delete a user from my users > table, but it said "ERROR: bug_assigned_to_fk referential integrity > violation - key in users still referenced from bug" Ok. Why not try deleting the information in bug that had assigned_to=user_id? Or do you want to keep that info in bug? Is this still in version 7.1? > Then I saw this statement in the .sql file. > "CREATE CONSTRAINT TRIGGER "bug_assigned_to_fk" AFTER DELETE ON "users" > FROM "bug" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE > PROCEDURE "RI_FKey_noaction_del" ('bug_assigned_to_fk', 'bug', 'users', > 'FULL', 'assigned_to', 'user_id');" Then I used this command to delete the > constraint trigger: > " drop trigger bug_assign_to_fk on bug;" > I received error: > "ERROR: DropTrigger: there is no trigger bug_assign_to_fk on relation bug" > I also tried > "drop trigger bug_assign_to_fk on user;" > I received this error: > "ERROR: DropTrigger: there is no trigger bug_assign_to_fk on relation > users" > > Here is the bug table. Please NOTE there is no constraint listed in the > bug table. > > \d bug How about \d+ bug ? > Mary -- Adrian Klaver adrian.klaver@gmail.com
Re: How do I drop a Complicated Constraint Trigger After Delete Execute Procedure?
От
"Wang, Mary Y"
Дата:
Yes. I do want to keep that information in the bug : assigned_to=user_id Yes. I'm embrassed to say it's still in 7.1.3. I know, I know that I need to upgrade, but I do need to fix a problem now. \d+ bug returns Table "bug" Attribute | Type | Modifier | Descript ion ---------------+---------+----------------------------------------------+--------- ---- bug_id | integer | not null default nextval('bug_pk_seq'::text) | group_id | integer | not null default '0' | status_id | integer | not null default '0' | priority | integer | not null default '0' | category_id | integer | not null default '0' | submitted_by | integer | not null default '0' | assigned_to | integer | not null default '0' | date | integer | not null default '0' | summary | text | | details | text | | close_date | integer | | bug_group_id | integer | not null default '0' | resolution_id | integer | not null default '0' | Indices: bug_group_id, bug_groupid_assignedto_statusid, bug_groupid_statusid, bug_pkey -----Original Message----- From: Adrian Klaver [mailto:adrian.klaver@gmail.com] Sent: Wednesday, February 10, 2010 5:10 PM To: pgsql-general@postgresql.org Cc: Wang, Mary Y Subject: Re: [GENERAL] How do I drop a Complicated Constraint Trigger After Delete Execute Procedure? On Wednesday 10 February 2010 4:56:21 pm Wang, Mary Y wrote: > Hi, > > Hmm. Things are still getting interesting around here. > > Here is my complicated problem. I tried to delete a user from my > users table, but it said "ERROR: bug_assigned_to_fk referential > integrity violation - key in users still referenced from bug" Ok. Why not try deleting the information in bug that had assigned_to=user_id? Or do you want to keep that info in bug? Is this still in version 7.1? > Then I saw this statement in the .sql file. > "CREATE CONSTRAINT TRIGGER "bug_assigned_to_fk" AFTER DELETE ON "users" > FROM "bug" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE > PROCEDURE "RI_FKey_noaction_del" ('bug_assigned_to_fk', 'bug', > 'users', 'FULL', 'assigned_to', 'user_id');" Then I used this command > to delete the constraint trigger: > " drop trigger bug_assign_to_fk on bug;" > I received error: > "ERROR: DropTrigger: there is no trigger bug_assign_to_fk on relation bug" > I also tried > "drop trigger bug_assign_to_fk on user;" > I received this error: > "ERROR: DropTrigger: there is no trigger bug_assign_to_fk on relation > users" > > Here is the bug table. Please NOTE there is no constraint listed in > the bug table. > > \d bug How about \d+ bug ? > Mary -- Adrian Klaver adrian.klaver@gmail.com
"Wang, Mary Y" <mary.y.wang@boeing.com> writes: > Here is my complicated problem. I tried to delete a user from my users table, but it said > "ERROR: bug_assigned_to_fk referential integrity violation - key in users still referenced from bug" > Ok. > Then I saw this statement in the .sql file. > "CREATE CONSTRAINT TRIGGER "bug_assigned_to_fk" AFTER DELETE ON "users" FROM "bug" NOT DEFERRABLE INITIALLY IMMEDIATEFOR EACH ROW EXECUTE PROCEDURE "RI_FKey_noaction_del" ('bug_assigned_to_fk', 'bug', 'users', 'FULL', 'assigned_to','user_id');" > Then I used this command to delete the constraint trigger: > " drop trigger bug_assign_to_fk on bug;" > I received error: > "ERROR: DropTrigger: there is no trigger bug_assign_to_fk on relation bug" It looks to me like you misspelled the trigger name --- what you quote there is bug_assigned_to_fk not bug_assign_to_fk. Also, the trigger is attached to table users not table bug. > Here is the bug table. Please NOTE there is no constraint listed in the bug table. I think in 7.1 that trigger would be shown as a trigger if you did \d users, but it's not going to be mentioned by \d bug. regards, tom lane
Re: How do I drop a Complicated Constraint Trigger After Delete Execute Procedure?
От
"Wang, Mary Y"
Дата:
Ok. I typed the correct name this time, and got the same error. "drop trigger bug_assigned_to_fk on users; ERROR: DropTrigger: there is no trigger bug_assigned_to_fk on relation users " "drop trigger bug_assigned_to_fk on bug; ERROR: DropTrigger: there is no trigger bug_assigned_to_fk on relation bug" Here is my user table: \d users Table "users" Attribute | Type | Modifier ----------------------+-----------------------+----------------------------------- ------------- user_id | integer | not null default nextval('users_pk _seq'::text) user_name | text | not null default '' email | text | not null default '' user_pw | character varying(32) | not null default '' realname | character varying(32) | not null default '' status | character(1) | not null default 'A' shell | character varying(20) | not null default '/bin/bash' unix_pw | character varying(40) | not null default '' unix_status | character(1) | not null default 'N' unix_uid | integer | not null default '0' unix_box | character varying(10) | not null default 'shell1' add_date | integer | not null default '0' confirm_hash | character varying(32) | mail_siteupdates | integer | not null default '0' mail_va | integer | not null default '0' authorized_keys | text | email_new | text | people_view_skills | integer | not null default '0' people_resume | text | not null default '' timezone | character varying(64) | default 'GMT' language | integer | not null default '1' third_party | integer | not null default 1 personal_status | character(32) | bemsid | integer | sensitive_info | character(64) | reason_access | text | organization | text | brass_first_time | character(1) | default '0' mail_sitenews_update | integer | default '0' doclinks_sort_order | character(1) | default 'A' Indices: idx_users_username, user_user, users_user_pw Someone mentioned about using 'alter table'. Would like would work? But I'm not sure how to do it because \d doesn't showthe constraint. Any ideas? Mary -----Original Message----- From: Tom Lane [mailto:tgl@sss.pgh.pa.us] Sent: Wednesday, February 10, 2010 6:30 PM To: Wang, Mary Y Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] How do I drop a Complicated Constraint Trigger After Delete Execute Procedure? "Wang, Mary Y" <mary.y.wang@boeing.com> writes: > Here is my complicated problem. I tried to delete a user from my > users table, but it said > "ERROR: bug_assigned_to_fk referential integrity violation - key in users still referenced from bug" > Ok. > Then I saw this statement in the .sql file. > "CREATE CONSTRAINT TRIGGER "bug_assigned_to_fk" AFTER DELETE ON "users" FROM "bug" NOT DEFERRABLE INITIALLY IMMEDIATEFOR EACH ROW EXECUTE PROCEDURE "RI_FKey_noaction_del" ('bug_assigned_to_fk', 'bug', 'users', 'FULL', 'assigned_to','user_id');" > Then I used this command to delete the constraint trigger: > " drop trigger bug_assign_to_fk on bug;" > I received error: > "ERROR: DropTrigger: there is no trigger bug_assign_to_fk on relation bug" It looks to me like you misspelled the trigger name --- what you quote there is bug_assigned_to_fk not bug_assign_to_fk. Also, the trigger is attached to table users not table bug. > Here is the bug table. Please NOTE there is no constraint listed in the bug table. I think in 7.1 that trigger would be shown as a trigger if you did \d users, but it's not going to be mentioned by \d bug. regards, tom lane
Re: How do I drop a Complicated Constraint Trigger After Delete Execute Procedure?
От
Adrian Klaver
Дата:
On Wednesday 10 February 2010 7:07:08 pm Wang, Mary Y wrote: > Ok. I typed the correct name this time, and got the same error. > "drop trigger bug_assigned_to_fk on users; > ERROR: DropTrigger: there is no trigger bug_assigned_to_fk on relation > users " "drop trigger bug_assigned_to_fk on bug; > ERROR: DropTrigger: there is no trigger bug_assigned_to_fk on relation > bug" Here is my user table: > \d users > Table "users" > Attribute | Type | Modifier > > ----------------------+-----------------------+---------------------------- >------- ------------- > user_id | integer | not null default > nextval('users_pk _seq'::text) > user_name | text | not null default '' > email | text | not null default '' > user_pw | character varying(32) | not null default '' > realname | character varying(32) | not null default '' > status | character(1) | not null default 'A' > shell | character varying(20) | not null default > '/bin/bash' unix_pw | character varying(40) | not null default > '' unix_status | character(1) | not null default 'N' > unix_uid | integer | not null default '0' > unix_box | character varying(10) | not null default 'shell1' > add_date | integer | not null default '0' > confirm_hash | character varying(32) | > mail_siteupdates | integer | not null default '0' > mail_va | integer | not null default '0' > authorized_keys | text | > email_new | text | > people_view_skills | integer | not null default '0' > people_resume | text | not null default '' > timezone | character varying(64) | default 'GMT' > language | integer | not null default '1' > third_party | integer | not null default 1 > personal_status | character(32) | > bemsid | integer | > sensitive_info | character(64) | > reason_access | text | > organization | text | > brass_first_time | character(1) | default '0' > mail_sitenews_update | integer | default '0' > doclinks_sort_order | character(1) | default 'A' > Indices: idx_users_username, > user_user, > users_user_pw > > Someone mentioned about using 'alter table'. Would like would work? But > I'm not sure how to do it because \d doesn't show the constraint. > > Any ideas? > Mary > For what it is worth the manuals for this version are here: http://www.postgresql.org/docs/manuals/archive.html I do not see anything in the manual that shows ALTER TABLE being useful in this situation. I am afraid 7.1 is before my time and at this point I cannot think of a solution other than set the assigned_to value in bugs to NULL where assigned_to=user_id. Sort of negates the point of a relationship between bugs and users. Another option would be to create a 'dummy' user to whom 'unassigned ' bugs would be referenced. -- Adrian Klaver adrian.klaver@gmail.com
"Wang, Mary Y" <mary.y.wang@boeing.com> writes: > Ok. I typed the correct name this time, and got the same error. > "drop trigger bug_assigned_to_fk on users; > ERROR: DropTrigger: there is no trigger bug_assigned_to_fk on relation users " > "drop trigger bug_assigned_to_fk on bug; > ERROR: DropTrigger: there is no trigger bug_assigned_to_fk on relation bug" Huh. Do you get anything from select * from pg_trigger where tgname = 'bug_assigned_to_fk'; or select * from pg_trigger where tgconstrname = 'bug_assigned_to_fk'; ? If I recall the 7.1 code at all, it has to be printing one or the other of those fields as the name shown in the FK error message ... regards, tom lane
Re: How do I drop a Complicated Constraint Trigger After Delete Execute Procedure?
От
"Wang, Mary Y"
Дата:
Wow!! Good memory. I didn't get anything with the 'select * from pg_trigger where tgname = 'bug_assigned_to_fk';' but I got something here : select * from pg_trigger where tgconstrname = 'bug_assigned_to_fk'; tgrelid | tgname | tgfoid | tgtype | tgenabled | tgisconstr aint | tgconstrname | tgconstrrelid | tgdeferrable | tginitdeferred | tgnarg s | tgattr | tgargs ---------+------------------------------+--------+--------+-----------+----------- -----+--------------------+---------------+--------------+----------------+------- --+--------+---------------------------------------------------------------------- ---- 7335118 | RI_ConstraintTrigger_9217018 | 1655 | 17 | t | t | bug_assigned_to_fk| 7329978 | f | f | 6 | | bug_assigned_to_fk\000bug\000users\000FULL\000assigned_to\000user_id\ 000 7335118 | RI_ConstraintTrigger_9217016 | 1654 | 9 | t | t | bug_assigned_to_fk| 7329978 | f | f | 6 | | bug_assigned_to_fk\000bug\000users\000FULL\000assigned_to\000user_id\ 000 7329978 | RI_ConstraintTrigger_9217014 | 1644 | 21 | t | t | bug_assigned_to_fk| 7335118 | f | f | 6 | | bug_assigned_to_fk\000bug\000users\000FULL\000assigned_to\000user_id\ 000 (3 rows) What do I do with them? ------------------------------------------------ Mary Y Wang -----Original Message----- From: Tom Lane [mailto:tgl@sss.pgh.pa.us] Sent: Wednesday, February 10, 2010 8:02 PM To: Wang, Mary Y Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] How do I drop a Complicated Constraint Trigger After Delete Execute Procedure? "Wang, Mary Y" <mary.y.wang@boeing.com> writes: > Ok. I typed the correct name this time, and got the same error. > "drop trigger bug_assigned_to_fk on users; > ERROR: DropTrigger: there is no trigger bug_assigned_to_fk on relation users " > "drop trigger bug_assigned_to_fk on bug; > ERROR: DropTrigger: there is no trigger bug_assigned_to_fk on relation bug" Huh. Do you get anything from select * from pg_trigger where tgname = 'bug_assigned_to_fk'; or select * from pg_trigger where tgconstrname = 'bug_assigned_to_fk'; ? If I recall the 7.1 code at all, it has to beprinting one or the other of those fields as the name shown in the FK error message ... regards, tom lane
Re: How do I drop a Complicated Constraint Trigger After Delete Execute Procedure?
От
"Wang, Mary Y"
Дата:
I guess I could just use "DELETE FROM pg_trigger WHERE tgname = "RI_ConstraintTrigger_9217018" I just wanted to make sure that I don't mess up the system table. Mary -----Original Message----- From: Wang, Mary Y Sent: Wednesday, February 10, 2010 8:10 PM To: 'Tom Lane' Cc: pgsql-general@postgresql.org Subject: RE: [GENERAL] How do I drop a Complicated Constraint Trigger After Delete Execute Procedure? Wow!! Good memory. I didn't get anything with the 'select * from pg_trigger where tgname = 'bug_assigned_to_fk';' but I got something here : select * from pg_trigger where tgconstrname = 'bug_assigned_to_fk'; tgrelid | tgname | tgfoid | tgtype | tgenabled | tgisconstr aint | tgconstrname | tgconstrrelid | tgdeferrable | tginitdeferred | tgnarg s | tgattr | tgargs ---------+------------------------------+--------+--------+-----------+----------- -----+--------------------+---------------+--------------+----------------+------- --+--------+---------------------------------------------------------------------- ---- 7335118 | RI_ConstraintTrigger_9217018 | 1655 | 17 | t | t | bug_assigned_to_fk| 7329978 | f | f | 6 | | bug_assigned_to_fk\000bug\000users\000FULL\000assigned_to\000user_id\ 000 7335118 | RI_ConstraintTrigger_9217016 | 1654 | 9 | t | t | bug_assigned_to_fk| 7329978 | f | f | 6 | | bug_assigned_to_fk\000bug\000users\000FULL\000assigned_to\000user_id\ 000 7329978 | RI_ConstraintTrigger_9217014 | 1644 | 21 | t | t | bug_assigned_to_fk| 7335118 | f | f | 6 | | bug_assigned_to_fk\000bug\000users\000FULL\000assigned_to\000user_id\ 000 (3 rows) What do I do with them? ------------------------------------------------ Mary Y Wang -----Original Message----- From: Tom Lane [mailto:tgl@sss.pgh.pa.us] Sent: Wednesday, February 10, 2010 8:02 PM To: Wang, Mary Y Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] How do I drop a Complicated Constraint Trigger After Delete Execute Procedure? "Wang, Mary Y" <mary.y.wang@boeing.com> writes: > Ok. I typed the correct name this time, and got the same error. > "drop trigger bug_assigned_to_fk on users; > ERROR: DropTrigger: there is no trigger bug_assigned_to_fk on relation users " > "drop trigger bug_assigned_to_fk on bug; > ERROR: DropTrigger: there is no trigger bug_assigned_to_fk on relation bug" Huh. Do you get anything from select * from pg_trigger where tgname = 'bug_assigned_to_fk'; or select * from pg_trigger where tgconstrname = 'bug_assigned_to_fk'; ? If I recall the 7.1 code at all, it has to beprinting one or the other of those fields as the name shown in the FK error message ... regards, tom lane
"Wang, Mary Y" <mary.y.wang@boeing.com> writes: > I got something here : > select * from pg_trigger where tgconstrname = 'bug_assigned_to_fk'; > tgrelid | tgname | tgfoid | tgtype | tgenabled | tgisconstr aint | tgconstrname | tgconstrrelid | tgdeferrable | tginitdeferred | tgnarg s | tgattr | tgargs > ---------+------------------------------+--------+--------+-----------+----------- -----+--------------------+---------------+--------------+----------------+------- --+--------+---------------------------------------------------------------------- ---- > 7335118 | RI_ConstraintTrigger_9217018 | 1655 | 17 | t | t | bug_assigned_to_fk| 7329978 | f | f | 6 | | bug_assigned_to_fk\000bug\000users\000FULL\000assigned_to\000user_id\ 000 > 7335118 | RI_ConstraintTrigger_9217016 | 1654 | 9 | t | t | bug_assigned_to_fk| 7329978 | f | f | 6 | | bug_assigned_to_fk\000bug\000users\000FULL\000assigned_to\000user_id\ 000 > 7329978 | RI_ConstraintTrigger_9217014 | 1644 | 21 | t | t | bug_assigned_to_fk| 7335118 | f | f | 6 | | bug_assigned_to_fk\000bug\000users\000FULL\000assigned_to\000user_id\ 000 > (3 rows) Ah, right ... if memory were better, I'd have remembered that FK triggers used to be named like this. Try drop trigger "RI_ConstraintTrigger_9217018" on bugs; and so forth. I'm not sure which of the three triggers are on which of the two tables, but it won't take you long to find out. (And yes, you need those double quotes.) regards, tom lane