Re: select * and save into a text file failed

On Friday 10 June 2005 10:03 am, Lee Wu wrote:
> That is exactly what I did:
>
> \o a_lot_room_to_hold_my_result
> select * from a_table
>
> either
> 1. out of memory for query result
> 2. killed
> 3. crash PG
>
> "If you have a very large table you can exhaust memory on the
> client side unless you are writing the data directly to a file."
> How besides "\o" and pg_dump?
>
> We have 4G RAM, and shared_buffers= 32768, it is a dedicate test
> box, while the table is about 2G.

Something to try (I don't know if it will work because I don't know
the exact internals of the PG libraries but it's something I've
observed): set the output formatting to unaligned (\pset format
unaligned).

I had a situation once where selecting a few thousand rows exhausted
my RAM. The problem was that one item in a text column was ~8k long
which meant that every other row had 8k of padding. And this was the
case for more than one column so the pager was having to swallow gobs
of data most of which was blank space. I ran the output to a file and
ran some tests. Switching to unaligned output dropped the size by
orders of magnitude. Depending on the nature of your data this may
help or do nothing at all.

Of course you are going to hit RAM or disk limitations on any given
machine. Cursors are there for your use and your project may require
them.

Cheers,
Steve