标簽
PostgreSQL , dblink , 長任務
https://github.com/digoal/blog/blob/master/201806/20180621_03.md#%E8%83%8C%E6%99%AF 背景
如果業務上需要在資料庫中跑LONG SQL，并且不希望跑的過程中因為視窗斷開，導緻資料庫任務使用者主動cancel query。有什麼方法？
使用DBLINK異步調用是不錯的方法，相當于資料庫内部建立了連接配接在背景跑。
https://github.com/digoal/blog/blob/master/201806/20180621_03.md#%E6%96%B9%E6%B3%95 方法
1、建立任務表，友善觀察任務狀态
create table tbl_task (
id serial8 primary key, -- 任務ID
client_info jsonb, -- 用戶端描述(usename, datname, search_path, client_addr, client_port)
sql text, -- SQL資訊
start_time timestamp, -- 開始時間
end_time timestamp default clock_timestamp(), -- 結束時間
info text -- 描述資訊
);
2、建立dblink 插件
create extension dblink;
3、建立生成dblink連接配接的函數，重複建立不報錯。
create or replace function conn(
name, -- dblink名字
text -- 連接配接串,URL
) returns void as $$
declare
begin
perform dblink_connect($1, $2);
return;
exception when others then
return;
end;
$$ language plpgsql strict;
4、建立異步調用封裝函數
create or replace function run_task(
sql text, -- 要執行的SQL
info text, -- 任務描述
conn_name name default 'link_for_task', -- dblink 名字
conn text default format('hostaddr=%s port=%s user=%s dbname=%s application_name=run_task', '127.0.0.1', current_setting('port'), current_user, current_database()) , -- 連接配接串
client_info jsonb default format('{"client_addr":"%s", "client_pot":"%s", "search_path":"%s", "usename":"%s", "datname":"%s"}', inet_client_addr(), inet_client_port(), replace(current_setting('search_path'),'"','\"'), current_user, current_database())::jsonb -- 用戶端資訊
) returns void as $$
declare
begin
perform conn(conn_name, conn); -- 連接配接。
-- perform dblink_get_result(conn_name); -- 消耗掉上一次異步連接配接的結果，否則會報錯。
-- 發送異步DBLINK調用
perform dblink_send_query(conn_name, sql||format('; insert into tbl_task(client_info,sql,start_time,info) values (%L, %L, %L, %L);', client_info, sql, clock_timestamp(),info ));
end;
$$ language plpgsql strict;
5、調用異步調用封裝函數
select run_task(
'select count(*) from test', -- 要RUN的SQL
'test dblink async call' -- 任務描述
);
6、檢視目前正在跑的背景任務
postgres=# select * from pg_stat_activity where application_name='run_task';
-[ RECORD 1 ]----+---------------------------------------------------------------------------------------------
datid | 13285
datname | postgres
pid | 1510
usesysid | 10
usename | postgres
application_name | run_task
client_addr | 127.0.0.1
client_hostname |
client_port | 55088
backend_start | 2018-06-21 18:04:20.964586+08
xact_start |
query_start | 2018-06-21 18:04:20.967177+08
state_change | 2018-06-21 18:04:20.969363+08
wait_event_type | Client
wait_event | ClientRead
state | idle
backend_xid |
backend_xmin |
query | select count(*) from test; insert into tbl_task(client_info,sql,start_time,info) values (E'{"datname": "postgres", "usename": "postgres", "client_pot": "", "client_addr": "", "search_path": "\\"$user\\", public"}', 'select count(*) from test', '2018-06-21 18:04:20.967118+08', 'test dblink async call')
backend_type | client backend
7、檢視任務狀态
postgres=# select * from tbl_task;
-[ RECORD 1 ]------------------------------------------------------------------------------------------------------------------------
id | 1
client_info | {"datname": "postgres", "usename": "postgres", "client_pot": "", "client_addr": "", "search_path": "\"$user\", public"}
sql | select count(*) from test
start_time | 2018-06-21 18:07:39.60439
end_time | 2018-06-21 18:07:47.331041
info | test dblink async call
https://github.com/digoal/blog/blob/master/201806/20180621_03.md#%E5%A6%82%E6%9E%9C%E6%98%AF%E6%95%B0%E6%8D%AE%E5%BA%93%E6%99%AE%E9%80%9A%E7%94%A8%E6%88%B7%E8%B0%83%E7%94%A8%E4%B8%8D%E6%94%AF%E6%8C%81trust%E8%AE%A4%E8%AF%81 如果是資料庫普通使用者調用，不支援trust認證
如果是普通使用者，請使用密碼認證，同時請務必保障pg_hba.conf使用的是密碼認證。
比如阿裡雲RDS PPAS的使用者：
select run_task(
'select count(*) from test', -- 要RUN的SQL
'test dblink async call' , -- 任務描述
'link_task',
format('hostaddr=%s port=%s user=%s dbname=%s application_name=run_task, password=%s', '127.0.0.1', current_setting('port'), current_user, current_database(), '目前使用者密碼')
);
如果是阿裡雲RDS PG 9.4的使用者，核心做過修改，請使用如下方法
select run_task(
'select count(*) from test', -- 要RUN的SQL
'test dblink async call' , -- 任務描述
'link_task',
format('user=%s dbname=%s application_name=run_task, password=%s', current_user, current_database(), '密碼')
);
https://github.com/digoal/blog/blob/master/201806/20180621_03.md#%E6%B3%A8%E6%84%8F 注意
1、DBLINK異步連接配接會占用連接配接，算連接配接數的。
2、單個DBLINK連接配接，如果異步調用的SQL沒有執行完，不能發起第二次請求。
NOTICE: could not send query: another command is already in progress
那麼你需要看看這個DBLINK的背景任務是否還在執行（通過前面查詢pg_stat_activity的方法, 執行完state狀态為idle），如果執行完了，那麼執行以下SQL取一下結果，就可以繼續使用這個DBLINK NAME發送異步請求了。
select * from dblink_get_result('link_for_task') as t(id text);
或者，你可以不用等這個DBLINK的異步任務執行完，馬上想發起另一個異步任務，那麼你需要使用一個新的DBLINK NAME。
select run_task(
$$select count(*) from test where c1='abc'$$, -- 要RUN的SQL
'test dblink async call', -- 任務描述
'new_dblink_name' -- 有别于前面已使用的DBLINK NAME
);
3、單個DBLINK連接配接，如果異步調用的SQL執行完了，調用dblink_get_result後，才能發起第二次請求。
NOTICE: could not send query: another command is already in progress
4、單個DBLINK連接配接，如果異步調用的SQL沒有執行完，調用dblink_get_result時，會等待異步調用執行完，才會有傳回。等待過程中堵塞目前調用dblink_get_result的會話。
5、調用DBLINK異步接口的會話如果斷開了，那麼它發起的dblink異步調用背景任務執行完成後，連接配接會自動釋放。
6、如果SQL中包含單引号，可以使用轉義的寫法，也可以使用沒有符号的寫法，不需要轉義。
select run_task(
$$select count(*) from test where c1='abc'$$, -- 要RUN的SQL
'test dblink async call' -- 任務描述
);
或
select run_task(
'select count(*) from test where c1=''abc''', -- 要RUN的SQL
'test dblink async call' -- 任務描述
);
select run_task(
E'select count(*) from test where c1=\'abc\'', -- 要RUN的SQL
'test dblink async call' -- 任務描述
);
美元符号内可以輸入任意個字元，成對出現即可。
select run_task(
$_qqq_$select count(*) from test where c1='abc'$_qqq_$, -- 要RUN的SQL
'test dblink async call' -- 任務描述
);
![【MySQL資料庫】資料庫索引事務1.索引2.事務[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)