Thread: Strange "missing tables" problem
Hello, I have a strange problem since I moved some tables to a schema, some tables are missing from the list (with \d or \dt) but they are still present anyway ???!!!!! Example : > $ psql mybase > Bienvenue dans psql 8.1.17, l'interface interactive de PostgreSQL. > Saisissez: > mybase=# > bw_rma=# \dt > Liste des relations > Schéma | Nom | Type | Propriétaire > -----------------+--------------------------+-------+-------------- > import | rebates_products | table | postgres > import | rebates_customers | table | postgres > rma | categories | table | postgres > rma | customers | table | postgres > rma | defauts | table | postgres > rma | providers | table | postgres No trace of my import.clients table ????? But if I do : > bw_rma=# SELECT count(*) FROM import.customers; > count > ------- > 86703 > (1 ligne) My table is there and I can access it !!! Any hint or help would be greatly appreciated ! I can do without it but, it's a little strange not to be able to list the objects present in the database... Denis
Hello, Small correction to my previous email : > I have a strange problem since I moved some tables to a schema, some > tables are missing from the list (with \d or \dt) but they are still > present anyway ???!!!!! > > Example : >> $ psql mybase >> Bienvenue dans psql 8.1.17, l'interface interactive de PostgreSQL. >> Saisissez: >> mybase=# >> bw_rma=# \dt >> Liste des relations >> Schéma | Nom | Type | Propriétaire >> -----------------+--------------------------+-------+-------------- >> import | rebates_products | table | postgres >> import | rebates_customers | table | postgres >> rma | categories | table | postgres >> rma | customers | table | postgres >> rma | defauts | table | postgres >> rma | providers | table | postgres > > No trace of my import.customers table ????? > > But if I do : >> bw_rma=# SELECT count(*) FROM import.customers; >> count >> ------- >> 86703 >> (1 ligne) > > My table is there and I can access it !!! > > Any hint or help would be greatly appreciated ! > > I can do without it, but it's a little strange not to be able to list > the objects present in the database... Denis
hi, You may try checking: SELECT * FROM pg_catalog.pg_class WHERE relname = 'customers' SELECT * FROM pg_catalog.pg_tables WHERE tablename = 'customers' to what's the status of your table. Regards, foo Denis BUCHER wrote: > Hello, > > Small correction to my previous email : > > >> I have a strange problem since I moved some tables to a schema, some >> tables are missing from the list (with \d or \dt) but they are still >> present anyway ???!!!!! >> >> Example : >> >>> $ psql mybase >>> Bienvenue dans psql 8.1.17, l'interface interactive de PostgreSQL. >>> Saisissez: >>> mybase=# >>> bw_rma=# \dt >>> Liste des relations >>> Schéma | Nom | Type | Propriétaire >>> -----------------+--------------------------+-------+-------------- >>> import | rebates_products | table | postgres >>> import | rebates_customers | table | postgres >>> rma | categories | table | postgres >>> rma | customers | table | postgres >>> rma | defauts | table | postgres >>> rma | providers | table | postgres >>> >> No trace of my import.customers table ????? >> >> But if I do : >> >>> bw_rma=# SELECT count(*) FROM import.customers; >>> count >>> ------- >>> 86703 >>> (1 ligne) >>> >> My table is there and I can access it !!! >> >> Any hint or help would be greatly appreciated ! >> >> I can do without it, but it's a little strange not to be able to list >> the objects present in the database... >> > > Denis > > >
Hello,
That's what I found, do you see something inside that looks interesting ?
bw_rma=# SELECT * FROM pg_catalog.pg_class WHERE relname = 'customers';
relname | relnamespace | reltype | relowner | relam | relfilenode |
reltablespace | relpages | reltuples | reltoastrelid | reltoastidxid |
relhasindex | relisshared | relkind | relnatts | relchecks | reltriggers
| relukeys | relfkeys | relrefs | relhasoids | relhaspkey | relhasrules
| relhassubclass | relacl
---------+--------------+---------+----------+-------+-------------+---------------+----------+-----------+---------------+---------------+-------------+-------------+---------+----------+-----------+-------------+----------+----------+---------+------------+------------+-------------+----------------+----------------------------------------------------------
customers | 17013 | 17022 | 10 | 0 | 17021 |
0 | 16202 | 86685 | 0 | 0 | f
| f | r | 9 | 0 | 0 |
0 | 0 | 0 | f | f | f |
f | {postgres=arwdRxt/postgres,as400=arwdRxt/postgres}
customers | 17055 | 16398 | 10 | 0 | 16397 |
0 | 2831 | 80929 | 0 | 0 | t
| f | r | 9 | 0 | 0 |
0 | 0 | 0 | f | f | f |
f | {postgres=arwdRxt/postgres,rma_php=r/postgres}
(2 lignes)
bw_rma=# SELECT * FROM pg_catalog.pg_tables WHERE tablename = 'customers';
schemaname | tablename | tableowner | tablespace | hasindexes |
hasrules | hastriggers
-----------------+-----------+------------+------------+------------+----------+-------------
bw_import_as400 | clients | postgres | | f | f
| f
rma | clients | postgres | | t | f
| f
(2 lignes)
Thanks a lot for your help :-)
Denis
Wojtek a écrit :
> hi,
>
> You may try checking:
> SELECT * FROM pg_catalog.pg_class WHERE relname = 'customers'
> SELECT * FROM pg_catalog.pg_tables WHERE tablename = 'customers'
> to what's the status of your table.
>
> Regards,
> foo
>
> Denis BUCHER wrote:
>> Hello,
>>
>> Small correction to my previous email :
>>
>>
>>> I have a strange problem since I moved some tables to a schema, some
>>> tables are missing from the list (with \d or \dt) but they are still
>>> present anyway ???!!!!!
>>>
>>> Example :
>>>
>>>> $ psql mybase
>>>> Bienvenue dans psql 8.1.17, l'interface interactive de PostgreSQL.
>>>> Saisissez:
>>>> mybase=#
>>>> bw_rma=# \dt
>>>> Liste des relations
>>>> Schéma | Nom | Type | Propriétaire
>>>> -----------------+--------------------------+-------+--------------
>>>> import | rebates_products | table | postgres
>>>> import | rebates_customers | table | postgres
>>>> rma | categories | table | postgres
>>>> rma | customers | table | postgres
>>>> rma | defauts | table | postgres
>>>> rma | providers | table | postgres
>>>>
>>> No trace of my import.customers table ?????
>>>
>>> But if I do :
>>>
>>>> bw_rma=# SELECT count(*) FROM import.customers;
>>>> count
>>>> -------
>>>> 86703
>>>> (1 ligne)
>>>>
>>> My table is there and I can access it !!!
>>>
>>> Any hint or help would be greatly appreciated !
>>>
>>> I can do without it, but it's a little strange not to be able to list
>>> the objects present in the database...
Hello,
It's even more strange :
# \dt customers
Liste des relations
Schéma | Nom | Type | Propriétaire
--------+---------+-------+--------------
rma | customers | table | postgres
(1 ligne)
# \dt import.customers
Liste des relations
Schéma | Nom | Type | Propriétaire
-----------------+---------+-------+--------------
import | customers | table | postgres
(1 ligne)
# \dt *.customers
Liste des relations
Schéma | Nom | Type | Propriétaire
-----------------+---------+-------+--------------
import | customers | table | postgres
rma | customers | table | postgres
(2 lignes)
I really don't understand what's happening here ?
Denis
> That's what I found, do you see something inside that looks interesting ?
>
> # SELECT * FROM pg_catalog.pg_class WHERE relname = 'customers';
> relname | relnamespace | reltype | relowner | relam | relfilenode |
> reltablespace | relpages | reltuples | reltoastrelid | reltoastidxid |
> relhasindex | relisshared | relkind | relnatts | relchecks | reltriggers
> | relukeys | relfkeys | relrefs | relhasoids | relhaspkey | relhasrules
> | relhassubclass | relacl
>
---------+--------------+---------+----------+-------+-------------+---------------+----------+-----------+---------------+---------------+-------------+-------------+---------+----------+-----------+-------------+----------+----------+---------+------------+------------+-------------+----------------+----------------------------------------------------------
> customers | 17013 | 17022 | 10 | 0 | 17021 |
> 0 | 16202 | 86685 | 0 | 0 | f
> | f | r | 9 | 0 | 0 |
> 0 | 0 | 0 | f | f | f |
> f | {postgres=arwdRxt/postgres,as400=arwdRxt/postgres}
> customers | 17055 | 16398 | 10 | 0 | 16397 |
> 0 | 2831 | 80929 | 0 | 0 | t
> | f | r | 9 | 0 | 0 |
> 0 | 0 | 0 | f | f | f |
> f | {postgres=arwdRxt/postgres,rma_php=r/postgres}
> (2 lignes)
>
> # SELECT * FROM pg_catalog.pg_tables WHERE tablename = 'customers';
> schemaname | tablename | tableowner | tablespace | hasindexes |
> hasrules | hastriggers
> -----------------+-----------+------------+------------+------------+----------+-------------
> bw_import_as400 | clients | postgres | | f | f
> | f
> rma | clients | postgres | | t | f
> | f
> (2 lignes)
>
> Thanks a lot for your help :-)
>
> Denis
>
> Wojtek a écrit :
>> hi,
>>
>> You may try checking:
>> SELECT * FROM pg_catalog.pg_class WHERE relname = 'customers'
>> SELECT * FROM pg_catalog.pg_tables WHERE tablename = 'customers'
>> to what's the status of your table.
>>
>> Regards,
>> foo
>>
>> Denis BUCHER wrote:
>>> Hello,
>>>
>>> Small correction to my previous email :
>>>
>>>
>>>> I have a strange problem since I moved some tables to a schema, some
>>>> tables are missing from the list (with \d or \dt) but they are still
>>>> present anyway ???!!!!!
>>>>
>>>> Example :
>>>>
>>>>> $ psql mybase
>>>>> Bienvenue dans psql 8.1.17, l'interface interactive de PostgreSQL.
>>>>> Saisissez:
>>>>> mybase=#
>>>>> bw_rma=# \dt
>>>>> Liste des relations
>>>>> Schéma | Nom | Type | Propriétaire
>>>>> -----------------+--------------------------+-------+--------------
>>>>> import | rebates_products | table | postgres
>>>>> import | rebates_customers | table | postgres
>>>>> rma | categories | table | postgres
>>>>> rma | customers | table | postgres
>>>>> rma | defauts | table | postgres
>>>>> rma | providers | table | postgres
>>>>>
>>>> No trace of my import.customers table ?????
>>>>
>>>> But if I do :
>>>>
>>>>> bw_rma=# SELECT count(*) FROM import.customers;
>>>>> count
>>>>> -------
>>>>> 86703
>>>>> (1 ligne)
>>>>>
>>>> My table is there and I can access it !!!
>>>>
>>>> Any hint or help would be greatly appreciated !
>>>>
>>>> I can do without it, but it's a little strange not to be able to list
>>>> the objects present in the database...
>
Possible xid rollover problem? (We saw behavior similar to this during a recent
xid rollover fiasco, where tables didn't appear in the various catalogs and psql
catalog commands, but the data was still there.)
What version of PostgreSQL are you on?
If you try a VACUUM on the pg_catalog.pg_class and pg_catalog.pg_tables tables,
do the proper rows come back?
- Bill
> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
> owner@postgresql.org] On Behalf Of Denis BUCHER
> Sent: Sunday, August 23, 2009 8:55 AM
> To: Wojtek
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Strange "missing tables" problem
>
> Hello,
>
> That's what I found, do you see something inside that looks interesting ?
>
> bw_rma=# SELECT * FROM pg_catalog.pg_class WHERE relname = 'customers';
> relname | relnamespace | reltype | relowner | relam | relfilenode |
> reltablespace | relpages | reltuples | reltoastrelid | reltoastidxid |
> relhasindex | relisshared | relkind | relnatts | relchecks | reltriggers
> | relukeys | relfkeys | relrefs | relhasoids | relhaspkey | relhasrules
> | relhassubclass | relacl
>
---------+--------------+---------+----------+-------+-------------+------------
---+----------
>
+-----------+---------------+---------------+-------------+-------------+-------
--+----------
>
+-----------+-------------+----------+----------+---------+------------+--------
----+---------
>
----+----------------+----------------------------------------------------------
> customers | 17013 | 17022 | 10 | 0 | 17021 |
> 0 | 16202 | 86685 | 0 | 0 | f
> | f | r | 9 | 0 | 0 |
> 0 | 0 | 0 | f | f | f |
> f | {postgres=arwdRxt/postgres,as400=arwdRxt/postgres}
> customers | 17055 | 16398 | 10 | 0 | 16397 |
> 0 | 2831 | 80929 | 0 | 0 | t
> | f | r | 9 | 0 | 0 |
> 0 | 0 | 0 | f | f | f |
> f | {postgres=arwdRxt/postgres,rma_php=r/postgres}
> (2 lignes)
>
> bw_rma=# SELECT * FROM pg_catalog.pg_tables WHERE tablename =
> 'customers';
> schemaname | tablename | tableowner | tablespace | hasindexes |
> hasrules | hastriggers
>
-----------------+-----------+------------+------------+------------+----------+
-------------
> bw_import_as400 | clients | postgres | | f | f
> | f
> rma | clients | postgres | | t | f
> | f
> (2 lignes)
>
> Thanks a lot for your help :-)
>
> Denis
>
> Wojtek a écrit :
> > hi,
> >
> > You may try checking:
> > SELECT * FROM pg_catalog.pg_class WHERE relname = 'customers'
> > SELECT * FROM pg_catalog.pg_tables WHERE tablename = 'customers'
> > to what's the status of your table.
> >
> > Regards,
> > foo
> >
> > Denis BUCHER wrote:
> >> Hello,
> >>
> >> Small correction to my previous email :
> >>
> >>
> >>> I have a strange problem since I moved some tables to a schema, some
> >>> tables are missing from the list (with \d or \dt) but they are still
> >>> present anyway ???!!!!!
> >>>
> >>> Example :
> >>>
> >>>> $ psql mybase
> >>>> Bienvenue dans psql 8.1.17, l'interface interactive de PostgreSQL.
> >>>> Saisissez:
> >>>> mybase=#
> >>>> bw_rma=# \dt
> >>>> Liste des relations
> >>>> Schéma | Nom | Type | Propriétaire
> >>>> -----------------+--------------------------+-------+--------------
> >>>> import | rebates_products | table | postgres
> >>>> import | rebates_customers | table | postgres
> >>>> rma | categories | table | postgres
> >>>> rma | customers | table | postgres
> >>>> rma | defauts | table | postgres
> >>>> rma | providers | table | postgres
> >>>>
> >>> No trace of my import.customers table ?????
> >>>
> >>> But if I do :
> >>>
> >>>> bw_rma=# SELECT count(*) FROM import.customers;
> >>>> count
> >>>> -------
> >>>> 86703
> >>>> (1 ligne)
> >>>>
> >>> My table is there and I can access it !!!
> >>>
> >>> Any hint or help would be greatly appreciated !
> >>>
> >>> I can do without it, but it's a little strange not to be able to list
> >>> the objects present in the database...
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
Denis BUCHER wrote: > Hello, > > It's even more strange : > why, looks logical to me :) > # \dt customers > Liste des relations > Schéma | Nom | Type | Propriétaire > --------+---------+-------+-------------- > rma | customers | table | postgres > (1 ligne) > you have 1 'customers' table in 'rma' schema and (my guess) currently you're connected as main user for this schema > # \dt import.customers > Liste des relations > Schéma | Nom | Type | Propriétaire > -----------------+---------+-------+-------------- > import | customers | table | postgres > (1 ligne) > > you have 1 'customers' table in 'import' schema > # \dt *.customers > Liste des relations > Schéma | Nom | Type | Propriétaire > -----------------+---------+-------+-------------- > import | customers | table | postgres > rma | customers | table | postgres > (2 lignes) > > you have two tables 'customers', one in schema 'import', the other one in schema 'rma' I assume you want to keep two copies of this table, right? foo
Hello, Wojtek a écrit : >> It's even more strange : >> > why, looks logical to me :) >> # \dt customers >> Liste des relations >> Schéma | Nom | Type | Propriétaire >> --------+---------+-------+-------------- >> rma | customers | table | postgres >> (1 ligne) >> > you have 1 'customers' table in 'rma' schema and (my guess) currently > you're connected as main user for this schema Yes but why the import.customers table doesn't appear, then ? >> # \dt import.customers >> Liste des relations >> Schéma | Nom | Type | Propriétaire >> -----------------+---------+-------+-------------- >> import | customers | table | postgres >> (1 ligne) >> >> > you have 1 'customers' table in 'import' schema >> # \dt *.customers >> Liste des relations >> Schéma | Nom | Type | Propriétaire >> -----------------+---------+-------+-------------- >> import | customers | table | postgres >> rma | customers | table | postgres >> (2 lignes) >> >> > you have two tables 'customers', one in schema 'import', the other one > in schema 'rma' Yes that's correct > I assume you want to keep two copies of this table, right? Yes, in fact they are two different tables... Denis
Hello,
> # VACUUM pg_catalog.pg_class;
> VACUUM
> # VACUUM pg_catalog.pg_tables;
> ATTENTION: ignore « pg_tables » --- could not execute VACUUM on indexes, views or system tables
> VACUUM
Denis
Bill Bartlett a écrit :
> Possible xid rollover problem? (We saw behavior similar to this during a recent
> xid rollover fiasco, where tables didn't appear in the various catalogs and psql
> catalog commands, but the data was still there.)
>
> What version of PostgreSQL are you on?
>
> If you try a VACUUM on the pg_catalog.pg_class and pg_catalog.pg_tables tables,
> do the proper rows come back?
>
> - Bill
>
>> -----Original Message-----
>> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
>> owner@postgresql.org] On Behalf Of Denis BUCHER
>> Sent: Sunday, August 23, 2009 8:55 AM
>> To: Wojtek
>> Cc: pgsql-general@postgresql.org
>> Subject: Re: [GENERAL] Strange "missing tables" problem
>>
>> Hello,
>>
>> That's what I found, do you see something inside that looks interesting ?
>>
>> bw_rma=# SELECT * FROM pg_catalog.pg_class WHERE relname = 'customers';
>> relname | relnamespace | reltype | relowner | relam | relfilenode |
>> reltablespace | relpages | reltuples | reltoastrelid | reltoastidxid |
>> relhasindex | relisshared | relkind | relnatts | relchecks | reltriggers
>> | relukeys | relfkeys | relrefs | relhasoids | relhaspkey | relhasrules
>> | relhassubclass | relacl
>>
> ---------+--------------+---------+----------+-------+-------------+------------
> ---+----------
> +-----------+---------------+---------------+-------------+-------------+-------
> --+----------
> +-----------+-------------+----------+----------+---------+------------+--------
> ----+---------
> ----+----------------+----------------------------------------------------------
>> customers | 17013 | 17022 | 10 | 0 | 17021 |
>> 0 | 16202 | 86685 | 0 | 0 | f
>> | f | r | 9 | 0 | 0 |
>> 0 | 0 | 0 | f | f | f |
>> f | {postgres=arwdRxt/postgres,as400=arwdRxt/postgres}
>> customers | 17055 | 16398 | 10 | 0 | 16397 |
>> 0 | 2831 | 80929 | 0 | 0 | t
>> | f | r | 9 | 0 | 0 |
>> 0 | 0 | 0 | f | f | f |
>> f | {postgres=arwdRxt/postgres,rma_php=r/postgres}
>> (2 lignes)
>>
>> bw_rma=# SELECT * FROM pg_catalog.pg_tables WHERE tablename =
>> 'customers';
>> schemaname | tablename | tableowner | tablespace | hasindexes |
>> hasrules | hastriggers
>>
> -----------------+-----------+------------+------------+------------+----------+
> -------------
>> bw_import_as400 | clients | postgres | | f | f
>> | f
>> rma | clients | postgres | | t | f
>> | f
>> (2 lignes)
>>
>> Thanks a lot for your help :-)
>>
>> Denis
>>
>> Wojtek a écrit :
>>> hi,
>>>
>>> You may try checking:
>>> SELECT * FROM pg_catalog.pg_class WHERE relname = 'customers'
>>> SELECT * FROM pg_catalog.pg_tables WHERE tablename = 'customers'
>>> to what's the status of your table.
>>>
>>> Regards,
>>> foo
>>>
>>> Denis BUCHER wrote:
>>>> Hello,
>>>>
>>>> Small correction to my previous email :
>>>>
>>>>
>>>>> I have a strange problem since I moved some tables to a schema, some
>>>>> tables are missing from the list (with \d or \dt) but they are still
>>>>> present anyway ???!!!!!
>>>>>
>>>>> Example :
>>>>>
>>>>>> $ psql mybase
>>>>>> Bienvenue dans psql 8.1.17, l'interface interactive de PostgreSQL.
>>>>>> Saisissez:
>>>>>> mybase=#
>>>>>> bw_rma=# \dt
>>>>>> Liste des relations
>>>>>> Schéma | Nom | Type | Propriétaire
>>>>>> -----------------+--------------------------+-------+--------------
>>>>>> import | rebates_products | table | postgres
>>>>>> import | rebates_customers | table | postgres
>>>>>> rma | categories | table | postgres
>>>>>> rma | customers | table | postgres
>>>>>> rma | defauts | table | postgres
>>>>>> rma | providers | table | postgres
>>>>>>
>>>>> No trace of my import.customers table ?????
>>>>>
>>>>> But if I do :
>>>>>
>>>>>> bw_rma=# SELECT count(*) FROM import.customers;
>>>>>> count
>>>>>> -------
>>>>>> 86703
>>>>>> (1 ligne)
>>>>>>
>>>>> My table is there and I can access it !!!
>>>>>
>>>>> Any hint or help would be greatly appreciated !
>>>>>
>>>>> I can do without it, but it's a little strange not to be able to list
>>>>> the objects present in the database...
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>
>
>
Denis Bucher
--
Denis Bucher Horus Digital Solutions sàrl Each problem has a solution
___________________________________________________________________________
Tél. +41-22-8000625 Fax: +41-22-8000622 www.hsolutions.ch
Denis BUCHER <dbucherml@hsolutions.ch> writes:
> I really don't understand what's happening here ?
"\dt customers" will show you the customers table that's visible
according to your search_path setting. Apparently schema "import"
is either not in your search path at all, or behind "rma".
regards, tom lane
Le dimanche 23 août 2009 à 14:26:06, Denis BUCHER a écrit : > Hello, > > I have a strange problem since I moved some tables to a schema, some > tables are missing from the list (with \d or \dt) but they are still > present anyway ???!!!!! > > Example : > > $ psql mybase > > Bienvenue dans psql 8.1.17, l'interface interactive de PostgreSQL. > > Saisissez: > > mybase=# > > bw_rma=# \dt > > Liste des relations > > Schéma | Nom | Type | Propriétaire > > -----------------+--------------------------+-------+-------------- > > import | rebates_products | table | postgres > > import | rebates_customers | table | postgres > > rma | categories | table | postgres > > rma | customers | table | postgres > > rma | defauts | table | postgres > > rma | providers | table | postgres > > No trace of my import.clients table ????? > > But if I do : > > bw_rma=# SELECT count(*) FROM import.customers; > > count > > ------- > > 86703 > > (1 ligne) > > My table is there and I can access it !!! > > Any hint or help would be greatly appreciated ! > > I can do without it but, it's a little strange not to be able to list > the objects present in the database... > \d does not show all the objects available in the database. If one is available in schema A and in schema B, it will be displayed at most once, depending on your search_path configuration. I suppose you have something like 'rma, import, ...' for search_path, so it only displays rma.customers and not impor.customers. Regards. -- Guillaume. http://www.postgresqlfr.org http://dalibo.com
Hello Tom, Tom Lane a écrit : > Denis BUCHER <dbucherml@hsolutions.ch> writes: >> I really don't understand what's happening here ? > > "\dt customers" will show you the customers table that's visible > according to your search_path setting. Apparently schema "import" > is either not in your search path at all, or behind "rma". It is in the search path. Do you mean that \dt customers shows only the FIRST "customers" table found ? If yes, that's a part of the explanation, BUT : If I do \dt (without specifying a table name), does it lists only ONE table of each name, and only the one in the first schema present in the search path. Denis
Hello, Guillaume Lelarge a écrit : >> I have a strange problem since I moved some tables to a schema, some >> tables are missing from the list (with \d or \dt) but they are still >> present anyway ???!!!!! >> >> Example : >>> $ psql mybase >>> Bienvenue dans psql 8.1.17, l'interface interactive de PostgreSQL. >>> Saisissez: >>> mybase=# >>> bw_rma=# \dt >>> Liste des relations >>> Schéma | Nom | Type | Propriétaire >>> -----------------+--------------------------+-------+-------------- >>> import | rebates_products | table | postgres >>> import | rebates_customers | table | postgres >>> rma | categories | table | postgres >>> rma | customers | table | postgres >>> rma | defauts | table | postgres >>> rma | providers | table | postgres >> No trace of my import.clients table ????? >> >> But if I do : >>> bw_rma=# SELECT count(*) FROM import.customers; >>> count >>> ------- >>> 86703 >>> (1 ligne) >> My table is there and I can access it !!! >> >> Any hint or help would be greatly appreciated ! >> >> I can do without it but, it's a little strange not to be able to list >> the objects present in the database... >> > > \d does not show all the objects available in the database. If one is > available in schema A and in schema B, it will be displayed at most once, > depending on your search_path configuration. > > I suppose you have something like 'rma, import, ...' for search_path, so it > only displays rma.customers and not impor.customers. Yes that's correct. Therefore my "problem" is the "normal" behavior of \dt. But "normal" means "expected". But I don't find it very secure/handy, because you expect to see all your tables. Is there a way to change the behavior of \dt so that it lists ALL tables present in search path ? Thanks a lot for your help Denis
Hi Denis, Le lundi 24 août 2009 à 10:21:33, Denis BUCHER a écrit : > > [...] > > I suppose you have something like 'rma, import, ...' for search_path, so > > it only displays rma.customers and not impor.customers. > > Yes that's correct. Therefore my "problem" is the "normal" behavior of > \dt. But "normal" means "expected". But I don't find it very > secure/handy, because you expect to see all your tables. Is there a way > to change the behavior of \dt so that it lists ALL tables present in > search path ? > No. But you can always take a look a this: http://radek.cc/2009/08/15/psqlrc-tricks-table-sizes/ You can probably do a \set dt your_query, and then :dt; instead of \dt. ... a few moments later... I tried and it works. Put this line in your .psqlrc file: \set dt '(SELECT n.nspname as \"Schéma\", c.relname as \"Nom\", CASE c.relkind WHEN \'r\' THEN \'table\' WHEN \'v\' THEN \'vue\' WHEN \'i\' THEN \'index\' WHEN \'S\' THEN \'séq uence\' WHEN \'s\' THEN \'spécial\' END as \"Type\", r.rolname as \"Propriétaire\" FROM pg_catalog.pg_class c JOIN pg_catalog.pg_roles r ON r.oid = c.relowner LEFT JOIN pg_cata log.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind IN (\'r\',\'\') AND n.nspname NOT IN (\'pg_catalog\', \'pg_toast\') ORDER BY 1,2)' It should be one line only, and you can add more schemas on the n.nspname filter (information_schema for example). Now, I get this with this .psqlrc trick: guillaume@laptop:~$ psql -q a a=# set search_path to public, toto; a=# \dt Liste des relations Schéma | Nom | Type | Propriétaire --------+-----+-------+-------------- public | t1 | table | ab1 public | t2 | table | a2 public | t3 | table | postgres toto | t4 | table | guillaume (4 lignes) a=# :dt; Schéma | Nom | Type | Propriétaire --------------------+-------------------------+-------+-------------- information_schema | sql_features | table | guillaume information_schema | sql_implementation_info | table | guillaume information_schema | sql_languages | table | guillaume information_schema | sql_packages | table | guillaume information_schema | sql_sizing | table | guillaume information_schema | sql_sizing_profiles | table | guillaume pgagent | pga_exception | table | guillaume pgagent | pga_job | table | guillaume pgagent | pga_jobagent | table | guillaume pgagent | pga_jobclass | table | guillaume pgagent | pga_joblog | table | guillaume pgagent | pga_jobstep | table | guillaume pgagent | pga_jobsteplog | table | guillaume pgagent | pga_schedule | table | guillaume public | t1 | table | ab1 public | t2 | table | a2 public | t3 | table | postgres toto | t1 | table | guillaume toto | t4 | table | guillaume (19 lignes) Hope it helps. -- Guillaume. http://www.postgresqlfr.org http://dalibo.com
Denis BUCHER <dbucherml@hsolutions.ch> writes:
> Yes that's correct. Therefore my "problem" is the "normal" behavior of
> \dt. But "normal" means "expected". But I don't find it very
> secure/handy, because you expect to see all your tables. Is there a way
> to change the behavior of \dt so that it lists ALL tables present in
> search path ?
It *is* the expected behavior. The idea is that "\dt foo" should
describe the same table that "select * from foo" would find.
If you want to see all the possible matches for foo, use "\dt *.foo".
regards, tom lane
Tom Lane a écrit : > Denis BUCHER <dbucherml@hsolutions.ch> writes: >> Yes that's correct. Therefore my "problem" is the "normal" behavior of >> \dt. But "normal" means "expected". But I don't find it very >> secure/handy, because you expect to see all your tables. Is there a way >> to change the behavior of \dt so that it lists ALL tables present in >> search path ? > > It *is* the expected behavior. The idea is that "\dt foo" should > describe the same table that "select * from foo" would find. > If you want to see all the possible matches for foo, use "\dt *.foo". > > regards, tom lane Oh yes, now I found the "list all tables" command : It's : \dt *.* Denis