Re: ERROR: column "ctid" does not exist

--- Shachar Shemesh <psql(at)shemesh(dot)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(at)jbstamping(dot)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(at)postgresql(dot)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