Re: Incremental results from libpq

Alvaro,

Your observation is true. In the query specified by Tom the
server has absolutely no idea that something is a miss in the coming
rows. It is absolutely normal to have a query like that return several
rows and then yack out the error. Here's an example: You issue a query
to return all of the rows in a mega-row table at time A. A second
individual updates say half of the rows at time B which is after you've
gotten 20% or the returned records. He/she then commits the transaction
permanently modifying the data. Now Oracle has retained the original
row values in the rollback or undo segments, but for argument's sake
assume that you've stopped retrieving data for a while, long enough for
the undo retention period to expire, and then continue retrieving data.
It is not uncommon in this case for some additional time to pass after
which you get an ORA-01555 Snapshot too old return message since Oracle
can no longer create a read consistent view of the data as of time A.
Something like this is also very common with those who "commit across a
cursor" which is another discussion altogether.

In any case the server does two things almost at once: 1) send
you the error message and set sqlca.sqlcode to -1555, 2) close the
cursor. Additional calls for data return an additional error message of
cursor not open.

-----Original Message-----
From: Alvaro Herrera [mailto:alvherre(at)commandprompt(dot)com]
Sent: Sunday, November 13, 2005 10:24 AM
To: Goulet, Dick
Cc: Frank van Vugt; Greg Stark; pgsql-interfaces(at)postgresql(dot)org
Subject: Re: [INTERFACES] Incremental results from libpq

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.