X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 06FDD9FA5C8 for ; Mon, 5 Jun 2006 13:47:25 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 43221-04 for ; Mon, 5 Jun 2006 13:47:16 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from outland.mohawksoft.com (unknown [64.46.156.80]) by postgresql.org (Postfix) with ESMTP id C35A09FA12F for ; Mon, 5 Jun 2006 13:47:15 -0300 (ADT) Received: from outland.mohawksoft.com (localhost [127.0.0.1]) by outland.mohawksoft.com (8.13.6/8.9.3) with ESMTP id k55H1oYJ007297; Mon, 5 Jun 2006 13:01:50 -0400 Received: (from mohawk@localhost) by outland.mohawksoft.com (8.13.6/8.13.3/Submit) id k55H1jno007296; Mon, 5 Jun 2006 13:01:45 -0400 X-Authentication-Warning: outland.mohawksoft.com: mohawk set sender to pgsql@mohawksoft.com using -f Received: from 24.91.171.78 (SquirrelMail authenticated user pgsql) by mail.mohawksoft.com with HTTP; Mon, 5 Jun 2006 13:01:45 -0400 (EDT) Message-ID: <18067.24.91.171.78.1149526905.squirrel@mail.mohawksoft.com> In-Reply-To: <44845E86.3080001@dunslane.net> References: <44835834.2020309@dunaweb.hu> <44835F86.5040008@dunaweb.hu> <20060605152258.GT53487@pervasive.com> <20512.1149521250@sss.pgh.pa.us> <20060605155349.GU53487@pervasive.com> <20813.1149523235@sss.pgh.pa.us> <18006.24.91.171.78.1149526092.squirrel@mail.mohawksoft.com> <44845E86.3080001@dunslane.net> Date: Mon, 5 Jun 2006 13:01:45 -0400 (EDT) Subject: Re: [PERFORM] psql -A (unaligned format) eats too much From: "Mark Woodward" To: "Andrew Dunstan" Cc: "Tom Lane" , "Jim C. Nasby" , "Zoltan Boszormenyi" , pgsql-hackers@postgresql.org User-Agent: SquirrelMail/1.4.5 MIME-Version: 1.0 Content-Type: text/plain;charset=iso-8859-1 Content-Transfer-Encoding: 8bit X-Priority: 3 (Normal) Importance: Normal X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200606/164 X-Sequence-Number: 84397 > Mark Woodward wrote: >>> "Jim C. Nasby" writes: >>> >>>> On Mon, Jun 05, 2006 at 11:27:30AM -0400, Tom Lane wrote: >>>> >>>>> I'm reading this as just another uninformed complaint about libpq's >>>>> habit of buffering the whole query result. It's possible that >>>>> there's >>>>> a memory leak in the -A path specifically, but nothing said so far >>>>> provided any evidence for that. >>>>> >>>> Certainly seems like it. It seems like it would be good to allow for >>>> libpq not to buffer, since there's cases where it's not needed... >>>> >>> See past discussions. The problem is that libpq's API says that when >>> it >>> hands you back the completed query result, the command is complete and >>> guaranteed not to fail later. A streaming interface could not make >>> that >>> guarantee, so it's not a transparent substitution. >>> >>> I wouldn't have any strong objection to providing a separate API that >>> operates in a streaming fashion, but defining it is something no one's >>> bothered to do yet. In practice, if you have to code to a variant API, >>> it's not that much more trouble to use a cursor... >>> >>> >> >> Wouldn't the "COPY (select ...) TO STDOUT" format being discussed solve >> this for free? >> >> >> > > It won't solve it in the general case for clients that expect a result > set. ISTM that "use a cursor" is a perfectly reasonable answer, though. I'm not sure I agree -- surprise! psql is often used as a command line tool and using a cursor is not acceptable. Granted, with an unaligned output, perhaps psql should not buffer the WHOLE result at once, but without rewriting that behavior, a COPY from query may be close enough.