Re: RI_ConstraintTrigger question

On 9/27/2005 12:20 AM, George Essig wrote:

> On 9/26/05, pobox(at)verysmall(dot)org <pobox(at)verysmall(dot)org> wro
>>
>> We have a database with about 30 tables and some RI. The RI constraints,
>> however, were not named upon creation of the database 2-3 years ago and
>> now when we get an error it contains <unnamed> for the constraint.
>>
>> I tried Google and the documentation, and I still have 2 questions -
>>
>> 1. Is it possible to rename RI_ConstraintTrigger, so that we do not get
>> <unnamed> in the errors.
>>
>> 2. Is there somewhere explanation what the RI_FKey_ procedures mean?
>
>
> I think RI stand for referential integrity. Foreign keys used to be
> implemented using 'create constraint trigger' which automatically names
> triggers 'RI_ConstraintTrigger_' then some integer which I guess is an oid
> (object id).

CREATE CONSTRAINT TRIGGER was an interface also provided for database
dumps, so that the constraints can be restored in the schema without
checking the reloaded data. This possibility has since been abandoned.

This however has nothing to do with naming constraints. Newer PG
versions have a different default naming scheme for constraints, the
user didn't explicitly provided a name for, which is Table_Column_fkey
instead of <unnamed>. This is stored in the pg_trigger.tgconstrname.

What you could do is to dump the database, edit the dump and restore it.
If it's a big database, you might want to take separate schema- and
data-dumps.

Jan

>
> Constraint triggers execute functions to implement a constraint. RI_FKey_...
> are the functions that implement foreign key constraints for different
> events like insert, update, and delete.
>
> When you upgrade a database it's likely that the oids for different database
> objects will change. In sounds like somehow you upgraded and retained
> references to old oids which don't exist anymore. Just a guess.
>
> I suggest you upgrade to a newer version of PostgreSQL and drop all of the
> 'RI_ConstraintTrigger_' trigger and recreate the foreign keys.
>
> George Essig
>

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #