Patrick Welche <prlw1@newn.cam.ac.uk> writes:
> PID USERNAME PRI NICE SIZE RES STATE TIME WCPU CPU COMMAND
> 1547 prlw1 50 0 128M 516K run 1:28 59.28% 59.28% psql
> 1552 postgres 50 0 1920K 632K run 1:37 24.32% 24.32% postgres
Sigh, I shoulda read this closely enough to notice that you were
complaining of psql memory overrun, not backend memory overrun :-(
The major problem here is that libpq's API is designed on the assumption
that libpq will buffer the whole query result in application memory
before letting the app see any of it. I see no way around that without
a fundamental redesign of the API. Which will happen someday, but not
today.
The minor problem is that libpq doesn't react very gracefully to running
out of memory. It detects it OK, but then aborts query processing,
which means it gets out of step with the backend. It needs to be fixed
so that it continues to absorb tuples (but drops them on the floor)
until the backend is done. I've known of this problem for some time,
but have had many higher-priority problems to worry about. Perhaps
someone else would like to take it on...
regards, tom lane