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
Hi!
A quick-check (haven't checked any details) - your unconditional use of
Global\ will not work on NT4. With 8.0 we said we wanted to support NT4
with some limits (IIRC, tablespaces don't work, and the intaller
definitly doesn't work). If we want to continue doing that (which I
think we do), the global\ has to be conditional - used on 2000+, not
used on <= NT4.
//Magnus
Show quoted text
-----Original Message-----
From: Qingqing Zhou [mailto:zhouqq@cs.toronto.edu]
Sent: den 4 juni 2005 15:26
To: Magnus Hagander
Cc: pgsql-patches@postgresql.org
Subject: Re: Simplify Win32 Signaling codeRevised 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
Import Notes
Resolved by subject fallback
Magnus Hagander wrote:
Hi!
A quick-check (haven't checked any details) - your unconditional use of
Global\ will not work on NT4. With 8.0 we said we wanted to support NT4
with some limits (IIRC, tablespaces don't work, and the intaller
definitly doesn't work). If we want to continue doing that (which I
think we do), the global\ has to be conditional - used on 2000+, not
used on <= NT4.
How should he make this change? Test OS version or use a different
feature?
--
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 19073Hi!
A quick-check (haven't checked any details) - your
unconditional use of
Global\ will not work on NT4. With 8.0 we said we wanted to
support NT4
with some limits (IIRC, tablespaces don't work, and the intaller
definitly doesn't work). If we want to continue doing that (which I
think we do), the global\ has to be conditional - used on 2000+, not
used on <= NT4.How should he make this change? Test OS version or use a different
feature?
Test the OS version. The other feature that can be used is named pipes,
and that's what we have today ;-) All other kernel objects that we could
use all use the namespaces in the new format, but not back on NT4
//Magnus
Import Notes
Resolved by subject fallback
Magnus Hagander wrote:
Hi!
A quick-check (haven't checked any details) - your
unconditional use of
Global\ will not work on NT4. With 8.0 we said we wanted to
support NT4
with some limits (IIRC, tablespaces don't work, and the intaller
definitly doesn't work). If we want to continue doing that (which I
think we do), the global\ has to be conditional - used on 2000+, not
used on <= NT4.How should he make this change? Test OS version or use a different
feature?Test the OS version. The other feature that can be used is named pipes,
and that's what we have today ;-) All other kernel objects that we could
use all use the namespaces in the new format, but not back on NT4
Do we have any place now were we test the Win32 version, that he could
use as an example, or is this configure.in stuff?
--
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 19073A quick-check (haven't checked any details) - your
unconditional use of
Global\ will not work on NT4. With 8.0 we said we wanted to
support NT4
with some limits (IIRC, tablespaces don't work, and the intaller
definitly doesn't work). If we want to continue doingthat (which I
think we do), the global\ has to be conditional - used on
2000+, not
used on <= NT4.
How should he make this change? Test OS version or use a different
feature?Test the OS version. The other feature that can be used is
named pipes,
and that's what we have today ;-) All other kernel objects
that we could
use all use the namespaces in the new format, but not back on NT4
Do we have any place now were we test the Win32 version, that he could
use as an example, or is this configure.in stuff?
It *must* be done at runtime. Because you use the same binary on NT4 and
more recent versions!
Nope, we don't have any checks like that today. It's fairly trivial, but
if examples are needed, look at pginstca.c in the pginstaller project
where we do this.
//Magnus
Import Notes
Resolved by subject fallback
""Magnus Hagander"" <mha@sollentuna.net> writes
It *must* be done at runtime. Because you use the same binary on NT4 and
more recent versions!Nope, we don't have any checks like that today. It's fairly trivial, but
if examples are needed, look at pginstca.c in the pginstaller project
where we do this.
Ok, understood. Shall I patch it now or after you've seen the details the
patch? And I don't have a NT on hand, so I hope I could do my best in
imagination ;-)
Regards,
Qingqing
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