Thread: ERROR: column "ctid" does not exist
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? My .odbc.ini file is: [ODBC Data Sources] PostgreSQL = PostgreSQL native driver [PostgreSQL] Description = Production Database Driver = /usr/local/lib/psqlodbc.so Trace = No TraceFile = /tmp/odbc.log Database = data Server = 192.168.2.254 UserName = ggibson Password = Port = 5432 Protocol = 6.4 ReadOnly = Yes RowVersioning = Yes ShowSystemTables = No ShowOidColumn = No FakeOidIndex = No ConnSettings = SET SEARCH_PATH TO public [ODBC] ;Tracing = 1 ;TraceFile = /tmp/odbctrace.log ;Debugging = 1 ;DebugFile = /tmp/odbcdebug.log -- George Gibson J.B. Stamping, Inc. 7413 Associate Ave. Cleveland, OH 44144 216.631.0013 216.631.1327 fax gtgibson@jbstamping.com
--- "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. > > My .odbc.ini file is: > > [ODBC Data Sources] > PostgreSQL = PostgreSQL native driver > > [PostgreSQL] > Description = Production Database > Driver = /usr/local/lib/psqlodbc.so > Trace = No > TraceFile = /tmp/odbc.log > Database = data > Server = 192.168.2.254 > UserName = ggibson > Password = > Port = 5432 > Protocol = 6.4 > ReadOnly = Yes > RowVersioning = Yes > ShowSystemTables = No > ShowOidColumn = No > FakeOidIndex = No > ConnSettings = SET SEARCH_PATH TO public > > [ODBC] > ;Tracing = 1 > ;TraceFile = /tmp/odbctrace.log > ;Debugging = 1 > ;DebugFile = /tmp/odbcdebug.log > > -- > George Gibson > J.B. Stamping, Inc. > 7413 Associate Ave. > Cleveland, OH 44144 > 216.631.0013 216.631.1327 fax > gtgibson@jbstamping.com > > > > > ---------------------------(end of > broadcast)--------------------------- > TIP 2: you can get off all lists at once with the > unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) __________________________________ Do you Yahoo!? Yahoo! Search - Find what you�re looking for faster http://search.yahoo.com
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.
>
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/
--- Shachar Shemesh <psql@shemesh.biz> wrote: > 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. Perhaps you are assuming that George is trying to update his views? But he's getting failure just on trying to select from 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 ctid value is globally unique for any given record. Of course it would be possible for a view to be constructed in such a way that the same ctid value could be selected for two separate "records". That is the only restriction that I can see on this approach. > 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. "create or replace view..." works on PostgreSQL versions 7.3 on, IIRC. "\dv viewname" to get view definition, "create or replace view ", copy and paste the view definition, edit to add what you want, hit enter and done. Easy! > > 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. Thinking about this some more, this has to be an OpenOffice issue, because I have not seen this problem elsewhere, even with "updateable cursors" set to true in the driver. Not sure what changing that setting would do to the rest of the application. I'm not sure that OpenOffice is even fully functional (yet) with PostgreSQL. Has anyone else found otherwise (I'd be glad to hear about it)? > > 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/ > > > ---------------------------(end of > broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org __________________________________ Do you Yahoo!? Yahoo! Mail - More reliable, more storage, less spam http://mail.yahoo.com
Jeff Eckermann wrote: >Perhaps you are assuming that George is trying to >update his views? But he's getting failure just on >trying to select from them... > > > The problem is that, in order to ALLOW updating, the ODBC need to do it's changes during the "Select" stage. This means that it doesn't really matter whether you are actually doing any updates, so long as you have not told the driver that you do not intend to do any. >>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. >> >> > >"create or replace view..." works on PostgreSQL >versions 7.3 on, IIRC. >"\dv viewname" to get view definition, "create or >replace view ", copy and paste the view definition, >edit to add what you want, hit enter and done. Easy! > > > update <table> doesn't change the table. update <table> changes values inside the table. I don't think you can issue "update <view>" at all. http://www.postgresql.org/docs/7.4/static/sql-update.html >Thinking about this some more, this has to be an >OpenOffice issue, because I have not seen this problem >elsewhere, even with "updateable cursors" set to true >in the driver. Not sure what changing that setting >would do to the rest of the application. > > OpenOffice has no way of knowing about these issues. It just uses an ODBC interface. Removing "updateable cursors" from the ODBC driver may actually solve this problem, but this will, of course, make OpenOffice read only. Shachar -- Shachar Shemesh Lingnu Open Systems Consulting http://www.lingnu.com/