Обсуждение: idea to have driver return immediately after a query
It was suggested by Robert Haas that it would be possible to return from a query immediately instead of reading the entire result set. Instead of reading it we just let the O/S buffer the results until we get around to reading it. Before I go to the trouble of prototyping this can anyone see a reason why this wouldn't work ?
On 25 March 2011 13:40, Dave Cramer <pg@fastcrypt.com> wrote: > It was suggested by Robert Haas that it would be possible to return > from a query immediately instead of reading the entire result set. > Instead of reading it we just let the O/S buffer the results until we > get around to reading it. Before I go to the trouble of prototyping > this can anyone see a reason why this wouldn't work ? What happens if the app then wants to run another query before reading the resultset? One common case is going to be run query - inspect resultset metadata - driver has to run internal queries to return that metadata. I'm a little worried about error handling too. For queries in a transaction, it might make sense to implement this via portals, much as done for fetchsize (i.e. always ask for only 1 row initially, and read that immediately; then reading the resultset beyond the first row triggers a fetch of the rest of the resultset as if you had set a large fetchsize). Then you don't have to worry about tying up the connection with an unread resultset. Though this means you have to use a named statement and lose the unnamed statement planning tweaks; and you will have to wait for the query to produce at least one row before returning. There are various things that the wire protocol / backend could do better here - a portal equivalent to DECLARE CURSOR WITH HOLD, and some way to say "defer planning on this named statement until Bind please", would both be useful. Oliver
> For queries in a transaction, it might make sense to implement this > via portals, much as done for fetchsize (i.e. always ask for only 1 > row initially... For what it's worth, you don't even need to ask for 1 row. At Truviso, we have a layer on top of the driver that asks for 0 rows initially, and this works fine (in terms of providing query metadata). There *may* be some plans with significant startup overhead where the cost of returning zero rows is much less than that of returning one row. Thanks, --- Maciek Sakrejda | System Architect | Truviso 1065 E. Hillsdale Blvd., Suite 215 Foster City, CA 94404 (650) 242-3500 Main www.truviso.com
On 25 March 2011 17:53, Maciek Sakrejda <msakrejda@truviso.com> wrote: >> For queries in a transaction, it might make sense to implement this >> via portals, much as done for fetchsize (i.e. always ask for only 1 >> row initially... > > For what it's worth, you don't even need to ask for 1 row. At Truviso, > we have a layer on top of the driver that asks for 0 rows initially, > and this works fine (in terms of providing query metadata). There > *may* be some plans with significant startup overhead where the cost > of returning zero rows is much less than that of returning one row. Asking for zero rows from a portal means "give me all the data", though. Oliver
On Thu, Mar 24, 2011 at 9:09 PM, Oliver Jowett <oliver@opencloud.com> wrote: > On 25 March 2011 13:40, Dave Cramer <pg@fastcrypt.com> wrote: >> It was suggested by Robert Haas that it would be possible to return >> from a query immediately instead of reading the entire result set. >> Instead of reading it we just let the O/S buffer the results until we >> get around to reading it. Before I go to the trouble of prototyping >> this can anyone see a reason why this wouldn't work ? > > What happens if the app then wants to run another query before reading > the resultset? One common case is going to be run query - inspect > resultset metadata - driver has to run internal queries to return that > metadata. I'm a little worried about error handling too. > > For queries in a transaction, it might make sense to implement this > via portals, much as done for fetchsize (i.e. always ask for only 1 > row initially, and read that immediately; then reading the resultset > beyond the first row triggers a fetch of the rest of the resultset as > if you had set a large fetchsize). Then you don't have to worry about > tying up the connection with an unread resultset. Though this means > you have to use a named statement and lose the unnamed statement > planning tweaks; and you will have to wait for the query to produce at > least one row before returning. > > There are various things that the wire protocol / backend could do > better here - a portal equivalent to DECLARE CURSOR WITH HOLD, and > some way to say "defer planning on this named statement until Bind > please", would both be useful. > > Oliver I've included Robert on this email as he intimated that he if protocol changes were made he would be interested in implementing them. Dave >
On Fri, Mar 25, 2011 at 6:33 AM, Dave Cramer <pg@fastcrypt.com> wrote: > On Thu, Mar 24, 2011 at 9:09 PM, Oliver Jowett <oliver@opencloud.com> wrote: >> On 25 March 2011 13:40, Dave Cramer <pg@fastcrypt.com> wrote: >>> It was suggested by Robert Haas that it would be possible to return >>> from a query immediately instead of reading the entire result set. >>> Instead of reading it we just let the O/S buffer the results until we >>> get around to reading it. Before I go to the trouble of prototyping >>> this can anyone see a reason why this wouldn't work ? >> >> What happens if the app then wants to run another query before reading >> the resultset? One common case is going to be run query - inspect >> resultset metadata - driver has to run internal queries to return that >> metadata. I'm a little worried about error handling too. >> >> For queries in a transaction, it might make sense to implement this >> via portals, much as done for fetchsize (i.e. always ask for only 1 >> row initially, and read that immediately; then reading the resultset >> beyond the first row triggers a fetch of the rest of the resultset as >> if you had set a large fetchsize). Then you don't have to worry about >> tying up the connection with an unread resultset. Though this means >> you have to use a named statement and lose the unnamed statement >> planning tweaks; and you will have to wait for the query to produce at >> least one row before returning. >> >> There are various things that the wire protocol / backend could do >> better here - a portal equivalent to DECLARE CURSOR WITH HOLD, and >> some way to say "defer planning on this named statement until Bind >> please", would both be useful. >> >> Oliver > > I've included Robert on this email as he intimated that he if protocol > changes were made he would be interested in implementing them. I was more interesting in helping review, but I do think it might be about time to consider a protocol version bump, if we can gather together in one bundle all the needs that aren't satisfied by the current version. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company