Re: Copy from a SELECT

I'm not using PgAccess. Using psql with scripts.

The machine did indeed run out of memory. When it happened, I
could not even get 'ps' to run at the Sun prompt. Told me no
memory.
Below are the error messages.

> unexpected character M following empty query response ("I"
message)
> unexpected character T following empty query response ("I"
message)
> unexpected character T following empty query response ("I"
message)
> unexpected character T following empty query response ("I"
message)
> server sent data ("D" message) without prior row description
("T" message)

The amount of data I'm trying to copy out is appr 45G in total
from a single 170G table.

I know \o does not require a tmp table.

At the minute, I do something a bit "naughty"/risky.

I have gzipped the OID files for the other big tables not
involved in the select in order to free up HDD space locally.
Am running this "CREATE table tmp_event_t_2000 AS SELECT * from
event_t where end_t < 978307200;"
Once that is finished running, I will copy out that table as CSV
to the NTFS location drop the tmp_event_t_2000 table, and gunzip
the compressed oid files.

I hope it doesn't screw anything up. I have tested this bodge
method on a test dB on another machine. After I uncompressed
the oid files and re-started the postmaster, I was able to
successfully use the table whose oids where compressed.

I have a tight deadline to get this all done and am desperate.
This project was properly scoped and the person who started it
left. Enter me. Who is now grumpy. But is appreciative of
the help from this mailing list.

Thanks,
Val

>>
>> I've got *very* limited HDD space for the amount of
information I need
>to
>> copy out. The place to where the CSV file is would be output
is NTFS
>and
>> from the Postgres documentation, it states that I should not
put the
>table
>> oid files there and link in order to free up space in the
default
>drive.
>>
>> I've tried using \o to stream the output to a file on the
NTFS area,
>but the
>> machine ran out of memory (memory = 2G).
>Are you using PgAccess? Did you think about using psql,
possibly via
>script?
>I can hardly imagine psql would run a 2G machine out of memory.
>>
>> I was looking for a work around to copy out the section of
data I
>wanted to
>> a temporary table and then copy that table out .....
>Using \o FILENAME within psql does not even require a temp
table,
>because
>query results are simply sent to FILENAME.
>>
>Or did I misunderstand your intentions completely?
>
>Regards, Christoph
>
>