PostgreSQL Discussion - RI_ConstraintTrigger question

Author

RI_ConstraintTrigger question

pobox@verysmall.org

2005-09-26, 7:23 am

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?

Thanks you.

Iv

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly

George Essig

2005-09-27, 3:23 am

On 9/26/05, pobox@verysmall.org <pobox@verysmall.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_ConstraintTrigge
r_' then some integer which I guess is an oid
(object id).

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_ConstraintTrigge
r_' trigger and recreate the foreign keys.

George Essig

Jan Wieck

2005-09-27, 8:24 pm

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

> On 9/26/05, pobox@verysmall.org <pobox@verysmall.org> wro
>
>
> I think RI stand for referential integrity. Foreign keys used to be
> implemented using 'create constraint trigger' which automatically names
> triggers 'RI_ConstraintTrigge
r_' 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_ConstraintTrigge
r_' 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@Yahoo.com #

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Tom Lane

2005-09-27, 8:24 pm

Jan Wieck <JanWieck@Yahoo.com> writes:
> On 9/27/2005 12:20 AM, George Essig wrote:
[color=darkred]
> What you could do is to dump the database, edit the dump and restore it.

Why not just drop and re-add the FK constraints?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Jan Wieck

2005-09-27, 8:24 pm

On 9/27/2005 3:27 PM, Tom Lane wrote:

> Jan Wieck <JanWieck@Yahoo.com> writes:
>
>
> Why not just drop and re-add the FK constraints?

Dropping unnamed constraints can be a bit tedious.

Jan

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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

pobox@verysmall.org

2005-09-27, 8:24 pm

Tom Lane wrote:
> Jan Wieck <JanWieck@Yahoo.com> writes:
>
>
> Why not just drop and re-add the FK constraints?
>
> regards, tom lane

From all responses it seems that dump/drop is is the only way. This is
what I also understood from the documentation. I just hoped there is
another way (ALTER or so), that can be run LIVE, as the problem is not
so severe in order to justify the downtime. For example few weeks ago we
had several smallint columns that became overpopulated and it was so
easy to change them to integer without any downtime. I hoped for
something similar with the FK constraints. We will have to leave these
for some quiet time one day.

(we run 8.0.3 on FreeBSD 5.4)

Thank you for the comments.

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Sponsored Links