Thread: Re: [GENERAL] How to enumerate foreign key constraints after

Moving to -hackers.

> Unfortunately, having all my users run contrib/adddepend isn't an
> option for me.  However, that script does contain a good deal of
> information that I may be able to use for detecting old-style foreign
> key constraints in my own code.

I assume you're doing the database upgrade for them or providing
instructions?  Could this be a mandatory portion of that process?

> Okay, more questions:
> I see that adddepend detects old-style foreign key constraints by
> looking for groups of 3 triggers having 6 or more identical function
> arguments.  Is that the best way to do it?  It occurs to me that an
> alternative might be to find triggers that call RI_FKey_check_ins()
> and have the tgisconstraint flag set.  Will either approach be safe in
> postgres 7.4?  Perhaps a combination of the two would be best?

Yes, a combination of the two would probably be better.  You would need
to be careful about function call names for FKeys, there are a fair
number of them.  Checking for 3 triggers with the function name starting
with RI_FKey* would probably be better.

That said, I've not heard of any issues with the current implementation
of adddepend, which is also used in a few other well used programs.

--
Rod Taylor <rbt@rbt.ca>

PGP Key: http://www.rbt.ca/rbtpub.asc

On Tue, Jun 10, 2003 at 10:25:05PM -0400, Rod Taylor wrote:

> > I see that adddepend detects old-style foreign key constraints by
> > looking for groups of 3 triggers having 6 or more identical function
> > arguments.  Is that the best way to do it?

> That said, I've not heard of any issues with the current implementation
> of adddepend, which is also used in a few other well used programs.

I used adddepend on a relatively complicated schema with lots of foreign
key constraints and sequences it worked pretty well.  It was from 7.1.3
to 7.4devel.

-- 
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Investigación es lo que hago cuando no sé lo que estoy haciendo"
(Wernher von Braun)

Rod Taylor <rbt@rbt.ca> wrote:

>> Unfortunately, having all my users run contrib/adddepend isn't an
>> option for me.  However, that script does contain a good deal of
>> information that I may be able to use for detecting old-style foreign
>> key constraints in my own code.
>
>I assume you're doing the database upgrade for them or providing
>instructions?  Could this be a mandatory portion of that process?

We're referring users to chapter 9.3 of the PostgreSQL Administrator's
Guide for data migration.  Even the standard postgres dump/restore
procedure is nearly too much for our users.  Anything more would be
asking for trouble.

>> Okay, more questions:
>> I see that adddepend detects old-style foreign key constraints by
>> looking for groups of 3 triggers having 6 or more identical function
>> arguments.  Is that the best way to do it?  It occurs to me that an
>> alternative might be to find triggers that call RI_FKey_check_ins()
>> and have the tgisconstraint flag set.  Will either approach be safe in
>> postgres 7.4?  Perhaps a combination of the two would be best?
>
>Yes, a combination of the two would probably be better.  You would need
>to be careful about function call names for FKeys, there are a fair
>number of them.  Checking for 3 triggers with the function name starting
>with RI_FKey* would probably be better.

Will the tgisconstraint flag always be set for these triggers?  Will
the function names still be RI_FKey* once these databases are migrated
to postgres 7.4 or later?

> >Yes, a combination of the two would probably be better.  You would need
> >to be careful about function call names for FKeys, there are a fair
> >number of them.  Checking for 3 triggers with the function name starting
> >with RI_FKey* would probably be better.
>
> Will the tgisconstraint flag always be set for these triggers?  Will
> the function names still be RI_FKey* once these databases are migrated
> to postgres 7.4 or later?

Until someone has a good reason to change things, they'll probably stay
the same, so you will probably get a release or two out of it.

But it is much easier to change the internal structures now, as
PostgreSQL doesn't have nearly as much of a backward compatibility issue
anymore.

--
Rod Taylor <rbt@rbt.ca>

PGP Key: http://www.rbt.ca/rbtpub.asc