Re: Working with very large datasets - Mailing list pgsql-sql
| From | Wilkinson Charlie E |
|---|---|
| Subject | Re: Working with very large datasets |
| Date | |
| Msg-id | 9DDDC002B736D711A7320004AC3699EB01C195D6@nct0010mb03.nc.no.irs.gov Whole thread Raw |
| In response to | Working with very large datasets (Wilkinson Charlie E <Charlie.E.Wilkinson@irs.gov>) |
| List | pgsql-sql |
<p><font size="2">> -----Original Message-----</font><br /><font size="2">> From: Stephan Szabo [<a
href="mailto:sszabo@megazone23.bigpanda.com">mailto:sszabo@megazone23.bigpanda.com</a>]</font><br/><font
size="2">[...]</font><br/><font size="2">> Was it psql that grew to 800Mb or a backend? If the former, </font><br
/><fontsize="2">> how many rows</font><br /><font size="2">> do you expect that to return? You probably want to
lookinto using</font><br /><font size="2">> cursors rather than returning the entire result set at
once.</font><p><fontsize="2">It was the front end, psql that got big. postmaster has been pretty</font><br /><font
size="2">wellbehaved through all this. I'm looking to get the full 100M+</font><br /><font size="2">records back. I
haveto print it all out and fax them to someone. ;)</font><br /><font size="2">Seriously, I want to dump it all to a
(big!)file and sift through</font><br /><font size="2">it with some familiar unix tools to check the data and
generally</font><br/><font size="2">look it over.</font><p><font size="2">Thanks to Stephan and Lex for the tips! I
tookyour CURSOR and LIMIT</font><br /><font size="2">clues and ran with them. I looked at straight SQL>psql and
PerlDBD:Pg</font><br /><font size="2">for solutions, but the end result was some "ecpg" C code:</font><p><font
size="2">----------------------------------</font><br/><font size="2">#include <stdio.h></font><br /><font
size="2">#defineNO_MORE_TUPLES (sqlca.sqlcode == ECPG_NOT_FOUND)</font><p><font size="2">int main(int argc, char*
agv[]){</font><p><fontsize="2"> EXEC SQL BEGIN DECLARE SECTION;</font><br /><font size="2"> int
myuser;</font><br/><font size="2"> char myname[121];</font><br /><font size="2"> int mysize;</font><br
/><fontsize="2"> int mymtime;</font><br /><font size="2"> EXEC SQL END DECLARE SECTION;</font><p><font
size="2"> EXEC SQL INCLUDE sqlca;</font><br /><font size="2"> EXEC SQL CONNECT TO userdb USER
xxxxxxx;</font><br/><font size="2"> EXEC SQL WHENEVER sqlerror sqlprint;</font><p><font size="2"> EXEC
SQLDECLARE mycursor CURSOR FOR SELECT user,name,size,mtime</font><br /><font size="2"> FROM users JOIN files
USING(fileid) ORDER BY user;</font><p><font size="2"> EXEC SQL OPEN mycursor;</font><br /><font size="2">
while(1){</font><br/><font size="2"> EXEC SQL FETCH FROM mycursor</font><br /><font
size="2"> INTO :myuser,:myname,:mysize,:mymtime;</font><br /><font size="2">
if(NO_MORE_TUPLES)</font><br/><font size="2"> break;</font><br /><font size="2">
myname[120]= '\0';</font><br /><font size="2"> printf("%d\t%s\t%d\t%d\n", myuser, myname, mysize,
mymtime);</font><br/><font size="2"> }</font><br /><font size="2"> EXEC SQL CLOSE
mycursor;</font><p><fontsize="2"> exit(0);</font><br /><font size="2">}</font><br /><font
size="2">-----------------------------------</font><p><fontsize="2">No idea how long it's going to take to complete its
run,but it</font><br /><font size="2">faithfully starts spitting out the records shortly after being</font><br /><font
size="2">invoked. Thanks again guys!</font><p><font size="2">For those interested, more helpful hints & examples on
epgc,</font><br/><font size="2">libpq and related goodies here:</font><br /><font size="2"> <a
href="http://www.cse.ucsc.edu/classes/cmps180/Winter02/proj5.html"
target="_blank">http://www.cse.ucsc.edu/classes/cmps180/Winter02/proj5.html</a></font><br/> <font size="2"> -
thanksto UCSC and Google. :-)</font><p><font size="2">-cw-</font>