Re: BUG #5791: Tables are not viewing through pgadmin

On Wed, Jan 19, 2011 at 4:20 AM, Benoygopal <benoy(at)cdactvm(dot)in> wrote:
> Dear Dave,
> Tables will not visible even after refreshing the tree view and after
> restarting the PC as well.
> It not predictable when the table will start displaying again. Some time the
> tables displayed in the next day as well.
> However we could able to login to the database,views up to schema but inside
> schema nothing is visible.
> But we could log and retrieve the data to the tables. Application could able
> to use triggers stored procedures etc even it is not visible through
> pgadmin. Please suggest any input is required from my side or suggest to
> check something related to the postgres database or pgadmin related
> configuration files when this condition occurs again.

There isn't really anything to check config-wise. This has always
worked for hundreds of thousands of users over the 6 or 7 years of
pgAdmin III, so I'm somewhat at a loss to know what to suggest.

How are the tables being created? Are you sure they're in the schema
you think they're in (ie. could the search path in your app put them
somewhere other than where you expect)?

For PostgreSQL 8.4 and 9.0, the query used to get the table info looks
something like the following. You could try running that if the
problem re-occurs (you should change the OID at the end to match the
OID of the schema you're looking in:

SELECT rel.oid, relname, rel.reltablespace AS spcoid, spcname,
pg_get_userbyid(relowner) AS relowner, relacl, relhasoids,
relhassubclass, reltuples, description, conname, conkey,
EXISTS(select 1 FROM pg_trigger
JOIN pg_proc pt ON pt.oid=tgfoid AND
pt.proname='logtrigger'
JOIN pg_proc pc ON
pc.pronamespace=pt.pronamespace AND pc.proname='slonyversion'
WHERE tgrelid=rel.oid) AS isrepl
, substring(array_to_string(rel.reloptions, ',') from
'fillfactor=([0-9]*)') AS fillfactor
, substring(array_to_string(rel.reloptions, ',') FROM
'autovacuum_enabled=([a-z|0-9]*)') AS autovacuum_enabled
, substring(array_to_string(rel.reloptions, ',') FROM
'autovacuum_vacuum_threshold=([0-9]*)') AS autovacuum_vacuum_threshold
, substring(array_to_string(rel.reloptions, ',') FROM
'autovacuum_vacuum_scale_factor=([0-9]*[.][0-9]*)') AS
autovacuum_vacuum_scale_factor
, substring(array_to_string(rel.reloptions, ',') FROM
'autovacuum_analyze_threshold=([0-9]*)') AS
autovacuum_analyze_threshold
, substring(array_to_string(rel.reloptions, ',') FROM
'autovacuum_analyze_scale_factor=([0-9]*[.][0-9]*)') AS
autovacuum_analyze_scale_factor
, substring(array_to_string(rel.reloptions, ',') FROM
'autovacuum_vacuum_cost_delay=([0-9]*)') AS
autovacuum_vacuum_cost_delay
, substring(array_to_string(rel.reloptions, ',') FROM
'autovacuum_vacuum_cost_limit=([0-9]*)') AS
autovacuum_vacuum_cost_limit
, substring(array_to_string(rel.reloptions, ',') FROM
'autovacuum_freeze_min_age=([0-9]*)') AS autovacuum_freeze_min_age
, substring(array_to_string(rel.reloptions, ',') FROM
'autovacuum_freeze_max_age=([0-9]*)') AS autovacuum_freeze_max_age
, substring(array_to_string(rel.reloptions, ',') FROM
'autovacuum_freeze_table_age=([0-9]*)') AS autovacuum_freeze_table_age
, rel.reloptions AS reloptions
, (CASE WHEN rel.reltoastrelid = 0 THEN false ELSE true END) AS hastoasttable
, reloftype, typname
FROM pg_class rel
LEFT OUTER JOIN pg_tablespace ta on ta.oid=rel.reltablespace
LEFT OUTER JOIN pg_description des ON (des.objoid=rel.oid AND des.objsubid=0)
LEFT OUTER JOIN pg_constraint c ON c.conrelid=rel.oid AND c.contype='p'
LEFT JOIN pg_type ON reloftype=pg_type.oid
WHERE relkind IN ('r','s','t') AND relnamespace = 2200::oid
ORDER BY relname

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company