Обсуждение: [ADMIN] behavior of \dt and schemas

Поиск
Список
Период
Сортировка

[ADMIN] behavior of \dt and schemas

От
John Scalia
Дата:
Hi all,

I've come across some rather non-intuitive behavior with \dt output. Say I have schema A and schema B, both with an identical mytable in them. If I set the search_path to A; and type \dt, I see

             List of relations
 Schema      |  Name   | Type   | Owner
-------------------------------------------------------
 a                | mytable | table   | postgres

So far so good, and I get a similar output If I just set the search_path to B, only now it properly shows the schema as b. However, if I set the search_path to a, b; and enter \dt, I only see the tables from the first schema "a", like the above. and not the second schema. I would have expected:

               List of relations
Schema      |  Name   | Type    | Owner
-------------------------------------------------------
 a                | mytable | table   | postgres
 b                | mytable | table   | postgres

So, to me this is somewhat non-intuitive behavior, but maybe I'm all wet here. Shouldn't \dt report all the tables it can see with the search_path set to some value? And btw, this is was the behavior on 9.4.10, so if it's changed in more recent versions, I haven't tested there yet.

Flame suit on,
Jay

Re: [ADMIN] behavior of \dt and schemas

От
Stephen Frost
Дата:
John,

* John Scalia (jayknowsunix@gmail.com) wrote:
> So far so good, and I get a similar output If I just set the search_path to
> B, only now it properly shows the schema as b. However, if I set the
> search_path to a, b; and enter \dt, I only see the tables from the first
> schema "a", like the above. and not the second schema. I would have
> expected:

That's intentional as an unqualified object reference would pick up the
object from the schema which shows up first in your search_path.

> So, to me this is somewhat non-intuitive behavior, but maybe I'm all wet
> here. Shouldn't \dt report all the tables it can see with the search_path
> set to some value? And btw, this is was the behavior on 9.4.10, so if it's
> changed in more recent versions, I haven't tested there yet.

It's not changed.

Thanks!

Stephen

Вложения

Re: [ADMIN] behavior of \dt and schemas

От
"David G. Johnston"
Дата:


On Friday, January 20, 2017, John Scalia <jayknowsunix@gmail.com> wrote:

So, to me this is somewhat non-intuitive behavior, but maybe I'm all wet here. Shouldn't \dt report all the tables it can see with the search_path set to some value? And btw, this is was the behavior on 9.4.10, so if it's changed in more recent versions, I haven't tested there yet.


It shows the definition of the table you would be referencing if you used that name in a query.  This seems like a useful behavior.

David J.

Re: [ADMIN] behavior of \dt and schemas

От
Tom Lane
Дата:
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Friday, January 20, 2017, John Scalia <jayknowsunix@gmail.com> wrote:
>> So, to me this is somewhat non-intuitive behavior, but maybe I'm all wet
>> here. Shouldn't \dt report all the tables it can see with the search_path
>> set to some value? And btw, this is was the behavior on 9.4.10, so if it's
>> changed in more recent versions, I haven't tested there yet.

> It shows the definition of the table you would be referencing if you used
> that name in a query.  This seems like a useful behavior.

Right --- according to our normal terminology, b.mytable is *not* visible,
because it is masked by a.mytable being ahead of it in the search path.
You'd have to write a qualified name to get at b.mytable.

You can write, eg, "\dt *.mytable" or "\dt *.*" if you would like it to
show tables that are not visible according to this rule.  Without a
dot in the pattern, \dt shows only visible tables, ie only the ones
you could name without putting a dot in the name.

            regards, tom lane


Re: [ADMIN] behavior of \dt and schemas

От
John Scalia
Дата:
Understood, but I would have suspected that the search_path value would have been expanded to report all the tables visible under each schema, and not mask any. It just surprised me a little.

On Fri, Jan 20, 2017 at 10:37 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Friday, January 20, 2017, John Scalia <jayknowsunix@gmail.com> wrote:
>> So, to me this is somewhat non-intuitive behavior, but maybe I'm all wet
>> here. Shouldn't \dt report all the tables it can see with the search_path
>> set to some value? And btw, this is was the behavior on 9.4.10, so if it's
>> changed in more recent versions, I haven't tested there yet.

> It shows the definition of the table you would be referencing if you used
> that name in a query.  This seems like a useful behavior.

Right --- according to our normal terminology, b.mytable is *not* visible,
because it is masked by a.mytable being ahead of it in the search path.
You'd have to write a qualified name to get at b.mytable.

You can write, eg, "\dt *.mytable" or "\dt *.*" if you would like it to
show tables that are not visible according to this rule.  Without a
dot in the pattern, \dt shows only visible tables, ie only the ones
you could name without putting a dot in the name.

                        regards, tom lane

Re: [ADMIN] behavior of \dt and schemas

От
John Scalia
Дата:
Especially as I just tested setting the search_path to both schemas and then created another new and uniquely named table under the second schema so this time \dt showed just the duplicated tables from the first schema, but also showed the uniquely named table from the second. That latter part would have been my expected behavior. I expected you'd all disagree with me, but it still seems a bit non-intuitive to me.

On Fri, Jan 20, 2017 at 11:09 AM, John Scalia <jayknowsunix@gmail.com> wrote:
Understood, but I would have suspected that the search_path value would have been expanded to report all the tables visible under each schema, and not mask any. It just surprised me a little.

On Fri, Jan 20, 2017 at 10:37 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Friday, January 20, 2017, John Scalia <jayknowsunix@gmail.com> wrote:
>> So, to me this is somewhat non-intuitive behavior, but maybe I'm all wet
>> here. Shouldn't \dt report all the tables it can see with the search_path
>> set to some value? And btw, this is was the behavior on 9.4.10, so if it's
>> changed in more recent versions, I haven't tested there yet.

> It shows the definition of the table you would be referencing if you used
> that name in a query.  This seems like a useful behavior.

Right --- according to our normal terminology, b.mytable is *not* visible,
because it is masked by a.mytable being ahead of it in the search path.
You'd have to write a qualified name to get at b.mytable.

You can write, eg, "\dt *.mytable" or "\dt *.*" if you would like it to
show tables that are not visible according to this rule.  Without a
dot in the pattern, \dt shows only visible tables, ie only the ones
you could name without putting a dot in the name.

                        regards, tom lane