Who is Running "FETCH FORWARD xxx FROM _psql

Products

VMware Tanzu Greenplum

Issue/Introduction

Symptoms:

pg_stat_activity always displays the query for a FETCH from a cursor (_psql_cursor) i.e.: "FETCH FORWARD xxx FROM _psql_cursor" .

gpadmin=# select
gpadmin-#     datname as "Db name",
gpadmin-#     usename as "User name",
gpadmin-#     procpid as "Pid",
gpadmin-#     sess_id as "SessionID",
gpadmin-#     waiting as "Waiting",
gpadmin-#     client_addr as "client Addr",
gpadmin-#     application_name as "App Name",
gpadmin-#     query_start as "Start time",
gpadmin-#     substring(current_query from 1 for 40) as "Current Query"
gpadmin-# from pg_stat_activity
gpadmin-# where current_query!='';
 Db name | User name | Pid  | SessionID | Waiting | client Addr | App Name |          Start time           |            Current Query             
---------+-----------+------+-----------+---------+-------------+----------+-------------------------------+--------------------------------------
 gpadmin | gpadmin   | 6224 |     36693 | f       |             | psql     | 2015-09-15 02:45:27.035508-05 | FETCH FORWARD 1000 FROM _psql_cursor
(1 row)

In the master log, the sequence of the events run by the process is mentioned below:

2015-09-15 02:45:27.024395 CDT,"gpadmin","gpadmin",p6224,th714118944,"[local]",,2015-09-15 02:44:40 CDT,476842,con36693,cmd17,seg-1,,dx24729,x476842,sx1,"LOG
","00000","statement: BEGIN",,,,,,"BEGIN",0,,"postgres.c",1566,
2015-09-15 02:45:27.034510 CDT,"gpadmin","gpadmin",p6224,th714118944,"[local]",,2015-09-15 02:44:40 CDT,476842,con36693,cmd18,seg-1,,dx24729,x476842,sx1,"LOG
","00000","statement: DECLARE _psql_cursor NO SCROLL CURSOR FOR
select
    datname as ""Db name"",
    usename as ""User name"",
    procpid as ""Pid"",
    sess_id as ""SessionID"",
    waiting as ""Waiting"",
    client_addr as ""client Addr"",
    application_name as ""App Name"",
    query_start as ""Start time"",
    substring(current_query from 1 for 40) as ""Current Query""
from pg_stat_activity
where current_query!='';",,,,,,"DECLARE _psql_cursor NO SCROLL CURSOR FOR
select
    datname as ""Db name"",
    usename as ""User name"",
    procpid as ""Pid"",
    sess_id as ""SessionID"",
    waiting as ""Waiting"",
    client_addr as ""client Addr"",
    application_name as ""App Name"",
    query_start as ""Start time"",
    substring(current_query from 1 for 40) as ""Current Query""
from pg_stat_activity
where current_query!='';",0,,"postgres.c",1566,
2015-09-15 02:45:27.035622 CDT,"gpadmin","gpadmin",p6224,th714118944,"[local]",,2015-09-15 02:44:40 CDT,476842,con36693,cmd20,seg-1,,dx24729,x476842,sx1,"LOG
","00000","statement: FETCH FORWARD 1000 FROM _psql_cursor",,,,,,"FETCH FORWARD 1000 FROM _psql_cursor",0,,"postgres.c",1566,
2015-09-15 02:45:27.036783 CDT,"gpadmin","gpadmin",p6224,th714118944,"[local]",,2015-09-15 02:44:40 CDT,476842,con36693,cmd21,seg-1,,dx24729,x476842,sx1,"LOG
","00000","statement: CLOSE _psql_cursor",,,,,,"CLOSE _psql_cursor",0,,"postgres.c",1566,
2015-09-15 02:45:27.036894 CDT,"gpadmin","gpadmin",p6224,th714118944,"[local]",,2015-09-15 02:44:40 CDT,476842,con36693,cmd22,seg-1,,dx24729,x476842,sx1,"LOG
","00000","statement: COMMIT",,,,,,"COMMIT",0,,"postgres.c",1566,

Environment

Cause

The above query on the cursor _psql_cursor appears when the FETCH_COUNT option is turned on.

Resolution

Remove the parameter FETCH_COUNT from the psql prompt.

gpadmin=# \unset FETCH_COUNT

If the option FETCH_COUNT is turned on every time a user logs on to psql, then check the file ".psqlrc" under the user's home directory, and remove the option to disable the options for each psql connection.

[gpadmin@gpdbsne ~]$ pwd
/home/gpadmin
[gpadmin@gpdbsne ~]$ cat .psqlrc
\set FETCH_COUNT 1000
[gpadmin@gpdbsne ~]$ mv .psqlrc .psqlrc-bk