Now \d+ is able to show the tablespace details of indices. A sample output
is followed:

test=# \d+ m
Table "public.m"
Column | Type | Modifiers | Description
--------+---------+-----------+-------------
i | integer | |
j | integer | |
Indexes:
"mi" btree (i) - Tablespace: "testspace"
"mj" btree (j)
Has OIDs: no



Index: describe.c
================================================== =================
RCS file: /projects/cvsroot/pgsql/src/bin/psql/describe.c,v
retrieving revision 1.115
diff -c -r1.115 describe.c
*** describe.c 6 Apr 2005 05:23:32 -0000 1.115
--- describe.c 23 May 2005 10:41:48 -0000
***************
*** 37,43 ****
const char *schemavar, const char *namevar,
const char *altnamevar, const char *visibilityrule);

! static void add_tablespace_footer(char relkind, Oid tablespace,
char **footers, int *count, PQExpBufferData buf);

/*----------------
--- 37,43 ----
const char *schemavar, const char *namevar,
const char *altnamevar, const char *visibilityrule);

! static bool add_tablespace_footer(char relkind, Oid tablespace,
char **footers, int *count, PQExpBufferData buf);

/*----------------
***************
*** 1022,1028 ****
{
printfPQExpBuffer(&buf,
"SELECT c2.relname, i.indisprimary, i.indisunique,
i.indisclustered, "
! "pg_catalog.pg_get_indexdef(i.indexrelid, 0, true)\n"
"FROM pg_catalog.pg_class c, pg_catalog.pg_class c2,
pg_catalog.pg_index i\n"
"WHERE c.oid = '%s' AND c.oid = i.indrelid AND i.indexrelid =
c2.oid\n"
"ORDER BY i.indisprimary DESC, i.indisunique DESC, c2.relname",
--- 1022,1028 ----
{
printfPQExpBuffer(&buf,
"SELECT c2.relname, i.indisprimary, i.indisunique,
i.indisclustered, "
! "pg_catalog.pg_get_indexdef(i.indexrelid, 0, true),
c2.reltablespace\n"
"FROM pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.p
g_index i\n"
"WHERE c.oid = '%s' AND c.oid = i.indrelid AND i.indexrelid =
c2.oid\n"
"ORDER BY i.indisprimary DESC, i.indisunique DESC, c2.relname",
***************
*** 1165,1170 ****
--- 1165,1190 ----
if (strcmp(PQgetvalue(result1, i, 3), "t") == 0)
appendPQExpBuffer(&buf, " CLUSTER");

+ /* Print tablespace of the index on the same line */
+ if (verbose)
+ {
+ PQExpBufferData tmpbuf;
+
+ count_footers += 1;
+ initPQExpBuffer(&tmpbuf);
+ if (add_tablespace_footer('i', atoi(PQgetvalue(result1, i, 5)),
+ footers, &count_footers, tmpbuf))
+ {
+ appendPQExpBuffer(&buf, " - ");
+ appendPQExpBuffer(&buf, tmpbuf.data);
+
+ count_footers -= 2;
+ }
+ else
+ count_footers -= 1;
+ termPQExpBuffer(&tmpbuf);
+ }
+
footers[count_footers++] = pg_strdup(buf.data);
}
}
***************
*** 1316,1323 ****
return retval;
}

!
! static void
add_tablespace_footer(char relkind, Oid tablespace, char **footers,
int *count, PQExpBufferData buf)
{
--- 1336,1343 ----
return retval;
}

! /* Return true if the relation uses non default tablespace; otherwise
return false */
! static bool
add_tablespace_footer(char relkind, Oid tablespace, char **footers,
int *count, PQExpBufferData buf)
{
***************
*** 1336,1342 ****
"WHERE oid = '%u';", tablespace);
result1 = PSQLexec(buf.data, false);
if (!result1)
! return;
/* Should always be the case, but.... */
if (PQntuples(result1) > 0)
{
--- 1356,1362 ----
"WHERE oid = '%u';", tablespace);
result1 = PSQLexec(buf.data, false);
if (!result1)
! return false;
/* Should always be the case, but.... */
if (PQntuples(result1) > 0)
{
***************
*** 1345,1352 ****
--- 1365,1376 ----
footers[(*count)++] = pg_strdup(buf.data);
}
PQclear(result1);
+
+ return true;
}
}
+
+ return false;
}

/*

On Mon, 2005-05-23 at 18:52 +0800, Qingqing Zhou wrote:
> Now \d+ is able to show the tablespace details of indices.

Should this be included in \d? Tablespace information for the table
itself is, so I think we should probably do the same for indexes.

Also, can you resend the patch as an attachment? Perhaps the ML software
munged your email or my MUA is just broken, but the patch you sent seems
to be corrupted.

-Neil



---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Neil Conway <neilc@samurai.com> writes:
> On Mon, 2005-05-23 at 18:52 +0800, Qingqing Zhou wrote:
>> Now \d+ is able to show the tablespace details of indices.

> Should this be included in \d? Tablespace information for the table
> itself is, so I think we should probably do the same for indexes.

Seems reasonable. In the minor-carping department, I didn't much like
the formatting:

Indexes:
"mi" btree (i) - Tablespace: "testspace"
"mj" btree (j)

That looks a bit ugly to me ... not sure why, exactly, but maybe it's
that there's too much punctuation. The underlying CREATE INDEX command
would just look like

"mi" btree (i) tablespace "testspace"

Does that look better or worse to you?

regards, tom lane

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

On Thu, 2005-06-02 at 22:15 -0400, Tom Lane wrote:
> Does that look better or worse to you?

I agree the patch's format is a bit off. What about

"mi" btree (i), tablespace "testspace"

"PRIMARY KEY" is currently separated from the rest of the index
description via a comma -- although on the other hand the column list
isn't preceded by a comma. Perhaps this whole format should be
rethought?

-Neil



---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

"Neil Conway" <neilc@samurai.com> writes
> On Thu, 2005-06-02 at 22:15 -0400, Tom Lane wrote:
> > Does that look better or worse to you?
>
> I agree the patch's format is a bit off. What about
>
> "mi" btree (i), tablespace "testspace"
>
> "PRIMARY KEY" is currently separated from the rest of the index
> description via a comma -- although on the other hand the column list
> isn't preceded by a comma. Perhaps this whole format should be
> rethought?
>

yes, you are right, both forms you showed up are better than mine - the
reason I use that format is because add_tablespace_footer() prints a
"Tablespace: \"tablespace_name\"" in the PQExpBufferData. I could hack the
content in the buffer to make it looks better. Is this acceptable?

Regards,
Qingqing

Qingqing Zhou wrote:
>
> "Neil Conway" <neilc@samurai.com> writes
> > On Thu, 2005-06-02 at 22:15 -0400, Tom Lane wrote:
> > > Does that look better or worse to you?
> >
> > I agree the patch's format is a bit off. What about
> >
> > "mi" btree (i), tablespace "testspace"
> >
> > "PRIMARY KEY" is currently separated from the rest of the index
> > description via a comma -- although on the other hand the column list
> > isn't preceded by a comma. Perhaps this whole format should be
> > rethought?
> >
>
> yes, you are right, both forms you showed up are better than mine - the
> reason I use that format is because add_tablespace_footer() prints a
> "Tablespace: \"tablespace_name\"" in the PQExpBufferData. I could hack the
> content in the buffer to make it looks better. Is this acceptable?

Yes, I am thinking you should replace the string "Tablespace:" with a
char pointer that is passed to the function and can be tailored to the
specific use.

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