From: val@webtribe.net
To: Christoph Haller <ch@rodos.fzk.de>
Cc: pgsql-sql@postgresql.org
Subject: Re: Copy from a SELECT
Date: Fri, 28 Feb 2003 13:24 +0000
Message-ID: <20030228132417.57AF818332@cheetah.webtribe.net> (raw)
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
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: val@webtribe.net, ch@rodos.fzk.de
Subject: Re: Copy from a SELECT
In-Reply-To: <20030228132417.57AF818332@cheetah.webtribe.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox