pobox@xxxxxxxxxxxxx wrote:
Tom Lane wrote:
Jan Wieck <JanWieck@xxxxxxxxx> 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 andnow 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.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(we run 8.0.3 on FreeBSD 5.4)
"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
Attachment:
smime.p7s
Description: S/MIME Cryptographic Signature
