pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedODBC: OID column in views
7+ messages / 3 participants
[nested] [flat]
* ODBC: OID column in views
@ 1999-06-29 07:53 Dave Page <dpage@vale-housing.co.uk>
0 siblings, 2 replies; 7+ messages in thread
From: Dave Page @ 1999-06-29 07:53 UTC (permalink / raw)
To: pgsql-interfaces
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)?
2) Under OID options, if you uncheck 'Show Column', the 'Fake Index' option
will correctly be greyed out. If it is ticked when it is greyed though, the
driver will still connect with: showoid='0',fakeoidindex='1'. Can this cause
any problems?
Regards,
Dave.
--
Dave Page, Network & Systems Manager, The Vale Housing Association Ltd.
dpage@vale-housing.co.uk
http://www.vale-housing.co.uk (Work)
http://www.pgadmin.freeserve.co.uk/ (Home of pgAdmin)
Beer can be a permanent solution - but only if you have enough of it!
Attachments:
psqlodbc.log (application/octet-stream, 20.3K) download
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [INTERFACES] ODBC: OID column in views
@ 1999-06-29 14:02 Byron Nikolaidis <byronn@solipsys.com>
parent: Dave Page <dpage@vale-housing.co.uk>
1 sibling, 0 replies; 7+ messages in thread
From: Byron Nikolaidis @ 1999-06-29 14:02 UTC (permalink / raw)
To: Dave Page <dpage@vale-housing.co.uk>; +Cc: pgsql-interfaces
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.
>
> 2) Under OID options, if you uncheck 'Show Column', the 'Fake Index' option
> will correctly be greyed out. If it is ticked when it is greyed though, the
> driver will still connect with: showoid='0',fakeoidindex='1'. Can this cause
> any problems?
>
I dont think so, but I'm not 100% sure.
Byron
^ permalink raw reply [nested|flat] 7+ messages in thread
* RE: [INTERFACES] ODBC: OID column in views
@ 1999-06-29 15:05 Dave Page <dpage@vale-housing.co.uk>
0 siblings, 1 reply; 7+ messages in thread
From: Dave Page @ 1999-06-29 15:05 UTC (permalink / raw)
To: 'Byron Nikolaidis' <byronn@solipsys.com>; +Cc: pgsql-interfaces
> -----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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [INTERFACES] ODBC: OID column in views
@ 1999-06-29 16:41 Byron Nikolaidis <byronn@solipsys.com>
parent: Dave Page <dpage@vale-housing.co.uk>
0 siblings, 0 replies; 7+ messages in thread
From: Byron Nikolaidis @ 1999-06-29 16:41 UTC (permalink / raw)
To: Dave Page <dpage@vale-housing.co.uk>; +Cc: pgsql-interfaces
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* RE: [INTERFACES] ODBC: OID column in views
@ 1999-06-30 08:19 Dave Page <dpage@vale-housing.co.uk>
0 siblings, 0 replies; 7+ messages in thread
From: Dave Page @ 1999-06-30 08:19 UTC (permalink / raw)
To: 'Byron Nikolaidis' <byronn@solipsys.com>; +Cc: pgsql-interfaces
> -----Original Message-----
> From: Byron Nikolaidis [mailto:byronn@solipsys.com]
> Sent: 29 June 1999 16:41
> To: Dave Page
> Cc: 'pgsql-interfaces@postgresql.org'
> Subject: Re: [INTERFACES] ODBC: OID column in views
>
<SNIP>
>
> 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.
This occurs whether it is a system view/table or not. I executed the
following this morning:
CREATE VIEW fred AS SELECT seckey, seclevel FROM sys_userpriv;
SELECT * FROM fred;
sys_userpriv is an existing populated table on my database. I got the same
error as previously:
conn=39272764, query='SELECT "oid" ,"seckey" ,"seclevel" FROM "fred" '
ERROR from backend during send_query: 'ERROR: system column oid not
available - fred is a view'
STATEMENT ERROR: func=SC_execute, desc='', errnum=1, errmsg='Error while
executing the query'
> The idea was that when looking at System Tables, you would
> always be interested
> in the oid column so the driver should show it.
Which makes sense...
> 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!
It appeared with 6.4 if I remember correctly, along with pg_indexes,
pg_rules, pg_user and pg_views.
> 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.
That's a little more complex than I want to get. One of the main places this
problem has cropped up is in the SQL function of pgAdmin - it's just a
textbox where the user can type and execute SQL on the fly. Obviously I want
to try to avoid having to parse the entered SQL myself when DAO, ODBC and
the backend are already doing it.
Regards,
Dave.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [INTERFACES] ODBC: OID column in views
@ 1999-06-30 12:31 José Soares <jose@sferacarta.com>
parent: Dave Page <dpage@vale-housing.co.uk>
1 sibling, 0 replies; 7+ messages in thread
From: José Soares @ 1999-06-30 12:31 UTC (permalink / raw)
To: Dave Page <dpage@vale-housing.co.uk>; +Cc: pgsql-interfaces
I have a problem similar about RowVersioning.
If I link a view with RowVersioning checked M$Access show the following message, when I
try to open the table:
--------------------------------------------------------------------------
Error while executing the query.
ERROR: system column xmin not available - table_name is a view (#1)
--------------------------------------------------------------------------
Dave Page ha scritto:
> 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)?
>
> 2) Under OID options, if you uncheck 'Show Column', the 'Fake Index' option
> will correctly be greyed out. If it is ticked when it is greyed though, the
> driver will still connect with: showoid='0',fakeoidindex='1'. Can this cause
> any problems?
>
> Regards,
>
> Dave.
--
______________________________________________________________
PostgreSQL 6.5.0 on i586-pc-linux-gnu, compiled by gcc 2.7.2.3
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Jose'
^ permalink raw reply [nested|flat] 7+ messages in thread
* RE: [INTERFACES] ODBC: OID column in views
@ 1999-06-30 14:00 Dave Page <dpage@vale-housing.co.uk>
0 siblings, 0 replies; 7+ messages in thread
From: Dave Page @ 1999-06-30 14:00 UTC (permalink / raw)
To: 'Byron Nikolaidis' <byronn@solipsys.com>; +Cc: pgsql-interfaces
> -----Original Message-----
> From: Byron Nikolaidis [mailto:byronn@solipsys.com]
> Sent: 30 June 1999 13:43
> To: Dave Page
> Subject: Re: [INTERFACES] ODBC: OID column in views
>
<SNIP>
>
> Are you sure the show oid thing isn't on? At the top of the
> logfile, there
> should be a printout of all the DSN values.
Yes, it is, but should the driver really be reporting the existance of the
oid column when there really isn't one causing DAO (and possibly
RDO/ADO...) a problem? In a table where the column does exist and can be
queried upon this is most useful and DAO works a treat.
Regards,
Dave.
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~1999-06-30 14:00 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-06-29 07:53 ODBC: OID column in views Dave Page <dpage@vale-housing.co.uk>
1999-06-29 14:02 ` Byron Nikolaidis <byronn@solipsys.com>
1999-06-30 12:31 ` José Soares <jose@sferacarta.com>
1999-06-29 15:05 RE: [INTERFACES] ODBC: OID column in views Dave Page <dpage@vale-housing.co.uk>
1999-06-29 16:41 ` Byron Nikolaidis <byronn@solipsys.com>
1999-06-30 08:19 RE: [INTERFACES] ODBC: OID column in views Dave Page <dpage@vale-housing.co.uk>
1999-06-30 14:00 RE: [INTERFACES] ODBC: OID column in views Dave Page <dpage@vale-housing.co.uk>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox