Showing index details with \d on psql

I like the idea of this but the format, as you say, could use
improvement. Can I recommend having a "*" next to the columns involved
in the index and have the column names listed next to the index names?
That would look better and be clearer, I think. Perhaps:

Table "mytable"
Attribute | Type | Modifier

- -----------+-----------------------+-----------------------------
post | integer | not null *
thread | smallint | *
reply | smallint | not null *
subject | character varying(60) | default 'foo' *
*Indices: 1. mytable_foobar (post) (PRIMARY KEY)
2. alphabet (subject, thread)
3. badname (thread)

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

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

While hacking around with psql, I came up with the idea of
identifying the columns in a table as to which index they
are in (and conversly, showing which columns an index
contains). I find this useful because a normal listing that
only tells you the name of the index is not very helpful
and usually needs a separate \d index_name entry. The only
concern is how to do it:
as a separate "index" column?
appending the name of the index to the Description column?
numbering the indexes and using the number to save space?
(The latter is used in the enclosed patch and
example below). Which columns the index affects follows the
name, and it also tells you if an index is a primary key. Here
is some sample output:

data=> \d mytable
Table "mytable"

Table "mytable"
Attribute | Type | Modifier

- -----------+-----------------------+-----------------------------
post | integer | not null (index #1)
thread | smallint | (index #2) (index #3)
reply | smallint | not null
subject | character varying(60) | default 'foo' (index #2)
Indices: 1. mytable_foobar (1) (PRIMARY KEY)
2. alphabet (4 2)
3. badname (2)

The numbers at the end of the index names are ugly, but it does
show you instantly the composition and order of the index. I
think once you get used to it, it can be very valuable and
save on calls to \d index_name. My big concern is the size that
each "(index #x)" takes up, but having them separate does make
them stand out more, and in most cases, columns will not belong
to a lot of indices.

The attached (rough) patch is against 7.1.2. Feedback
welcome, as always. :)

Greg Sabino Mullane
greg@turnstep.com
PGP Key: 0x14964AC8 200110062052

-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html

iQA/AwUBO7+rPrybkGcUlkrIEQKz7gCcCyPUDAGGwMbwPa09Rc2pqMbD0cYAn1qY
Yw6/kJdux/vwdN4waU5rdPmH
=/PN6
-----END PGP SIGNATURE-----

*** ./src/bin/psql/describe.c.orig	Wed Mar 21 23:00:19 2001
--- ./src/bin/psql/describe.c	Sat Oct  6 20:46:45 2001
***************
*** 748,754 ****
/* count indices */
if (!error && tableinfo.hasindex)
{
! 			sprintf(buf, "SELECT c2.relname\n"
"FROM pg_class c, pg_class c2, pg_index i\n"
"WHERE c.relname = '%s' AND c.oid = i.indrelid AND i.indexrelid = c2.oid\n"
"ORDER BY c2.relname",
--- 748,754 ----
/* count indices */
if (!error && tableinfo.hasindex)
{
! 			sprintf(buf, "SELECT c2.relname, i.indkey, i.indisprimary\n"
"FROM pg_class c, pg_class c2, pg_index i\n"
"WHERE c.relname = '%s' AND c.oid = i.indrelid AND i.indexrelid = c2.oid\n"
"ORDER BY c2.relname",
***************
*** 810,823 ****
/* print indices */
for (i = 0; i < index_count; i++)
{
! 			sprintf(buf, "%s %s",
! 					index_count == 1 ? "Index:" : (i == 0 ? "Indices:" : "        "),
! 					PQgetvalue(result1, i, 0)
);
- 			if (i < index_count - 1)
- 				strcat(buf, ",");

footers[count_footers++] = xstrdup(buf);
}

/* print contraints */
--- 810,845 ----
/* print indices */
for (i = 0; i < index_count; i++)
{
! 		  char *indexname, *indexlist;
! 		  indexname = PQgetvalue(result1, i, 0);
! 		  indexlist = PQgetvalue(result1, i, 1);
! 			sprintf(buf, "%s %3d. %s (%s)%s",
! 					index_count == 1 ? "Index:" : (i == 0 ? "Indices:" : "        "),i+1,
! 					indexname,indexlist,
! 					strcmp(PQgetvalue(result1, i, 2), "t") == 0 ? " (PRIMARY KEY)" : ""
);

footers[count_footers++] = xstrdup(buf);
+ 
+ 			/* strtokx is overkill here */
+ 			int j;
+ 			char dummy[6]; /* Should be plenty */
+ 			char showindex[10+31];
+ 			int bar=0;
+ 			for (j=0; j<=strlen(indexlist); j++) {
+               if (indexlist[j]==0 || indexlist[j]==32) {
+ 				bar = atoi(dummy);
+                 if (bar>0) /* pg_class has a -2! */
+ 				{
+ 					sprintf(showindex, "(index #%d)", i+1);
+ 					if (cells[(bar-1) * cols + 2][0])
+ 						strcat(cells[(bar-1) * cols + 2], " ");
+ 					strcat(cells[(bar-1) * cols + 2], showindex);
+ 				}
+                 dummy[0] = '\0';
+ 			  }
+ 			  else { strcat(dummy,&indexlist[j]); }
+ 			}
}

/* print contraints */

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I like the idea of this but the format, as you say, could use
improvement. Can I recommend having a "*" next to the columns
involved in the index and have the column names listed next to
the index names? That would look better and be clearer, I think.

Agreed - it's probably more intuitive for someone to ask
"is this column in an index?" and "what does this index cover?"
rather than "which index is this column in?" Attached patch is
against 7.1.2. As a side benfit, it does multiple stars for
columns that belong to multiple indices, which is a nice feature
to be able to spot right away.

Greg Sabino Mullane
greg@turnstep.com
PGP Key: 0x14964AC8 200110122041

-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html

iQA/AwUBO8eqrbybkGcUlkrIEQI+BwCeMkkc2UfyJs+vaJNWS9BFSdZInvAAnjIF
vRuRPuHJWmQ8dqxorK72wLgM
=VdAI
-----END PGP SIGNATURE-----

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I like the idea of this but the format, as you say, could use
improvement. Can I recommend having a "*" next to the columns
involved in the index and have the column names listed next to
the index names? That would look better and be clearer, I think.

Agreed - it's probably more intuitive for someone to ask
"is this column in an index?" and "what does this index cover?"
rather than "which index is this column in?" Attached patch is
against 7.1.2. As a side benfit, it does multiple stars for
columns that belong to multiple indices, which is a nice feature
to be able to spot right away.

Looks very good. I have attached the describe.c file from current CVS.
Can I ask you to submit a patch against this file? Seems there have
been some coding style improvements since 7.2 and I would like your
patch to match. Notice the use of the _() macro. Also, looks like we
have a PRIMARY KEY section in the code now. Please figure out how your
patch mixes in with that.

Also, can you send over sample output from the new patch. Thanks.

I am uncertain if I can get this into 7.2 because we are so close to
beta. This is shaping up into a nice psql addition.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

..

Notice the use of the _() macro. Also, looks like we have a
PRIMARY KEY section in the code now.

New patch applied. I think I was able to wrap my brain around
the new _() macro and use of snprintf this early in the weekend.
:) I removed the separate counts of primary key and unique keys,
and simply added labels to the index list, since that's what
they are anyway.

One thing for someone to look into (I think it's related to the
backend/utils code, which I am not familiar with) is that any
describing of indexes in psql generates an error about
not finding the function 'pg_get_expr'

Sample output from this patch:

Table "b1"
Column | Type | Modifiers
- --------+--------------------------+--------------------------
post | integer | *
thread | smallint | not null *
reply | smallint | not null *
stat | character varying(1) | not null default 'N' * *
uid | integer | not null
ctime | timestamp with time zone | default 'now'
atime | timestamp with time zone |
Indexes abc (post, reply, stat)
def (stat)
foo_pkey [PRIMARY_KEY] (thread)

The code also makes allowances for the table pg_class, just in
case anyone does a \d on it (one of its indexes refers to column
"-2")

Greg Sabino Mullane
greg@turnstep.com
PGP Key: 0x14964AC8 200110131131

-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html

iQA/AwUBO8hhcrybkGcUlkrIEQJtNACggHKbcz6+MXGkR34T/Woe8JyyP8EAoLty
ZxuvaVQQ3H34HYfNWsrDUOwr
=f2Oy
-----END PGP SIGNATURE-----

Greg Sabino Mullane writes:

Table "b1"
Column | Type | Modifiers
- --------+--------------------------+--------------------------
post | integer | *
thread | smallint | not null *
reply | smallint | not null *
stat | character varying(1) | not null default 'N' * *
uid | integer | not null
ctime | timestamp with time zone | default 'now'
atime | timestamp with time zone |
Indexes abc (post, reply, stat)
def (stat)
foo_pkey [PRIMARY_KEY] (thread)

Please use "(primary key)" and add a colon after "Indexes".

I don't like the '*' things. They look ugly and convey little real
information.

--
Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Please use "(primary key)" and add a colon after "Indexes".

Done and done. I capitalized and used brackets to make it
stand out more from the index contents, but it is now changed
back.

I don't like the '*' things. They look ugly and convey little
real information.

They convey "this column is indexed" and also indicate in how many
indexes it appears.

Anyway, revised patch attached.

Revised sample output:

Table "b1"
Column | Type | Modifiers
- --------+--------------------------+--------------------------
post | integer | *
thread | smallint | not null *
reply | smallint | not null *
stat | character varying(1) | not null default 'N' * *
uid | integer | not null
ctime | timestamp with time zone | default 'now'
atime | timestamp with time zone |
Indexes: abc (post, reply, stat)
def (stat)
foo_pkey (primary key) (thread)

Greg Sabino Mullane
greg@turnstep.com
PGP Key: 0x14964AC8 200110131806

-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html

iQA/AwUBO8i8YrybkGcUlkrIEQI1ngCcCtzjcBB8hMrBsAh+wbSsExJCpUYAoP4E
FWCa51FCDi1BP8zTgdujnDlu
=4P3T
-----END PGP SIGNATURE-----

I don't like the '*' things. They look ugly and convey little
real information.

They convey "this column is indexed" and also indicate in how many
indexes it appears.

I tend to agree with Peter on that part ... the asterisks add more
clutter than information. I also think that they could lead to
ambiguity; for example, it's not obvious that the * is not part of
the default clause where there's a default.

I have a large number of problems with this part of your patch:

char *s = _("Indexes:");

! snprintf(buf, sizeof(buf), "%*s %s%s%s",
! (int)strlen(s),
! i == 0 ? s : "",
! PQgetvalue(result1, i, 0),
! strcmp(PQgetvalue(result1, i, 2), "t") == 0 ? _(" (primary key)") : "",
! strcmp(PQgetvalue(result1, i, 3), "t") == 0 ? _(" (unique)") : "");
!
! char indexchar[5]; /* Should be plenty */
! int indexnumber=0;
! char * indexlist = PQgetvalue(result1, i, 1);
! int j,found;
! for (j=0,found=0;j<=strlen(indexlist); j++) {
! if (indexlist[j] == 0 || indexlist[j] == 32) {
! indexnumber = atoi(indexchar);
! if (indexnumber>0) /* pg_class has a -2! */
! {
! strcat(cells[(indexnumber-1) * cols + 2],
! cells[(indexnumber-1) * cols +2][0] ? " *" : "*");
! strcat(buf, ++found==1 ? " (" : ", ");
! strcat(buf, cells[(indexnumber-1) * cols]);
! }
! indexchar[0] = '\0';
! }
! else { strcat(indexchar,&indexlist[j]); }
! }
! if (found) /* must cover for pg_class again */
! strcat(buf, ")");
footers[count_footers++] = xstrdup(buf);

Gripe #1: declarations after the start of a block are a C++-ism. They
are not legal in ANSI C.

Gripe #2: what is indexchar[], why is it being used without
initialization, and what is your justification for thinking 5 is enough
space?

Gripe #3: "32" is not a portable spelling of "' '".

Gripe #4: looks to me like it will fail when indexes are on columns
numbered 10 or above, because the loop will do strcat() multiple times.

Gripe #5: doing the wrong thing on indexes that mention system columns
(negative column numbers) isn't acceptable.

You are really doing things quite the hard way here anyhow, since
pg_get_indexdef would produce the info you want without so much work,
and with less dependency in psql on backend catalog details. I'd
suggest pulling the pg_get_indexdef result instead of indkey in the
SELECT, and then just print the part after USING.

BTW, "primary key" implies "unique", so I think it's not necessary to
print both annotations for a primary key.

regards, tom lane

Greg Sabino Mullane writes:

I don't like the '*' things. They look ugly and convey little
real information.

They convey "this column is indexed" and also indicate in how many
indexes it appears.

I understood that, but a new user would not without a footnote. And
obviously, '*' is just an arbitrary character -- what would we use next to
say "this column is part of a table constraint"? I think this part is
very useful as it stands:

Indexes: abc (post, reply, stat)
def (stat)
foo_pkey (primary key) (thread)

Hmm, shouldn't that be "foo_pkey(thread) (primary key)". I understand
what you meant about the brackets, but brackets aren't typically used in
writing and look artificial. Maybe something like

foo_pkey (thread) -- primary key

--
Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter

Peter Eisentraut <peter_e@gmx.net> writes:

Hmm, shouldn't that be "foo_pkey(thread) (primary key)". I understand
what you meant about the brackets, but brackets aren't typically used in
writing and look artificial. Maybe something like

foo_pkey (thread) -- primary key

Or

primary key: foo_pkey (thread)
unique: foo_serial_key (serialcol)

Note my advice nearby for Greg to make use of pg_get_indexdef, which
returns a CREATE INDEX command string for the index. I have an ulterior
motive here, which is that pg_get_indexdef will deliver useful
information about functional and partial indexes, and I think \d ought
to show that stuff.

In the case of a partial index, anything tacked onto the end of the line
is likely to be confused with the partial index condition. Consider
these examples, all obtained from the regression database with
select pg_get_indexdef(oid) from pg_class where relkind='i';

CREATE INDEX onek2_u2_prtl ON onek2 USING btree (unique2) WHERE (stringu1 < 'B'::name)
CREATE INDEX rect2ind ON fast_emp4000 USING rtree (home_base bigbox_ops)
CREATE UNIQUE INDEX func_index_index ON func_index_heap USING btree (textcat(f1, f2))

I suggest that \d should produce entries like this:

Indexes: onek2_u2_prtl btree (unique2) WHERE (stringu1 < 'B'::name)

Indexes: rect2ind rtree (home_base bigbox_ops)

Indexes: unique: func_index_index btree (textcat(f1, f2))

or possibly this would read better:

Indexes: func_index_index unique btree (textcat(f1, f2))

(s/unique/primary key/ where appropriate).

regards, tom lane

You are really doing things quite the hard way here anyhow, since
pg_get_indexdef would produce the info you want without so much work,
and with less dependency in psql on backend catalog details. I'd
suggest pulling the pg_get_indexdef result instead of indkey in the
SELECT, and then just print the part after USING.

Quick question - where are all these handy 'pg_get_indexdef' and friends
functions documented???

Chris

"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:

Quick question - where are all these handy 'pg_get_indexdef' and friends
functions documented???

They ain't :-(. Feel free to contribute some documentation patches...

regards, tom lane

Peter Eisentraut <peter_e@gmx.net> writes:

Christopher Kings-Lynne writes:

Quick question - where are all these handy 'pg_get_indexdef' and friends
functions documented???

I think they aren't documented because we'd rather not have them exposed
to users, since they might change whenever it seems convenient. Whether
this is a good strategy is another question.

My take is that they aren't documented because Jan didn't bother to
document them :-(. I think we're much better off encouraging
applications to use these functions rather than burrowing around
in the system catalogs for themselves, which seems the only alternative.

regards, tom lane

Christopher Kings-Lynne writes:

Quick question - where are all these handy 'pg_get_indexdef' and friends
functions documented???

I think they aren't documented because we'd rather not have them exposed
to users, since they might change whenever it seems convenient. Whether
this is a good strategy is another question.

--
Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter

"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:

Quick question - where are all these handy 'pg_get_indexdef' and friends
functions documented???

They ain't :-(. Feel free to contribute some documentation patches...

When did they appear? Are they 7.2-devel only, or are they hidden in 7.1.3
as well? I ask because I'm a committer on the phpPgAdmin (WebPg) project
and I've never heard of them! They seem like they'd be helpful in replacing
some of our 20 line sql statements!

Chris

"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:

Quick question - where are all these handy 'pg_get_indexdef' and friends
functions documented???

They ain't :-(. Feel free to contribute some documentation patches...

When did they appear? Are they 7.2-devel only, or are they hidden in 7.1.3
as well?

pg_get_indexdef goes back to 6.4, according to the cvs logs at
http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/utils/adt/ruleutils.c

regards, tom lane

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I understood that, but a new user would not without a footnote. And
obviously, '*' is just an arbitrary character...

Hey, the asterick was Bruce's idea! :) Consider it dropped.

foo_pkey (thread) -- primary key

I'll put that and the other suggestions by Tom Lane into
my next patch, when I have time for it. Please don't let my
possible acquirement of free time in the future dissuade
others from knocking the patch out.

Greg Sabino Mullane
greg@turnstep.com
PGP Key: 0x14964AC8 200110171426

-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html

iQA/AwUBO88e+bybkGcUlkrIEQKfLgCfRhobqXuWMwBhqSjl+5p391IYxvEAnjLh
KGaka5SSVF87brjN6joSy7us
=jv4S
-----END PGP SIGNATURE-----

Greg, do you have an updated version of the patch you would like applied
to 7.3?

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

Greg Sabino Mullane wrote:

my next patch, when I have time for it. Please don't let my
possible acquirement of free time in the future dissuade
others from knocking the patch out.

Greg Sabino Mullane
greg@turnstep.com
PGP Key: 0x14964AC8 200110171426

-- End of PGP signed section, PGP failed!

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Patch applied. Thanks.

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

Greg Sabino Mullane wrote:
[ There is text before PGP section. ]

-- Start of PGP signed section.

Greg, do you have an updated version of the patch you would like
applied to 7.3

Yes, I think this one should work:

http://www.gtsm.com/postgres/showindex2.patch

Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200202221455

-- End of PGP signed section.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026