Cursor fetch Problem.

Started by Harryover 13 years ago10 messagesgeneral

shirlekar.harshal@gmail.com

over 13 years ago

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Amit Kapila

amit.kapila16@gmail.com

over 13 years ago

In reply to: Harry (#1)

Re: Cursor fetch Problem.

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Harry

shirlekar.harshal@gmail.com

over 13 years ago

In reply to: Harry (#1)

Re: Cursor fetch Problem.

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Amit Kapila

amit.kapila16@gmail.com

over 13 years ago

In reply to: Harry (#3)

Re: Cursor fetch Problem.

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Harry

shirlekar.harshal@gmail.com

over 13 years ago

In reply to: Amit Kapila (#4)

Re: Cursor fetch Problem.

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Amit Kapila

amit.kapila16@gmail.com

over 13 years ago

In reply to: Harry (#3)

Re: Cursor fetch Problem.

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Harry

shirlekar.harshal@gmail.com

over 13 years ago

In reply to: Harry (#1)

Re: Cursor fetch Problem.

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Import Notes

Reply to msg id not found: 1356602479135-5737999.post@n5.nabble.com

Amit Kapila

amit.kapila16@gmail.com

over 13 years ago

In reply to: Harry (#5)

Re: Cursor fetch Problem.

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Harry

shirlekar.harshal@gmail.com

over 13 years ago

In reply to: Amit Kapila (#8)

Re: Cursor fetch Problem.

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

#10

Amit Kapila

amit.kapila16@gmail.com

over 13 years ago

In reply to: Harry (#7)

Re: Cursor fetch Problem.

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 API for this session. check again if the new entry has gone from pg_stat_activity?

With Regards,
Amit Kapila.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general