Search Postgresql Archives

Re: idle in transaction, why

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 





On 11/06/2017 02:38 PM, Merlin Moncure wrote:
On Mon, Nov 6, 2017 at 2:50 PM, Rob Sargent <robjsargent@xxxxxxxxx> wrote:

On 11/06/2017 01:41 PM, Tom Lane wrote:
Rob Sargent <robjsargent@xxxxxxxxx> 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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux