Обсуждение: Re: \dt doesn't show all relations in user's schemas (8.4.2)
----- "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 inmore 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
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 namesin 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>
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/