Re: [HACKERS] Another nasty cache problem

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