Thread: psql: \d+ show tablespace of indices
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
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
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
"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
Now \d show tablespace of indices per discussion.
test=# \d e
Table "public.e"
Column | Type | Modifiers
--------+---------+-----------
i | integer | not null
j | integer | not null
k | integer |
Indexes:
"e_pkey" PRIMARY KEY, btree (i, j), tablespace "haha"
"ei" btree (i)
"ej" btree (j), tablespace "haha"
"ek" btree (k)
Tablespace: "haha"
Attachment
Revised patch to avoid "lost signals before signaling mechanism is set up
in Win32". This was tested by plus a line:
Sleep(10*1000);
in the front of pgwin32_signal_initialize().
Regards,
Qingqing
Attachment
Patch applied. Thanks. --------------------------------------------------------------------------- Qingqing Zhou wrote: > > Now \d show tablespace of indices per discussion. > > test=# \d e > Table "public.e" > Column | Type | Modifiers > --------+---------+----------- > i | integer | not null > j | integer | not null > k | integer | > Indexes: > "e_pkey" PRIMARY KEY, btree (i, j), tablespace "haha" > "ei" btree (i) > "ej" btree (j), tablespace "haha" > "ek" btree (k) > Tablespace: "haha" > > Content-Description: [ Attachment, skipping... ] > > ---------------------------(end of broadcast)--------------------------- > TIP 8: explain analyze is your friend -- 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