Thread: How do I drop a Complicated Constraint Trigger After Delete Execute Procedure?
How do I drop a Complicated Constraint Trigger After Delete Execute Procedure?
From
"Wang, Mary Y"
Date:
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?
From
Adrian Klaver
Date:
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?
From
"Wang, Mary Y"
Date:
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
Re: How do I drop a Complicated Constraint Trigger After Delete Execute Procedure?
From
Tom Lane
Date:
"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?
From
"Wang, Mary Y"
Date:
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?
From
Adrian Klaver
Date:
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
Re: How do I drop a Complicated Constraint Trigger After Delete Execute Procedure?
From
Tom Lane
Date:
"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?
From
"Wang, Mary Y"
Date:
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?
From
"Wang, Mary Y"
Date:
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
Re: How do I drop a Complicated Constraint Trigger After Delete Execute Procedure?
From
Tom Lane
Date:
"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