--Etoca9nXhLMPgAOd
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

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

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

--Etoca9nXhLMPgAOd
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: Digital signature
Content-Disposition: inline

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)

iD8DBQFDX5I0rzgMPqB3kigRAuu0AJ0aQxsHZ9icWy50rTcazy sUtcmS1ACeJJ6g
s1grb6I1imVQ3qnCQMCbS3s=
=LQoS
-----END PGP SIGNATURE-----

--Etoca9nXhLMPgAOd--

Stephen Frost <sfrost (AT) snowman (DOT) net> writes:
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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

--G7SA92dllFmDflQV
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

* Tom Lane (tgl (AT) sss (DOT) pgh.pa.us) wrote:
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

--G7SA92dllFmDflQV
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: Digital signature
Content-Disposition: inline

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)

iD8DBQFDX6uBrzgMPqB3kigRAsG+AJsFw6Sm0KF0gj9gFnq1QE c1sIf29QCfW18X
5h9Rafj6sEo5qmOcXvnae4E=
=uVnW
-----END PGP SIGNATURE-----

--G7SA92dllFmDflQV--

Stephen Frost <sfrost (AT) snowman (DOT) net> writes:
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

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:
--
Bruce Momjian | http://candle.pha.pa.us
pgman (AT) candle (DOT) 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 broadcast)---------------------------
TIP 6: explain analyze is your friend

--xBVDolUUcKTtnLo/
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

* Bruce Momjian (pgman (AT) candle (DOT) pha.pa.us) wrote:
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


--xBVDolUUcKTtnLo/
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: Digital signature
Content-Disposition: inline

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)

iD8DBQFDYOALrzgMPqB3kigRApo0AJ9nXACcayAGd+wEzBoAJn wdEt71zwCgj4MU
CWBV5+rjPG+ZCihgdgaDEq8=
=gnN9
-----END PGP SIGNATURE-----

--xBVDolUUcKTtnLo/--

OK, updated.

---------------------------------------------------------------------------

Stephen Frost wrote:
-- Start of PGP signed section.

--
Bruce Momjian | http://candle.pha.pa.us
pgman (AT) candle (DOT) 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 broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org