Re: Strange "missing tables" problem

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