and waiting
| От | Gurjeet Singh |
|---|---|
| Тема | |
| Дата | |
| Msg-id | 65937bea0801311430r365f1e6w6b9ad8f5322c3b34@mail.gmail.com обсуждение исходный текст |
| Ответы |
Re: |
| Список | pgsql-hackers |
Hi guys,<br /><br /> I saw a strange behaviour on one of the production boxes. The pg_stat_activity shows a process
as<IDLE> and yet 'waiting' !!! On top of it (understandably, since its IDLE), there are no entries for this pid
inpg_locks!<br /><br /> Following are the snapshots of the two system views.<br /><br /><span style="font-family:
couriernew,monospace;"> procpid | current_query | waiting | duration | backend_start</span><br
style="font-family:courier new,monospace;" /><span style="font-family: courier
new,monospace;">---------+-----------------------+---------+------------------+-------------------------------</span><br
style="font-family:courier new,monospace;" /><span style="font-family: courier new,monospace;"> 20762 |
<IDLE> | f | | 2008-01-31 13:38:30.848898-08</span><br style="font-family:
couriernew,monospace;" /><span style="font-family: courier new,monospace;"> 19776 | <IDLE> |
t | 00:38:34.76833 | 2008-01-31 12:51:29.005744-08</span><br style="font-family: courier new,monospace;" /><span
style="font-family:courier new,monospace;"> 20356 | <IDLE> | f | 00:38:29.971425 |
2008-01-3113:17:37.617497-08</span><br style="font-family: courier new,monospace;" /><span style="font-family: courier
new,monospace;"> 19775 | <IDLE> | f | 00:38:27.187201 | 2008-01-31
12:51:28.999242-08</span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier
new,monospace;"> 19774 | <IDLE> | f | 00:38:27.187068 | 2008-01-31
12:51:28.90554-08</span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier
new,monospace;"> 20728 | <IDLE> | f | 00:14:03.913027 | 2008-01-31
13:36:11.345822-08</span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier
new,monospace;"> 9727 | <IDLE> | f | 00:03:07.444273 | 2008-01-24
22:25:00.289931-08</span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier
new,monospace;"> 9684 | <IDLE> | f | 00:00:07.704656 | 2008-01-24
22:22:00.007377-08</span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier
new,monospace;"> 19390 | <IDLE> in transaction | f | 00:00:00.027585 | 2008-01-31
12:30:07.999246-08</span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier
new,monospace;"> 19389 | <IDLE> in transaction | t | -00:00:00.000255 | 2008-01-31
12:30:07.973868-08</span><brstyle="font-family: courier new,monospace;" /><br style="font-family: courier
new,monospace;"/><span style="font-family: courier new,monospace;">select * from pg_locks where pid in ( 19776, 19389
);</span><brstyle="font-family: courier new,monospace;" /><br style="font-family: courier new,monospace;" /><span
style="font-family:courier new,monospace;"> locktype | database | relation | page | tuple | transactionid |
classid| objid | objsubid | transaction | pid | mode | granted</span><br style="font-family: courier
new,monospace;"/><span style="font-family: courier
new,monospace;">---------------+----------+----------+------+-------+---------------+---------+-------+----------+-------------+-------+------------------+---------</span><br
style="font-family:courier new,monospace;" /><span style="font-family: courier new,monospace;"> relation |
16584| 17070 | | | | | | | 3700350056 | 19389 | RowExclusiveLock |
t</span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier
new,monospace;"> relation | 16584 | 17106 | | | | | | |
3700350056| 19389 | RowExclusiveLock | t</span><br style="font-family: courier new,monospace;" /><span
style="font-family:courier new,monospace;"> relation | 16584 | 17068 | | |
| | | | 3700350056 | 19389 | RowExclusiveLock | t</span><br style="font-family: courier
new,monospace;"/><span style="font-family: courier new,monospace;"> transactionid | | | |
| 3700350056 | | | | 3700350056 | 19389 | ExclusiveLock | t</span><br style="font-family:
couriernew,monospace;" /><span style="font-family: courier new,monospace;"> relation | 16584 | 17108 |
| | | | | | 3700350056 | 19389 | RowExclusiveLock | t</span><br
style="font-family:courier new,monospace;" /><span style="font-family: courier new,monospace;">(5 rows)</span><br
style="font-family:courier new,monospace;" /><br clear="all" style="font-family: courier new,monospace;" /><br />
The'duration' column above is just now()-query_start. These are not just two instant snapshots, but we could see this
outputconsistently for quite long.<br /><br /> I tracked the 'waiting' column a little bit in the source code, and
sawthat it is actually generated from PgBackendStatus.st_waiting . Is it possible that, for some reason, postgres
forgotto update this for a backend?<br /><br /><span style="font-family: courier new,monospace;">select
version();</span><brstyle="font-family: courier new,monospace;" /><span style="font-family: courier
new,monospace;"> version</span><br style="font-family: courier new,monospace;"
/><spanstyle="font-family: courier
new,monospace;">--------------------------------------------------------------------------------------------</span><br
style="font-family:courier new,monospace;" /><span style="font-family: courier new,monospace;"> PostgreSQL 8.2.4 on
x86_64-unknown-linux-gnu,compiled by GCC gcc (GCC) 4.1.0 (SUSE Linux)</span><br style="font-family: courier
new,monospace;"/><br /> This issue has been seen twice now.<br /><br />-- <br />gurjeet[.singh]@EnterpriseDB.com<br
/>singh.gurjeet@{gmail | hotmail | indiatimes | yahoo }.com<br /><br />EnterpriseDB <a
href="http://www.enterprisedb.com">http://www.enterprisedb.com</a><br/><br />17° 29' 34.37"N, 78° 30' 59.76"E -
Hyderabad<br/>18° 32' 57.25"N, 73° 56' 25.42"E - Pune<br />37° 47' 19.72"N, 122° 24' 1.69" W - San Francisco *<br
/><br/><a href="http://gurjeet.frihost.net">http://gurjeet.frihost.net</a><br /><br />Mail sent from my BlackLaptop
device
В списке pgsql-hackers по дате отправления: