Re: Postgres partially hang after inactivity

On Wed, May 6, 2009 at 12:48 PM, Marco Maccaferri <macca(at)maccasoft(dot)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