postgres ODBC problem with view

classic Classic list List threaded Threaded
10 messages Options
Reply | Threaded
Open this post in threaded view
|

postgres ODBC problem with view

Reizinger Zoltán
  Hi all,

See View problem in forum post:
http://user.services.openoffice.org/en/forum/viewtopic.php?f=40&t=34843&p=159735#p159735

I can reproduce it with any OOo, 3.2.1, OOO330m9, DEV300m89, on win7
32bit, with psqlODBC0804, and postgersql 9.0.
I created a view in psql, I can access it via JDBC driver, but get error
with ODBC:

The data content could not be loaded.
SQL Status: 42703
Error code: 7
ERROR: column "ctid" does not exist;
Error while executing the query
The SQL command leading to this error is:
SELECT * FROM "openoffice"."public"."Test_view"

I found similar problem in issue tracker, with different error message:
http://qa.openoffice.org/issues/show_bug.cgi?id=10229
I think it is similar problem, and we can not solve it OOo, it is a
driver problem?
Thanks,
Zoltan


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: postgres ODBC problem with view

Josh Berkus
Zoltan,

> The data content could not be loaded.
> SQL Status: 42703
> Error code: 7
> ERROR: column "ctid" does not exist;

That's very odd. ctid is a system column; no driver should be accessing
it, for any reason.

--
                                  -- Josh Berkus
                                     PostgreSQL Experts Inc.
                                     http://www.pgexperts.com

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: postgres ODBC problem with view

Frank Schönheit
Hi Josh/Zoltan,

>> The data content could not be loaded.
>> SQL Status: 42703
>> Error code: 7
>> ERROR: column "ctid" does not exist;
>
> That's very odd. ctid is a system column; no driver should be accessing
> it, for any reason.

Ocke knows more about this, but I suspect the PQ driver might deliver
that column's name as BestRowIdentifier, which might tempt our RowSet to
use it in some kind of "SELECT * FROM <table> WHERE ctid=?" statement
(for re/fetching rows).

Ocke?

Ciao
Frank
--
ORACLE
Frank Schönheit | Software Engineer | [hidden email]
Oracle Office Productivity: http://www.oracle.com/office

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: postgres ODBC problem with view

Josh Berkus

> Ocke knows more about this, but I suspect the PQ driver might deliver
> that column's name as BestRowIdentifier, which might tempt our RowSet to
> use it in some kind of "SELECT * FROM <table> WHERE ctid=?" statement
> (for re/fetching rows).

If so, that's rather problematic.  The CTID is a physical pointer ID,
and as such can change out from under a current session if there are
concurrent updates.  Using the CTID to grab rows could result in errors
or even seeing expired data.

Aside from the fact that CTIDs are completely unindexed.

--
                                  -- Josh Berkus
                                     PostgreSQL Experts Inc.
                                     http://www.pgexperts.com

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: postgres ODBC problem with view

Frank Schönheit
Hi Josh,

> If so, that's rather problematic.  The CTID is a physical pointer ID,
> and as such can change out from under a current session if there are
> concurrent updates.  Using the CTID to grab rows could result in errors
> or even seeing expired data.

Which means SCOPE would be something other than bestRowSession - and the
bug on OOo side would be to use CTID for the given purpose nonetheless.

@Zoltan: Care to submit an issue?

Ciao
Frank
--
ORACLE
Frank Schönheit | Software Engineer | [hidden email]
Oracle Office Productivity: http://www.oracle.com/office

---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: postgres ODBC problem with view

Reizinger Zoltán
  2010.10.11. 21:26 keltezéssel, Frank Schönheit írta:

> Hi Josh,
>
>> If so, that's rather problematic.  The CTID is a physical pointer ID,
>> and as such can change out from under a current session if there are
>> concurrent updates.  Using the CTID to grab rows could result in errors
>> or even seeing expired data.
> Which means SCOPE would be something other than bestRowSession - and the
> bug on OOo side would be to use CTID for the given purpose nonetheless.
>
> @Zoltan: Care to submit an issue?
Done: http://qa.openoffice.org/issues/show_bug.cgi?id=115024
Zoltan
> Ciao
> Frank


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: postgres ODBC problem with view

Ocke Janssen-2
In reply to this post by Frank Schönheit
  Moin,
On 10/11/10 21:06, Frank Schönheit wrote:

> Hi Josh/Zoltan,
>
>>> The data content could not be loaded.
>>> SQL Status: 42703
>>> Error code: 7
>>> ERROR: column "ctid" does not exist;
>> That's very odd. ctid is a system column; no driver should be accessing
>> it, for any reason.
> Ocke knows more about this, but I suspect the PQ driver might deliver
> that column's name as BestRowIdentifier, which might tempt our RowSet to
> use it in some kind of "SELECT * FROM<table>  WHERE ctid=?" statement
> (for re/fetching rows).
I just groked for getBestRowIdentifier and it isn't used anywhere. So
the problem is some where else. I'll take a look at it.

- oj
> Ocke?
>
> Ciao
> Frank



---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: postgres ODBC problem with view

Ocke Janssen-2
In reply to this post by Frank Schönheit
  Moin,
On 10/11/10 21:06, Frank Schönheit wrote:

> Hi Josh/Zoltan,
>
>>> The data content could not be loaded.
>>> SQL Status: 42703
>>> Error code: 7
>>> ERROR: column "ctid" does not exist;
>> That's very odd. ctid is a system column; no driver should be accessing
>> it, for any reason.
> Ocke knows more about this, but I suspect the PQ driver might deliver
> that column's name as BestRowIdentifier, which might tempt our RowSet to
> use it in some kind of "SELECT * FROM<table>  WHERE ctid=?" statement
> (for re/fetching rows).
I just groked for getBestRowIdentifier and it isn't used anywhere. So
the problem is some where else. I'll take a look at it.

- oj
> Ocke?
>
> Ciao
> Frank



---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: postgres ODBC problem with view

Ocke Janssen-2
Sorry for the duplicates. But it is working again ;-)

-oj

On 10/13/10 08:01, Ocke Janssen wrote:

> Moin,
> On 10/11/10 21:06, Frank Schönheit wrote:
>> Hi Josh/Zoltan,
>>
>>>> The data content could not be loaded.
>>>> SQL Status: 42703
>>>> Error code: 7
>>>> ERROR: column "ctid" does not exist;
>>> That's very odd. ctid is a system column; no driver should be accessing
>>> it, for any reason.
>> Ocke knows more about this, but I suspect the PQ driver might deliver
>> that column's name as BestRowIdentifier, which might tempt our RowSet to
>> use it in some kind of "SELECT * FROM<table> WHERE ctid=?" statement
>> (for re/fetching rows).
> I just groked for getBestRowIdentifier and it isn't used anywhere. So
> the problem is some where else. I'll take a look at it.
>
> - oj
>> Ocke?
>>
>> Ciao
>> Frank
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [hidden email]
> For additional commands, e-mail: [hidden email]
>


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: postgres ODBC problem with view

Ocke Janssen-2
In reply to this post by Frank Schönheit
Moin,

On 10/11/10 21:06, Frank Schönheit wrote:

> Hi Josh/Zoltan,
>
>>> The data content could not be loaded.
>>> SQL Status: 42703
>>> Error code: 7
>>> ERROR: column "ctid" does not exist;
>>
>> That's very odd. ctid is a system column; no driver should be accessing
>> it, for any reason.
>
> Ocke knows more about this, but I suspect the PQ driver might deliver
> that column's name as BestRowIdentifier, which might tempt our RowSet to
> use it in some kind of "SELECT * FROM<table>  WHERE ctid=?" statement
> (for re/fetching rows).
>
> Ocke?
The problem is that Postgres doesn't support updateable cursor for
views. Already discussed in 2004 :-)

http://archives.postgresql.org/pgsql-odbc/2004-03/msg00045.php

Solution:
http://comments.gmane.org/gmane.comp.openoffice.dba.devel/3037



Best regards,

Ocke

>
> Ciao
> Frank


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]