Re: ERROR: column "ctid" does not exist - Mailing list pgsql-odbc

I don't think this problem is easilly solveable, though some tweaking
with the driver sources may help here.

The problem boils down to asking the ODBC to provide updateable cursors.
There is one problem with updateable cursors - Postgres doesn't support
them.

The ODBC driver works around this problem by modifying the query to
include fields that will allow it to uniquely identify the row in
question, and then update it via a seperate SQL statement. These require
the OID (identification) and CTID (makeing sure that the row was not
changed since it was retrieved). Unfortunetly, views don't have these rows.

I strongly recommend against Jeff's suggested solution, unless you know
exactly what you are doing. It should be ok if all the fields of the
view come from just one table, but will probably fail miserably for
anything else. I also don't know how a query such as "update <view>...."
will be accepted by postgresql.

So your options are, in a nutshell, either not select view via ODBC, ask
OpenOffice to select them in readonly mode (where ODBC doesn't add the
ctid and oid fields, as it doesn't need them), or tweak the driver to
work around this issue some other way.

One possible way of doing the later is to sense failure to get the
records, and switch to read-only mode automatically.

                Shachar

George T. Gibson wrote:

>Thanks for the quick response.
>
>But, if you already have many views created, and rather than going back
>and recreating all of them, is there a setting in the for the driver that
>can solve the problem?
>
>I tried setting both
>     > ShowOidColumn       = Yes
>     > FakeOidIndex        = Yes
>but I did not get any different results.  Is that what these settings are
>for?  Am I doing something wrong with them?
>
>Jeff Eckermann said:
>
>
>>--- "George T. Gibson" <gtgibson@jbstamping.com>
>>wrote:
>>
>>
>>>I am trying to access PostgreSQL from Openoffice
>>>using iodbc on Linux
>>>
>>>When trying to look at any views I get ERROR: column
>>>"ctid" does not exist.
>>>
>>>Any suggestions?
>>>
>>>
>>Every table contains a few system columns, including
>>"ctid", which identifies the storage location of the
>>record (IIRC).  Normally these system columns are not
>>visible when you list a tables columns, but you can
>>select them.
>>
>>The PostgreSQL ODBC driver uses the "ctid" as a unique
>>identifier for a record.
>>
>>AFAIK there is no reason why you could not just add
>>"ctid" to your select list in your view definition.
>>
>>Note that using the ctid as an identifier in an
>>application is not a good idea, as the ctid value for
>>a particular record could change over time.
>>
>>
>>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 3: if posting/reading through Usenet, please send an appropriate
>      subscribe-nomail command to majordomo@postgresql.org so that your
>      message can get through to the mailing list cleanly
>
>


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