Search Postgresql Archives

Re: RI_ConstraintTrigger question

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

On 9/26/05, pobox@xxxxxxxxxxxxx <pobox@xxxxxxxxxxxxx> 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@xxxxxxxxx #

---------------------------(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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux