Search Postgresql Archives

Re: Unsuccessful SIGINT - More Info

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxxxxxx>

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@xxxxxxxxxxxxxx>



---------------------------(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@xxxxxxxxxxxxxx so that your
      message can get through to the mailing list cleanly





[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux