Re: DDL commands take forever

2009/12/29 Leonardo M. <l(dot)rame(at)griensu(dot)com>:
> El mar, 29-12-2009 a las 14:18 -0500, Merlin Moncure escribió:
>> >>
>> >
>> > Well, I'm trying to debug the problem, and found that when I do a simple
>> > "select * from table" from my app, then go to pgAdmin, and do "select *
>> > from pg_locks", it shows many locks (23 to be exact).
>>
>> Those locks are not coming from your query.  Something else is
>> connecting and acquiring locks.  If your database is busy, this is
>> normal: every transaction is going to apply a lock of some sort or
>> another.   The key thing to look out for is the level of lock, what it
>> is on, and whether or not it has been granted.
>>
>> Also, you should check: select * from pg_stat_activity; to see who is
>> executing what queries and if you have any long running transactions
>> that shouldn't be.
>>
>> merlin
>>
>
> In fact, my app is acquiring the locks. Also the app doesn't have long
> running transactions, and the locks shows even if I just to a SELECT, to
> reproduce the problem don't have to do an "BEGIN ... COMMIT/ROLLBACK".
>
> I found the problem is in the way I connect the app to the database,
> when I set the Transaction Isolation Level to ReadCommitted, the locking
> problem appears, when I use the default connection method, the locks
> doesn't appear when I do "select * from pg_locks".
>
> This solves the locking problem, but what happens to transactions? the
> app is still working in transaction mode, or just applying changes after
> every Insert/Update/Delete?.

huh...the default transaction mode _is_ read committed :-).

merlin