pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedWhy are a lot of ROLLBACK queries in idle state
6+ messages / 2 participants
[nested] [flat]
* Why are a lot of ROLLBACK queries in idle state
@ 2016-11-18 09:55 rajan <vgmonnet@gmail.com>
2016-11-18 10:26 ` Re: Why are a lot of ROLLBACK queries in idle state Thomas Kellerer <spam_eater@gmx.net>
0 siblings, 1 reply; 6+ messages in thread
From: rajan @ 2016-11-18 09:55 UTC (permalink / raw)
To: pgsql-admin
Hi,
When querying the pg_stat_acitivity I find a lot of ROLLBACK queries in idle
state. Can someone help me know why?
Thanks in advance.
select query, state, waiting, query_start from pg_stat_activity where query
ilike '%rollback%';
query
| state | waiting | query_start
-------------------------------------------------------------------------------------------------+--------+---------+-------------------------------
ROLLBACK
| idle | f | 2016-11-17 03:02:24.544345+00
ROLLBACK
| idle | f | 2016-11-18 09:32:19.372843+00
ROLLBACK
| idle | f | 2016-11-15 13:31:54.756704+00
ROLLBACK
| idle | f | 2016-11-18 07:08:45.51883+00
ROLLBACK
| idle | f | 2016-11-17 12:41:42.731161+00
ROLLBACK
| idle | f | 2016-11-18 09:32:17.91838+00
ROLLBACK
| idle | f | 2016-11-16 13:20:31.044959+00
ROLLBACK
| idle | f | 2016-11-17 10:42:09.652842+00
ROLLBACK
| idle | f | 2016-11-17 06:43:38.280102+00
ROLLBACK
| idle | f | 2016-11-15 12:48:05.320185+00
ROLLBACK
| idle | f | 2016-11-18 07:35:38.029441+00
ROLLBACK
| idle | f | 2016-11-10 10:28:01.60524+00
ROLLBACK
| idle | f | 2016-11-15 13:57:14.68677+00
ROLLBACK
| idle | f | 2016-11-18 09:33:49.067333+00
ROLLBACK
| idle | f | 2016-11-17 13:10:14.768031+00
ROLLBACK
| idle | f | 2016-11-18 03:18:21.688177+00
ROLLBACK
| idle | f | 2016-11-17 12:41:42.719949+00
select query, state, waiting, query_start from pg_stat_activity where query
ilike '%rollback%'; | active | f | 2016-11-18 09:55:05.476337+00
ROLLBACK
| idle | f | 2016-11-18 08:24:59.286648+00
ROLLBACK
| idle | f | 2016-11-17 13:10:14.767571+00
ROLLBACK
| idle | f | 2016-11-18 09:32:19.448064+00
ROLLBACK
| idle | f | 2016-11-15 13:57:14.680491+00
ROLLBACK
| idle | f | 2016-11-11 09:18:26.057973+00
ROLLBACK
| idle | f | 2016-11-15 12:24:52.661909+00
ROLLBACK
| idle | f | 2016-11-11 09:18:26.064561+00
ROLLBACK
| idle | f | 2016-11-18 06:56:57.2566+00
ROLLBACK
| idle | f | 2016-11-11 09:16:21.458585+00
ROLLBACK
| idle | f | 2016-11-16 13:21:02.534579+00
-----
--
Thanks,
Rajan.
--
View this message in context: http://postgresql.nabble.com/Why-are-a-lot-of-ROLLBACK-queries-in-idle-state-tp5930917.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Why are a lot of ROLLBACK queries in idle state
2016-11-18 09:55 Why are a lot of ROLLBACK queries in idle state rajan <vgmonnet@gmail.com>
@ 2016-11-18 10:26 ` Thomas Kellerer <spam_eater@gmx.net>
2016-11-18 11:56 ` Re: Why are a lot of ROLLBACK queries in idle state rajan <vgmonnet@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Thomas Kellerer @ 2016-11-18 10:26 UTC (permalink / raw)
To: pgsql-admin
rajan schrieb am 18.11.2016 um 10:55:
> When querying the pg_stat_acitivity I find a lot of ROLLBACK queries in idle
> state. Can someone help me know why?
>
> Thanks in advance.
>
> select query, state, waiting, query_start from pg_stat_activity where query
> ilike '%rollback%';
The _session_ is idle, not the query.
Quote from the manual:
If the session is idle, the "query" column of pg_stat_activity shows the last executed statement in that session.
So this simply means all those sessions properly ended their transaction using a ROLLBACK statement.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Why are a lot of ROLLBACK queries in idle state
2016-11-18 09:55 Why are a lot of ROLLBACK queries in idle state rajan <vgmonnet@gmail.com>
2016-11-18 10:26 ` Re: Why are a lot of ROLLBACK queries in idle state Thomas Kellerer <spam_eater@gmx.net>
@ 2016-11-18 11:56 ` rajan <vgmonnet@gmail.com>
2016-11-18 13:01 ` Re: Why are a lot of ROLLBACK queries in idle state Thomas Kellerer <spam_eater@gmx.net>
0 siblings, 1 reply; 6+ messages in thread
From: rajan @ 2016-11-18 11:56 UTC (permalink / raw)
To: pgsql-admin
Thanks for the Reply.
If the _session_ is idle then shouldn't it be removed from pg_stat_activity?
-----
--
Thanks,
Rajan.
--
View this message in context: http://postgresql.nabble.com/Why-are-a-lot-of-ROLLBACK-queries-in-idle-state-tp5930917p5930934.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Why are a lot of ROLLBACK queries in idle state
2016-11-18 09:55 Why are a lot of ROLLBACK queries in idle state rajan <vgmonnet@gmail.com>
2016-11-18 10:26 ` Re: Why are a lot of ROLLBACK queries in idle state Thomas Kellerer <spam_eater@gmx.net>
2016-11-18 11:56 ` Re: Why are a lot of ROLLBACK queries in idle state rajan <vgmonnet@gmail.com>
@ 2016-11-18 13:01 ` Thomas Kellerer <spam_eater@gmx.net>
2016-11-20 06:38 ` Re: Why are a lot of ROLLBACK queries in idle state rajan <vgmonnet@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Thomas Kellerer @ 2016-11-18 13:01 UTC (permalink / raw)
To: pgsql-admin
rajan schrieb am 18.11.2016 um 12:56:
> If the _session_ is idle then shouldn't it be removed from pg_stat_activity?
The session is only removed from pg_stat_activity when it's closed, i.e. when the client disconnects.
Being "idle" and disconnecting are two completely different things
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Why are a lot of ROLLBACK queries in idle state
2016-11-18 09:55 Why are a lot of ROLLBACK queries in idle state rajan <vgmonnet@gmail.com>
2016-11-18 10:26 ` Re: Why are a lot of ROLLBACK queries in idle state Thomas Kellerer <spam_eater@gmx.net>
2016-11-18 11:56 ` Re: Why are a lot of ROLLBACK queries in idle state rajan <vgmonnet@gmail.com>
2016-11-18 13:01 ` Re: Why are a lot of ROLLBACK queries in idle state Thomas Kellerer <spam_eater@gmx.net>
@ 2016-11-20 06:38 ` rajan <vgmonnet@gmail.com>
2016-11-20 07:58 ` Re: Why are a lot of ROLLBACK queries in idle state Thomas Kellerer <spam_eater@gmx.net>
0 siblings, 1 reply; 6+ messages in thread
From: rajan @ 2016-11-20 06:38 UTC (permalink / raw)
To: pgsql-admin
thanks for the reply.
how do I kill these idle sessions? And if I do, will it cause any issues?
-----
--
Thanks,
Rajan.
--
View this message in context: http://postgresql.nabble.com/Why-are-a-lot-of-ROLLBACK-queries-in-idle-state-tp5930917p5931144.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Why are a lot of ROLLBACK queries in idle state
2016-11-18 09:55 Why are a lot of ROLLBACK queries in idle state rajan <vgmonnet@gmail.com>
2016-11-18 10:26 ` Re: Why are a lot of ROLLBACK queries in idle state Thomas Kellerer <spam_eater@gmx.net>
2016-11-18 11:56 ` Re: Why are a lot of ROLLBACK queries in idle state rajan <vgmonnet@gmail.com>
2016-11-18 13:01 ` Re: Why are a lot of ROLLBACK queries in idle state Thomas Kellerer <spam_eater@gmx.net>
2016-11-20 06:38 ` Re: Why are a lot of ROLLBACK queries in idle state rajan <vgmonnet@gmail.com>
@ 2016-11-20 07:58 ` Thomas Kellerer <spam_eater@gmx.net>
0 siblings, 0 replies; 6+ messages in thread
From: Thomas Kellerer @ 2016-11-20 07:58 UTC (permalink / raw)
To: pgsql-admin
rajan schrieb am 20.11.2016 um 07:38:
> thanks for the reply.
>
> how do I kill these idle sessions? And if I do, will it cause any issues?
Why do you think you need to kill them?
An idle connection is not really a problem and keeping them around for re-use is exactly what connection pools are doing.
If you think those connections are a problem, you should rather investigate why your applications doesn't close, not blindly kill them.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2016-11-20 07:58 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2016-11-18 09:55 Why are a lot of ROLLBACK queries in idle state rajan <vgmonnet@gmail.com>
2016-11-18 10:26 ` Thomas Kellerer <spam_eater@gmx.net>
2016-11-18 11:56 ` rajan <vgmonnet@gmail.com>
2016-11-18 13:01 ` Thomas Kellerer <spam_eater@gmx.net>
2016-11-20 06:38 ` rajan <vgmonnet@gmail.com>
2016-11-20 07:58 ` Thomas Kellerer <spam_eater@gmx.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox