Re: idle in transaction, why

On 11/06/2017 02:38 PM, Merlin Moncure wrote:
> 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
Gosh I wish I could learn to proof-read my posts.
My support crew graciously set

idle_transaction_timeout = 1

Now to ponder if I need zero or some large number.

Thanks again