Thread: Unexpected behavior from psql

This is a minor issue. Not really a problem for me at the moment, but
I wanted to understand if it was intended:

tjhart=# \d
No relations found.
tjhart=# \dn
         List of schemas
         Name        |  Owner
--------------------+----------
  information_schema | postgres
  pg_catalog         | postgres
  pg_toast           | postgres
  pg_toast_temp_1    | postgres
  public             | postgres
(5 rows)

tjhart=# create schema foo;
CREATE SCHEMA
tjhart=# create table foo.foo_table(bar text);
CREATE TABLE
tjhart=# create table public.foo_table(bar text);
CREATE TABLE
tjhart=# create table foo.bar_table(baz text);
CREATE TABLE
tjhart=# set search_path to foo, public;
SET
tjhart=# \d
           List of relations
  Schema |   Name    | Type  | Owner
--------+-----------+-------+--------
  foo    | bar_table | table | tjhart
  foo    | foo_table | table | tjhart
(2 rows)

tjhart=# set search_path to public, foo;
SET
tjhart=# \d
           List of relations
  Schema |   Name    | Type  | Owner
--------+-----------+-------+--------
  foo    | bar_table | table | tjhart
  public | foo_table | table | tjhart
(2 rows)
tjhart=# select * from public.foo_table;
  bar
-----
(0 rows)

tjhart=# select * from foo.foo_table;
  bar
-----
(0 rows)

As you can see, one 'foo_table' is obscured from the listing,
depending on the order of schemas listed in the search path. I have no
problem accessing either table.

Is this intended?

On Sun, 2009-01-11 at 15:33 -0600, Tim Hart wrote:
> tjhart=# set search_path to foo, public;
> SET
> tjhart=# \d
>            List of relations
>   Schema |   Name    | Type  | Owner
> --------+-----------+-------+--------
>   foo    | bar_table | table | tjhart
>   foo    | foo_table | table | tjhart
> (2 rows)
>
> tjhart=# set search_path to public, foo;
> SET
> tjhart=# \d
>            List of relations
>   Schema |   Name    | Type  | Owner
> --------+-----------+-------+--------
>   foo    | bar_table | table | tjhart
>   public | foo_table | table | tjhart
> (2 rows)

This behavior seems consistent to me. If you type an unqualified name
like:

SELECT * FROM foo_table;

You'll get the one from the first namespace listed in search_path. It
makes sense for "\d" to display only the tables that can be seen without
specifying a fully-qualified name.

Regards,
    Jeff Davis

On Jan 11, 2009, at 4:41 PM, Jeff Davis wrote:

> On Sun, 2009-01-11 at 15:33 -0600, Tim Hart wrote:
>> tjhart=# set search_path to foo, public;
>> SET
>> tjhart=# \d
>>           List of relations
>>  Schema |   Name    | Type  | Owner
>> --------+-----------+-------+--------
>>  foo    | bar_table | table | tjhart
>>  foo    | foo_table | table | tjhart
>> (2 rows)
>>
>> tjhart=# set search_path to public, foo;
>> SET
>> tjhart=# \d
>>           List of relations
>>  Schema |   Name    | Type  | Owner
>> --------+-----------+-------+--------
>>  foo    | bar_table | table | tjhart
>>  public | foo_table | table | tjhart
>> (2 rows)
>
> This behavior seems consistent to me. If you type an unqualified name
> like:
>
> SELECT * FROM foo_table;
>
> You'll get the one from the first namespace listed in search_path. It
> makes sense for "\d" to display only the tables that can be seen
> without
> specifying a fully-qualified name.
>
> Regards,
>     Jeff Davis


Would it be worthwhile to specify this in the documentation? One of
the sources of my confusion was the following statement:

http://www.postgresql.org/docs/8.3/interactive/app-psql.html:

\distvS
"... to obtain a listing of all the matching objects..."

I fully understand that 'foo' would need to be qualified when used in
sql, pl/pgsql, etc.

I understood the documentation to read that all tables, views, and
sequences in the search path would be listed, not just those unobscured.

Tim

On Sun, 2009-01-11 at 17:13 -0600, Tim Hart wrote:
> Would it be worthwhile to specify this in the documentation? One of
> the sources of my confusion was the following statement:
>
> http://www.postgresql.org/docs/8.3/interactive/app-psql.html:
>
> \distvS
> "... to obtain a listing of all the matching objects..."
>
> I fully understand that 'foo' would need to be qualified when used in
> sql, pl/pgsql, etc.
>
> I understood the documentation to read that all tables, views, and
> sequences in the search path would be listed, not just those unobscured.
>

See the paragraph:

"A pattern that contains a dot (.) is interpreted as a schema name
pattern followed by an object name pattern. For example, \dt foo*.*bar*
displays all tables whose table name includes bar that are in schemas
whose schema name starts with foo. When no dot appears, then the pattern
matches only objects that are visible in the current schema search path.
Again, a dot within double quotes loses its special meaning and is
matched literally."

And:

"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 *.*. "

Perhaps it could be a little clearer in the short descriptions, do you
have a suggestion?

Regards,
    Jeff Davis

On Jan 11, 2009, at 8:16 PM, Jeff Davis wrote:

> On Sun, 2009-01-11 at 17:13 -0600, Tim Hart wrote:
>> Would it be worthwhile to specify this in the documentation? One of
>> the sources of my confusion was the following statement:
>>
>> http://www.postgresql.org/docs/8.3/interactive/app-psql.html:
>>
>> \distvS
>> "... to obtain a listing of all the matching objects..."
>>
>> I fully understand that 'foo' would need to be qualified when used in
>> sql, pl/pgsql, etc.
>>
>> I understood the documentation to read that all tables, views, and
>> sequences in the search path would be listed, not just those
>> unobscured.
>>
>
> See the paragraph:
>
> "A pattern that contains a dot (.) is interpreted as a schema name
> pattern followed by an object name pattern. For example, \dt
> foo*.*bar*
> displays all tables whose table name includes bar that are in schemas
> whose schema name starts with foo. When no dot appears, then the
> pattern
> matches only objects that are visible in the current schema search
> path.
> Again, a dot within double quotes loses its special meaning and is
> matched literally."
>
> And:
>
> "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 *.*. "
>
> Perhaps it could be a little clearer in the short descriptions, do you
> have a suggestion?
>
> Regards,
>     Jeff Davis


In hindsight for me, a simple note would have been sufficient.
Something along the lines of

"Note: Without an explicit pattern, \d lists objects according to
current scoping rules. For a full listing, use "*.*"

Just a thought. I consider myself better informed. Thanks for the info.

Tim