Re: [gnome-db]Not all tables in PostgreSQL

[Adam Tauno Williams <adam morrison-ind com>]

>>>>Is there a known problem with table enumeration?  I create a new
>>>>PostgreSQL database with one table and one sequence.  I attached
>>>>with gnomedb-fe and gASQL to see if it worked and it did!  Then I 
>>>>added a few more tables and sequences and .... they don't show up 
>>>>in either gnomedb-fe or gASQL.  I can see them in psql using "\d".  
>>>>I've restarted the applications,  logged out and back in, etc... but
>>>>can't get the tables. I have never seen this problem before.
>>>
" SELECT a.realname AS \"Name\" "
>>>
" FROM pg_class a, pg_user b "
>>>        "WHERE ( relkind = 'r') and relname !~ '^pg_' "
>>>        "AND relname !~ '^xin[vx][0-9]+' AND "
>>>        "b.usesysid = a.relowner AND "
>>>        "NOT (EXISTS (SELECT viewname FROM pg_views "
>>>        "WHERE viewname=a.relname)) ");
>>>That's the query the postgres provider does to get the list of
>>>tables from the postgres server. Maybe the names of your tables don't
>>>match the "AND relname !~ '^xin[vx][0-9]+'" condition?
>> ~ $ cat test.sql
>>         SELECT a.relname AS Name 
>>          FROM pg_class a, pg_user b 
>>         WHERE ( relkind = 'r') and relname !~ '^pg_' 
>>         AND relname !~ '^xin[vx][0-9]+' AND 
>>         b.usesysid = a.relowner AND 
>>         NOT (EXISTS (SELECT viewname FROM pg_views 
>>         WHERE viewname=a.relname));
>> ~ $ psql maindb < test.sql 
>>        name       
>> ------------------
>>  applications
>>  data_tags
>>  application_tags
>>  data_values
>> (4 rows)
>>Only the "applications" table shows up through gnome-db.  Notice that
>>I had to change a.realname to a.relname,  as the realname column doesn't 
>>seem to exist in pg_class (don't know if that is just a type, or not).  
>>These tables can be enumerated via unixODBC.
>I did copy/paste from the sources, except for the SELECT line, so yes,
>it's a typo from my part, it should be a.relname.
>Vivien, do you know what's happening? What Postgres version are you
>using?

I'm using postgresql-7.0.3-8.

Systems and Network Administrator
Morrison Industries
1825 Monroe Ave NW.
Grand Rapids, MI. 49505