Re: DDL commands take forever

2009/12/29 Leonardo M. <l(dot)rame(at)griensu(dot)com>:
> El mar, 29-12-2009 a las 15:05 -0500, Merlin Moncure escribió:
>> > 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
>>
>
> Merlin, knowning this, I'm asking to the developers of the connection
> library because in their code, if I use the default connection mode,
> then the transactions are ingnored, applying the changes immediately
> after every Insert, Update or Delete.

right. IIRC the zeos library has a transaction mode that controls if
commits are explicit or invoked via the library commit method. either
way, you you need to make sure that transactions are not left
open...this can lead (as you noticed) to unexpected problems like
blocking queries, performance problems, data loss, etc.

if you notice the slow ddl issue again, throw an immediate select *
from pg_locks and look for granted = f. If you find some and they
match your pid, then you know that you have a transaction open that is
blocking you. From there, it's just a matter if using pg_locks and
pg_stat_activity to narrow down who/what is doing it. You should
especially take note of 'idle in transaction' in
pg_stat_activity...this is classic red flag of leaky application code.

merlin