Thread: BUG #14317: psql \dt not working as expected

VGhlIGZvbGxvd2luZyBidWcgaGFzIGJlZW4gbG9nZ2VkIG9uIHRoZSB3ZWJz
aXRlOgoKQnVnIHJlZmVyZW5jZTogICAgICAxNDMxNwpMb2dnZWQgYnk6ICAg
ICAgICAgIEx1Y2lhbiBDaXVmdWRlYW4KRW1haWwgYWRkcmVzczogICAgICBs
dWNpYW4uY2l1ZnVkZWFuQGdtYWlsLmNvbQpQb3N0Z3JlU1FMIHZlcnNpb246
IDkuNS40Ck9wZXJhdGluZyBzeXN0ZW06ICAgd2luZG93cyA3IHg2NApEZXNj
cmlwdGlvbjogICAgICAgIAoKSWYgb25lIGNyZWF0ZXMgYSB0YWJsZSB3aXRo
IHRoZSBzYW1lIG5hbWUgYXMgb25lIG9mIHRoZSBwZ19jYXRhbG9nIHRhYmxl
cw0KDQoxLiBDUkVBVEUgVEFCTEUgcGdfdGFibGVzICgNCiAgICBuYW1lICAg
ICAgICAgICAgdmFyY2hhcig4MCksDQogICAgbG9jYXRpb24gICAgICAgIHBv
aW50DQopOw0KDQp0aGVuIFxkdCB3aWxsIG5vdCBzaG93IGl0Og0KDQogICAg
ICAgICBMaXN0IG9mIHJlbGF0aW9ucw0KIFNjaGVtYSB8ICBOYW1lICB8IFR5
cGUgIHwgIE93bmVyDQotLS0tLS0tLSstLS0tLS0tLSstLS0tLS0tKy0tLS0t
LS0tLS0NCiBwdWJsaWMgfCBjaXRpZXMgfCB0YWJsZSB8IHBvc3RncmVzDQoo
MSByb3cpDQoNCndoaWxlIHRoaXMgcXVlcnkgd2lsbDoNCg0Kc2VsZWN0ICog
ZnJvbSBwZ190YWJsZXM7DQoNCkV4cGVjdGF0aW9uczogXGR0IHNob3dzIGFs
bCB0YWJsZXMgaW4gdGhlIHB1YmxpYyBzY2hlbWEuDQoNCk5vdGUgdGhhdCBp
dCBpcyBhbHNvIHVuZXhwZWN0ZWQgdGhhdCB0aGlzIHF1ZXJ5Og0KDQpzZWxl
Y3QgKiBmcm9tIHBnX3RhYmxlczsNCg0KZ29lcyB0byBwZ19jYXRhbG9nLnBn
X3RhYmxlcyBpbnN0ZWFkIG9mIHB1YmxpYy5wZ190YWJsZXMuCgo=

On 9/8/2016 8:40 AM, lucian.ciufudean@gmail.com wrote:
> If one creates a table with the same name as one of the pg_catalog tables
>
> 1. CREATE TABLE pg_tables (
>      name            varchar(80),
>      location        point
> );
>
> then \dt will not show it:
>
>           List of relations
>   Schema |  Name  | Type  |  Owner
> --------+--------+-------+----------
>   public | cities | table | postgres
> (1 row)
>
> while this query will:
>
> select * from pg_tables;
>
> Expectations: \dt shows all tables in the public schema.
>
> Note that it is also unexpected that this query:
>
> select * from pg_tables;
>
> goes to pg_catalog.pg_tables instead of public.pg_tables.

this is because pg_catalog is implicitly in the search path in front of
any explicit search path you specify, including the default of
`$user,public` ...

suggestion:  don't use names like pg_XXXXX for anything.


--
john r pierce, recycling bits in santa cruz

lucian.ciufudean@gmail.com writes:
> If one creates a table with the same name as one of the pg_catalog tables
> then \dt will not show it:

This is expected, if you are using the default search_path, because
pg_catalog will be in front of public and will mask your version of
pg_tables.

> Expectations: \dt shows all tables in the public schema.

Nope, \dt shows *visible* tables, ie those that can be referenced without
schema qualification.  See this bit in the psql documentation:

       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 * as the pattern. (An
       object is said to be visible if its containing schema is in the
       search path and no object of the same kind and name appears earlier
       in the search path. This is equivalent to the statement that the
       object can be referenced by name without explicit schema
       qualification.) To see all objects in the database regardless of
       visibility, use *.* as the pattern.

> Note that it is also unexpected that this query:
> select * from pg_tables;
> goes to pg_catalog.pg_tables instead of public.pg_tables.

Again, you are confused about how the search path works.

If you really want to, you can put public in front of pg_catalog
in your search path, but it's not recommendable --- it's a security
hazard, for one thing.

            regards, tom lane