pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedHelp converting constraint triggers
5+ messages / 3 participants
[nested] [flat]
* Help converting constraint triggers
@ 2005-08-03 15:27 Chris Hoover <revoohc@gmail.com>
0 siblings, 2 replies; 5+ messages in thread
From: Chris Hoover @ 2005-08-03 15:27 UTC (permalink / raw)
To: pgsql-admin
I need some help understanding how to convert constraint triggers to
normal foreign keys. I have several of them in my database that a
previous dba created. Since they don't show up in most PostgreSQL
tools, they are becoming a real pain to deal with.
Here is an example of on of the constraint triggers:
CREATE CONSTRAINT TRIGGER sys_user_group_id_fkey
AFTER INSERT OR UPDATE ON sys_user
FROM sys_group
NOT DEFERRABLE INITIALLY IMMEDIATE
FOR EACH ROW
EXECUTE PROCEDURE "RI_FKey_check_ins" ('sys_user_group_id_fkey',
'sys_user', 'sys_group', 'UNSPECIFIED', 'group_id', 'group_id');
I believe this is the same as the foriegn key definition,
alter table sys_user
add constraint sys_user_group_id_fkey foreign key(group_id)
references sys_group(group_id)
not deferrrable;
Is this correct? If not, how would be proper foreign key be defined?
Thanks,
Chris
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Help converting constraint triggers
@ 2005-08-03 15:53 Tom Lane <tgl@sss.pgh.pa.us>
parent: Chris Hoover <revoohc@gmail.com>
1 sibling, 1 reply; 5+ messages in thread
From: Tom Lane @ 2005-08-03 15:53 UTC (permalink / raw)
To: Chris Hoover <revoohc@gmail.com>; +Cc: pgsql-admin
Chris Hoover <revoohc@gmail.com> writes:
> I need some help understanding how to convert constraint triggers to
> normal foreign keys.
You might try the contrib/adddepend script ...
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Help converting constraint triggers
@ 2005-08-03 16:24 Chris Hoover <revoohc@gmail.com>
parent: Chris Hoover <revoohc@gmail.com>
1 sibling, 1 reply; 5+ messages in thread
From: Chris Hoover @ 2005-08-03 16:24 UTC (permalink / raw)
To: Hemapriya <priyam_1121@yahoo.com>; pgsql-admin
What version of pgadmin are you using? I looked in pgadmin3 and it
does not show the constraint triggers.
On 8/3/05, Hemapriya <priyam_1121@yahoo.com> wrote:
>
> pgadmin will list all the trigger functions defined
> in each schema. it is a freeware.
>
> --- Chris Hoover <revoohc@gmail.com> wrote:
>
> > I need some help understanding how to convert
> > constraint triggers to
> > normal foreign keys. I have several of them in my
> > database that a
> > previous dba created. Since they don't show up in
> > most PostgreSQL
> > tools, they are becoming a real pain to deal with.
> >
> > Here is an example of on of the constraint triggers:
> > CREATE CONSTRAINT TRIGGER sys_user_group_id_fkey
> > AFTER INSERT OR UPDATE ON sys_user
> > FROM sys_group
> > NOT DEFERRABLE INITIALLY IMMEDIATE
> > FOR EACH ROW
> > EXECUTE PROCEDURE "RI_FKey_check_ins"
> > ('sys_user_group_id_fkey',
> > 'sys_user', 'sys_group', 'UNSPECIFIED', 'group_id',
> > 'group_id');
> >
> >
> > I believe this is the same as the foriegn key
> > definition,
> >
> > alter table sys_user
> > add constraint sys_user_group_id_fkey foreign
> > key(group_id)
> > references sys_group(group_id)
> > not deferrrable;
> >
> > Is this correct? If not, how would be proper
> > foreign key be defined?
> >
> > Thanks,
> >
> > Chris
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 5: don't forget to increase your free space map
> > settings
> >
>
>
> __________________________________________________
> Do You Yahoo!?
> Tired of spam? Yahoo! Mail has the best spam protection around
> http://mail.yahoo.com
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Help converting constraint triggers
@ 2005-08-03 16:26 Chris Hoover <revoohc@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 5+ messages in thread
From: Chris Hoover @ 2005-08-03 16:26 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
I tried running this an a test db, and it got some, but not all of the
constraint triggers. Is there anything else to try?
Also, was my interpretation of the constraint trigger correct? While
there are many constraint triggers defined, I don't think the volume
is large enough to make it prohibitive for me to just convert them by
hand.
Thanks,
Chris
On 8/3/05, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Chris Hoover <revoohc@gmail.com> writes:
> > I need some help understanding how to convert constraint triggers to
> > normal foreign keys.
>
> You might try the contrib/adddepend script ...
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Help converting constraint triggers
@ 2005-08-03 17:23 Hemapriya <priyam_1121@yahoo.com>
parent: Chris Hoover <revoohc@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Hemapriya @ 2005-08-03 17:23 UTC (permalink / raw)
To: Chris Hoover <revoohc@gmail.com>; pgsql-admin
pgAdmin III v1.2.0..
--- Chris Hoover <revoohc@gmail.com> wrote:
> What version of pgadmin are you using? I looked in
> pgadmin3 and it
> does not show the constraint triggers.
>
> On 8/3/05, Hemapriya <priyam_1121@yahoo.com> wrote:
> >
> > pgadmin will list all the trigger functions
> defined
> > in each schema. it is a freeware.
> >
> > --- Chris Hoover <revoohc@gmail.com> wrote:
> >
> > > I need some help understanding how to convert
> > > constraint triggers to
> > > normal foreign keys. I have several of them in
> my
> > > database that a
> > > previous dba created. Since they don't show up
> in
> > > most PostgreSQL
> > > tools, they are becoming a real pain to deal
> with.
> > >
> > > Here is an example of on of the constraint
> triggers:
> > > CREATE CONSTRAINT TRIGGER sys_user_group_id_fkey
> > > AFTER INSERT OR UPDATE ON sys_user
> > > FROM sys_group
> > > NOT DEFERRABLE INITIALLY IMMEDIATE
> > > FOR EACH ROW
> > > EXECUTE PROCEDURE "RI_FKey_check_ins"
> > > ('sys_user_group_id_fkey',
> > > 'sys_user', 'sys_group', 'UNSPECIFIED',
> 'group_id',
> > > 'group_id');
> > >
> > >
> > > I believe this is the same as the foriegn key
> > > definition,
> > >
> > > alter table sys_user
> > > add constraint sys_user_group_id_fkey foreign
> > > key(group_id)
> > > references sys_group(group_id)
> > > not deferrrable;
> > >
> > > Is this correct? If not, how would be proper
> > > foreign key be defined?
> > >
> > > Thanks,
> > >
> > > Chris
> > >
> > > ---------------------------(end of
> > > broadcast)---------------------------
> > > TIP 5: don't forget to increase your free space
> map
> > > settings
> > >
> >
> >
> > __________________________________________________
> > Do You Yahoo!?
> > Tired of spam? Yahoo! Mail has the best spam
> protection around
> > http://mail.yahoo.com
> >
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: explain analyze is your friend
>
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2005-08-03 17:23 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-08-03 15:27 Help converting constraint triggers Chris Hoover <revoohc@gmail.com>
2005-08-03 15:53 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-08-03 16:26 ` Chris Hoover <revoohc@gmail.com>
2005-08-03 16:24 ` Chris Hoover <revoohc@gmail.com>
2005-08-03 17:23 ` Hemapriya <priyam_1121@yahoo.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox