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
I'm interested if anyone is using tablespaces? Do we have any actual
reports of people actually using them, to advantage, in the field??
Maybe the next postgresql.org survey could be on tablespace usage?
Chris
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
Christopher Kings-Lynne wrote:
I'm interested if anyone is using tablespaces? Do we have any actual
reports of people actually using them, to advantage, in the field??Maybe the next postgresql.org survey could be on tablespace usage?
Chris
I have seen that tablespaces are widely used and highly appreciated.
I have not seen people complaining about the current implementation.
best regards,
hans
--
Cybertec Geschwinde u Schoenig
Schoengrabern 134, A-2020 Hollabrunn, Austria
Tel: +43/664/393 39 74
www.cybertec.at, www.postgresql.at
On Fri, 2005-06-03 at 08:41 +0200, Hans-Jürgen Schönig wrote:
Christopher Kings-Lynne wrote:
I'm interested if anyone is using tablespaces? Do we have any actual
reports of people actually using them, to advantage, in the field??Maybe the next postgresql.org survey could be on tablespace usage?
I have seen that tablespaces are widely used and highly appreciated.
I have not seen people complaining about the current implementation.
My recent experience is that it is mostly the new Windows users who are
using 8.0. Yes, there are people using Tablespaces on those. The only
complaint is why can't you move pg_xlog easily also?
The migration to 8.0 for a many users appears very slow, with many
PostgreSQL users still planning to enter production on 7.3 and 7.4. This
has much to do with supported versions of integrated products, rather
than any lack of interest in 8.0.
Best Regards, Simon Riggs
On Fri, 2005-06-03 at 11:17 +0800, Christopher Kings-Lynne wrote:
Maybe the next postgresql.org survey could be on tablespace usage?
Could we plan a more comprehensive survey, with more than one question?
Judging by the number of people who fill out surveys, we would still get
thousands of replies if we asked them 10 questions instead of 1. That
would allow us to cross-correlate the answers to gain an even better
picture of what is happening and what is wanted.
Best Regards, Simon Riggs
"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 19073Now \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"
Attachments:
describe.difftext/plain; charset=US-ASCII; name=describe.diffDownload+47-26
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
Attachments:
kill.difftext/plain; charset=US-ASCII; name=kill.diffDownload+123-123
postmaster.difftext/plain; charset=US-ASCII; name=postmaster.diffDownload+58-58
signal.difftext/plain; charset=US-ASCII; name=signal.diffDownload+348-250
win32.difftext/plain; charset=US-ASCII; name=win32.diffDownload+22-5
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