Re: RI_ConstraintTrigger question

pobox(at)verysmall(dot)org wrote:
> Tom Lane wrote:

>> Jan Wieck <JanWieck(at)Yahoo(dot)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