Обсуждение: column "oid" does not exist

I have been searching for a couple of hours now and cannot seem to find
a resolution to my problem other than to recreate my tables with OIDs
turned on. So hear goes...

I get the error message in the subject whenever I attempt to use an
application that supports ODBC to access a user table in my database.
When I create a table using "WITH OIDS", I have no problems viewing the
table with any application. But when I use "WITHOUT OIDS", the only two
applications that I have found that will work are MSQuery and MSAccess.
(I am trying to use Avery DesignPro and OpenOffice without success.)

The things that I have tried (beside pounding my head against a wall)
are just about all of the options in the configuration dialog and, of
course, different applications. My use of PostgreSQL here is dependent
upon my being able to get ODBC to work reliably. Why does it even care
about selecting the OID field? All of the notes I have seen seem to
indicate that its necessity is largely gone and certainly not relevant
for anything that I am doing.

Why would some apps be able to run a simple select query without
generating this error while others cannot? I assume that the difference
is in the way the ODBC calls are made by the client app. If this is so,
why does not the driver handle this circumstance more elegantly?

In a possibly related issue, when I select from a view using these same
apps, I get the error "colum ctid does not exist". Is this the same issue?

I would like to follow the suggestions related to not using OIDs, but
this one has me stumped.

Grateful for any help,

-- Matt

Matthew and Karen Brown wrote:

> I get the error message in the subject whenever I attempt to use an
> application that supports ODBC to access a user table in my database.
> When I create a table using "WITH OIDS", I have no problems viewing
> the table with any application. But when I use "WITHOUT OIDS", the
> only two applications that I have found that will work are MSQuery and
> MSAccess. (I am trying to use Avery DesignPro and OpenOffice without
> success.)
>
Yes, that makes sense.

> The things that I have tried (beside pounding my head against a wall)
> are just about all of the options in the configuration dialog and, of
> course, different applications. My use of PostgreSQL here is dependent
> upon my being able to get ODBC to work reliably. Why does it even care
> about selecting the OID field? All of the notes I have seen seem to
> indicate that its necessity is largely gone and certainly not relevant
> for anything that I am doing.
>
PostgreSQL does not support updateable cursors. To work around this
problem, the ODBC driver uses readonly cursors for its operations. In
order for updates to still work, the ODBC driver adds the OID field to
all of your queries, and then uses that field to perform the update on a
seperate query.

> Why would some apps be able to run a simple select query without
> generating this error while others cannot? I assume that the
> difference is in the way the ODBC calls are made by the client app. If
> this is so, why does not the driver handle this circumstance more
> elegantly?

If you open your query as "static readonly", there is no need for the
OID field, and the query is not changed before being sent to the
database server. That must be the difference between them.

As for why ODBC cannot do that himself, I don't know.

>
> In a possibly related issue, when I select from a view using these
> same apps, I get the error "colum ctid does not exist". Is this the
> same issue?
>
exactly the same issue, for exactly the same reason. A view doesn't have
the ctid and oid fields, and so you cannot select in read-write mode
from a view.

> I would like to follow the suggestions related to not using OIDs, but
> this one has me stumped.
>
Sorry. No easy way out that I can see.

> Grateful for any help,
>
> -- Matt



--
Shachar Shemesh
Lingnu Open Systems Consulting
http://www.lingnu.com/

Thank you for clarifying this for me. I guess I will just have to dump
the tables and recreate them with OIDs.

Thanks again.

-- Matt

Shachar Shemesh wrote:

> Matthew and Karen Brown wrote:
>
>> I get the error message in the subject whenever I attempt to use an
>> application that supports ODBC to access a user table in my database.
>> When I create a table using "WITH OIDS", I have no problems viewing
>> the table with any application. But when I use "WITHOUT OIDS", the
>> only two applications that I have found that will work are MSQuery
>> and MSAccess. (I am trying to use Avery DesignPro and OpenOffice
>> without success.)
>>
> Yes, that makes sense.
>
>> The things that I have tried (beside pounding my head against a wall)
>> are just about all of the options in the configuration dialog and, of
>> course, different applications. My use of PostgreSQL here is
>> dependent upon my being able to get ODBC to work reliably. Why does
>> it even care about selecting the OID field? All of the notes I have
>> seen seem to indicate that its necessity is largely gone and
>> certainly not relevant for anything that I am doing.
>>
> PostgreSQL does not support updateable cursors. To work around this
> problem, the ODBC driver uses readonly cursors for its operations. In
> order for updates to still work, the ODBC driver adds the OID field to
> all of your queries, and then uses that field to perform the update on
> a seperate query.
>
>> Why would some apps be able to run a simple select query without
>> generating this error while others cannot? I assume that the
>> difference is in the way the ODBC calls are made by the client app.
>> If this is so, why does not the driver handle this circumstance more
>> elegantly?
>
>
> If you open your query as "static readonly", there is no need for the
> OID field, and the query is not changed before being sent to the
> database server. That must be the difference between them.
>
> As for why ODBC cannot do that himself, I don't know.
>
>>
>> In a possibly related issue, when I select from a view using these
>> same apps, I get the error "colum ctid does not exist". Is this the
>> same issue?
>>
> exactly the same issue, for exactly the same reason. A view doesn't
> have the ctid and oid fields, and so you cannot select in read-write
> mode from a view.
>
>> I would like to follow the suggestions related to not using OIDs, but
>> this one has me stumped.
>>
> Sorry. No easy way out that I can see.
>
>> Grateful for any help,
>>
>> -- Matt
>
>
>
>