Received: from sss2.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2]) by hub.org (8.9.3/8.9.3) with ESMTP id UAA93957 for ; Wed, 16 Feb 2000 20:47:25 -0500 (EST) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss2.sss.pgh.pa.us (8.9.3/8.9.3) with ESMTP id UAA05291; Wed, 16 Feb 2000 20:47:05 -0500 (EST) To: "Bryan White" cc: pgsql-interfaces@postgreSQL.org Subject: Re: [INTERFACES] Managing the memory requierments of large query results In-reply-to: <005b01bf78cc$634cc3e0$2dd260d1@arcamax.com> References: <005b01bf78cc$634cc3e0$2dd260d1@arcamax.com> Comments: In-reply-to "Bryan White" message dated "Wed, 16 Feb 2000 17:23:02 -0500" Date: Wed, 16 Feb 2000 20:47:05 -0500 Message-ID: <5288.950752025@sss.pgh.pa.us> From: Tom Lane "Bryan White" writes: > It is my understanding that when a query is issued the backend runs the > query and accumulates the results in memory and when it completes it > transmits the entire result set to the front end. No, the backend does not accumulate the result; it transmits tuples to the frontend on-the-fly. The current implementation of frontend libpq does buffer the result rows on the frontend side, because it presents a random-access-into-the-query-result API to the client application. (There's been talk of offering an alternative API that eliminates the buffering and the random-access option, but nothing's been done yet.) > I have studied the documentation and found Cursors and Asyncronous Query > Processing. Cursors seems to solve the problem on the front end but I get > the impression the back end will buffer the entire result until the cursor > is closed. A cursor should solve the problem just fine. If you can put your finger on what part of the documentation misled you, maybe we can improve it. > Asyncronous Query Processing as I understand it is more about not blocking > the client during the query and it does not fundementally alter the result > buffering on either end. Correct, it just lets a single-threaded client continue to do other stuff while waiting for the (whole) result to arrive. regards, tom lane