dumping 8M bit fields

On Thu, Sep 21, 2006 at 08:14:49PM -0400, Rajarshi Guha wrote:

Hi, I have a table with 8M rows and one of the fields is a bit (1024
bits) field. I am trying to dump the bit field for 8M rows to a file:

<snip>

out of memory for query result

I am surprised since I did an equivalent query but selecting varchar
field for 6.9M rows which worked fine.

The memory needed is related to how much screen space it takes. So a
1024 bitfield takes 1024 bytes to display. Times 6.9M rows is about 7GB
of data.

If the itention is to get all the data out into a file, try COPY. That
just dumps the data, without trying to store it first.

Hope this helps,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

On Thu, 2006-09-21 at 20:14 -0400, Rajarshi Guha wrote:

Hi, I have a table with 8M rows and one of the fields is a bit (1024
bits) field. I am trying to dump the bit field for 8M rows to a file:

psql -U cicc3 -A -F " " -t -o pubchem.fp -c "select cid,gfp from
pubchem_compound;

However I get

out of memory for query result

psql is trying to load all of the data into RAM before outputting any of
it. More specifically, it's trying to load all of the output
representations of all the data into RAM before outputting it.

For 1024 bits, the output representation will be 1k. For 8M rows that's
a lot of RAM needed.

It would be better to use something like COPY or a cursor.

Regards,
Jeff Davis