Thread: Cursor fetch Problem.

Cursor fetch Problem.

From
Harry
Date:
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.


Re: Cursor fetch Problem.

From
Amit Kapila
Date:
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.



Re: Cursor fetch Problem.

From
Harry
Date:
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.


Re: Cursor fetch Problem.

From
Amit Kapila
Date:
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.



Re: Cursor fetch Problem.

From
Amit Kapila
Date:
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.



Re: Cursor fetch Problem.

From
Harry
Date:
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.


Re: Cursor fetch Problem.

From
Amit Kapila
Date:
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.



Re: Cursor fetch Problem.

From
Harry
Date:
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.


Re: Cursor fetch Problem.

From
Harry
Date:
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.


Re: Cursor fetch Problem.

From
Amit kapila
Date:
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.