\d doesn't work for schemas not in your search path

Steps to reproduce:

Set up a test DB (e.g. via pgcli or psql):

CREATE DATABASE test;
\c test
CREATE SCHEMA foo;
CREATE TABLE foo.bar (x INT, y INT, z INT);

Try to \d foo.bar

Expected results: pgcli shows the definition of table foo.bar

Observed results: pgcli presents the error, Did not find any relation named foo.bar.

Below is a transcript from a pgcli session. You can see that \dt foo.bar works fine, but \d foo.bar fails until I put foo into search_path.

user@/tmp:otherdb> create database test;
CREATE DATABASE
Time: 0.477s
user@/tmp:otherdb> \c test
You are now connected to database "test" as user "user"
Time: 0.017s
user@/tmp:test> create schema foo
CREATE SCHEMA
Time: 0.003s
user@/tmp:test> create table foo.bar (x int, y int, z int);
CREATE TABLE
Time: 0.005s
user@/tmp:test> \dt foo.bar
+----------+--------+--------+---------+
| Schema   | Name   | Type   | Owner   |
|----------+--------+--------+---------|
| foo      | bar    | table  | user    |
+----------+--------+--------+---------+
SELECT 1
Time: 0.014s
user@/tmp:test> \d foo.bar
Did not find any relation named foo.bar.
Time: 0.002s
user@/tmp:test> set search_path = foo;
SET
Time: 0.001s
user@/tmp:test> \d foo.bar
+----------+---------+-------------+
| Column   | Type    | Modifiers   |
|----------+---------+-------------|
| x        | integer |             |
| y        | integer |             |
| z        | integer |             |
+----------+---------+-------------+
Time: 0.019s

I strongly suspect the problem is dbcommands.py line 691 which adds pg_catalog.pg_table_is_visible(c.oid) to the query. pg_table_is_visible has the description, "is table visible in search path?"

The complete query pgcli generated is (reformatted for your pleasure):

SELECT
    c.oid, n.nspname, c.relname
FROM
    pg_catalog.pg_class c
    LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE
    pg_catalog.pg_table_is_visible(c.oid)
    AND n.nspname ~ '^(foo)$'
    AND c.relname OPERATOR(pg_catalog.~) '^(bar)$'
ORDER BY
    2, 3

If I comment out the pg_table_is_visible condition, this query successfully returns the one row you'd expect.

For comparison, here's the first query my psql generates in response to \d foo.bar in the same DB (again, reformatted):

SELECT
    c.oid, n.nspname, c.relname
FROM
    pg_catalog.pg_class c
    LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE
    c.relname ~ '^(bar)$'
    AND n.nspname ~ '^(foo)$'
ORDER BY
    2, 3

Versions:

macOS 10.13.6
PostgreSQL 9.5.10
Python 3.7.4
pgcli 2.1.1
pgspecial 1.11.7

Thanks to everyone who works on pgcli, I'm enjoying using it!

dbcli / pgspecial

\d doesn't work for schemas not in your search path #88