Hello,
I have this problem with PostgreSQL 6.5.2:
table timelog199911 has
logs=> select count(*) from timelog199911;count
------
208749
(1 row)
logs=> select distinct confid
logs-> from timelog199910
logs-> where
logs-> confid IS NOT NULL;
pqReadData() -- backend closed the channel unexpectedly. This probably means the backend terminated abnormally
before or while processing the request.
We have lost the connection to the backend, so further processing is
impossible. Terminating.
The logged message in stderr (of postmaster) is
FATAL 1: Memory exhausted in AllocSetAlloc()
The process size grows to 76 MB (this is somehow a limit of Postgres on
BSD/OS, but this is not my question now).
Why would it require so much memory? The same query without distinct is
processed fast, but I don't need that much data back in the application.
The format is:
Table = timelog
+----------------------------------+----------------------------------+-------+
| Field | Type | Length|
+----------------------------------+----------------------------------+-------+
| loginname | text | var |
| site | varchar() | 16 |
| start_time | datetime | 8 |
| elapsed | timespan | 12 |
| port | text | var |
| valid | bool default 't' | 1 |
| ipaddress | inet | var |
| confid | int4 | 4 |
| session_id | text | var |
+----------------------------------+----------------------------------+-------+
Indices: timelog_loginname_idx timelog_start_time_idx
(indexes are btree on the indicate fields).
Weird, isn't it?
Daniel