Thread: small psql patch - show Schema name for \dt \dv \dS
I can't remember who said they were working on schema related psql
changes, but I kept finding myself wishing I could see what schema a
table or view exists in when I use \dt, \dv, etc. So, here is a patch
which does just that.
It sorts on "Schema" first, and "Name" second.
It also changes the test for system objects to key off the namespace
name starting with 'pg_' instead of the object name.
Sample output:
test=# create schema testschema;
CREATE SCHEMA
test=# create view testschema.ts_view as select 1;
CREATE VIEW
test=# \dv
List of relations
Name | Schema | Type | Owner
--------------------+------------+------+----------
__testpassbyval | public | view | postgres
fooview | public | view | postgres
master_pg_proc | public | view | postgres
rmt_pg_proc | public | view | postgres
vw_dblink_get_pkey | public | view | postgres
vw_dblink_replace | public | view | postgres
ts_view | testschema | view | postgres
(7 rows)
If there are no objections, please apply.
Thanks!
Joe
Index: src/bin/psql/describe.c
===================================================================
RCS file: /opt/src/cvs/pgsql/src/bin/psql/describe.c,v
retrieving revision 1.55
diff -c -r1.55 describe.c
*** src/bin/psql/describe.c 12 Jul 2002 18:43:19 -0000 1.55
--- src/bin/psql/describe.c 18 Jul 2002 21:53:52 -0000
***************
*** 1022,1030 ****
printfPQExpBuffer(&buf,
"SELECT c.relname as \"%s\",\n"
" CASE c.relkind WHEN 'r' THEN '%s' WHEN 'v' THEN '%s' WHEN 'i' THEN '%s' WHEN 'S' THEN '%s' WHEN 's'
THEN'%s' END as \"%s\",\n"
" u.usename as \"%s\"",
! _("Name"), _("table"), _("view"), _("index"), _("sequence"),
_("special"), _("Type"), _("Owner"));
if (desc)
--- 1022,1031 ----
printfPQExpBuffer(&buf,
"SELECT c.relname as \"%s\",\n"
+ " n.nspname as \"%s\",\n"
" CASE c.relkind WHEN 'r' THEN '%s' WHEN 'v' THEN '%s' WHEN 'i' THEN '%s' WHEN 'S' THEN '%s' WHEN 's'
THEN'%s' END as \"%s\",\n"
" u.usename as \"%s\"",
! _("Name"), _("Schema"), _("table"), _("view"), _("index"), _("sequence"),
_("special"), _("Type"), _("Owner"));
if (desc)
***************
*** 1034,1047 ****
if (showIndexes)
appendPQExpBuffer(&buf,
",\n c2.relname as \"%s\""
! "\nFROM pg_class c, pg_class c2, pg_index i, pg_user u\n"
"WHERE c.relowner = u.usesysid\n"
"AND i.indrelid = c2.oid AND i.indexrelid = c.oid\n",
_("Table"));
else
appendPQExpBuffer(&buf,
! "\nFROM pg_class c, pg_user u\n"
! "WHERE c.relowner = u.usesysid\n");
appendPQExpBuffer(&buf, "AND c.relkind IN (");
if (showTables)
--- 1035,1050 ----
if (showIndexes)
appendPQExpBuffer(&buf,
",\n c2.relname as \"%s\""
! "\nFROM pg_class c, pg_class c2, pg_index i, pg_user u, pg_namespace n\n"
"WHERE c.relowner = u.usesysid\n"
+ "AND c.relnamespace = n.oid\n"
"AND i.indrelid = c2.oid AND i.indexrelid = c.oid\n",
_("Table"));
else
appendPQExpBuffer(&buf,
! "\nFROM pg_class c, pg_user u, pg_namespace n\n"
! "WHERE c.relowner = u.usesysid\n"
! "AND c.relnamespace = n.oid\n");
appendPQExpBuffer(&buf, "AND c.relkind IN (");
if (showTables)
***************
*** 1058,1071 ****
appendPQExpBuffer(&buf, ")\n");
if (showSystem)
! appendPQExpBuffer(&buf, " AND c.relname ~ '^pg_'\n");
else
! appendPQExpBuffer(&buf, " AND c.relname !~ '^pg_'\n");
if (name)
appendPQExpBuffer(&buf, " AND c.relname ~ '^%s'\n", name);
! appendPQExpBuffer(&buf, "ORDER BY 1;");
res = PSQLexec(buf.data);
termPQExpBuffer(&buf);
--- 1061,1074 ----
appendPQExpBuffer(&buf, ")\n");
if (showSystem)
! appendPQExpBuffer(&buf, " AND n.nspname ~ '^pg_'\n");
else
! appendPQExpBuffer(&buf, " AND n.nspname !~ '^pg_'\n");
if (name)
appendPQExpBuffer(&buf, " AND c.relname ~ '^%s'\n", name);
! appendPQExpBuffer(&buf, "ORDER BY 2,1;");
res = PSQLexec(buf.data);
termPQExpBuffer(&buf);
Your patch has been added to the PostgreSQL unapplied patches list at:
http://candle.pha.pa.us/cgi-bin/pgpatches
I will try to apply it within the next 48 hours.
---------------------------------------------------------------------------
Joe Conway wrote:
> I can't remember who said they were working on schema related psql
> changes, but I kept finding myself wishing I could see what schema a
> table or view exists in when I use \dt, \dv, etc. So, here is a patch
> which does just that.
>
> It sorts on "Schema" first, and "Name" second.
>
> It also changes the test for system objects to key off the namespace
> name starting with 'pg_' instead of the object name.
>
> Sample output:
>
> test=# create schema testschema;
> CREATE SCHEMA
> test=# create view testschema.ts_view as select 1;
> CREATE VIEW
> test=# \dv
> List of relations
> Name | Schema | Type | Owner
> --------------------+------------+------+----------
> __testpassbyval | public | view | postgres
> fooview | public | view | postgres
> master_pg_proc | public | view | postgres
> rmt_pg_proc | public | view | postgres
> vw_dblink_get_pkey | public | view | postgres
> vw_dblink_replace | public | view | postgres
> ts_view | testschema | view | postgres
> (7 rows)
>
> If there are no objections, please apply.
>
> Thanks!
>
> Joe
>
> Index: src/bin/psql/describe.c
> ===================================================================
> RCS file: /opt/src/cvs/pgsql/src/bin/psql/describe.c,v
> retrieving revision 1.55
> diff -c -r1.55 describe.c
> *** src/bin/psql/describe.c 12 Jul 2002 18:43:19 -0000 1.55
> --- src/bin/psql/describe.c 18 Jul 2002 21:53:52 -0000
> ***************
> *** 1022,1030 ****
>
> printfPQExpBuffer(&buf,
> "SELECT c.relname as \"%s\",\n"
> " CASE c.relkind WHEN 'r' THEN '%s' WHEN 'v' THEN '%s' WHEN 'i' THEN '%s' WHEN 'S' THEN '%s' WHEN 's'
THEN'%s' END as \"%s\",\n"
> " u.usename as \"%s\"",
> ! _("Name"), _("table"), _("view"), _("index"), _("sequence"),
> _("special"), _("Type"), _("Owner"));
>
> if (desc)
> --- 1022,1031 ----
>
> printfPQExpBuffer(&buf,
> "SELECT c.relname as \"%s\",\n"
> + " n.nspname as \"%s\",\n"
> " CASE c.relkind WHEN 'r' THEN '%s' WHEN 'v' THEN '%s' WHEN 'i' THEN '%s' WHEN 'S' THEN '%s' WHEN 's'
THEN'%s' END as \"%s\",\n"
> " u.usename as \"%s\"",
> ! _("Name"), _("Schema"), _("table"), _("view"), _("index"), _("sequence"),
> _("special"), _("Type"), _("Owner"));
>
> if (desc)
> ***************
> *** 1034,1047 ****
> if (showIndexes)
> appendPQExpBuffer(&buf,
> ",\n c2.relname as \"%s\""
> ! "\nFROM pg_class c, pg_class c2, pg_index i, pg_user u\n"
> "WHERE c.relowner = u.usesysid\n"
> "AND i.indrelid = c2.oid AND i.indexrelid = c.oid\n",
> _("Table"));
> else
> appendPQExpBuffer(&buf,
> ! "\nFROM pg_class c, pg_user u\n"
> ! "WHERE c.relowner = u.usesysid\n");
>
> appendPQExpBuffer(&buf, "AND c.relkind IN (");
> if (showTables)
> --- 1035,1050 ----
> if (showIndexes)
> appendPQExpBuffer(&buf,
> ",\n c2.relname as \"%s\""
> ! "\nFROM pg_class c, pg_class c2, pg_index i, pg_user u, pg_namespace n\n"
> "WHERE c.relowner = u.usesysid\n"
> + "AND c.relnamespace = n.oid\n"
> "AND i.indrelid = c2.oid AND i.indexrelid = c.oid\n",
> _("Table"));
> else
> appendPQExpBuffer(&buf,
> ! "\nFROM pg_class c, pg_user u, pg_namespace n\n"
> ! "WHERE c.relowner = u.usesysid\n"
> ! "AND c.relnamespace = n.oid\n");
>
> appendPQExpBuffer(&buf, "AND c.relkind IN (");
> if (showTables)
> ***************
> *** 1058,1071 ****
> appendPQExpBuffer(&buf, ")\n");
>
> if (showSystem)
> ! appendPQExpBuffer(&buf, " AND c.relname ~ '^pg_'\n");
> else
> ! appendPQExpBuffer(&buf, " AND c.relname !~ '^pg_'\n");
>
> if (name)
> appendPQExpBuffer(&buf, " AND c.relname ~ '^%s'\n", name);
>
> ! appendPQExpBuffer(&buf, "ORDER BY 1;");
>
> res = PSQLexec(buf.data);
> termPQExpBuffer(&buf);
> --- 1061,1074 ----
> appendPQExpBuffer(&buf, ")\n");
>
> if (showSystem)
> ! appendPQExpBuffer(&buf, " AND n.nspname ~ '^pg_'\n");
> else
> ! appendPQExpBuffer(&buf, " AND n.nspname !~ '^pg_'\n");
>
> if (name)
> appendPQExpBuffer(&buf, " AND c.relname ~ '^%s'\n", name);
>
> ! appendPQExpBuffer(&buf, "ORDER BY 2,1;");
>
> res = PSQLexec(buf.data);
> termPQExpBuffer(&buf);
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
--
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. --------------------------------------------------------------------------- Joe Conway wrote: > I can't remember who said they were working on schema related psql > changes, but I kept finding myself wishing I could see what schema a > table or view exists in when I use \dt, \dv, etc. So, here is a patch > which does just that. > > It sorts on "Schema" first, and "Name" second. > > It also changes the test for system objects to key off the namespace > name starting with 'pg_' instead of the object name. > > Sample output: > > test=# create schema testschema; > CREATE SCHEMA > test=# create view testschema.ts_view as select 1; > CREATE VIEW > test=# \dv > List of relations > Name | Schema | Type | Owner > --------------------+------------+------+---------- > __testpassbyval | public | view | postgres > fooview | public | view | postgres > master_pg_proc | public | view | postgres > rmt_pg_proc | public | view | postgres > vw_dblink_get_pkey | public | view | postgres > vw_dblink_replace | public | view | postgres > ts_view | testschema | view | postgres > (7 rows) > > If there are no objections, please apply. -- 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 can't remember who said they were working on schema related psql > changes That would be me :) > It also changes the test for system objects to key off the namespace > name starting with 'pg_' instead of the object name. I *think* you want to make it more specific and look for items in the pg_catalog namespace, since this is where the system objects now live. My own patch is pretty much complete - anyone have other feedback on it? It appears as though current_schemas() is not quite finished - any ideas on when this will be done? Once it accepts a boolean arg and returns system schemas, and I can figure out a way to say "WHERE nspname in current_schemas();" my psql patch should be complete. (and the ** operator does not seem to work with current_schemas() either). Currently, it does it's own schema search, which is better than nothing until I can integrate current_schemas in there. Greg Sabino Mullane greg@turnstep.com PGP Key: 0x14964AC8 200207200933 -----BEGIN PGP SIGNATURE----- Comment: http://www.turnstep.com/pgp.html iD8DBQE9OWeCvJuQZxSWSsgRArGvAKCcV127HpPZb8LC8kBZ2G8lUfMv6QCeNieu exnojwcicrUpc1pI0eCOWRU= =+ZUG -----END PGP SIGNATURE-----
"Greg Sabino Mullane" <greg@turnstep.com> writes:
>> It also changes the test for system objects to key off the namespace
>> name starting with 'pg_' instead of the object name.
> I *think* you want to make it more specific and look for items in the
> pg_catalog namespace, since this is where the system objects now live.
Well, you definitely want to ignore pg_toast, and I'd think you want to
ignore pg_temp_NNN (except perhaps your own), so Joe's approach is more
nearly right.
> My own patch is pretty much complete - anyone have other feedback on it?
I had looked at it and decided it wasn't ready for prime time, though
I must confess I don't recall why. I thought you were off doing more
work on it.
> It appears as though current_schemas() is not quite finished - any
> ideas on when this will be done? Once it accepts a boolean arg
It's done that for some time. However, "nsp in current_schemas" isn't
going to solve psql's problem anyway, since it ignores the fundamental
issue of visibility: just because an object is in your search path does
not mean you can see it. It might be masked by a similarly-named object
earlier in the path.
What I suspect we will need to do is export namespace.c's RelationIsVisible
and friends as SQL functions. Then psql's commands could include
"where relation_is_visible(pg_class.oid)" to filter the output.
This still begs the question of exactly how psql wildcard patterns
should interact with schemas. Does '.' become a special (and not
wildcardable) character in such patterns? Do we allow wildcards
in the schema part? If we do, then "\d *.*" would be different
from "\d *" (the former shows everything, the latter only what is
visible in your search path). I'm not sure if this is useful or
merely confusing.
regards, tom lane
Joe Conway <mail@joeconway.com> writes:
> It sorts on "Schema" first, and "Name" second.
> Sample output:
> List of relations
> Name | Schema | Type | Owner
> --------------------+------------+------+----------
> __testpassbyval | public | view | postgres
> fooview | public | view | postgres
> master_pg_proc | public | view | postgres
> rmt_pg_proc | public | view | postgres
> vw_dblink_get_pkey | public | view | postgres
> vw_dblink_replace | public | view | postgres
> ts_view | testschema | view | postgres
It seems rather odd and confusing that the sort order takes the second
column as the major key. I think that the sort ordering should be
column 1 then 2. That means either putting the schema column first,
or keeping this column ordering and sorting on relname before schema.
I lean towards the first choice but can see a case for the second.
regards, tom lane
Tom Lane wrote: > Joe Conway <mail@joeconway.com> writes: > >>It sorts on "Schema" first, and "Name" second. > > >>Sample output: >> List of relations >> Name | Schema | Type | Owner >>--------------------+------------+------+---------- >> __testpassbyval | public | view | postgres >> fooview | public | view | postgres >> master_pg_proc | public | view | postgres >> rmt_pg_proc | public | view | postgres >> vw_dblink_get_pkey | public | view | postgres >> vw_dblink_replace | public | view | postgres >> ts_view | testschema | view | postgres > > > It seems rather odd and confusing that the sort order takes the second > column as the major key. I think that the sort ordering should be > column 1 then 2. That means either putting the schema column first, > or keeping this column ordering and sorting on relname before schema. > I lean towards the first choice but can see a case for the second. > > regards, tom lane OK -- I see your point on the second option, but I also lean towards the first. I'll change it to schema column first, order by column 1 then 2. Joe
Greg Sabino Mullane wrote: > That would be me :) > OK. I'll submit another small patch to swap the columns as Tom requested, but I'll leave the question as to which exact catalogs/relations should be visible to you :-) Regards, Joe
Tom Lane wrote:
> It seems rather odd and confusing that the sort order takes the second
> column as the major key.
Maybe I'm a rather odd and confused kind of guy ;-)
> I think that the sort ordering should be
> column 1 then 2. That means either putting the schema column first,
> or keeping this column ordering and sorting on relname before schema.
> I lean towards the first choice but can see a case for the second.
Here's another small patch, to implement Tom's first choice above. I
looked at re-adding your own "pg_temp_n" schema back into the results.
But I don't see a way to determine your own BackendId unless we add a
builtin function to expose MyBackendId as a user callable function.
Should we do this, or did I just miss something?
If there are no objections, please apply.
Thanks,
Joe
Index: src/bin/psql/describe.c
===================================================================
RCS file: /opt/src/cvs/pgsql/src/bin/psql/describe.c,v
retrieving revision 1.56
diff -c -r1.56 describe.c
*** src/bin/psql/describe.c 20 Jul 2002 05:57:31 -0000 1.56
--- src/bin/psql/describe.c 20 Jul 2002 20:57:16 -0000
***************
*** 1021,1031 ****
initPQExpBuffer(&buf);
printfPQExpBuffer(&buf,
! "SELECT c.relname as \"%s\",\n"
! " n.nspname as \"%s\",\n"
" CASE c.relkind WHEN 'r' THEN '%s' WHEN 'v' THEN '%s' WHEN 'i' THEN '%s' WHEN 'S' THEN '%s' WHEN 's'
THEN'%s' END as \"%s\",\n"
" u.usename as \"%s\"",
! _("Name"), _("Schema"), _("table"), _("view"), _("index"), _("sequence"),
_("special"), _("Type"), _("Owner"));
if (desc)
--- 1021,1031 ----
initPQExpBuffer(&buf);
printfPQExpBuffer(&buf,
! "SELECT n.nspname as \"%s\",\n"
! " c.relname as \"%s\",\n"
" CASE c.relkind WHEN 'r' THEN '%s' WHEN 'v' THEN '%s' WHEN 'i' THEN '%s' WHEN 'S' THEN '%s' WHEN 's'
THEN'%s' END as \"%s\",\n"
" u.usename as \"%s\"",
! _("Schema"), _("Name"), _("table"), _("view"), _("index"), _("sequence"),
_("special"), _("Type"), _("Owner"));
if (desc)
***************
*** 1068,1074 ****
if (name)
appendPQExpBuffer(&buf, " AND c.relname ~ '^%s'\n", name);
! appendPQExpBuffer(&buf, "ORDER BY 2,1;");
res = PSQLexec(buf.data);
termPQExpBuffer(&buf);
--- 1068,1074 ----
if (name)
appendPQExpBuffer(&buf, " AND c.relname ~ '^%s'\n", name);
! appendPQExpBuffer(&buf, "ORDER BY 1,2;");
res = PSQLexec(buf.data);
termPQExpBuffer(&buf);
Joe Conway <mail@joeconway.com> writes:
> I looked at re-adding your own "pg_temp_n" schema back into the results.
> But I don't see a way to determine your own BackendId unless we add a
> builtin function to expose MyBackendId as a user callable function.
> Should we do this, or did I just miss something?
I don't think it's important. Real soon now we will be changing psql's
queries to take visibility into account directly (cf my comments to
Greg Mullane yesterday). That should take care of making one's temp
tables visible, I think; and if not we can address the issue at that
time.
regards, tom lane
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 > It's done that for some time. However, "nsp in current_schemas" isn't > going to solve psql's problem anyway, since it ignores the fundamental > issue of visibility: just because an object is in your search path does > not mean you can see it. It might be masked by a similarly-named object > earlier in the path. I think that can be solved by doing an ORDER BY the current_schemas() results and a LIMIT 1. The bigger problem is the above-mentioned 'nsp in current_schemas'. I cannot think of an easy way to do this. In other words, the equivalent of "select * from pg_class where relnamespace in (current_schemas())" Is there a function in the core to check an array? The *= construct in contrib/array would be nice, but it cannot be used to compile psql. My other options are to create a function, create some internal SQL calls, or do some really ugly C parsing of the results of "select current_schemas(true)." I am leaning towards the latter, but a nice internal function that breaks an array into a table would be great. Greg Sabino Mullane greg@turnstep.com PGP Key: 0x14964AC8 200207242119 -----BEGIN PGP SIGNATURE----- Comment: http://www.turnstep.com/pgp.html iD8DBQE9P1LJvJuQZxSWSsgRAoG3AJ9Daapv2Hixx0JwE3s6Fn2v9sqU+QCghSpc gPhA2xG2bRH1kXhgnLSm58Y= =0s2B -----END PGP SIGNATURE-----
Perhaps current_schemas would be easier to use if it was changed to return one schema per row returned? Need a little uglyness around it until attributes are re-worked. On Wed, 2002-07-24 at 21:23, Greg Sabino Mullane wrote: > > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > > > It's done that for some time. However, "nsp in current_schemas" isn't > > going to solve psql's problem anyway, since it ignores the fundamental > > issue of visibility: just because an object is in your search path does > > not mean you can see it. It might be masked by a similarly-named object > > earlier in the path. > > I think that can be solved by doing an ORDER BY the current_schemas() > results and a LIMIT 1. The bigger problem is the above-mentioned 'nsp in > current_schemas'. I cannot think of an easy way to do this. In other > words, the equivalent of "select * from pg_class where relnamespace > in (current_schemas())" Is there a function in the core to check > an array? The *= construct in contrib/array would be nice, but it > cannot be used to compile psql. My other options are to create a > function, create some internal SQL calls, or do some really ugly > C parsing of the results of "select current_schemas(true)." I > am leaning towards the latter, but a nice internal function that > breaks an array into a table would be great. > > Greg Sabino Mullane greg@turnstep.com > PGP Key: 0x14964AC8 200207242119 > > -----BEGIN PGP SIGNATURE----- > Comment: http://www.turnstep.com/pgp.html > > iD8DBQE9P1LJvJuQZxSWSsgRAoG3AJ9Daapv2Hixx0JwE3s6Fn2v9sqU+QCghSpc > gPhA2xG2bRH1kXhgnLSm58Y= > =0s2B > -----END PGP SIGNATURE----- > > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html >
Rod Taylor <rbt@zort.ca> writes:
> Perhaps current_schemas would be easier to use if it was changed to
> return one schema per row returned?
No. If it returned a rowset then the ordering would be lost, in a
logical sense at least. Remember that SQL does not believe row ordering
is semantically significant.
As I keep saying, current_schemas is not the solution to Greg's problem.
We need to expose additional (already existing) backend functionality
in the form of SQL functions that check object visibility.
regards, tom lane
I just added pg_stat_get_backend_mypid() that returns the pid. Does
that help you?
---------------------------------------------------------------------------
Joe Conway wrote:
> Tom Lane wrote:
> > It seems rather odd and confusing that the sort order takes the second
> > column as the major key.
>
> Maybe I'm a rather odd and confused kind of guy ;-)
>
> > I think that the sort ordering should be
> > column 1 then 2. That means either putting the schema column first,
> > or keeping this column ordering and sorting on relname before schema.
> > I lean towards the first choice but can see a case for the second.
>
> Here's another small patch, to implement Tom's first choice above. I
> looked at re-adding your own "pg_temp_n" schema back into the results.
> But I don't see a way to determine your own BackendId unless we add a
> builtin function to expose MyBackendId as a user callable function.
> Should we do this, or did I just miss something?
>
> If there are no objections, please apply.
>
> Thanks,
>
> Joe
> Index: src/bin/psql/describe.c
> ===================================================================
> RCS file: /opt/src/cvs/pgsql/src/bin/psql/describe.c,v
> retrieving revision 1.56
> diff -c -r1.56 describe.c
> *** src/bin/psql/describe.c 20 Jul 2002 05:57:31 -0000 1.56
> --- src/bin/psql/describe.c 20 Jul 2002 20:57:16 -0000
> ***************
> *** 1021,1031 ****
> initPQExpBuffer(&buf);
>
> printfPQExpBuffer(&buf,
> ! "SELECT c.relname as \"%s\",\n"
> ! " n.nspname as \"%s\",\n"
> " CASE c.relkind WHEN 'r' THEN '%s' WHEN 'v' THEN '%s' WHEN 'i' THEN '%s' WHEN 'S' THEN '%s' WHEN 's'
THEN'%s' END as \"%s\",\n"
> " u.usename as \"%s\"",
> ! _("Name"), _("Schema"), _("table"), _("view"), _("index"), _("sequence"),
> _("special"), _("Type"), _("Owner"));
>
> if (desc)
> --- 1021,1031 ----
> initPQExpBuffer(&buf);
>
> printfPQExpBuffer(&buf,
> ! "SELECT n.nspname as \"%s\",\n"
> ! " c.relname as \"%s\",\n"
> " CASE c.relkind WHEN 'r' THEN '%s' WHEN 'v' THEN '%s' WHEN 'i' THEN '%s' WHEN 'S' THEN '%s' WHEN 's'
THEN'%s' END as \"%s\",\n"
> " u.usename as \"%s\"",
> ! _("Schema"), _("Name"), _("table"), _("view"), _("index"), _("sequence"),
> _("special"), _("Type"), _("Owner"));
>
> if (desc)
> ***************
> *** 1068,1074 ****
> if (name)
> appendPQExpBuffer(&buf, " AND c.relname ~ '^%s'\n", name);
>
> ! appendPQExpBuffer(&buf, "ORDER BY 2,1;");
>
> res = PSQLexec(buf.data);
> termPQExpBuffer(&buf);
> --- 1068,1074 ----
> if (name)
> appendPQExpBuffer(&buf, " AND c.relname ~ '^%s'\n", name);
>
> ! appendPQExpBuffer(&buf, "ORDER BY 1,2;");
>
> res = PSQLexec(buf.data);
> termPQExpBuffer(&buf);
>
> ---------------------------(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
Bruce Momjian wrote: > I just added pg_stat_get_backend_mypid() that returns the pid. Does > that help you? Sorry for the slow response. Backend pid doesn't solve this issue, because the temp schema name for a logged-in user is based on MyBackendId, not pid, i.e. your temp schema name is something like pg_temp_1 when MyBackendId = 1, etc. It would be easy enough to write a function exposing this, the question is whether it is desirable to expose it? Joe
Joe Conway wrote: > Bruce Momjian wrote: > > I just added pg_stat_get_backend_mypid() that returns the pid. Does > > that help you? > > Sorry for the slow response. > > Backend pid doesn't solve this issue, because the temp schema name for a > logged-in user is based on MyBackendId, not pid, i.e. your temp schema > name is something like pg_temp_1 when MyBackendId = 1, etc. It would be > easy enough to write a function exposing this, the question is whether > it is desirable to expose it? I see pg_stat_get_backend_idset() and pg_stat_get_backend_pid(), seems we should report the slot of the current backend. -- 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
>> Backend pid doesn't solve this issue, because the temp schema name for a
>> logged-in user is based on MyBackendId, not pid, i.e. your temp schema
>> name is something like pg_temp_1 when MyBackendId = 1, etc. It would be
>> easy enough to write a function exposing this, the question is whether
>> it is desirable to expose it?
I see no reason to do so at all.
What you want is not the BackendId; what you want is the temp schema
name, and current_schemas() can tell you that.
But so far I haven't seen any convincing argument why any client query
would really want to look at current_schemas(), either. What you
actually want to know about is visibility, and computing that from
current_schemas() is quite an expensive proposition. I think what
we need to expose is RelationIsVisible and friends, not BackendId.
regards, tom lane
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 > But so far I haven't seen any convincing argument why any client query > would really want to look at current_schemas(), either. What you > actually want to know about is visibility, and computing that from > current_schemas() is quite an expensive proposition. I think what > we need to expose is RelationIsVisible and friends, not BackendId. I need something other than RelationIsVisible for psql for the case when someone says "\d foo" - I need to be able to decide which "foo" table I should display: pg_temp_1.foo, public.foo, greg.foo, etc... which schemas to search, and the order to do the searching, is why some sort of access to current_schemas() is needed. Greg Sabino Mullane greg@turnstep.com PGP Key: 0x14964AC8 200208021010 -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.0.7 (GNU/Linux) iD8DBQE9SpfEvJuQZxSWSsgRAmw/AJ0RZ2vbx2zDQlFrLwRyVkZ1kvQB0ACgwyqT 9xugxhqatGJrrDWzzfez114= =GZtD -----END PGP SIGNATURE-----
greg@turnstep.com writes:
> I need something other than RelationIsVisible for psql for the case
> when someone says "\d foo" - I need to be able to decide which
> "foo" table I should display: pg_temp_1.foo, public.foo, greg.foo,
Au contraire, RelationIsVisible is *exactly* what you need. I'm
envisioning that where we currently have, say,
select ... from pg_class p, ...
where relname like 'foo%' and ...
we'd write something like
select ... from pg_class p, ...
where relname like 'foo%' and pg_relation_is_visible(p.oid) and ...
An alternative approach is
select ... from pg_class p, ...
where p.oid = 'foo'::regclass
which works today and is probably more efficient, but (a) it does not
lend itself to wildcard searches, and (b) you have to be prepared to
deal with an ERROR instead of zero rows out if foo doesn't exist.
What remains to be thought about is how schemas ought to interact
with wildcard patterns --- in particular, what should you say in
psql backslash commands when you want to access something that is
*not* in your current search path? Can you wildcard the schema part?
And so forth. But visibility stops being an issue as soon as the
user tells you which schema to look in.
> etc... which schemas to search, and the order to do the searching,
> is why some sort of access to current_schemas() is needed.
current_schemas is provided for those who insist on reimplementing the
backend RelationIsVisible functionality for themselves. (pgAdmin falls
in that category, IIRC.) I don't think we want to go that route in
psql, however. It's not simple to do at the SQL level.
regards, tom lane
Tom Lane wrote: > >> Backend pid doesn't solve this issue, because the temp schema name for a > >> logged-in user is based on MyBackendId, not pid, i.e. your temp schema > >> name is something like pg_temp_1 when MyBackendId = 1, etc. It would be > >> easy enough to write a function exposing this, the question is whether > >> it is desirable to expose it? > > I see no reason to do so at all. > > What you want is not the BackendId; what you want is the temp schema > name, and current_schemas() can tell you that. > > But so far I haven't seen any convincing argument why any client query > would really want to look at current_schemas(), either. What you > actually want to know about is visibility, and computing that from > current_schemas() is quite an expensive proposition. I think what > we need to expose is RelationIsVisible and friends, not BackendId. Well, if we are going to reach 7.3, we had better figure out what we are doing in this area. Let's start talking. -- 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
Your patch has been added to the PostgreSQL unapplied patches list at:
http://candle.pha.pa.us/cgi-bin/pgpatches
I will try to apply it within the next 48 hours.
---------------------------------------------------------------------------
Joe Conway wrote:
> Tom Lane wrote:
> > It seems rather odd and confusing that the sort order takes the second
> > column as the major key.
>
> Maybe I'm a rather odd and confused kind of guy ;-)
>
> > I think that the sort ordering should be
> > column 1 then 2. That means either putting the schema column first,
> > or keeping this column ordering and sorting on relname before schema.
> > I lean towards the first choice but can see a case for the second.
>
> Here's another small patch, to implement Tom's first choice above. I
> looked at re-adding your own "pg_temp_n" schema back into the results.
> But I don't see a way to determine your own BackendId unless we add a
> builtin function to expose MyBackendId as a user callable function.
> Should we do this, or did I just miss something?
>
> If there are no objections, please apply.
>
> Thanks,
>
> Joe
> Index: src/bin/psql/describe.c
> ===================================================================
> RCS file: /opt/src/cvs/pgsql/src/bin/psql/describe.c,v
> retrieving revision 1.56
> diff -c -r1.56 describe.c
> *** src/bin/psql/describe.c 20 Jul 2002 05:57:31 -0000 1.56
> --- src/bin/psql/describe.c 20 Jul 2002 20:57:16 -0000
> ***************
> *** 1021,1031 ****
> initPQExpBuffer(&buf);
>
> printfPQExpBuffer(&buf,
> ! "SELECT c.relname as \"%s\",\n"
> ! " n.nspname as \"%s\",\n"
> " CASE c.relkind WHEN 'r' THEN '%s' WHEN 'v' THEN '%s' WHEN 'i' THEN '%s' WHEN 'S' THEN '%s' WHEN 's'
THEN'%s' END as \"%s\",\n"
> " u.usename as \"%s\"",
> ! _("Name"), _("Schema"), _("table"), _("view"), _("index"), _("sequence"),
> _("special"), _("Type"), _("Owner"));
>
> if (desc)
> --- 1021,1031 ----
> initPQExpBuffer(&buf);
>
> printfPQExpBuffer(&buf,
> ! "SELECT n.nspname as \"%s\",\n"
> ! " c.relname as \"%s\",\n"
> " CASE c.relkind WHEN 'r' THEN '%s' WHEN 'v' THEN '%s' WHEN 'i' THEN '%s' WHEN 'S' THEN '%s' WHEN 's'
THEN'%s' END as \"%s\",\n"
> " u.usename as \"%s\"",
> ! _("Schema"), _("Name"), _("table"), _("view"), _("index"), _("sequence"),
> _("special"), _("Type"), _("Owner"));
>
> if (desc)
> ***************
> *** 1068,1074 ****
> if (name)
> appendPQExpBuffer(&buf, " AND c.relname ~ '^%s'\n", name);
>
> ! appendPQExpBuffer(&buf, "ORDER BY 2,1;");
>
> res = PSQLexec(buf.data);
> termPQExpBuffer(&buf);
> --- 1068,1074 ----
> if (name)
> appendPQExpBuffer(&buf, " AND c.relname ~ '^%s'\n", name);
>
> ! appendPQExpBuffer(&buf, "ORDER BY 1,2;");
>
> res = PSQLexec(buf.data);
> termPQExpBuffer(&buf);
>
> ---------------------------(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
It appears this patch was already applied, I think by Tom Lane.
Thanks.
---------------------------------------------------------------------------
Joe Conway wrote:
> Tom Lane wrote:
> > It seems rather odd and confusing that the sort order takes the second
> > column as the major key.
>
> Maybe I'm a rather odd and confused kind of guy ;-)
>
> > I think that the sort ordering should be
> > column 1 then 2. That means either putting the schema column first,
> > or keeping this column ordering and sorting on relname before schema.
> > I lean towards the first choice but can see a case for the second.
>
> Here's another small patch, to implement Tom's first choice above. I
> looked at re-adding your own "pg_temp_n" schema back into the results.
> But I don't see a way to determine your own BackendId unless we add a
> builtin function to expose MyBackendId as a user callable function.
> Should we do this, or did I just miss something?
>
> If there are no objections, please apply.
>
> Thanks,
>
> Joe
> Index: src/bin/psql/describe.c
> ===================================================================
> RCS file: /opt/src/cvs/pgsql/src/bin/psql/describe.c,v
> retrieving revision 1.56
> diff -c -r1.56 describe.c
> *** src/bin/psql/describe.c 20 Jul 2002 05:57:31 -0000 1.56
> --- src/bin/psql/describe.c 20 Jul 2002 20:57:16 -0000
> ***************
> *** 1021,1031 ****
> initPQExpBuffer(&buf);
>
> printfPQExpBuffer(&buf,
> ! "SELECT c.relname as \"%s\",\n"
> ! " n.nspname as \"%s\",\n"
> " CASE c.relkind WHEN 'r' THEN '%s' WHEN 'v' THEN '%s' WHEN 'i' THEN '%s' WHEN 'S' THEN '%s' WHEN 's'
THEN'%s' END as \"%s\",\n"
> " u.usename as \"%s\"",
> ! _("Name"), _("Schema"), _("table"), _("view"), _("index"), _("sequence"),
> _("special"), _("Type"), _("Owner"));
>
> if (desc)
> --- 1021,1031 ----
> initPQExpBuffer(&buf);
>
> printfPQExpBuffer(&buf,
> ! "SELECT n.nspname as \"%s\",\n"
> ! " c.relname as \"%s\",\n"
> " CASE c.relkind WHEN 'r' THEN '%s' WHEN 'v' THEN '%s' WHEN 'i' THEN '%s' WHEN 'S' THEN '%s' WHEN 's'
THEN'%s' END as \"%s\",\n"
> " u.usename as \"%s\"",
> ! _("Schema"), _("Name"), _("table"), _("view"), _("index"), _("sequence"),
> _("special"), _("Type"), _("Owner"));
>
> if (desc)
> ***************
> *** 1068,1074 ****
> if (name)
> appendPQExpBuffer(&buf, " AND c.relname ~ '^%s'\n", name);
>
> ! appendPQExpBuffer(&buf, "ORDER BY 2,1;");
>
> res = PSQLexec(buf.data);
> termPQExpBuffer(&buf);
> --- 1068,1074 ----
> if (name)
> appendPQExpBuffer(&buf, " AND c.relname ~ '^%s'\n", name);
>
> ! appendPQExpBuffer(&buf, "ORDER BY 1,2;");
>
> res = PSQLexec(buf.data);
> termPQExpBuffer(&buf);
>
> ---------------------------(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) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073