Pivotal Knowledge Base

フォローする

データベース上で "FETCH FORWARD xxx FROM _psql_cursor" が実行されている原因

環境

製品 バージョン
Pivotal Greenplum(GPDB) 4.3.5.2

事象

pg_stat_activity で、いつも特定カーソル(_psql_cursor)から FETCH を行うクエリー(すなわち、"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)

マスターのログには、以下のような、当該プロセスで実行された一連のイベントが見られる。

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,

このクエリーはどこから実行されているのであろうか?

原因

上述の特定カーソル _psql_cursor に対するクエリーは、FETCH_COUNT オプションが有効になっている場合に実行される。

解決策

psql プロンプトから、FETCH_COUNT パラメーターを削除する。

gpadmin=# \unset FETCH_COUNT 

psql にユーザーログオンする度に FETCH_COUNT オプションが有効になってしまう場合は、当該ユーザーのホームディレクトリにある ".psqlrc" ファイルを確認し、各 psql 接続に対して当該オプションを無効にするために、設定行を削除する。

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

コメント

Powered by Zendesk