psql's \d display of unique index vs. constraint

Hi all,

I often come across tables with either a unique index or a unique
constraint on them, and psql isn't helpful at showing the difference
between the two. Normally, I don't care which is which, except for
when I have to manually drop and recreate the index or constraint to
speed up a bulk load.

Consider the following two tables, con_tbl and idx_tbl:
CREATE TABLE con_tbl (
pkid int PRIMARY KEY,
identifier text
);
ALTER TABLE con_tbl
ADD CONSTRAINT "con_tbl_identifier_key" UNIQUE ("identifier");

CREATE TABLE idx_tbl (
pkid int PRIMARY KEY,
identifier text
);
CREATE UNIQUE INDEX "idx_tbl_identifier_key" ON "idx_tbl" ("identifier");

If you use psql's \d command to describe the two tables, you'll see:
<snip>
Indexes:
"idx_tbl_pkey" PRIMARY KEY, btree (pkid)
"idx_tbl_identifier_key" UNIQUE, btree (identifier)
<snip>
Indexes:
"con_tbl_pkey" PRIMARY KEY, btree (pkid)
"con_tbl_identifier_key" UNIQUE, btree (identifier)

These two displays are exactly the same, except for the names I chose.
However, if you try either of:
DROP INDEX "con_tbl_identifier_key";
ALTER TABLE "idx_tbl"
DROP CONSTRAINT "idx_tbl_identifier_key";

you'll see both of these commands fail -- you have to know which was
declared as a constraint, and which as an index, in order to know to
use:
ALTER TABLE "con_tbl"
DROP CONSTRAINT "con_tbl_identifier_key";
DROP INDEX "idx_tbl_identifier_key";

Unless there's a simple psql command or display option I'm missing
(\d+ doesn't help), I think it would be better if the \d display of
"idx_tbl_identifier_key" was kept as-is, and the display for
"con_tbl_identifier_key" was presented under a separate "Unique
Constraints" section or a similar heading. Even better would be
allowing either ALTER TABLE ... DROP CONSTRAINT or DROP INDEX to work
regardless of how the unique index or constraint was declared, though
perhaps that would be more work.

Tested with a recent psql:
$ psql -V
psql (PostgreSQL) 9.0devel
contains support for command-line editing

Thanks
Josh

On fre, 2010-04-09 at 18:01 -0400, Josh Kupershmidt wrote:
> I often come across tables with either a unique index or a unique
> constraint on them, and psql isn't helpful at showing the difference
> between the two. Normally, I don't care which is which, except for
> when I have to manually drop and recreate the index or constraint to
> speed up a bulk load.

Yes, I have also been annoyed by that. Perhaps you could work out a
proposed change and send it to the hackers list. You don't necessarily
need to code it up, but make some mock-ups about how things would look
in different situations.

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> On fre, 2010-04-09 at 18:01 -0400, Josh Kupershmidt wrote:
>> I often come across tables with either a unique index or a unique
>> constraint on them, and psql isn't helpful at showing the difference
>> between the two. Normally, I don't care which is which, except for
>> when I have to manually drop and recreate the index or constraint to
>> speed up a bulk load.

> Yes, I have also been annoyed by that. Perhaps you could work out a
> proposed change and send it to the hackers list. You don't necessarily
> need to code it up, but make some mock-ups about how things would look
> in different situations.

Please note that we already rejected the use of a separate constraints
subheading in connection with EXCLUDE constraints; a patch to introduce
one in order to distinguish unique constraints from manually-created
unique indexes isn't likely to fare much better. My recollection is
that it's intentional that psql obscures the difference, because for
most querying purposes there isn't any difference. I agree that
sometimes you'd like to know the difference, so I could see making some
small change that would make it possible to tell the difference when
needed. But I think it shouldn't make the two cases look completely
unrelated. Maybe something like saying "unique constraint" vs just
"unique" would fly.

regards, tom lane

On Tue, Apr 13, 2010 at 1:36 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
>> On fre, 2010-04-09 at 18:01 -0400, Josh Kupershmidt wrote:
>>> I often come across tables with either a unique index or a unique
>>> constraint on them, and psql isn't helpful at showing the difference
>>> between the two. Normally, I don't care which is which, except for
>>> when I have to manually drop and recreate the index or constraint to
>>> speed up a bulk load.
>
>> Yes, I have also been annoyed by that.  Perhaps you could work out a
>> proposed change and send it to the hackers list.  You don't necessarily
>> need to code it up, but make some mock-ups about how things would look
>> in different situations.
>
> Please note that we already rejected the use of a separate constraints
> subheading in connection with EXCLUDE constraints; a patch to introduce
> one in order to distinguish unique constraints from manually-created
> unique indexes isn't likely to fare much better.  My recollection is
> that it's intentional that psql obscures the difference, because for
> most querying purposes there isn't any difference.  I agree that
> sometimes you'd like to know the difference, so I could see making some
> small change that would make it possible to tell the difference when
> needed.  But I think it shouldn't make the two cases look completely
> unrelated.  Maybe something like saying "unique constraint" vs just
> "unique" would fly.

Yeah, probably make it show up for \d+ or something.

On Tue, Apr 13, 2010 at 4:53 PM, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> wrote:
> Yeah, probably make it show up for \d+ or something.

FWIW, I'm not religious about psql's formatting; I'd be happy with
this information being displayed only for \d+, in whatever form makes
folks happy.

I unfortunately don't have much time to try a patch myself at the moment :(

Josh

Josh Kupershmidt wrote:
> FWIW, I'm not religious about psql's formatting; I'd be happy with
> this information being displayed only for \d+, in whatever form makes
> folks happy.
>
> I unfortunately don't have much time to try a patch myself at the moment :(
>

It's a straightforward patch to write with clear value, which we can
always use more of. I added it to
http://wiki.postgresql.org/wiki/Prioritised_Todo#psql so people looking
for an easy patch to chew on one day can find it.

--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
greg(at)2ndQuadrant(dot)com www.2ndQuadrant.us

Greg Smith wrote:
> Josh Kupershmidt wrote:
> > FWIW, I'm not religious about psql's formatting; I'd be happy with
> > this information being displayed only for \d+, in whatever form makes
> > folks happy.
> >
> > I unfortunately don't have much time to try a patch myself at the moment :(
> >
>
> It's a straightforward patch to write with clear value, which we can
> always use more of. I added it to
> http://wiki.postgresql.org/wiki/Prioritised_Todo#psql so people looking
> for an easy patch to chew on one day can find it.

[ Thread moved to hackers.]

What is "Prioritised" Todo? It looks like a copy of the TODO list that
was created on March 23, 2010, and only you and Simon have modified it:

http://wiki.postgresql.org/index.php?title=Prioritised_Todo&action=history

Of course it is not getting updates that are added to the main TODO
list. What can this cause except confusion! I am hoping to hear a
reasonable explanation why this was done, and why changes are going
there instead of the main TODO list, which is here:

http://wiki.postgresql.org/wiki/Todo

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

Bruce Momjian wrote:
> What is "Prioritised" Todo? It looks like a copy of the TODO list that
> was created on March 23, 2010, and only you and Simon have modified it:
>
> http://wiki.postgresql.org/index.php?title=Prioritised_Todo&action=history
>

Well, the updates I made to that one were strictly an accident; I didn't
notice I was editing the forked version. I have put everything I did in
that session back onto the right one. The "Prioritised Todo" wasn't
linked to anywhere that you'd find it except via a bit of bad late night
searching like I did.

I'm not sure what Simon was tinkering with there, but having fallen
victim to it myself I agree having it there with that name is not a
great choice. I moved that bit of work in progress he was doing to
http://wiki.postgresql.org/wiki/User:Simon and deleted the one with the
confusing name. Sorry about propagating my own confusion to others.

--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
greg(at)2ndQuadrant(dot)com www.2ndQuadrant.us

Greg Smith wrote:
> Bruce Momjian wrote:
> > What is "Prioritised" Todo? It looks like a copy of the TODO list that
> > was created on March 23, 2010, and only you and Simon have modified it:
> >
> > http://wiki.postgresql.org/index.php?title=Prioritised_Todo&action=history
> >
>
> Well, the updates I made to that one were strictly an accident; I didn't
> notice I was editing the forked version. I have put everything I did in
> that session back onto the right one. The "Prioritised Todo" wasn't
> linked to anywhere that you'd find it except via a bit of bad late night
> searching like I did.
>
> I'm not sure what Simon was tinkering with there, but having fallen
> victim to it myself I agree having it there with that name is not a
> great choice. I moved that bit of work in progress he was doing to
> http://wiki.postgresql.org/wiki/User:Simon and deleted the one with the
> confusing name. Sorry about propagating my own confusion to others.

Well, unless Simon wants to keep it for some reason, it should be
removed, and if kept, renamed. Simon?

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

Bruce Momjian wrote:
>> http://wiki.postgresql.org/wiki/User:Simon
> Well, unless Simon wants to keep it for some reason, it should be
> removed, and if kept, renamed. Simon?
>

I already retitled the copy left on the personal page and deleted the
one that was causing the confusion. I doubt anyone will accidentally
consider official a page labeled "Simon's Work in Progress: Prioritised
Todo" that's attached to User:Simon, that nothing links to, and that
doesn't show up on the first set of results if you search for "todo" either.

--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
greg(at)2ndQuadrant(dot)com www.2ndQuadrant.us

On Wed, 2010-04-14 at 23:28 -0400, Bruce Momjian wrote:
> Greg Smith wrote:
> > Bruce Momjian wrote:
> > > What is "Prioritised" Todo? It looks like a copy of the TODO list that
> > > was created on March 23, 2010, and only you and Simon have modified it:
> > >
> > > http://wiki.postgresql.org/index.php?title=Prioritised_Todo&action=history
> > >
> >
> > Well, the updates I made to that one were strictly an accident; I didn't
> > notice I was editing the forked version. I have put everything I did in
> > that session back onto the right one. The "Prioritised Todo" wasn't
> > linked to anywhere that you'd find it except via a bit of bad late night
> > searching like I did.
> >
> > I'm not sure what Simon was tinkering with there, but having fallen
> > victim to it myself I agree having it there with that name is not a
> > great choice. I moved that bit of work in progress he was doing to
> > http://wiki.postgresql.org/wiki/User:Simon and deleted the one with the
> > confusing name. Sorry about propagating my own confusion to others.
>
> Well, unless Simon wants to keep it for some reason, it should be
> removed, and if kept, renamed. Simon?

Happy for it to be deleted.

--
Simon Riggs www.2ndQuadrant.com

Greg Smith wrote:
> Bruce Momjian wrote:
> >> http://wiki.postgresql.org/wiki/User:Simon
> > Well, unless Simon wants to keep it for some reason, it should be
> > removed, and if kept, renamed. Simon?
> >
>
> I already retitled the copy left on the personal page and deleted the
> one that was causing the confusion. I doubt anyone will accidentally
> consider official a page labeled "Simon's Work in Progress: Prioritised
> Todo" that's attached to User:Simon, that nothing links to, and that
> doesn't show up on the first set of results if you search for "todo" either.

Thanks for cleaning this up.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com