Very long "<IDLE> in transaction" query

От

Gnanakumar

Тема

Дата

3 мая 2012 г. в 07:21:03

Msg-id

008101cd2916$1c023b20$5406b160$@com

обсуждение

Список

pgsql-admin

Дерево обсуждения

Very long "<IDLE> in transaction" query "Gnanakumar" <gnanam@zoniac.com> 3 мая 2012 г. в 07:21:03

Re: Very long "<IDLE> in transaction" query Thomas Markus <t.markus@proventis.net> 8 мая 2012 г. в 03:25:03

Re: Very long "<IDLE> in transaction" query Jan Lentfer <Jan.Lentfer@web.de> 3 мая 2012 г. в 08:19:21

Re: Very long "<IDLE> in transaction" query "Albe Laurenz" <laurenz.albe@wien.gv.at> 3 мая 2012 г. в 11:07:20

Re: Very long "<IDLE> in transaction" query "Gnanakumar" <gnanam@zoniac.com> 4 мая 2012 г. в 06:54:36

Re: Very long "<IDLE> in transaction" query "ktm@rice.edu" <ktm@rice.edu> 4 мая 2012 г. в 10:38:57

Re: Very long "<IDLE> in transaction" query "Kevin Grittner" <Kevin.Grittner@wicourts.gov> 4 мая 2012 г. в 10:57:41

Re: Very long "<IDLE> in transaction" query "ktm@rice.edu" <ktm@rice.edu> 4 мая 2012 г. в 12:06:13

Re: Very long "<IDLE> in transaction" query "Albe Laurenz" <laurenz.albe@wien.gv.at> 4 мая 2012 г. в 08:09:38

Re: Very long "<IDLE> in transaction" query "Gnanakumar" <gnanam@zoniac.com> 4 мая 2012 г. в 08:34:24

Hi,

Recently, in our Production server, we found a "single query" being held up
in " in transaction" for more than 19 hours using the following query:
select date_trunc('second', current_timestamp - query_start) as runtime,
datname as database_name, current_query from pg_stat_activity where
current_query != '' order by 1 desc

but we're clueless which was the root cause of this issue and still hunting.
As we know, query output doesn't show up the actual query/statement.

We then ran the 3rd query available from PostgreSQL Wiki - Lock Monitoring
http://wiki.postgresql.org/wiki/Lock_Monitoring  From query result output, I
could infer only the following but still not able to find out the real root
cause:
	1) 2 tables are involved (table1 and table2)
	2) Mostly table1's indexes are appearing in the output.

Pasted below result output containing only " in transaction".  For
security reasons, I've masked/renamed table names and index names in
"relname" column.  Though all index names are renamed to as
"table1_xxxxx_indx", all are different index names and not the same index.
Yes, we do have more than 30 indexes in table1.

Can somebody help me out what is going wrong/causing these " in
transaction"?

datname |            relname            | transactionid |      mode       |
granted | usename |             substr             |          query_start
|       age        | procpid 
---------+-------------------------------+---------------+-----------------+
---------+---------+--------------------------------+-----------------------
--------+------------------+---------
 prodndb | table1_xxxxx_indx             |               | AccessShareLock |
t       | dbuser  |  in transaction          | 2012-05-01
14:56:53.617912-04 | 19:20:59.644261  |   14740
 prodndb | table1_xxxxx_indx             |               | AccessShareLock |
t       | dbuser  |  in transaction          | 2012-05-01
14:56:53.617912-04 | 19:20:59.644261  |   14740
 prodndb | table1_xxxxx_indx             |               | AccessShareLock |
t       | dbuser  |  in transaction          | 2012-05-01
14:56:53.617912-04 | 19:20:59.644261  |   14740
 prodndb | table1_xxxxx_indx             |               | AccessShareLock |
t       | dbuser  |  in transaction          | 2012-05-01
14:56:53.617912-04 | 19:20:59.644261  |   14740
 prodndb | table1_xxxxx_indx             |               | AccessShareLock |
t       | dbuser  |  in transaction          | 2012-05-01
14:56:53.617912-04 | 19:20:59.644261  |   14740
 prodndb | table1_xxxxx_indx             |               | AccessShareLock |
t       | dbuser  |  in transaction          | 2012-05-01
14:56:53.617912-04 | 19:20:59.644261  |   14740
 prodndb | table1_xxxxx_indx             |               | AccessShareLock |
t       | dbuser  |  in transaction          | 2012-05-01
14:56:53.617912-04 | 19:20:59.644261  |   14740
 prodndb | table1_xxxxx_indx             |               | AccessShareLock |
t       | dbuser  |  in transaction          | 2012-05-01
14:56:53.617912-04 | 19:20:59.644261  |   14740
 prodndb | table1_xxxxx_indx             |               | AccessShareLock |
t       | dbuser  |  in transaction          | 2012-05-01
14:56:53.617912-04 | 19:20:59.644261  |   14740
 prodndb | table1_xxxxx_indx             |               | AccessShareLock |
t       | dbuser  |  in transaction          | 2012-05-01
14:56:53.617912-04 | 19:20:59.644261  |   14740
 prodndb | table1_xxxxx_indx             |               | AccessShareLock |
t       | dbuser  |  in transaction          | 2012-05-01
14:56:53.617912-04 | 19:20:59.644261  |   14740
 prodndb | table1_xxxxx_indx             |               | AccessShareLock |
t       | dbuser  |  in transaction          | 2012-05-01
14:56:53.617912-04 | 19:20:59.644261  |   14740
 prodndb | table1_xxxxx_indx             |               | AccessShareLock |
t       | dbuser  |  in transaction          | 2012-05-01
14:56:53.617912-04 | 19:20:59.644261  |   14740
 prodndb | table1_xxxxx_indx             |               | AccessShareLock |
t       | dbuser  |  in transaction          | 2012-05-01
14:56:53.617912-04 | 19:20:59.644261  |   14740
 prodndb | table1_xxxxx_indx             |               | AccessShareLock |
t       | dbuser  |  in transaction          | 2012-05-01
14:56:53.617912-04 | 19:20:59.644261  |   14740
 prodndb | table1_xxxxx_indx  		 |               | AccessShareLock |
t       | dbuser  |  in transaction          | 2012-05-01
14:56:53.617912-04 | 19:20:59.644261  |   14740
 prodndb | table1_xxxxx_indx             |               | AccessShareLock |
t       | dbuser  |  in transaction          | 2012-05-01
14:56:53.617912-04 | 19:20:59.644261  |   14740
 prodndb | table1_xxxxx_indx             |               | AccessShareLock |
t       | dbuser  |  in transaction          | 2012-05-01
14:56:53.617912-04 | 19:20:59.644261  |   14740
 prodndb | table1_xxxxx_indx             |               | AccessShareLock |
t       | dbuser  |  in transaction          | 2012-05-01
14:56:53.617912-04 | 19:20:59.644261  |   14740
 prodndb | table1_xxxxx_indx             |               | AccessShareLock |
t       | dbuser  |  in transaction          | 2012-05-01
14:56:53.617912-04 | 19:20:59.644261  |   14740
 prodndb | table1_xxxxx_indx             |               | AccessShareLock |
t       | dbuser  |  in transaction          | 2012-05-01
14:56:53.617912-04 | 19:20:59.644261  |   14740
 prodndb | table1_xxxxx_indx             |               | AccessShareLock |
t       | dbuser  |  in transaction          | 2012-05-01
14:56:53.617912-04 | 19:20:59.644261  |   14740
 prodndb | table1_xxxxx_indx             |               | AccessShareLock |
t       | dbuser  |  in transaction          | 2012-05-01
14:56:53.617912-04 | 19:20:59.644261  |   14740
 prodndb | table1_xxxxx_indx             |               | AccessShareLock |
t       | dbuser  |  in transaction          | 2012-05-01
14:56:53.617912-04 | 19:20:59.644261  |   14740
 prodndb | table1_xxxxx_indx             |               | AccessShareLock |
t       | dbuser  |  in transaction          | 2012-05-01
14:56:53.617912-04 | 19:20:59.644261  |   14740
 prodndb | table1_xxxxx_indx             |               | AccessShareLock |
t       | dbuser  |  in transaction          | 2012-05-01
14:56:53.617912-04 | 19:20:59.644261  |   14740
 prodndb | table1_xxxxx_indx             |               | AccessShareLock |
t       | dbuser  |  in transaction          | 2012-05-01
14:56:53.617912-04 | 19:20:59.644261  |   14740
 prodndb | table1_xxxxx_indx             |               | AccessShareLock |
t       | dbuser  |  in transaction          | 2012-05-01
14:56:53.617912-04 | 19:20:59.644261  |   14740
 prodndb | table2                        |               | AccessShareLock |
t       | dbuser  |  in transaction          | 2012-05-01
14:56:53.617912-04 | 19:20:59.644261  |   14740
 prodndb | table1_xxxxx_indx             |               | AccessShareLock |
t       | dbuser  |  in transaction          | 2012-05-01
14:56:53.617912-04 | 19:20:59.644261  |   14740
 prodndb | table1_xxxxx_indx             |               | AccessShareLock |
t       | dbuser  |  in transaction          | 2012-05-01
14:56:53.617912-04 | 19:20:59.644261  |   14740
 prodndb | table1_xxxxx_indx             |               | AccessShareLock |
t       | dbuser  |  in transaction          | 2012-05-01
14:56:53.617912-04 | 19:20:59.644261  |   14740
 prodndb | table1_xxxxx_indx             |               | AccessShareLock |
t       | dbuser  |  in transaction          | 2012-05-01
14:56:53.617912-04 | 19:20:59.644261  |   14740
 prodndb | table1_xxxxx_indx             |               | AccessShareLock |
t       | dbuser  |  in transaction          | 2012-05-01
14:56:53.617912-04 | 19:20:59.644261  |   14740
 prodndb | table1_pk                     |               | AccessShareLock |
t       | dbuser  |  in transaction          | 2012-05-01
14:56:53.617912-04 | 19:20:59.644261  |   14740
 prodndb | table1_xxxxx_idx              |               | AccessShareLock |
t       | dbuser  |  in transaction          | 2012-05-01
14:56:53.617912-04 | 19:20:59.644261  |   14740
 prodndb | table1_xxxxx_indx             |               | AccessShareLock |
t       | dbuser  |  in transaction          | 2012-05-01
14:56:53.617912-04 | 19:20:59.644261  |   14740
 prodndb | table1_xxxxx_indx             |               | AccessShareLock |
t       | dbuser  |  in transaction          | 2012-05-01
14:56:53.617912-04 | 19:20:59.644261  |   14740
 prodndb | table1_xxxxx_indx             |               | AccessShareLock |
t       | dbuser  |  in transaction          | 2012-05-01
14:56:53.617912-04 | 19:20:59.644261  |   14740
 prodndb | table1_xxxxx_indx             |               | AccessShareLock |
t       | dbuser  |  in transaction          | 2012-05-01
14:56:53.617912-04 | 19:20:59.644261  |   14740
 prodndb | table1_xxxxx_indx             |               | AccessShareLock |
t       | dbuser  |  in transaction          | 2012-05-01
14:56:53.617912-04 | 19:20:59.644261  |   14740
 prodndb | table1_xxxxx_indx             |               | AccessShareLock |
t       | dbuser  |  in transaction          | 2012-05-01
14:56:53.617912-04 | 19:20:59.644261  |   14740
 prodndb | table1_xxxxx_indx             |               | AccessShareLock |
t       | dbuser  |  in transaction          | 2012-05-01
14:56:53.617912-04 | 19:20:59.644261  |   14740
 prodndb | table1_xxxxx__indx            |               | AccessShareLock |
t       | dbuser  |  in transaction          | 2012-05-01
14:56:53.617912-04 | 19:20:59.644261  |   14740
 prodndb | table1                        |               | AccessShareLock |
t       | dbuser  |  in transaction          | 2012-05-01
14:56:53.617912-04 | 19:20:59.644261  |   14740
 prodndb |                               |     668748028 | ExclusiveLock   |
t       | dbuser  |  in transaction          | 2012-05-01
14:56:53.617912-04 | 19:20:59.644261  |   14740
 prodndb | table1_xxxxx__indx            |               | AccessShareLock |
t       | dbuser  |  in transaction          | 2012-05-01
14:56:53.617912-04 | 19:20:59.644261  |   14740
 prodndb | table1_xxxxx__indx            |               | AccessShareLock |
t       | dbuser  |  in transaction          | 2012-05-01
14:56:53.617912-04 | 19:20:59.644261  |   14740

But no rows were returned for the 2nd query available in PostgreSQL Wiki -
Lock Monitoring.

We're running PostgreSQL v8.2.22 and pgpool v3.1.1 (only connection pooling
feature is used).

Regards,
Gnanam

В списке pgsql-admin по дате отправления

Предыдущее

От: Devrim GÜNDÜZ

Дата: 3 мая 2012 г. в 04:34:29

Сообщение: Re: postgresql stream replication question

Следующее

От: Jan Lentfer

Дата: 3 мая 2012 г. в 08:19:21

Сообщение: Re: Very long "<IDLE> in transaction" query