Thread: Renaming a constraint's index
There was some discussion last week on -bugs about how renaming an index
that belongs to a unique or primary key constraint is allowed, but can
lead to situations that can't be dumped/restored properly. This isn't
really pg_dump's fault, IMHO. We should rather make the backend enforce
that the index's name stays in sync with the constraint's name. (Well,
I guess we could imagine making pg_dump deal with this by issuing
ALTER TABLE ADD CONSTRAINT and then ALTER INDEX RENAME, but ... ick.)
There seem to be three things we could do:
1. Make ALTER INDEX RENAME fail if the index belongs to a constraint.
This is trivial code-wise, but doesn't seem especially helpful to users.
2. Make ALTER INDEX RENAME automatically rename the constraint, too.
This would take a few dozen lines of code but is certainly not hard.
3. Invent an ALTER TABLE RENAME CONSTRAINT command, and have it also
rename the underlying index. This would take more code than would be
reasonable to add to 8.3 at this late date, I think, but it would
add more functionality since you could also rename constraints of
other types.
Now, doing either #1 or #2 today would not foreclose doing #3 later
(actually, we *must* do either #1 or #2 together with #3 in order to
meet the goal of not letting the names diverge).
I'm thinking about doing #2 for 8.3 and leaving #3 as a TODO item.
Comments?
regards, tom lane
Tom Lane wrote: > There was some discussion last week on -bugs about how renaming an index > that belongs to a unique or primary key constraint is allowed, but can > lead to situations that can't be dumped/restored properly. This isn't > really pg_dump's fault, IMHO. We should rather make the backend enforce > that the index's name stays in sync with the constraint's name. (Well, > I guess we could imagine making pg_dump deal with this by issuing > ALTER TABLE ADD CONSTRAINT and then ALTER INDEX RENAME, but ... ick.) > > There seem to be three things we could do: > > 1. Make ALTER INDEX RENAME fail if the index belongs to a constraint. > This is trivial code-wise, but doesn't seem especially helpful to users. > > 2. Make ALTER INDEX RENAME automatically rename the constraint, too. > This would take a few dozen lines of code but is certainly not hard. > > 3. Invent an ALTER TABLE RENAME CONSTRAINT command, and have it also > rename the underlying index. This would take more code than would be > reasonable to add to 8.3 at this late date, I think, but it would > add more functionality since you could also rename constraints of > other types. > > Now, doing either #1 or #2 today would not foreclose doing #3 later > (actually, we *must* do either #1 or #2 together with #3 in order to > meet the goal of not letting the names diverge). > > I'm thinking about doing #2 for 8.3 and leaving #3 as a TODO item. > Comments? > > > +1 cheers andrew
On Jan 16, 2008, at 5:20 PM, Tom Lane wrote: > There was some discussion last week on -bugs about how renaming an > index > that belongs to a unique or primary key constraint is allowed, but can > lead to situations that can't be dumped/restored properly. This isn't > really pg_dump's fault, IMHO. We should rather make the backend > enforce > that the index's name stays in sync with the constraint's name. > (Well, > I guess we could imagine making pg_dump deal with this by issuing > ALTER TABLE ADD CONSTRAINT and then ALTER INDEX RENAME, but ... ick.) > > There seem to be three things we could do: > > 1. Make ALTER INDEX RENAME fail if the index belongs to a constraint. > This is trivial code-wise, but doesn't seem especially helpful to > users. +1. IMO, the constraint should be the canonical source of the name, not the other way around. > 2. Make ALTER INDEX RENAME automatically rename the constraint, too. > This would take a few dozen lines of code but is certainly not hard. -1 (see above) > 3. Invent an ALTER TABLE RENAME CONSTRAINT command, and have it also > rename the underlying index. This would take more code than would be > reasonable to add to 8.3 at this late date, I think, but it would > add more functionality since you could also rename constraints of > other types. +1 > Now, doing either #1 or #2 today would not foreclose doing #3 later > (actually, we *must* do either #1 or #2 together with #3 in order to > meet the goal of not letting the names diverge). > > I'm thinking about doing #2 for 8.3 and leaving #3 as a TODO item. > Comments? Like I said, I don't think it makes sense for the index to drive constraint names. If someone *really* needed to do this in 8.3, could they accomplish it by updating the catalog tables? I'd rather wait for 8.4 than put #2 in... -- Decibel!, aka Jim C. Nasby, Database Architect decibel@decibel.org Give your computer some brain candy! www.distributed.net Team #1828
Decibel! <decibel@decibel.org> writes:
> On Jan 16, 2008, at 5:20 PM, Tom Lane wrote:
>> There seem to be three things we could do:
>>
>> 1. Make ALTER INDEX RENAME fail if the index belongs to a constraint.
>> This is trivial code-wise, but doesn't seem especially helpful to
>> users.
> +1. IMO, the constraint should be the canonical source of the name,
> not the other way around.
>> 2. Make ALTER INDEX RENAME automatically rename the constraint, too.
>> This would take a few dozen lines of code but is certainly not hard.
> -1 (see above)
> ...
> Like I said, I don't think it makes sense for the index to drive
> constraint names.
Maybe not, but as long as psql \d shows indexes rather than constraints,
there'll be an awfully strong bias to use ALTER TABLE/ALTER INDEX
when you decide you don't like the name. I don't see any great
moral failing in allowing things to be renamed either way.
regards, tom lane
>>> On Wed, Jan 16, 2008 at 5:20 PM, in message <29518.1200525640@sss.pgh.pa.us>, Tom Lane <tgl@sss.pgh.pa.us> wrote: > 2. Make ALTER INDEX RENAME automatically rename the constraint, too. > 3. Invent an ALTER TABLE RENAME CONSTRAINT command, and have it also > rename the underlying index. > I'm thinking about doing #2 for 8.3 and leaving #3 as a TODO item. +1 Something should be done for 8.3 to prevent unusable dumps. The absence of primary key rename has been mildly annoying at times. I don't see any problem with allowing it to work both ways. -Kevin