Thread: Cursor fetch Problem.
Hi All, I am getting a problem i.e. in database while checking pg_stat_activity faced issue statement <Fetch all in unnamed portal> is residing as process for couple of days also, not able to kill them through pg_terminate_backend function. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Cursor-fetch-Problem-tp5737915.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On Wednesday, December 26, 2012 5:12 PM Harry wrote: > Hi All, > > I am getting a problem i.e. in database while checking pg_stat_activity > faced issue statement <Fetch all in unnamed portal> is residing as > process > for couple of days also, How have you concluded, it stays for couple of days? pg_stat_activity will show last statement executed in backend. What is the value of 'state' for that backend. It's better if you can send the output of pg_stat_activity for that backend. > not able to kill them through pg_terminate_backend function. Can you try once pg_cancel_backend(pid) and then pg_terminate_backend. With Regards, Amit Kapila.
Hi Amit, Thanks for Reply. Kindly see my below output. 16650;"sampledb";11965;10;"enterprisedb";"";"192.168.0.231";"";53897;"*2012-12-19 11:39:48.234799+05:30";"2012-12-19 11:39:53.288441+05:30";"2012-12-19 11:39:53.288441+05:30*";f;"DECLARE BEGIN EXEC 16650;"sampledb";12156;10;"enterprisedb";"";"192.168.0.231";"";53983;*"2012-12-19 12:18:38.57709+05:30";"2012-12-19 12:18:43.922301+05:30";"2012-12-19 12:18:43.922301+05:30"*;f;"DECLARE BEGIN EXEC 16650;"sampledb";13243;10;"enterprisedb";"Postgres Studio - Browser";"192.168.0.180";"";3907;"2012-12-26 16:35:45.753172+05:30";"";"2012-12-26 16:35:46.577723+05:30";f;"<IDLE>" Also, tried to Kill it Firstly by using Cancel Backend and then Terminate Backend output showing "True" but still remaining as a process (i.e. in pg_stat_activity). -- View this message in context: http://postgresql.1045698.n5.nabble.com/Cursor-fetch-Problem-tp5737915p5737995.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On Thursday, December 27, 2012 11:51 AM Harry wrote: > Hi Amit, > Thanks for Reply. > Kindly see my below output. > 16650;"sampledb";11965;10;"enterprisedb";"";"192.168.0.231";"";53897;"* > 2012-12-19 > 11:39:48.234799+05:30";"2012-12-19 11:39:53.288441+05:30";"2012-12-19 > 11:39:53.288441+05:30*";f;"DECLARE > BEGIN > EXEC > 16650;"sampledb";12156;10;"enterprisedb";"";"192.168.0.231";"";53983;*" > 2012-12-19 > 12:18:38.57709+05:30";"2012-12-19 12:18:43.922301+05:30";"2012-12-19 > 12:18:43.922301+05:30"*;f;"DECLARE > BEGIN > EXEC > 16650;"sampledb";13243;10;"enterprisedb";"Postgres Studio - > Browser";"192.168.0.180";"";3907;"2012-12-26 > 16:35:45.753172+05:30";"";"2012-12-26 16:35:46.577723+05:30";f;"<IDLE>" Above shows that first two sessions are running from last few days. I am interested to know what is the transaction state in first 2 sessions. In current version that information is part of pg_stat_activity, but don't know how to get in the version you are using. If possible for you, get this information. If you are using Linux system the try ps ax | grep postgres and show the output > Also, tried to Kill it Firstly by using Cancel Backend and then > Terminate > Backend output showing "True" but still remaining as a process (i.e. in > pg_stat_activity). Are you aware whether there is actually such long query running in first 2 sessions. If you are not interested in first 2 sessions, you can even use OS kill command. With Regards, Amit Kapila.
On Thursday, December 27, 2012 11:51 AM Harry wrote: > Hi Amit, > Thanks for Reply. > Kindly see my below output. > > Also, tried to Kill it Firstly by using Cancel Backend and then > Terminate > Backend output showing "True" but still remaining as a process (i.e. in > pg_stat_activity). Can you check the server log and see if there is any of below the statements in the log: FATAL: terminating connection due to administrator command ERROR: canceling statement due to user request With Regards, Amit Kapila.
Below is the Linux ps -ef | grep postgres output :- 501 12163 5473 0 Dec19 ? 00:00:00 postgres: enterprisedb sampledb 192.168.0.231[53991] ?EDB-SPL Procedure successfully completed 501 12167 5473 0 Dec19 ? 00:00:00 postgres: enterprisedb sampledb 192.168.0.231[53995] ?EDB-SPL Procedure successfully completed. Also, if i try to kill from OS the whole database gets shut down. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Cursor-fetch-Problem-tp5737915p5737997.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On Thursday, December 27, 2012 2:44 PM Harry wrote: > Below is the Linux ps -ef | grep postgres output :- > > 501 12163 5473 0 Dec19 ? 00:00:00 postgres: enterprisedb > sampledb 192.168.0.231[53991] ?EDB-SPL Procedure successfully completed > 501 12167 5473 0 Dec19 ? 00:00:00 postgres: enterprisedb > sampledb 192.168.0.231[53995] ?EDB-SPL Procedure successfully > completed. > > Also, if i try to kill from OS the whole database gets shut down. Have you checked server logs for any specific messages like below or any other message after you call cancel/terminate Backend API? FATAL: terminating connection due to administrator command ERROR: canceling statement due to user request With Regards, Amit Kapila.
No any statements as u mentioned. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Cursor-fetch-Problem-tp5737915p5738120.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Hi, Kindly see the below attached statements related to Cursor Fetch Issue it's still residing as a process. 500 20222 31036 79 Dec27 ? 16:22:31 postgres: user1 sampledb 192.168.0.40[36022] FETCH 500 20829 31036 81 Dec27 ? 16:18:48 postgres: user1 sampledb 192.168.0.40[57591] FETCH 500 20867 31036 81 Dec27 ? 16:09:33 postgres: user1 sampledb 192.168.0.40[45316] FETCH 500 20870 31036 81 Dec27 ? 16:09:12 postgres: user1 sampledb 192.168.0.40[45343] FETCH -- View this message in context: http://postgresql.1045698.n5.nabble.com/Cursor-fetch-Problem-tp5737915p5738099.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On Friday, December 28, 2012 10:58 AM Harry wrote: > Hi, > Kindly see the below attached statements related to Cursor Fetch Issue it's > still residing as a process. >500 20222 31036 79 Dec27 ? 16:22:31 postgres: user1 sampledb 192.168.0.40[36022] FETCH >500 20829 31036 81 Dec27 ? 16:18:48 postgres: user1 sampledb 192.168.0.40[57591] FETCH >500 20867 31036 81 Dec27 ? 16:09:33 postgres: user1 sampledb 192.168.0.40[45316] FETCH >500 20870 31036 81 Dec27 ? 16:09:12 postgres: user1 sampledb 192.168.0.40[45343] FETCH Not sure if FETCH has hanged due to some reason or some other problem due to which cancel or terminate backend is not working. Can you provide a stacktrace of hanged backends? Also do let me know you Postgresql version and OS? Just for test, can you connect a new backend with psql. verify the entry for same in pg_stat_activity. call terminate APIfor this session. check again if the new entry has gone from pg_stat_activity? With Regards, Amit Kapila.