Re: Incremental results from libpq

Frank,

With a lot of things Oracle the real answer is: it depends.

For a simple "select * from <table_name>" even with a where
clause you may simply get the results one row at a time as they are
extracted & deemed appropriate for the result set. But if you've
included a group by or order by clause, or a union/intersect/minus
clause then yes, the database will assemble results before sending them
to the client.

Also Oracle supports cursors which allow you to pop one or more
rows off of the result set at a time. The way Postgresql returns data
all at once is different.

Dick Goulet
Senior Oracle DBA
Oracle Certified DBA

-----Original Message-----
From: pgsql-interfaces-owner(at)postgresql(dot)org
[mailto:pgsql-interfaces-owner(at)postgresql(dot)org] On Behalf Of Frank van
Vugt
Sent: Thursday, November 10, 2005 3:33 PM
To: Greg Stark
Cc: pgsql-interfaces(at)postgresql(dot)org
Subject: Re: [INTERFACES] Incremental results from libpq

> > If I'm informed correctly then Oracle and others are generating
> > the complete static result set on the server-side, which will then
> > stay cached until all rows/chunks are fetched.

> That's obviously not true. Try doing "select * from huge_table" on
Oracle
> and you'll see records start appearing immediately.

Then it seems I haven't (been informed correctly) :)

Now, given that OCI is offering some kind of asynchronous interface as
well,
how are they handling an error occuring after a partial result-set has
been
retrieved by the client?

--
Best,

Frank.

---------------------------(end of broadcast)---------------------------
TIP 1: 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

Goulet, Dick wrote:

> For a simple "select * from <table_name>" even with a where
> clause you may simply get the results one row at a time as they are
> extracted & deemed appropriate for the result set. But if you've
> included a group by or order by clause, or a union/intersect/minus
> clause then yes, the database will assemble results before sending them
> to the client.

So, what happens with the 1/x query Tom mentioned? How does Oracle
handles that situation? Notice there's no special clause in the query
itself, so if it's extracted and returned, there's no way for the server
to know that there's a problem laying ahead.

> Also Oracle supports cursors which allow you to pop one or more
> rows off of the result set at a time. The way Postgresql returns data
> all at once is different.

Postgres supports cursors too. The Qt guys, and everyone else, could be
using it to get incremental results right now, no libpq mods necessary.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> So, what happens with the 1/x query Tom mentioned? How does Oracle
> handles that situation? Notice there's no special clause in the query
> itself, so if it's extracted and returned, there's no way for the server
> to know that there's a problem laying ahead.

>> Also Oracle supports cursors which allow you to pop one or more
>> rows off of the result set at a time. The way Postgresql returns data
>> all at once is different.

> Postgres supports cursors too. The Qt guys, and everyone else, could be
> using it to get incremental results right now, no libpq mods necessary.

Note that in this context, it's really pretty misleading to make any
blanket assertions about "Postgres does this" or "Postgres does that".
The behavior is partially determined by the client-side code, and
might well differ depending on which client library you are using,
as well as how you are using it.

For all I know, similar remarks might apply to Oracle. Do they support
more than one application-side API?

regards, tom lane

Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:

> Postgres supports cursors too. The Qt guys, and everyone else, could be
> using it to get incremental results right now, no libpq mods necessary.

Not really since the way Postgres supports cursors is at the SQL level. Users
of Qt and every other driver could be using cursors if their drivers support
them, but Qt can't really be reasonably expected to go into users' SQL and
modify them to use cursors.

Moreover cursors aren't really that great a substitute. With cursors you have
to manually fetch individual records. You're just trading off the
inefficiencies of batching up all the results for entirely different
inefficiencies. Now for every record you retrieve you need a network round
trip as well as a round trip down through your driver, the kernel layers on
both machines, and the backend as well.

The efficient approach as Oracle and other mature network layers implement is
to issue the query once, then pipeline the results back to the application
buffering a substantial amount in the driver. DBD::Oracle goes to some lengths
to ensure the number of records buffered is a reasonable multiple of the
default TCP mss of 1500 bytes.

So even though the application only retrieves one record at a time it's just
pulling it out of an array that's already prefilled. When the array gets low
the next block of records is retrieved from the server (where they're probably
already buffered as well). The result is a constant flow of network traffic
that keeps the application and network as busy as possible.

--
greg