\dt doesn't show all relations in user's schemas (8.4.2)
Hello,
I am running PostgreSQL 8.4.2. For the testcase I have a database, a
user, and two schemas within the database. Two tables in those two
different schemas have the same name, but only on of those tables
shows up using the "\dt" command.
How-To-Repeat:
-- psql template1
CREATE ROLE testrole
NOSUPERUSER
NOCREATEDB
NOCREATEROLE
NOINHERIT
LOGIN
ENCRYPTED PASSWORD 'testrole'
;
CREATE DATABASE test
OWNER testrole
TEMPLATE template0
LC_COLLATE = 'de_DE.UTF-8'
LC_CTYPE = 'de_DE.UTF-8'
ENCODING 'UTF-8'
;
ALTER ROLE testrole SET search_path=schema1, schema2;
-- psql test testrole
show search_path;
-- search_path
-- ------------------
-- schema1, schema2
-- (1 row)
CREATE SCHEMA schema1;
CREATE SCHEMA schema2;
CREATE TABLE schema1.table1
(
field1 VARCHAR(10)
);
CREATE TABLE schema2.table1
(
field1 VARCHAR(10)
);
\dt
-- List of relations
-- Schema | Name | Type | Owner
-- ---------+--------+-------+----------
-- schema1 | table1 | table | testrole
-- (1 row)
-- Only one of the two relations is shown
CREATE TABLE schema2.table2
(
field1 VARCHAR(10)
);
\dt
-- List of relations
-- Schema | Name | Type | Owner
-- ---------+--------+-------+----------
-- schema1 | table1 | table | testrole
-- schema2 | table2 | table | testrole
-- (2 rows)
I think both table1 in schema1 and schema2 should show up in the \dt
listing, because the user has access to both tables, is the owner of
both tables and has the search_path set accordingly.
If I delete the "AND pg_catalog.pg_table_is_visible(c.oid)" from the
where clause of the SQL statement that is issued when using "\dt"
command, all the tables show up as expected (but I am unaware of the
possible side effects...):
Schema | Name | Type | Owner
---------+--------+-------+----------
schema1 | table1 | table | testrole
schema2 | table1 | table | testrole
schema2 | table2 | table | testrole
(3 rows)
Does it work as expected? Where is that behaviour explained? Is there
a command to show all the relations (/objects) a user has access to?
Best regards,
Ralph
2009/12/19 Ralph Graulich <ralph.graulich@t-online.de>
-- Only one of the two relations is shown
I would call it a bug. Reproduced here, on 8.4.2 and 8.3.8
--
Filip Rembiałkowski
JID,mailto:filip.rembialkowski@gmail.com
http://filip.rembialkowski.net/
----- "Filip Rembiałkowski" <plk.zuber@gmail.com> wrote:
2009/12/19 Ralph Graulich < ralph.graulich@t-online.de >
-- Only one of the two relations is shown
I would call it a bug. Reproduced here, on 8.4.2 and 8.3.8
Try \dt *.table1
Import Notes
Reply to msg id not found: 1184306009.4066751261430557175.JavaMail.root@sz0030a.emeryville.ca.mail.comcast.net | Resolved by subject fallback
According to the docs,
http://www.postgresql.org/docs/8.4/interactive/app-psql.html says in
part:
Whenever the pattern parameter is omitted completely, the \d commands
display all objects that are visible in the current schema search path
— this is equivalent to using the pattern *. To see all objects in the
database, use the pattern *.*.
So yeah, according to the documented expected behaviour it would be a bug.
2009/12/21 Filip Rembiałkowski <plk.zuber@gmail.com>:
2009/12/19 Ralph Graulich <ralph.graulich@t-online.de>
-- Only one of the two relations is shown
I would call it a bug. Reproduced here, on 8.4.2 and 8.3.8
--
Filip Rembiałkowski
JID,mailto:filip.rembialkowski@gmail.com
http://filip.rembialkowski.net/
--
When fascism comes to America, it will be intolerance sold as diversity.
2009/12/21 Adrian Klaver <aklaver@comcast.net>:
----- "Filip Rembiałkowski" <plk.zuber@gmail.com> wrote:
2009/12/19 Ralph Graulich < ralph.graulich@t-online.de >
-- Only one of the two relations is shown
I would call it a bug. Reproduced here, on 8.4.2 and 8.3.8
Try \dt *.table1
While that should work, suppose you have three schemas with the same
table, and your search path is set to look at two. \dt by itself
should only show the two in your search path, so it's not equivalent,
but it is handy...
----- "Scott Marlowe" <scott.marlowe@gmail.com> wrote:
2009/12/21 Adrian Klaver <aklaver@comcast.net>:
----- "Filip Rembiałkowski" <plk.zuber@gmail.com> wrote:
2009/12/19 Ralph Graulich < ralph.graulich@t-online.de >
-- Only one of the two relations is shown
I would call it a bug. Reproduced here, on 8.4.2 and 8.3.8
Try \dt *.table1
While that should work, suppose you have three schemas with the same
table, and your search path is set to look at two. \dt by itself
should only show the two in your search path, so it's not equivalent,
but it is handy...
Interested in a definitive answer to this as I understood that the below held and that in order to see identical names in more than one schema you needed to schema qualify the names or use wildcards.
http://www.postgresql.org/docs/8.4/static/runtime-config-client.html
When there are objects of identical names in different schemas, the one found first in the search path is used
Adrian Klaver
aklaver@comcast.net
Import Notes
Reply to msg id not found: 583431718.4084211261432886717.JavaMail.root@sz0030a.emeryville.ca.mail.comcast.net | Resolved by subject fallback
On Mon, Dec 21, 2009 at 3:06 PM, Adrian Klaver <aklaver@comcast.net> wrote:
----- "Scott Marlowe" <scott.marlowe@gmail.com> wrote:
2009/12/21 Adrian Klaver <aklaver@comcast.net>:
----- "Filip Rembiałkowski" <plk.zuber@gmail.com> wrote:
2009/12/19 Ralph Graulich < ralph.graulich@t-online.de >
-- Only one of the two relations is shown
I would call it a bug. Reproduced here, on 8.4.2 and 8.3.8
Try \dt *.table1
While that should work, suppose you have three schemas with the same
table, and your search path is set to look at two. \dt by itself
should only show the two in your search path, so it's not equivalent,
but it is handy...Interested in a definitive answer to this as I understood that the below held and that in order to see identical names in more than one schema you needed to schema qualify the names or use wildcards.
http://www.postgresql.org/docs/8.4/static/runtime-config-client.html
When there are objects of identical names in different schemas, the one found first in the search path is used
So, there are two parts of the docs that don't really agree with each
other completely. While this behaviour seems natural and expected
when running select, update, insert, alter and so on, it seems to be
somewhat iffy in the case of \dt. I'm not sure which is the right
behaviour. I'd lean towards listing the two tables with the same name
in different schemas with schema.tablename notation for each one so
you know which is which. I'm guessing that /dt is using search_path
and takes the first one only right now.
So, either the docs for \dt need fixing to reflect reality, or they're
right and psql \dt needs fixing.
Scott Marlowe <scott.marlowe@gmail.com> writes:
So, either the docs for \dt need fixing to reflect reality, or they're
right and psql \dt needs fixing.
The documentation says
Whenever the pattern parameter
is omitted completely, the \d commands display all objects
that are visible in the current schema search path -- this is
equivalent to using the pattern *.
To see all objects in the database, use the pattern *.*.
Seems clear enough to me.
regards, tom lane
On Monday 21 December 2009 3:42:10 pm Tom Lane wrote:
Scott Marlowe <scott.marlowe@gmail.com> writes:
So, either the docs for \dt need fixing to reflect reality, or they're
right and psql \dt needs fixing.The documentation says
Whenever the pattern parameter
is omitted completely, the \d commands display all objects
that are visible in the current schema search path -- this is
equivalent to using the pattern *.
To see all objects in the database, use the pattern *.*.Seems clear enough to me.
regards, tom lane
Well yes and no. The first couple of times I read this I was tripped up by
layout:
"the pattern *. To see all objects in the database, use the pattern *.*." I
took it to mean pattern '*.' until I realized it was '*' period. Taught me to
slow down when reading.
The other issue is what defines 'visible'. Previous investigations led me to:
"When there are objects of identical names in different schemas, the one found
first in the search path is used"
This is not obvious from the \d command explanation.
--
Adrian Klaver
aklaver@comcast.net
Adrian Klaver <aklaver@comcast.net> writes:
On Monday 21 December 2009 3:42:10 pm Tom Lane wrote:
Seems clear enough to me.
Well yes and no. The first couple of times I read this I was tripped
up by layout: "the pattern *. To see all objects in the database, use
the pattern *.*." I took it to mean pattern '*.' until I realized it
was '*' period. Taught me to slow down when reading.
Hmm. We're sort of relying on font differences there, but period tends
to look about the same in many fonts. Maybe it would help to rephrase
these sentences to keep the example patterns away from punctuation.
For instance
... this is equivalent to using * as the pattern.
To see all objects in the database, the pattern *.*
can be used.
Grammatically this is less nice (passive voice :-() but keeping the
patterns away from the periods might be worth it.
regards, tom lane
On Mon, Dec 21, 2009 at 4:42 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Scott Marlowe <scott.marlowe@gmail.com> writes:
So, either the docs for \dt need fixing to reflect reality, or they're
right and psql \dt needs fixing.The documentation says
Whenever the pattern parameter
is omitted completely, the \d commands display all objects
that are visible in the current schema search path -- this is
equivalent to using the pattern *.
To see all objects in the database, use the pattern *.*.Seems clear enough to me.
Then you should see BOTH tables with the same name in different
schemas, right? Cause the OP was saying that it picks only the first
one to display.
Scott Marlowe <scott.marlowe@gmail.com> writes:
Then you should see BOTH tables with the same name in different
schemas, right? Cause the OP was saying that it picks only the first
one to display.
Well, yes, because only the first one is visible. The second one is
masked by the first.
regards, tom lane
On Mon, Dec 21, 2009 at 6:10 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Scott Marlowe <scott.marlowe@gmail.com> writes:
Then you should see BOTH tables with the same name in different
schemas, right? Cause the OP was saying that it picks only the first
one to display.Well, yes, because only the first one is visible. The second one is
masked by the first.
But the docs say that ALL objects in the schema path will be shown.
So, my point stands, either the docs are wrong, or the behaviour is.
I'd think it's the docs.
Scott Marlowe <scott.marlowe@gmail.com> writes:
On Mon, Dec 21, 2009 at 6:10 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Well, yes, because only the first one is visible. �The second one is
masked by the first.
But the docs say that ALL objects in the schema path will be shown.
So, my point stands, either the docs are wrong, or the behaviour is.
I'd think it's the docs.
It says the *visible* objects will be shown. Ones that are masked
aren't any more visible than if they were in some other schema
altogether: either way, if you want to reference such an object in
a SQL statement, you'd have to schema-qualify it.
regards, tom lane
On Mon, Dec 21, 2009 at 6:14 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Scott Marlowe <scott.marlowe@gmail.com> writes:
On Mon, Dec 21, 2009 at 6:10 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Well, yes, because only the first one is visible. The second one is
masked by the first.But the docs say that ALL objects in the schema path will be shown.
So, my point stands, either the docs are wrong, or the behaviour is.
I'd think it's the docs.It says the *visible* objects will be shown. Ones that are masked
aren't any more visible than if they were in some other schema
altogether: either way, if you want to reference such an object in
a SQL statement, you'd have to schema-qualify it.
Ahh, right, it's about visibility. Hadn't caught that part.
On Monday 21 December 2009 5:17:49 pm Scott Marlowe wrote:
On Mon, Dec 21, 2009 at 6:14 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Scott Marlowe <scott.marlowe@gmail.com> writes:
On Mon, Dec 21, 2009 at 6:10 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Well, yes, because only the first one is visible. The second one is
masked by the first.But the docs say that ALL objects in the schema path will be shown.
So, my point stands, either the docs are wrong, or the behaviour is.
I'd think it's the docs.It says the *visible* objects will be shown. Ones that are masked
aren't any more visible than if they were in some other schema
altogether: either way, if you want to reference such an object in
a SQL statement, you'd have to schema-qualify it.Ahh, right, it's about visibility. Hadn't caught that part.
I think that is where the biggest misunderstanding lies. The problem is that
people new to the database may not fully understand what visible means in this
context. I know this tripped me up the first time I encountered the identical
name situation. I made the same assumption the OP did, the tables where in the
search_path and I had permissions on them so they should be 'visible'. It took
some digging around to find the correct answer. I not sure how the best way is
to clarify that in the psql documentation.
--
Adrian Klaver
aklaver@comcast.net
Adrian Klaver <aklaver@comcast.net> writes:
On Monday 21 December 2009 5:17:49 pm Scott Marlowe wrote:
Ahh, right, it's about visibility. Hadn't caught that part.
I think that is where the biggest misunderstanding lies.
On looking at the page some more, it strikes me that part of the problem
is that the info is buried at the bottom of the "Patterns" section,
below some extremely geeky details that few people would care about.
People probably stop reading before they even see it, let alone figure
out what "visible" means.
I wonder how we can rearrange this? I think the reasoning was that the
second through fourth paras explain the pattern language, and the
explanation about * and *.* doesn't make sense until you've read that.
We could just swap the fourth and fifth paras but that would break up
the pattern language definition in a strange way. Any ideas?
regards, tom lane
On Monday 21 December 2009 6:17:22 pm Tom Lane wrote:
Adrian Klaver <aklaver@comcast.net> writes:
On Monday 21 December 2009 5:17:49 pm Scott Marlowe wrote:
Ahh, right, it's about visibility. Hadn't caught that part.
I think that is where the biggest misunderstanding lies.
On looking at the page some more, it strikes me that part of the problem
is that the info is buried at the bottom of the "Patterns" section,
below some extremely geeky details that few people would care about.
People probably stop reading before they even see it, let alone figure
out what "visible" means.I wonder how we can rearrange this? I think the reasoning was that the
second through fourth paras explain the pattern language, and the
explanation about * and *.* doesn't make sense until you've read that.
We could just swap the fourth and fifth paras but that would break up
the pattern language definition in a strange way. Any ideas?regards, tom lane
Fifth para become second as follows :
Whenever the pattern parameter is omitted completely, the \d commands display
all objects that are visible in the current schema search path — this is
equivalent to using the pattern *. To see all objects in the database, use the
pattern *.*. For more detailed explanation see below.
--
Adrian Klaver
aklaver@comcast.net
2009/12/22 Scott Marlowe <scott.marlowe@gmail.com>
Ahh, right, it's about visibility. Hadn't caught that part.
While it appears that the docs and utility are logically consistent, this is
SO counter-intuitive.
I was just scanning the man page from top to bottom, looking for a way to
show all tables...
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index 8533c29..d704776 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -877,7 +877,7 @@ testdb=>
If <command>\d</command> is used without a
<replaceable class="parameter">pattern</replaceable> argument, it is
equivalent to <command>\dtvs</command> which will show a list of
- all tables, views, and sequences. This is purely a convenience
+ all visible tables, views, and sequences. This is purely a convenience
measure.
</para>
</note>--
Filip Rembiałkowski
JID,mailto:filip.rembialkowski@gmail.com
http://filip.rembialkowski.net/