On May 15, 2010, at 7:28 PM, Tom Lane wrote:
Tom Duffey <
tduffey@trillitech.com> writes:
On May 15, 2010, at 4:51 PM, Tom Lane wrote:
What's being done on the client side with the data?
I am executing the query in psql at the command line and piping the
result to a file, e.g.,
psql < get_data.sql > data.sql
Well, I tried executing a large "copy (select ...)" query and couldn't
see any memory bloat at all in either the backend or psql. So there's
something relevant that you haven't told us.
Could we see the full schema (eg via psql \dt) for the table being
copied?
I hope you are right! The actual query is different because I was not aware until right before I posted this question that you can have a WHERE clause with COPY. Here is the actual query I ran:
SELECT point_id || E'\t' || status || E'\t' || value || E'\t' || timestamp
FROM point_history
WHERE timestamp > NOW() - interval '18 months';
And here is the table schema:
prod=> \dt point_history
List of relations
Schema | Name | Type | Owner
--------+---------------+-------+----------------
public | point_history | table | prod
(1 row)
prod=> \d point_history
Table "public.point_history"
Column | Type | Modifiers
-----------+-----------------------------+-----------
point_id | integer | not null
value | real | not null
status | integer | not null
timestamp | timestamp without time zone | not null
Indexes:
"point_history_pkey" PRIMARY KEY, btree (point_id, "timestamp")
Foreign-key constraints:
"$1" FOREIGN KEY (point_id) REFERENCES point(id)
Tom
