Skip site navigation (1) Skip section navigation (2)

Re: psql large RSS (1.6GB)

From: Dustin Sallings <dustin(at)spy(dot)net>
To: TTK Ciar <ttk2(at)hardpoint(dot)ciar(dot)org>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: psql large RSS (1.6GB)
Date: 2004-10-31 06:02:54
Message-ID: 8132EBC8-2B02-11D9-A2DE-000A957659CC@spy.net (view raw or flat)
Thread:
Lists: pgsql-performance
On Oct 27, 2004, at 0:57, TTK Ciar wrote:

>   At a guess, it looks like the data set is being buffered in its
> entirety by psql, before any data is written to the output file,
> which is surprising.  I would have expected it to grab data as it
> appeared on the socket from postmaster and write it to disk.  Is
> there something we can do to stop psql from buffering results?
> Does anyone know what's going on here?

	Yes, the result set is sent back to the client before it can be used.  
An easy workaround when dealing with this much data is to use a cursor. 
  Something like this:

db# start transaction;
START TRANSACTION
db# declare logcur cursor for select * from some_table;
DECLARE CURSOR
db# fetch 5 in logcur;
[...]
(5 rows)

	This will do approximately what you expected the select to do in the 
first, place, but the fetch will decide how many rows to buffer into 
the client at a time.

>   If the solution is to just write a little client that uses perl
> DBI to fetch rows one at a time and write them out, that's doable,
> but it would be nice if psql could be made to "just work" without
> the monster RSS.

	It wouldn't make a difference unless that driver implements the 
underlying protocol on its own.

--
SPY                      My girlfriend asked me which one I like better.
pub  1024/3CAE01D5 1994/11/03 Dustin Sallings <dustin(at)spy(dot)net>
|    Key fingerprint =  87 02 57 08 02 D0 DA D6  C8 0F 3E 65 51 98 D8 BE
L_______________________ I hope the answer won't upset her. ____________


In response to

Responses

pgsql-performance by date

Next:From: Markus BertheauDate: 2004-10-31 10:27:01
Subject: Re: psql large RSS (1.6GB)
Previous:From: Markus BertheauDate: 2004-10-30 21:12:25
Subject: Re: can't handle large number of INSERT/UPDATEs

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group