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

[Vivien Malerba <malerba TorresQuevedo HispaLinux es>]

On Fri, Jul 27, 2001 at 07:17:42AM -0400, Adam Tauno Williams wrote:
> >>>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 tested this yesterday and it works correctly for me. I have a 
Postgres V7.0.3 and libgda 0.2.10.

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


Tell me more about that.

Cheers,

Vivien