pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedPostgresql & Oracle Heteregenous services - strange behaviour
5+ messages / 3 participants
[nested] [flat]
* Postgresql & Oracle Heteregenous services - strange behaviour
@ 2007-01-17 12:52 Marcin Stępnicki <mstepnicki@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Marcin Stępnicki @ 2007-01-17 12:52 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Postgresql & Oracle Heteregenous services - strange behaviour
@ 2007-01-17 12:58 Andrew Sullivan <ajs@crankycanuck.ca>
parent: Marcin Stępnicki <mstepnicki@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Andrew Sullivan @ 2007-01-17 12:58 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Postgresql & Oracle Heteregenous services - strange behaviour
@ 2007-01-17 13:04 Richard Huxton <dev@archonet.com>
parent: Andrew Sullivan <ajs@crankycanuck.ca>
0 siblings, 2 replies; 5+ messages in thread
From: Richard Huxton @ 2007-01-17 13:04 UTC (permalink / raw)
To: Andrew Sullivan <ajs@crankycanuck.ca>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Postgresql & Oracle Heteregenous services - strange behaviour
@ 2007-01-17 13:25 Marcin Stępnicki <mstepnicki@gmail.com>
parent: Richard Huxton <dev@archonet.com>
1 sibling, 0 replies; 5+ messages in thread
From: Marcin Stępnicki @ 2007-01-17 13:25 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Postgresql & Oracle Heteregenous services - strange behaviour
@ 2007-01-19 13:14 Marcin Stępnicki <mstepnicki@gmail.com>
parent: Richard Huxton <dev@archonet.com>
1 sibling, 0 replies; 5+ messages in thread
From: Marcin Stępnicki @ 2007-01-19 13:14 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2007-01-19 13:14 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-01-17 12:52 Postgresql & Oracle Heteregenous services - strange behaviour Marcin Stępnicki <mstepnicki@gmail.com>
2007-01-17 12:58 ` Andrew Sullivan <ajs@crankycanuck.ca>
2007-01-17 13:04 ` Richard Huxton <dev@archonet.com>
2007-01-17 13:25 ` Marcin Stępnicki <mstepnicki@gmail.com>
2007-01-19 13:14 ` Marcin Stępnicki <mstepnicki@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox