Обсуждение: tracking down idle transactions in pg_locks

I've been tracking down a problem with idle transactions being left open
and not being able to vacuum one of my tables. I think I have tracked it
down to proprietary app, the  problem seems to be that it leaves "idle
transactions" open.

However the table that we are having problems vacuuming (probably just
because it is the most active table) is in the  same db, but a different
scheme not accessed by that app. Could this app still be causing the
problem?

I did a select on the pg_locks table and filtered by the pid of the idle
transaction. There were 8 locks, 7 were on tables that the app uses
however the 8th was of locktype=virtualxid and mode=ExclusiveLock. Could
this be some sort of global lock?

I have been able to verify that closing the app, does allow us to vacuum
the table.

--
David Gardner

David Gardner <david@gardnerit.net> writes:
> I've been tracking down a problem with idle transactions being left open
> and not being able to vacuum one of my tables. I think I have tracked it
> down to proprietary app, the  problem seems to be that it leaves "idle
> transactions" open.

> However the table that we are having problems vacuuming (probably just
> because it is the most active table) is in the  same db, but a different
> scheme not accessed by that app. Could this app still be causing the
> problem?

Different schema won't help --- VACUUM assumes that *any* other
transaction in the same database might potentially be able to look at
the target table later, so it can't remove rows that that transaction
might be able to "see".

The most common cause of this problem is apps that issue "COMMIT; BEGIN"
and then go to sleep.  If that's what this app is doing (if you don't
know, turn on statement logging and find out...) then updating to a more
recent PG version might help.  In 8.3 a transaction doesn't block VACUUM's
row reclamation until it's done something more than just BEGIN.

            regards, tom lane

Thanks for your help, I was getting rather confident this app was to
blame, it just didn't make sense to me that it would show up on an
unrelated table. This gives me more information to contact the developer
with.

W are running 8.3,  I have been able to  reproduce the problem, it
occurs after an insert has occurred.


Tom Lane wrote:
> David Gardner <david@gardnerit.net> writes:
>
>> I've been tracking down a problem with idle transactions being left open
>> and not being able to vacuum one of my tables. I think I have tracked it
>> down to proprietary app, the  problem seems to be that it leaves "idle
>> transactions" open.
>>
>
>
>> However the table that we are having problems vacuuming (probably just
>> because it is the most active table) is in the  same db, but a different
>> scheme not accessed by that app. Could this app still be causing the
>> problem?
>>
>
> Different schema won't help --- VACUUM assumes that *any* other
> transaction in the same database might potentially be able to look at
> the target table later, so it can't remove rows that that transaction
> might be able to "see".
>
> The most common cause of this problem is apps that issue "COMMIT; BEGIN"
> and then go to sleep.  If that's what this app is doing (if you don't
> know, turn on statement logging and find out...) then updating to a more
> recent PG version might help.  In 8.3 a transaction doesn't block VACUUM's
> row reclamation until it's done something more than just BEGIN.
>
>             regards, tom lane
>


--
David Gardner

David Gardner <david@gardnerit.net> writes:
> Tom Lane wrote:
>> The most common cause of this problem is apps that issue "COMMIT; BEGIN"
>> and then go to sleep.  If that's what this app is doing (if you don't
>> know, turn on statement logging and find out...) then updating to a more
>> recent PG version might help.  In 8.3 a transaction doesn't block VACUUM's
>> row reclamation until it's done something more than just BEGIN.

> W are running 8.3,  I have been able to  reproduce the problem, it
> occurs after an insert has occurred.

Oh ... well that's just bad design :-(.  If the app goes to sleep with
an uncommitted insert then it's capable of blocking other transactions,
quite independently of VACUUM.  For instance, an attempt to insert a
conflicting unique-key value would have to block.

            regards, tom lane