Обсуждение: posible BUG on psql... or maybe worst

I was making some table creation on one of our development DB and found
that psql's \dt has problems showing all tables available. Basically, if
you have to tables with the same name in different schemas, only one
will be listed (the one on the schema that is first in the search_path).

IMHO, \dt should show all the tables per-schema.

Now what I can't find is where the problem is. \dt executes this query:

SELECT n.nspname as "Schema",
   c.relname as "Name",
   CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i'
THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as "Type",
   r.rolname as "Owner"
FROM pg_catalog.pg_class c
      JOIN pg_catalog.pg_roles r ON r.oid = c.relowner
      LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','')
   AND n.nspname <> 'pg_catalog'
   AND n.nspname !~ '^pg_toast'
   AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;

The query looks ok, but it doesn't bring the 2 tables in the list.

Hi,


Schemas are a lot like directories at operating system level (except that can't be nested).
When you ls (or dir) in /home/martin/ , normally you don't expect to see  /home/johnny/  listed as well.

But if you really want to see all tables, try adjusting search_path like this:

SET search_path to  myschema1,myschema2,public;

Then it should list all relations as you expect.

Diego Schulz escribió:
>
>
> Hi,
>
>
> Schemas are a lot like directories at operating system level (except
> that can't be nested).
> When you ls (or dir) in /home/martin/ , normally you don't expect to
> see  /home/johnny/  listed as well.
>
> But if you really want to see all tables, try adjusting search_path like
> this:
>
> SET search_path to  myschema1,myschema2,public;
>
> Then it should list all relations as you expect.
>

Sorry, forgot to say that I SET search_path acordinlly to see relations
from both schemas. But whan the table has the same name I only get the
one from the first schema in the search_path.

I can confirm the behaviour you described.


\dt+ *.contactos
                 List of relations
 Schema |   Name    | Type  |  Owner  | Description
--------+-----------+-------+---------+-------------
 prueba | contactos | table | dschulz |
 public | contactos | table | dschulz |


dschulz=# \dt+
                       List of relations
 Schema |         Name         | Type  |  Owner  | Description
-----------+----------------------------+-------+------------+-------------
 prueba | contactos              | table | dschulz |
 public  | bitacora                 | table | dschulz |
 public  | documentos           | table | dschulz |
 public  | documentos_tipos  | table | dschulz |
... (snip) ...

(no table public.contactos listed here)


dschulz=# select version();
                                             version
-------------------------------------------------------------------------------------------------
 PostgreSQL 8.3.5 on i386-portbld-freebsd7.1, compiled by GCC cc (GCC) 4.2.1 20070719  [FreeBSD]
(1 row)



But you can always use

\dt+  *.

to list all relations in all schemas.


cheers

Diego Schulz wrote:
> > Sorry, forgot to say that I SET search_path acordinlly to see relations
> > from both schemas. But whan the table has the same name I only get the one
> > from the first schema in the search_path.
> >
>
>
> I can confirm the behaviour you described.

Yes, \dt was designed that way, and *.name is the proper way to show
tables in all schemas.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

Iwant quit this milis. could you help me? I forget my milis password.





      Selalu bisa chat di profil jaringan, blog, atau situs web pribadi! Yahoo! memungkinkan Anda selalu bisa chat
melaluiPingbox. Coba! http://id.messenger.yahoo.com/pingbox/ 

Tonny Sapri wrote:
> Iwant quit this milis. could you help me? I forget my milis password.
>
>
>

go to this web site http://www.postgresql.org/mailpref/pgsql-general
click "Sign In" at the top left.
find the section that says "Lost Password".
put your email address in there and click the "Mail My Password" button.
check your email for your list password.

go back to the web site and login properly
tick the check box next to pgsql-general
click the unsubscribe button


HTH,

klint.

--
Klint Gore
Database Manager
Sheep CRC
A.G.B.U.
University of New England
Armidale NSW 2350

Ph: 02 6773 3789
Fax: 02 6773 3266
EMail: kgore4@une.edu.au