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.kla...@gmail.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How do I drop a Complicated Constraint Trigger After Delete Execute Procedure?
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.kla...@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.kla...@gmail.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How do I drop a Complicated Constraint Trigger After Delete Execute Procedure?
Wang, Mary Y mary.y.w...@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 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
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How do I drop a Complicated Constraint Trigger After Delete Execute Procedure?
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
-Original Message-
From: Tom Lane [mailto:t...@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.w...@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 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
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How do I drop a Complicated Constraint Trigger After Delete Execute Procedure?
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.kla...@gmail.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How do I drop a Complicated Constraint Trigger After Delete Execute Procedure?
Wang, Mary Y mary.y.w...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
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:t...@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.w...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How do I drop a Complicated Constraint Trigger After Delete Execute Procedure?
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:t...@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.w...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How do I drop a Complicated Constraint Trigger After Delete Execute Procedure?
Wang, Mary Y mary.y.w...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
