huge backend processes

maybe i'm doing something wrong here:

CREATE TABLE samples
(
mark abstime,
subnet inet,
bytes_in float8,
bytes_out float8
);
CREATE INDEX samples_mark ON samples (mark);

--- fill it with lots and lots of data

BEGIN WORK;
DECLARE mycurs CURSOR FOR
SELECT DATE_PART('epoch', mark), subnet, bytes_in, bytes_out
FROM samples
WHERE mark >= 'epoch or another date'::abstime;

-- LOOP
FETCH FORWARD 1000 IN mycurs

END WORK;

given the above, actually done using C/libpq, i run my program, which
does a PQclear after each FETCH.

after reading 250000 records, top says:

PID USERNAME PRI NICE SIZE RES STATE TIME WCPU CPU COMMAND
13748 postgres105 0 22724K 20588K RUN 3:05 86.14% 86.14% postgres

at some point, it runs out of memory (or something):

279001 records read (1177 rec/sec)
testprog: query failed - FETCH FORWARD 1000 IN samples;
testprog: (7) pqReadData() -- backend closed the channel unexpectedly.
This probably means the backend terminated abnormally before or while
processing the request.

i have had this problem with other processes on other tables.

the problem is usually if i am selecting a huge number of records,
and defining some conversion or something in the selected fields.

if i revert my code to (note: less the DATE_PART conversion):

DECLARE mycurs CURSOR FOR
SELECT mark, subnet, bytes_in, bytes_out
FROM samples
WHERE mark >= 'epoch or another date'::abstime;

it works fine.

--
[ Jim Mercer Reptilian Research jim(at)reptiles(dot)org +1 416 410-5633 ]
[ The telephone, for those of you who have forgotten, was a commonly used ]
[ communications technology in the days before electronic mail. ]
[ They're still easy to find in most large cities. -- Nathaniel Borenstein ]