search

ANXS / postgresql

Fairly full featured Ansible role for Postgresql.
http://anxs.io/
MIT License
848 stars 573 forks source link

run big transaction procedure seem like be blocked #508

Closed hotsmile closed 3 years ago

hotsmile commented 3 years ago

i have a procedure , if i directly run it ,but it seems like be locked ,if i split it three sub-procedure ,i can run it quickly , i don't know what's the reason. maybe the pg transactions are different with Oracle . now the main procedure like this CREATE OR REPLACE PROCEDURE sqm_rpt."report_data_js_service_pkg$rpt_ts_other_info_prc_main"(p_month character varying, p_mainprc character varying DEFAULT NULL::character varying) LANGUAGE plpgsql AS $procedure$ begin begin
call report_data_js_service_pkg$rpt_ts_other_info_prc1(p_month,p_mainprc); end; commit;

begin call report_data_js_service_pkg$rpt_ts_other_info_prc2(p_month); end; commit;

begin call report_data_js_service_pkg$rpt_ts_other_info_prc3(p_month);

end; commit; end; $procedure$ ;

I execute three procedures in PL / PgSQL serially and get the result quickly 。if the sub-transaction problem ,i think my main procedure has done it . my pg verison is that PostgreSQL 12.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39), 64-bit, TelePG

gclough commented 3 years ago

Hi @hotsmile , this isn't something that we can help with as this repository holds code is to deploy PostgreSQL with Ansible, not the database code itself.

You should probably subscribe to one of the mailing lists, and post your question there:

https://www.postgresql.org/list/

You probably want to use the pgsql-performance list.