Re: Strange "missing tables" problem

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(at)postgresql(dot)org [mailto:pgsql-general-
>> owner(at)postgresql(dot)org] On Behalf Of Denis BUCHER
>> Sent: Sunday, August 23, 2009 8:55 AM
>> To: Wojtek
>> Cc: pgsql-general(at)postgresql(dot)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(at)postgresql(dot)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