Thread: 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