Обсуждение: Out of memry with large result set

Hello,

I am doing a query via psql on a huge database, and I want to have its
output piped to other unix processes.
As the result set is very big, I've got: "out of memory for query
result".
How can I manage that, without playing with cursors, as I do not want
to change the sql query ?

Under mysql, I have the same issue, but by using: mysql -quick, I have
what I want.
Is there something equivalent under postgresql ? Or should I use
another clients ?

Thanks,

Olivier

olivier.scalbert@algosyn.com wrote:
> I am doing a query via psql on a huge database, and I want to have its
> output piped to other unix processes.
> As the result set is very big, I've got: "out of memory for query
> result".
> How can I manage that, without playing with cursors, as I do not want
> to change the sql query ?
>
> Under mysql, I have the same issue, but by using: mysql -quick, I have
> what I want.
> Is there something equivalent under postgresql ? Or should I use
> another clients ?
>
>

Try copy (query) to stdout.

For me, psql sits at 4.9mb ram on a 3x10^16 row query.

klint.

--
Klint Gore
Database Manager
Sheep CRC
A.G.B.U.
University of New England
Armidale NSW 2350

Ph: 02 6773 3789
Fax: 02 6773 3266
EMail: kgore4@une.edu.au

> Try copy (query) to stdout.
>
> For me, psql sits at 4.9mb ram on a 3x10^16 row query.
>
> klint.
>

Thanks Klint.
Can I use a 'copy to' for a query ? I thought I can only do 'copy
table to stdout'
I will do some tests tomorrow and keep you inform.

Olivier

On Mon, Jul 14, 2008 at 11:53 PM, olivier.scalbert@algosyn.com
<olivier.scalbert@algosyn.com> wrote:
>
>> Try copy (query) to stdout.
>>
>> For me, psql sits at 4.9mb ram on a 3x10^16 row query.
>>
>> klint.
>>
>
> Thanks Klint.
> Can I use a 'copy to' for a query ? I thought I can only do 'copy
> table to stdout'
> I will do some tests tomorrow and keep you inform.

In 8.2.9 this works:

copy (select * from mytable) to stdout;