Re: DDL commands take forever

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

--
Leonardo M. Ramé
Griensu S.A. - Medical IT Córdoba
Tel.: 0351-4247979