Re: Running out of memory while making a join

Hi,

How is the best way to attach a debugger to the SELECT and identify why is it exhausting server storage.

This page is more focused on getting a stack trace after a crash, but provides some information about how to identify the backend to connect to, how to connect with gdb, etc:

http://wiki.postgresql.org/wiki/Getting_a_stack_trace_of_a_running_PostgreSQL_backend_on_Linux/BSD

Short version is:

Get the pid of the backend that's growing with

    SELECT pg_backend_pid()

before running the test, from `top`, or from pg_stat_activity.

    gdb -p pid_of_backend

when you get a (gdb) prompt:

    cont<enter>

Let the memory use grow until you think it's fairly big. Hit control-C in the gdb terminal to interrupt execution of the backend. You'll get another (gdb) prompt.

Now follow Tom's advice:

In gdb,call MemoryContextStats(TopMemoryContext)
should produce some useful information on the process's stderr file.

It may also be helpful to have a stack trace or two or three to see what Pg is doing while it's running and growing, so if you want, also run:

    bt

then "cont" again, control C, bt again, cont, control c, bt, and provide the copied and pasted backtraces in case they provide additional information about what's going on.

--
Craig Ringer