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

[Adam Tauno Williams <awilliam whitemice org>]

>>>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.
>Idid 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?
>For me, it has worked since the beginning :-)

FYI

CREATE SEQUENCE "application_id" start 100 increment 1 maxvalue
2147483647 minvalue 1  cache 1 ;
CREATE SEQUENCE "data_tag_id" start 100 increment 1 maxvalue 2147483647
minvalue 1  cache 1 ;
CREATE SEQUENCE "data_value_id" start 100 increment 1 maxvalue
2147483647 minvalue 1  cache 1 ;
CREATE TABLE "applications" (
	"application_id" int4 DEFAULT nextval('application_id'::text),
	"application_name" character varying(40),
	"application_docs" character varying(200)
);
CREATE TABLE "data_tags" (
	"data_tag_id" int4 DEFAULT nextval('data_tag_id'::text),
	"data_tag_desc" character varying(40),
	"data_tag_type" int4
);
CREATE TABLE "application_tags" (
	"application_id" int4,
	"data_tag_id" int4
);
CREATE TABLE "data_values" (
	"data_value_id" int4 DEFAULT nextval('data_value_id'::text),
	"data_tag_id" int4,
	"data_value" character varying(100)
);

I'd try it with the latest CVS but I'm having a few build problems.