Обсуждение: identifying duplicate RI triggers

I started looking at the RI triggers in a database that started in 7.0.
Over time has been migrated to 7.1, 7.2, and 7.2.3 and I think all the
dumping and reloading has left me with some extra triggers.  When do see
this:

siteadmin=# select tgname, tgargs from pg_trigger where tgname in
('RI_ConstraintTrigger_1462942','RI_ConstraintTrigger_1462994');
            tgname            |
            tgargs

------------------------------+-----------------------------------------------------------------------------------------
 RI_ConstraintTrigger_1462942 |
 <unnamed>\000fe_user\000fe_user_type\000UNSPECIFIED\000user_type_id\000user_type_id\000
 RI_ConstraintTrigger_1462994 |
 <unnamed>\000fe_user\000fe_user_type\000UNSPECIFIED\000user_type_id\000user_type_id\000
(2 rows)

I am looking at identical triggers right?  On the referenced side I see
the same thing:

siteadmin=# select tgname, tgargs from pg_trigger where tgname in
('RI_ConstraintTrigger_1462944', 'RI_ConstraintTrigger_1462946',
'RI_Constrain
tTrigger_1462996','RI_ConstraintTrigger_1462998');
            tgname            |
            tgargs

------------------------------+-----------------------------------------------------------------------------------------
 RI_ConstraintTrigger_1462944 |
 <unnamed>\000fe_user\000fe_user_type\000UNSPECIFIED\000user_type_id\000user_type_id\000
 RI_ConstraintTrigger_1462946 |
 <unnamed>\000fe_user\000fe_user_type\000UNSPECIFIED\000user_type_id\000user_type_id\000
 RI_ConstraintTrigger_1462996 |
 <unnamed>\000fe_user\000fe_user_type\000UNSPECIFIED\000user_type_id\000user_type_id\000
 RI_ConstraintTrigger_1462998 |
 <unnamed>\000fe_user\000fe_user_type\000UNSPECIFIED\000user_type_id\000user_type_id\000
(4 rows)

Are these tied together in any way?  My guess is
RI_ConstraintTrigger_1462942, RI_ConstraintTrigger_1462944 are
RI_ConstraintTrigger_1462946 a set and should be deleted together?  Just
for curiosity sake... could I delete RI_ConstraintTrigger_1462942,
RI_ConstraintTrigger_1462996 and RI_ConstraintTrigger_1462998  and still
have a valid trigger?

I am curious if foreign keys are easier to manage if created with the
"create contraint" syntax rather than the create table foo(bar integer
not null references table2(col2)); syntax?

culley

ps. sorry if this is garbled-- haven't posted through this interface
before...

On Fri, 8 Nov 2002, culley harrelson wrote:

> I started looking at the RI triggers in a database that started in 7.0.
> Over time has been migrated to 7.1, 7.2, and 7.2.3 and I think all the
> dumping and reloading has left me with some extra triggers.  When do see
> this:
>
> siteadmin=# select tgname, tgargs from pg_trigger where tgname in
> ('RI_ConstraintTrigger_1462942','RI_ConstraintTrigger_1462994');
>             tgname            |
>             tgargs
>
------------------------------+-----------------------------------------------------------------------------------------
>  RI_ConstraintTrigger_1462942 |
>  <unnamed>\000fe_user\000fe_user_type\000UNSPECIFIED\000user_type_id\000user_type_id\000
>  RI_ConstraintTrigger_1462994 |
>  <unnamed>\000fe_user\000fe_user_type\000UNSPECIFIED\000user_type_id\000user_type_id\000
> (2 rows)

You can't just use those, you also need to look at least at tgfoid.
There are generally 3 triggers per constraint that IIRC all have
the same args but different function oids.

Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> You can't just use those, you also need to look at least at tgfoid.
> There are generally 3 triggers per constraint that IIRC all have
> the same args but different function oids.

I think looking at tgtype would also serve to distinguish the three
triggers associated with an RI constraint.

In 7.3, management of RI triggers is a lot easier because the system
associates the triggers with a pg_constraint entry (pg_constraint is
a new system catalog).  There is a contrib module contrib/adddepend
that tries to reverse-engineer pg_constraint entries from the bare
trigger definitions that you'd get from a dump of an older database.
I'm not sure how bulletproof adddepend is, but it might be worth
looking at to see how it decides to group triggers into a constraint.

I believe also there is trigger-grouping logic in Red Hat's RH Admin
utility (see http://www.redhat.com/software/database/); you could try
that to see what it thinks you have.

            regards, tom lane