Обсуждение: How to troubleshoot a halted postgres 8.3 ?

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
singletransaction. 
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_adminworks and shows me several transactions running. 

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

In the status section of pg_admin, I can see a total of 6 transactions running, 3 in idle mode, the other 3 doing some
kindof select. The lock section has several hundred lines listed, but they don't tell me what exactly is locked. 

So, please give me some general idea on how to proceed with such a situation.

Thanks,

Stefan

Hi Scott,

>   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.

>   Look at the pg_stat_activity and pg_locks views.

Thanks, you were right, I added an additional commit which solved the problem. The hint was that the pg_lock view
containeda lock entry which said granted "No". 

This raises though the question of isolation level, afaik postgres default is committed read. The doc says that another
querywould see the data as it was before the start of the transaction, which in my case would have been fine since the
additionalcolumns created by the alter statement were not used in the select which blocked. 
Is this some kind of "simple" implementation of a committed read ?

Thanks,

Stefan