Thread: ERROR: column "ctid" does not exist

ERROR: column "ctid" does not exist

From
"George T. Gibson"
Date:
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




Re: ERROR: column "ctid" does not exist

From
Jeff Eckermann
Date:
--- "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

Re: ERROR: column "ctid" does not exist

From
"George T. Gibson"
Date:
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.
>


Re: ERROR: column "ctid" does not exist

From
Shachar Shemesh
Date:
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/


Re: ERROR: column "ctid" does not exist

From
Jeff Eckermann
Date:
--- 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

Re: ERROR: column "ctid" does not exist

From
Shachar Shemesh
Date:
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/