BUG #14317: psql \dt not working as expected

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

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

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

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs