Re: idle in transaction, why

On Mon, Nov 6, 2017 at 2:50 PM, Rob Sargent <robjsargent(at)gmail(dot)com> wrote:
>
>
> On 11/06/2017 01:41 PM, Tom Lane wrote:
>>
>> Rob Sargent <robjsargent(at)gmail(dot)com> writes:
>>>
>>> idle_in_transaction_session_timeout | 0 | default |
>>> | | A value of 0 turns off the timeout. | user
>>
>> Meh. I think we're barking up the wrong tree anyway: so far as I can
>> find, there is no error message reading 'idle transaction timeout'
>> in the existing PG sources (and I sure hope no committer would have
>> thought that such an ambiguous message text was satisfactory).
>> So I think your error is coming from client-side or third-party code.
>> What other moving parts have you got in there?
>>
>> regards, tom lane
>
> The most likely culprit is JOOQ, which I chose as a learning experience
> (normally I use ORM tools). But that said, I just ran the same data into my
> test env, (postgres 10.0 (real) on centos 6.9, ubuntu client) and all went
> swimmingly. It's a sizable payload (several batches of over 100K items,
> deserialized from json) and takes 5 minutes to save.
>
> I was hoping to blame the virt or the beta. Not a good time to start doubt
> JOOQ

I can't speak to JOOQ (who are fantastic postgres supporters BTW), but
in the java world this typically comes from one of two things:

1) you have long running in-transaction process that has very heavy
computation between sql statements. this is a rare case
--or--
2) you are connecting pooling and the app sent a connection back into
the pool without having a transaction committed.

"2" is a common and dangerous bug. It can happen due to bug in
application code (most likely), the jdbc wrapping library code (less
likely), or the connection pooler itself if you're using one. A
typical cause of application side problems is manual transaction
management and some uncaught exception paths where errors (say, a
duplicate key error). So investigate causes like that first
(database errors in the database log might be a helpful clue) and go
from there. If the problem is within JOOQ, you ought to take it up
with them, which I encourage you to do, since I consider JOOQ to be a
wonderful treatment of SQL integration from the java perspective.

merlin