Thread: Postgresql & Oracle Heteregenous services - strange behaviour
I'm not sure if it's proper group, if not then please direct me to more
appropriate one (unixodbc?)
I've set up connection from oracle to postgresql via unixodbc and oracle's
heterogeneus services. The connection works, but when I issue the
following:
select "p_nr_pesel" from "zew_patients"@my_postgresql where
"p_patient_id"=19300;
I see this in postgresql logs:
[6210] DEBUG: query: select * from "zew_patients"
[6210] DEBUG: query: SELECT "A1"."p_nr_pesel" FROM "zew_patients"
"A1" WHERE ("A1"."p_patient_id" = 19300)
The point is, I don't why oh why the first select is issued (some cache?),
but it slows the query a lot for obvious reasons. When I issue it for the
second time in the same session, I see only the second select (that's how
it should be from my point of view). After reconnecting I see both selects
again :(.
If it's known issue, I'd be grateful even for simple RTFM and a link :).
Thank you for your time,
Marcin
--
| And Do What You Will be the challenge | http://apcoln.linuxpl.org
| So be it in love that harms none | http://biznes.linux.pl
| For this is the only commandment. | http://www.juanperon.info
`---* JID: Aragorn_Vime@jabber.org *---' http://www.naszedzieci.org
On Wed, Jan 17, 2007 at 01:52:25PM +0100, Marcin St?pnicki wrote:
> I see this in postgresql logs:
>
> [6210] DEBUG: query: select * from "zew_patients"
> [6210] DEBUG: query: SELECT "A1"."p_nr_pesel" FROM "zew_patients"
> "A1" WHERE ("A1"."p_patient_id" = 19300)
>
> The point is, I don't why oh why the first select is issued (some cache?),
<speculation level="rank">
I bet it's getting the column list from the table or some such thing.
This is a lousy way to do it (the information_schema would be more
correct, although maybe no faster).
The reason it isn't repeated, I bet, is that your connection is
persistent, so the information gets cached.
</speculation>
A
--
Andrew Sullivan | ajs@crankycanuck.ca
Everything that happens in the world happens at some place. --Jane Jacobs
Andrew Sullivan wrote:
> On Wed, Jan 17, 2007 at 01:52:25PM +0100, Marcin St?pnicki wrote:
>> I see this in postgresql logs:
>>
>> [6210] DEBUG: query: select * from "zew_patients"
>> [6210] DEBUG: query: SELECT "A1"."p_nr_pesel" FROM "zew_patients"
>> "A1" WHERE ("A1"."p_patient_id" = 19300)
>>
>> The point is, I don't why oh why the first select is issued (some cache?),
>
> <speculation level="rank">
>
> I bet it's getting the column list from the table or some such thing.
> This is a lousy way to do it (the information_schema would be more
> correct, although maybe no faster).
That'd be my guess. And then it's not fetching any rows, expecting
cursor-like behaviour. Of course we fetch all the rows before returning
any results.
The real solution would be to add "LIMIT 0" or "LIMIT 1" to the
column-finding query, but I doubt that's possible with the Oracle plugin.
Perhaps check if there's a "fetch N rows at a time" option for the ODBC
setup that might help you.
-- Richard Huxton Archonet Ltd
Dnia Wed, 17 Jan 2007 13:04:28 +0000, Richard Huxton napisał(a): > Andrew Sullivan wrote: >> I bet it's getting the column list from the table or some such thing. >> This is a lousy way to do it (the information_schema would be more >> correct, although maybe no faster). > > That'd be my guess. And then it's not fetching any rows, expecting > cursor-like behaviour. Of course we fetch all the rows before returning > any results. Bingo! select * from from "zew_patients"@my_postgresql results in select a1.col1, a1.col2, a1.col3 from zew_patients in postgresql logs. > The real solution would be to add "LIMIT 0" or "LIMIT 1" to the > column-finding query, but I doubt that's possible with the Oracle plugin. > Perhaps check if there's a "fetch N rows at a time" option for the ODBC > setup that might help you. Thank you both, I'll poke around and drop a note when I find something :). -- | And Do What You Will be the challenge | http://apcoln.linuxpl.org | So be it in love that harms none | http://biznes.linux.pl | For this is the only commandment. | http://www.juanperon.info `---* JID: Aragorn_Vime@jabber.org *---' http://www.naszedzieci.org
Dnia Wed, 17 Jan 2007 13:04:28 +0000, Richard Huxton napisał(a): > That'd be my guess. And then it's not fetching any rows, expecting > cursor-like behaviour. Of course we fetch all the rows before returning > any results. > > The real solution would be to add "LIMIT 0" or "LIMIT 1" to the > column-finding query, but I doubt that's possible with the Oracle plugin. > > Perhaps check if there's a "fetch N rows at a time" option for the ODBC > setup that might help you. I've tried various settings, upgraded to psqlodbc 8.02.0200, looked into the source (info.c, retry_public_schema label) and there's a query which gets the columns (select n.nspname, c.relname, a.attname, a.atttypid (...)), I now can even see it in logs but there's still this additional select before. I think it's not psqlodbc problem, because when I issue my queries directly from isql from unixodbc everything seems normal - I get this additional select only when quering from Oracle using @my_server. Therefore, I think it is Oracle's fault and try to move this topic to Metalink. Thank you again. -- | And Do What You Will be the challenge | http://apcoln.linuxpl.org | So be it in love that harms none | http://biznes.linux.pl | For this is the only commandment. | http://www.juanperon.info `---* JID: Aragorn_Vime@jabber.org *---' http://www.naszedzieci.org