PHP PDO+PostgreSQL-事务处理


PHP PDO + PostgreSQL - transactions handling

我有一个问题,在可用的资源中找不到合适的答案。

问题描述当我使用PDO从php调用postgres函数时,我不会收到响应(ajax调用正在等待),因此我会超时。参数paaasing没有问题。当我将函数作为事务直接在postgres中运行时,它运行良好:

begin;
select lcp_mess_ordering(1);
fetch all po_cursor;
commit;

我做错了什么?

代码详细信息:

PHP部分

try {
$conn = pdoDbConnect();
$conn->beginTransaction();
$query = 'select lcp_mess_ordering(
            pi_msg_id := :post_pi_msg_id
        )';
$stmt = $conn->prepare($query);
$stmt->bindParam('post_pi_msg_id', $_POST['msg_id'], PDO::PARAM_INT);   
$stmt->execute();
$result = $stmt->fetchAll();                            
$stmt->closeCursor();
$conn->commit();
unset($stmt);
(...)

PostgreSQL函数

CREATE OR REPLACE FUNCTION lcp_mess_ordering(IN pi_msg_id integer, OUT po_cursor    refcursor, OUT po_err_num integer, OUT po_err_desc text)
RETURNS record AS
$BODY$  
DECLARE
v_proc_name text;
v_step integer;
v_next_step integer;
v_msg_id integer;
v_message_row record;
v_max_step integer;
mess_cursor cursor for select "MSG_ID", "MSG_STEP" from lct_messages_tmp where "MSG_AUDIT_RD" is null;
BEGIN
(...)
select "MSG_STEP" into v_step from tbr_messages where "MSG_ID" = pi_msg_id;
select max("MSG_STEP") into v_max_step from tbr_messages where "MSG_AUDIT_RD" is null;
for v_msg_id in select "MSG_ID" from tbr_messages where "MSG_STEP" = v_step
loop
    select "MSG_NEXT_STEP" into v_next_step from tbr_messages where "MSG_ID" = v_msg_id;
    IF v_next_step = (v_step + 1) THEN
        update tbr_messages set
        "MSG_NEXT_STEP" = null,
        "MSG_AUDIT_MD" = now()
        where "MSG_ID" = v_msg_id;
    END IF;
end loop;
update tbr_messages set
"MSG_STEP" = (v_step + 1),
"MSG_AUDIT_MD" = now()
where "MSG_STEP" = v_step;
open mess_cursor;
loop
    fetch mess_cursor into v_message_row;
    exit when not found;
    IF v_message_row."MSG_STEP" = v_step + 1 THEN
        update tbr_messages set
        "MSG_STEP" = v_step,
        "MSG_AUDIT_MD" = now()
        where "MSG_ID" = v_message_row."MSG_ID";
    END IF;
end loop;
OPEN po_cursor FOR 
(...)
RETURN;
EXCEPTION
    WHEN others THEN 
    po_err_num := SQLSTATE;
    po_err_desc := SQLERRM;
    RETURN;

END;
$BODY$
LANGUAGE plpgsql VOLATILE

缺少ajax部分:

$('body').on('click', '#down_btn', function(e){
    e.preventDefault();

    var msg_id = $(this).attr('data-msg_id');

    var request = $.ajax({      
        url: '../admin_php/mess_move_down.php',
        type: 'post',
        data: { 'msg_id': msg_id },
        dataType: "html",
    });
    request.done(function( data ) {
        $( '#assigned_section' ).html( data );
    });

});

问题发生在所在的位置

$conn->commit();

位于代码中。

值得一读:http://php.net/manual/en/pdo.transactions.php