Re: Unsuccessful SIGINT - More Info

Based on the backend_start time in pg_stat_activity, I was able to
find the problem query in our logs. The query is a simple one, but
returns a lot of results for a report. This was the error in the logs:

org.postgresql.util.PSQLException: Ran out of memory retrieving query
results.
         at org.postgresql.core.v3.QueryExecutorImpl.processResults
(QueryExecutorImpl.java:1291)
         at org.postgresql.core.v3.QueryExecutorImpl.execute
(QueryExecutorImpl.java:188)
         at org.postgresql.jdbc2.AbstractJdbc2Statement.execute
(AbstractJdbc2Statement.java:452)
         at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags
(AbstractJdbc2Statement.java:340)
         at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery
(AbstractJdbc2Statement.java:239)
    ...
java.lang.OutOfMemoryError

The instance of the application is no longer running where this error
occurred, but the server still shows the hung non-sigint-able
connection.

On 1-Dec-06, at 5:54 PM, Brian Wipf wrote:

> Sorry, I forgot to mention this is on PostgreSQL 8.1.5. The server
> is SUSE Linux 10.1, the client is OS X Server 10.4.8.
>
> On 1-Dec-06, at 5:42 PM, Brian Wipf wrote:
>
>> I have a connection that I am unable to kill with a sigint.
>>
>> ps auxww for the process in question:
>> postgres  3578  0.3  3.6 6526396 1213344 ?     S    Dec01   0:32
>> postgres: postgres ssprod 192.168.0.52(49333) SELECT
>>
>> and gdb shows:
>> (gdb) bt
>> #0  0x00002ba62c18f085 in send () from /lib64/libc.so.6
>> #1  0x0000000000504765 in internal_flush ()
>> #2  0x0000000000504896 in internal_putbytes ()
>> #3  0x00000000005048fc in pq_putmessage ()
>> #4  0x0000000000505ea4 in pq_endmessage ()
>> #5  0x000000000043e37a in printtup ()
>> #6  0x00000000004e9349 in ExecutorRun ()
>> #7  0x0000000000567931 in PortalRunSelect ()
>> #8  0x00000000005685f0 in PortalRun ()
>> #9  0x0000000000565ea8 in PostgresMain ()
>> #10 0x0000000000540624 in ServerLoop ()
>> #11 0x000000000054131a in PostmasterMain ()
>> #12 0x000000000050676e in main ()
>>
>> lsof on the client machine (192.168.0.52) shows no connections on
>> port 49333, so it doesn't appear to be a simple matter of killing
>> the client connection. If I have to, I can reboot the client
>> machine, but this seems like overkill and I'm not certain this
>> will fix the problem. Anything else I can try on the server or the
>> client short of restarting the database or rebooting the client?
>>
>> Brian Wipf
>> <brian@clickspace.com>
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: explain analyze is your friend
>