Обсуждение: [GENERAL] idle in transaction, why
Using postgres 10-beta3 (hopefully 10.0 this week) on virtual CentOS7 and this JDBC driver postgresql:42.1.4
The postgresql.conf file has
#idle_in_transaction_session_timeout = 0 # in milliseconds, 0 is disabled
my db url has "?prepareThreshold=0" since I bump into "already defined" statements otherwise
but I'm getting
org.jooq.exception.DataAccessException: SQL [select "projectfile"."id", "projectfile"."name", "projectfile"."filetype", "projectfile"."uri", "projectfile"."people_id" from "projectfile" where "projectfile"."uri" = ?]; ERROR: idle transaction timeout
<lots of calls>2017-11-06T11:32:20-07:00 - - - Caused by: org.postgresql.util.PSQLException: ERROR: idle transaction timeout
2017-11-06T11:32:20-07:00 - - - at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2477)
2017-11-06T11:32:20-07:00 - - - at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2190)
2017-11-06T12:12:43-07:00 - - - at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:300)
2017-11-06T12:12:43-07:00 - - - at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:428)
2017-11-06T12:12:43-07:00 - - - at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:354)
2017-11-06T12:12:43-07:00 - - - at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:169)
2017-11-06T12:12:43-07:00 - - - at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:158)
at various calls, sometimes the first one, sometime several calls later (some select, some insert), all in a transaction initiated in my code. From what I can piece together from the bind values all the queries are sound - at least the selects expected to find things work as expected.
Where should I be looking?
Using postgres 10-beta3 (hopefully 10.0 this week) on virtual CentOS7 and this JDBC driver postgresql:42.1.4
The postgresql.conf file has
#idle_in_transaction_session_
timeout = 0 # in milliseconds, 0 is disabled
There are numerous places where default settings can be configured.
You should probably login as your application user and do "show idle_in_transaction_session_timeout" to see what a clean session has for a value and then figure out from there where that value is coming from.
David J.
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> You should probably login as your application user and do "show
> idle_in_transaction_session_timeout" to see what a clean session has for a
> value and then figure out from there where that value is coming from.
You don't have to guess about the latter: the pg_settings view will tell
you exactly where the active value came from. See the source, sourcefile,
sourceline columns.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On 11/06/2017 01:09 PM, David G. Johnston wrote:
From logging in with the application role I getUsing postgres 10-beta3 (hopefully 10.0 this week) on virtual CentOS7 and this JDBC driver postgresql:42.1.4
The postgresql.conf file has
#idle_in_transaction_session_
timeout = 0 # in milliseconds, 0 is disabled There are numerous places where default settings can be configured.You should probably login as your application user and do "show idle_in_transaction_session_timeout" to see what a clean session has for a value and then figure out from there where that value is coming from.David J.
coon=> show idle_in_transaction_session_timeout;
idle_in_transaction_session_timeout
-------------------------------------
0
(1 row)
On 11/06/2017 01:17 PM, Tom Lane wrote:
> "David G. Johnston" <david.g.johnston@gmail.com> writes:
>> You should probably login as your application user and do "show
>> idle_in_transaction_session_timeout" to see what a clean session has for a
>> value and then figure out from there where that value is coming from.
> You don't have to guess about the latter: the pg_settings view will tell
> you exactly where the active value came from. See the source, sourcefile,
> sourceline columns.
>
> regards, tom lane
select name, setting, source, sourcefile, sourceline, extra_desc,
context from pg_settings where name ~ 'idle'; name | setting | source | sourcefile |
sourceline | extra_desc | context
-------------------------------------+---------+---------+------------+------------+---------------------------------------+---------
idle_in_transaction_session_timeout| 0 | default |
| | A value of 0 turns off the timeout. | user tcp_keepalives_idle | 7200 | default |
| | A value of 0 uses the system default. | user
(2 rows)
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Rob Sargent <robjsargent@gmail.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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On 11/06/2017 01:41 PM, Tom Lane wrote: > Rob Sargent <robjsargent@gmail.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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
On 11/06/2017 01:50 PM, Rob Sargent wrote: > > > On 11/06/2017 01:41 PM, Tom Lane wrote: >> Rob Sargent <robjsargent@gmail.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 My bet is that those 'org.postgres' messages came from the jdbc driver. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
On Mon, Nov 6, 2017 at 2:50 PM, Rob Sargent <robjsargent@gmail.com> wrote: > > > On 11/06/2017 01:41 PM, Tom Lane wrote: >> >> Rob Sargent <robjsargent@gmail.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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
On 11/06/2017 02:38 PM, Merlin Moncure wrote:
On Mon, Nov 6, 2017 at 2:50 PM, Rob Sargent <robjsargent@gmail.com> wrote:On 11/06/2017 01:41 PM, Tom Lane wrote:Rob Sargent <robjsargent@gmail.com> writes:idle_in_transaction_session_timeout | 0 | default | | | A value of 0 turns off the timeout. | userMeh. 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 laneThe 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 JOOQI 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
"2" definitely fits this bill. The difference between test and prod is pgboucer which I've forgotten to mention at all in this thread. I do start a tx in my code a la:
public void writedb(DSLContext ctx) {But I don't think this is out of the ordinary. However writing lists with up to 1,175,151 records might not be (2 this size, to at 131K). I'll take this up with JOOQ and pgbouncer. (fasterxml is having trouble with this size too. Not sure how I can break this up if need be done, they're all generate from the same analysis run.)
logger.error("{}: start transaction at {}", getRunTag(), System.currentTimeMillis());
ctx.transaction(ltx -> {
startProcess(ctx);
writeSegments(ctx);
finishProcess(ctx);
});
logger.error("{}: end transaction at {}", getRunTag(), System.currentTimeMillis());
}
Thanks to all.
On 11/06/2017 02:38 PM, Merlin Moncure wrote:
Gosh I wish I could learn to proof-read my posts.On Mon, Nov 6, 2017 at 2:50 PM, Rob Sargent <robjsargent@gmail.com> wrote:On 11/06/2017 01:41 PM, Tom Lane wrote:Rob Sargent <robjsargent@gmail.com> writes:idle_in_transaction_session_timeout | 0 | default | | | A value of 0 turns off the timeout. | userMeh. 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 laneThe 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 JOOQI 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
My support crew graciously set
idle_transaction_timeout = 1Now to ponder if I need zero or some large number.
Thanks again
Rob Sargent schrieb am 06.11.2017 um 23:09: > 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. The unit of that setting is milliseconds (if no unit is specified). zero disables that feature. One millisecond seems like an awfully short period to allow a transaction to be idle. I would figure values in "minutes" to be more realistic depending on the workload and characteristics of the application. A transaction that has several seconds of "think time" between individual statements doesn't seem that unrealistic. Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
> On Nov 7, 2017, at 12:16 AM, Thomas Kellerer <spam_eater@gmx.net> wrote: > > Rob Sargent schrieb am 06.11.2017 um 23:09: >> 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. > > The unit of that setting is milliseconds (if no unit is specified). > zero disables that feature. > > One millisecond seems like an awfully short period to allow a transaction to be idle. > > I would figure values in "minutes" to be more realistic depending on the workload and characteristics of the application. > > A transaction that has several seconds of "think time" between individual statements doesn't seem that unrealistic. > > > Thomas > > > I started with the default zero and the save went through perfectly. It takes ten minutes so I’ll have a concurrency issueI imagine. > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
On Tue, Nov 7, 2017 at 7:44 AM, Rob Sargent <robjsargent@gmail.com> wrote: > > >> On Nov 7, 2017, at 12:16 AM, Thomas Kellerer <spam_eater@gmx.net> wrote: >> >> I would figure values in "minutes" to be more realistic depending on the workload and characteristics of the application. >> >> A transaction that has several seconds of "think time" between individual statements doesn't seem that unrealistic. >> > I started with the default zero and the save went through perfectly. It takes ten minutes so I’ll have a concurrency issueI imagine. 10 minutes is long-ish but if it's not run all the time etc it may be what you're stuck with. Idle in transaction doesn't necessarily mean concurrency issues, as long as you're not operating on a whole table other people need to update as well. I guess now's a good time to profile what your code is doing, what's take the longest, and see if maybe you can split that big transaction up into bite sized pieces. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
On 11/07/2017 09:09 AM, Scott Marlowe wrote: > On Tue, Nov 7, 2017 at 7:44 AM, Rob Sargent <robjsargent@gmail.com> wrote: >> >>> On Nov 7, 2017, at 12:16 AM, Thomas Kellerer <spam_eater@gmx.net> wrote: >>> >>> I would figure values in "minutes" to be more realistic depending on the workload and characteristics of the application. >>> >>> A transaction that has several seconds of "think time" between individual statements doesn't seem that unrealistic. >>> >> I started with the default zero and the save went through perfectly. It takes ten minutes so I’ll have a concurrency issueI imagine. > 10 minutes is long-ish but if it's not run all the time etc it may be > what you're stuck with. Idle in transaction doesn't necessarily mean > concurrency issues, as long as you're not operating on a whole table > other people need to update as well. I guess now's a good time to > profile what your code is doing, what's take the longest, and see if > maybe you can split that big transaction up into bite sized pieces. Short story: The save operation in question is insert only. Done in a single transaction (currently). The last run generated 1.7M new 'segments', each of those grouped into one of 131K segmentsets (so another 1.7M members), those 131K sets recorded as 'outputs'. In production we'll start 22 jobs, one per chromosome (no XY) for each pedigree. We spread those across machines, starting roughly at the same time. Analysis time is measured in days and is pretty much linear with chromosome size (and inversely to processor count) but pretty sure at some point two of those send their payload within minutes of each other. You know they will. Do two sets of writes to same table interfere with each other horribly? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
> On Nov 7, 2017, at 12:16 AM, Thomas Kellerer <spam_eater@gmx.net> wrote: > > Rob Sargent schrieb am 06.11.2017 um 23:09: >> 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. > > The unit of that setting is milliseconds (if no unit is specified). > zero disables that feature. > > One millisecond seems like an awfully short period to allow a transaction to be idle. > > I would figure values in "minutes" to be more realistic depending on the workload and characteristics of the application. > > A transaction that has several seconds of "think time" between individual statements doesn't seem that unrealistic. > > > Thomas > I see I didn’t clarify that the timeout was set in the pgbouncer configuration. (I had shown upstream that it was NOT setfor the postgres server.) In pgbouncer, the unit is seconds. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general