Обсуждение: Postgres partially hang after inactivity
Hi. I have a weird problem with PostgreSQL on a Windows machine. This is a test installation used to beta test a webapplication running with Tomcat, so most of the times it sits idle. Yesterday and today I had to update some table definitions, so issued the 'alter table...' command and the server hanged. The webapplication runs well so the queries are working but seems that somehow the modifications are locked. I'm not sure if writing works (haven't tested it, will do the next time). I'm new to postgres so I don't know exactly what to do to track down this problem, the log files doesn't seems to report any issue. Any suggestion about where to look to understand what's wrong ? As additional informations, the webapplication uses hibernate to access the database, I'm not sure but it is possible that the sessions are not always closed cleanly or closed at all. Postgres version is 8.3.7. Regards, Marco.
On Wed, May 6, 2009 at 12:48 PM, Marco Maccaferri <macca@maccasoft.com> wrote: > Hi. > > I have a weird problem with PostgreSQL on a Windows machine. This is a test > installation used to beta test a webapplication running with Tomcat, so most > of the times it sits idle. Yesterday and today I had to update some table > definitions, so issued the 'alter table...' command and the server hanged. > The webapplication runs well so the queries are working but seems that > somehow the modifications are locked. I'm not sure if writing works (haven't > tested it, will do the next time). probably, you had a transaction sitting around that is not closed (to see: select * from pg_stat_activity). the alter table is waiting for that transaction to finish, but itself blocks all _new_ transactions (alter table acquires a strong lock on the table). Cancel the alter table query or the query that is blocking your alter table. Again, check pg_stat_activity and ungranted locks (select * from pg_locks where granted = false). merlin
On 06/05/2009 21:39 Merlin Moncure ha scritto: > probably, you had a transaction sitting around that is not closed (to > see: select * from pg_stat_activity). > > the alter table is waiting for that transaction to finish, but itself > blocks all _new_ transactions (alter table acquires a strong lock on > the table). Cancel the alter table query or the query that is > blocking your alter table. Again, check pg_stat_activity and > ungranted locks (select * from pg_locks where granted = false). By querying these tables I found that the connection pooling was not setup properly and that there were few unclosed sessions hanging around. Thank you for your help. Regards, Marco.