From: | Merlin Moncure <mmoncure(at)gmail(dot)com> |
---|---|
To: | l(dot)rame(at)griensu(dot)com |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: DDL commands take forever |
Date: | 2009-12-29 21:04:21 |
Message-ID: | b42b73150912291304v1199676ehbb0b5c4558e89ba1@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
2009/12/29 Leonardo M. <l(dot)rame(at)griensu(dot)com>:
> El mar, 29-12-2009 a las 15:44 -0500, Merlin Moncure escribió:
>> 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
>
> I did the Select * from pg_locks right after your answer, and found that
> almost all locks originated by my app have "granted = t", also, all are
> in "<IDLE> in transaction". The interesting thing is the app is doing
> only Selects, without opening transactions.
ok, the problem is clear: find out why those happened (a client issued
'begin' without subsequent 'commit') and your problem will go away.
Turn on sql logging if you have to.
merlin
From | Date | Subject | |
---|---|---|---|
Next Message | Raymond O'Donnell | 2009-12-29 21:05:44 | Re: DDL commands take forever |
Previous Message | Leonardo M. Ramé | 2009-12-29 20:59:27 | Re: DDL commands take forever |