Skip site navigation (1) Skip section navigation (2)

Re: Copy from a SELECT

From: val(at)webtribe(dot)net
To: Christoph Haller <ch(at)rodos(dot)fzk(dot)de>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: Copy from a SELECT
Date: 2003-02-28 13:24:00
Message-ID: 20030228132417.57AF818332@cheetah.webtribe.net (view raw or flat)
Thread:
Lists: pgsql-sql
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
>
>


pgsql-sql by date

Next:From: Tomasz MyrtaDate: 2003-02-28 13:28:27
Subject: Re: DELETE FROM t WHERE EXISTS
Previous:From: Dawn HollingsworthDate: 2003-02-28 12:54:48
Subject: Query Against a dblink View Takes Too Long to Return

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group