Re: Unsuccessful SIGINT - More Info - Mailing list pgsql-general

I finally reboot the client server. It took a couple of minutes after
that, but the hung connection did go away on the server.

I found a similar cause to my problem in the archives:
http://archives.postgresql.org/pgsql-jdbc/2005-05/msg00044.php

In order for the PostgreSQL JDBC adaptor to not fetch the entire
result set it is necessary to call Statement.setFetchSize().
 From the archive: "Currently it only takes effect with autocommit
off and TYPE_FORWARD_ONLY resultsets"

Now I know the cause at least. If anyone has an idea on how to kill a
similar hung connection without rebooting the server, I would
appreciate any suggestions.

Thanks,

Brian Wipf
<brian@clickspace.com>

On 1-Dec-06, at 6:30 PM, Brian Wipf wrote:

> 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
>>
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that
> your
>       message can get through to the mailing list cleanly
>