Re: How to troubleshoot a halted postgres 8.3 ?

On Tue, Oct 20, 2009 at 8:46 AM, stefan <stefan(at)intermediate(dot)de> wrote:

>
> Hi,
>
> I have a postgres 8.3 db in which I execute a transaction with a lot of
> alter tabel etc, update etc statements in a single transaction.
> I also execute several select statements in parallel.
> This puts my database in a state which I would describe as halted, i.e. the
> sql interface does not respond any more, pg_admin works and shows me several
> transactions running.
>
> Remember, alter table statements require a full table lock. So if that
transaction (including your update statements) doesn't end somehow (commit,
rollback) then all other connections looking at that table will wait.

> How do I proceed to analyze what's happened? Of course, my first thought is
> some kind of transaction deadlock, but how do I check for this ?
>

Look at the pg_stat_activity and pg_locks views.

--Scott