Re: Strange "missing tables" problem - Mailing list pgsql-general

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