Cc: pgsql-sql@postgresql.org
Subject: Re: Copy from a SELECT
Content-type: text/plain
Date: Fri, 28 Feb 2003 13:24 +0000
Mime-version: 1.0
To: Christoph Haller <ch@rodos.fzk.de>
From: val@webtribe.net
Message-Id: <20030228132417.57AF818332@cheetah.webtribe.net>
Content-Transfer-Encoding: quoted-printable

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

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

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

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

I know \o does not require a tmp table.=20=20

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

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

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

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

Thanks,
Val


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