Thread: Index name different from constraint name
Greetings,
Not entirely sure if this would be considered a 'bug' but it's
certainly annoying and creates some confusion. Basically, it's like
this:
test=3D> create table a (
test(> b int primary key
test(> );
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "a_pkey" for=
table "a"
CREATE TABLE
test=3D> \d a
Table "sfrost.a"
Column | Type | Modifiers=20
--------+---------+-----------
b | integer | not null
Indexes:
"a_pkey" PRIMARY KEY, btree (b)
-- Now, attempt to rename the table, and associated stuff
test=3D> alter table a rename to c;
ALTER TABLE
test=3D> alter index a_pkey rename to c_pkey;
ALTER INDEX
test=3D> \d c
Table "sfrost.c"
Column | Type | Modifiers=20
--------+---------+-----------
b | integer | not null
Indexes:
"c_pkey" PRIMARY KEY, btree (b)
-- All looks good, but there's something hidden...
test=3D> \q
> pg_dump -s -n sfrost test
--
-- PostgreSQL database dump
--
[...]
CREATE TABLE c (
b integer NOT NULL
);
[...]
ALTER TABLE ONLY c
ADD CONSTRAINT a_pkey PRIMARY KEY (b);
[...]
--
-- PostgreSQL database dump complete
--
The *constraint* name isn't changed, and that's what pg_dump uses. Fair
enough on pg_dump's part, but it's very confusing that \d (or even \d++)
doesn't show the issue that's created by just renaming the index. It
would seem to me that \d can use its current format when the names are
the same, but if they're different it should display the index and the
constraint seperately.
Sorry, havn't really got time to write a patch at the moment. Hopefully
someone might have a minute to look into it tho.
Thanks,
Stephen
Stephen Frost <sfrost@snowman.net> writes:
> test=> create table a (
> test(> b int primary key
> test(> );
> NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "a_pkey" for=
> table "a"
> CREATE TABLE
> test=> alter table a rename to c;
> ALTER TABLE
> test=> alter index a_pkey rename to c_pkey;
> ALTER INDEX
Arguably we should forbid ALTER INDEX RENAME on an index that belongs to
a constraint, and make you rename the constraint instead (and have that
implicitly change the index name too).
regards, tom lane
* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> Stephen Frost <sfrost@snowman.net> writes:
> > test=3D> create table a (
> > test(> b int primary key
> > test(> );
> > NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "a_pkey"=
for=3D
> > table "a"
> > CREATE TABLE
> > test=3D> alter table a rename to c;
> > ALTER TABLE
> > test=3D> alter index a_pkey rename to c_pkey;
> > ALTER INDEX
>=20
> Arguably we should forbid ALTER INDEX RENAME on an index that belongs to
> a constraint, and make you rename the constraint instead (and have that
> implicitly change the index name too).
That would work too, though I don't think you can just rename a
constraint. You have to drop/add it, which means dropping and then
adding the index back it looks like, which kind of sucks if it's a big
table (one reason I wasn't just dropping/recreating the table in the
first place). This was on 8.0.3; can you rename constraints w/ 8.1? If
so, and if that's made to implicitly change the index name, I'd be
happy.
Thanks,
Stephen
Stephen Frost <sfrost@snowman.net> writes:
> * Tom Lane (tgl@sss.pgh.pa.us) wrote:
>> Arguably we should forbid ALTER INDEX RENAME on an index that belongs to
>> a constraint, and make you rename the constraint instead (and have that
>> implicitly change the index name too).
> That would work too, though I don't think you can just rename a
> constraint.
Yeah, we'd need to add syntax for that, but it seems useful anyway.
Plan B would be to make the ALTER INDEX RENAME update the associated
constraint too, but that doesn't give you a facility to rename
constraints of other types.
Anyway, point is that I think we should force the index and constraint
names to track each other, rather than complicating matters by
supporting the situation where they are different.
regards, tom lane
Added to TODO:
o Have ALTER INDEX update the name of a constraint using that index
o Allow ALTER TABLE RENAME CONSTRAINT
---------------------------------------------------------------------------
Tom Lane wrote:
> Stephen Frost <sfrost@snowman.net> writes:
> > * Tom Lane (tgl@sss.pgh.pa.us) wrote:
> >> Arguably we should forbid ALTER INDEX RENAME on an index that belongs to
> >> a constraint, and make you rename the constraint instead (and have that
> >> implicitly change the index name too).
>
> > That would work too, though I don't think you can just rename a
> > constraint.
>
> Yeah, we'd need to add syntax for that, but it seems useful anyway.
>
> Plan B would be to make the ALTER INDEX RENAME update the associated
> constraint too, but that doesn't give you a facility to rename
> constraints of other types.
>
> Anyway, point is that I think we should force the index and constraint
> names to track each other, rather than complicating matters by
> supporting the situation where they are different.
>
> regards, tom lane
>
> ---------------------------(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
>
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
* Bruce Momjian (pgman@candle.pha.pa.us) wrote:
> Added to TODO:
>=20
> o Have ALTER INDEX update the name of a constraint using that ind=
ex
> o Allow ALTER TABLE RENAME CONSTRAINT
More like:
Add ALTER TABLE RENAME CONSTRAINT; implicitly rename linked indexes also
Is there much dependency on constraint names? If not, it'd seem like
implementing this would be pretty straight-forward. For some reason, I
thought there was some dependency on constraint names, but I can't
remember what...
Thanks,
Stephen
> -------------------------------------------------------------------------=
--
>=20
> Tom Lane wrote:
> > Stephen Frost <sfrost@snowman.net> writes:
> > > * Tom Lane (tgl@sss.pgh.pa.us) wrote:
> > >> Arguably we should forbid ALTER INDEX RENAME on an index that belong=
s to
> > >> a constraint, and make you rename the constraint instead (and have t=
hat
> > >> implicitly change the index name too).
> >=20
> > > That would work too, though I don't think you can just rename a
> > > constraint.
> >=20
> > Yeah, we'd need to add syntax for that, but it seems useful anyway.
> >=20
> > Plan B would be to make the ALTER INDEX RENAME update the associated
> > constraint too, but that doesn't give you a facility to rename
> > constraints of other types.
> >=20
> > Anyway, point is that I think we should force the index and constraint
> > names to track each other, rather than complicating matters by
> > supporting the situation where they are different.
> >=20
> > regards, tom lane
> >=20
> > ---------------------------(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
> >=20
>=20
> --=20
> Bruce Momjian | http://candle.pha.pa.us
> pgman@candle.pha.pa.us | (610) 359-1001
> + If your life is a hard drive, | 13 Roberts Road
> + Christ can be your backup. | Newtown Square, Pennsylvania 19=
073
OK, updated. --------------------------------------------------------------------------- Stephen Frost wrote: -- Start of PGP signed section. > * Bruce Momjian (pgman@candle.pha.pa.us) wrote: > > Added to TODO: > > > > o Have ALTER INDEX update the name of a constraint using that index > > o Allow ALTER TABLE RENAME CONSTRAINT > > More like: > Add ALTER TABLE RENAME CONSTRAINT; implicitly rename linked indexes also > > Is there much dependency on constraint names? If not, it'd seem like > implementing this would be pretty straight-forward. For some reason, I > thought there was some dependency on constraint names, but I can't > remember what... > > Thanks, > > Stephen > > > --------------------------------------------------------------------------- > > > > Tom Lane wrote: > > > Stephen Frost <sfrost@snowman.net> writes: > > > > * Tom Lane (tgl@sss.pgh.pa.us) wrote: > > > >> Arguably we should forbid ALTER INDEX RENAME on an index that belongs to > > > >> a constraint, and make you rename the constraint instead (and have that > > > >> implicitly change the index name too). > > > > > > > That would work too, though I don't think you can just rename a > > > > constraint. > > > > > > Yeah, we'd need to add syntax for that, but it seems useful anyway. > > > > > > Plan B would be to make the ALTER INDEX RENAME update the associated > > > constraint too, but that doesn't give you a facility to rename > > > constraints of other types. > > > > > > Anyway, point is that I think we should force the index and constraint > > > names to track each other, rather than complicating matters by > > > supporting the situation where they are different. > > > > > > regards, tom lane > > > > > > ---------------------------(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 > > > > > > > -- > > Bruce Momjian | http://candle.pha.pa.us > > pgman@candle.pha.pa.us | (610) 359-1001 > > + If your life is a hard drive, | 13 Roberts Road > > + Christ can be your backup. | Newtown Square, Pennsylvania 19073 -- End of PGP section, PGP failed! -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073