Thread: RE: [INTERFACES] ODBC: OID column in views

> -----Original Message-----
> From: owner-pgsql-interfaces@postgreSQL.org
> [mailto:owner-pgsql-interfaces@postgreSQL.org]On Behalf Of Byron
> Nikolaidis
> Sent: 29 June 1999 14:02
> To: Dave Page
> Cc: 'pgsql-interfaces@postgresql.org'
> Subject: Re: [INTERFACES] ODBC: OID column in views
> 
> 
> 
> 
> Dave Page wrote:
> 
> > I've recently come across a couple of minor problems(?) in 
> the ODBC driver:
> >
> > 1) When querying a view eg:
> >
> > SELECT * FROM pg_tables;
> >
> > the driver will always attempt (and fail) to retrieve the 
> oid column thus
> > returning an ODBC call failed error. This occurs whether 
> the 'Show OID
> > Column' is selected or not. I can see the obvious overhead 
> in checking
> > whether or not a query is on a view or table - is the 
> current behaviour the
> > best solution or is there another way around this (other 
> than specifying
> > column names in queries)?
> >
> 
> I'm not sure what you mean by "the driver will always attempt 
> to retrieve the oid
> column"...  What is the driver doing?  If it is sending 
> another query, could you show
> me exactly what because its been a while since I looked at the code.

The commlog shows the following when the query 'SELECT * FROM pg_tables;' is
executed via DAO (I've just thought though, would it be the driver or
perhaps DAO that is expanding the wildcard?):

conn=175915324, query='SELECT "oid" ,"tablename" ,"tableowner" ,"hasindexes"
,"hasrules" ,"hastriggers"  FROM "pg_tables" '
ERROR from backend during send_query: 'ERROR:  system column oid not
available - pg_tables is a view'
STATEMENT ERROR: func=SC_execute, desc='', errnum=1, errmsg='Error while
executing the query'

Presumably if it's DAO that's expanding the wildcard, it would still be the
driver reporting the existance of the oid column?

Regards,    
Dave.

Dave Page wrote:

> >
> > I'm not sure what you mean by "the driver will always attempt
> > to retrieve the oid
> > column"...  What is the driver doing?  If it is sending
> > another query, could you show
> > me exactly what because its been a while since I looked at the code.
>
> The commlog shows the following when the query 'SELECT * FROM pg_tables;' is
> executed via DAO (I've just thought though, would it be the driver or
> perhaps DAO that is expanding the wildcard?):
>
> conn=175915324, query='SELECT "oid" ,"tablename" ,"tableowner" ,"hasindexes"
> ,"hasrules" ,"hastriggers"  FROM "pg_tables" '
> ERROR from backend during send_query: 'ERROR:  system column oid not
> available - pg_tables is a view'
> STATEMENT ERROR: func=SC_execute, desc='', errnum=1, errmsg='Error while
> executing the query'
>
> Presumably if it's DAO that's expanding the wildcard, it would still be the
> driver reporting the existance of the oid column?
>

Ahhhh,  I see now.  DAO is calling SQLColumns() on the "pg_tables" table.  Since
the driver realizes it is a system table (i.e., because it begins with pg_), it
automatically throws the OID column in there.  There is no option to turn that
off and it is not intelligent enough to check whether it is a view.

The idea was that when looking at System Tables, you would always be interested
in the oid column so the driver should show it.

Wait a minute, I don't remember any "pg_tables" view???  Is that really a system
table, or something you created?  If its something you created, you can just not
start it with "pg_" and problem solved!

Another possibility is to use SQLTables() meta function to get the tables
instead of using a low level query.  Theoretically, it would be more portable.

Byron