RI_ConstraintTrigger question

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

George Essig

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

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_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@Yahoo.com #

Jan Wieck <JanWieck@Yahoo.com> writes:

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

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.

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

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

Jan Wieck <JanWieck@Yahoo.com> writes:

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

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.

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?

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 #

Tom Lane wrote:

Jan Wieck <JanWieck@Yahoo.com> writes:

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

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.

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

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.

pobox@verysmall.org wrote:

Tom Lane wrote:

Jan Wieck <JanWieck@Yahoo.com> writes:

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

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.

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

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)

I guess it should be possible to alter the name in the system columns
directly. I believe the name is in the field "conname" of the table
"pg_constraint" in the pg_catalog schema. Be aware, though, that
you can easily destroy your database when messing around in
the system tables. But I'd guess that changing a name is ok, since
it shouldn't change the on-disk representation of any data.

I'd still recommend that you backup all your data before you change
anything in the system schema, and since postgres caches some data
from the pg_catalog.* tables, close your session or even restart the
database after you are done making your changes.

greetings, Florian Pflug