PostgreSQL存储过程调用后无更新且返回-1问题求助

核心问题：PostgreSQL数组为1-based索引，传入的起始索引不匹配 #

PostgreSQL的数组默认从1开始索引，但你的C#代码传入的start_with值为0，存储过程中用counter := 0读取数组元素时会拿到NULL，导致UPDATE语句的WHERE "Id" = NULL永远无法匹配数据行，自然不会产生任何更新，而ExecuteNonQuery()返回-1是PostgreSQL对存储过程执行的正常返回值（不代表受影响行数）。

修复步骤 #

1. 调整C#代码的索引参数 #

将起始索引改为1，结束索引改为集合的总长度（1-based的最后一个元素索引等于集合长度）：

cmd.Parameters["start_with"].Value = 1;
cmd.Parameters["end_with"].Value = rfi.Ids.Count;

2. 优化存储过程逻辑 #

• 移除循环内的频繁COMMIT：存储过程默认继承外部连接的事务上下文，循环内多次提交会降低性能且无必要
• 简化循环逻辑，确保数组索引正确：

CREATE OR REPLACE PROCEDURE shipment_status.updatefilelocation(
    ids_to_update uuid[],
    files_to_update text[],
    start_with integer,
    end_with integer)
LANGUAGE 'plpgsql'
AS $BODY$
DECLARE counter INT;
DECLARE currentID UUID;
DECLARE currentFILE TEXT;
BEGIN
    counter := start_with;  
    WHILE counter <= end_with LOOP
        currentID := ids_to_update[counter];
        currentFile := files_to_update[counter];
        
        UPDATE shipment_status."RawShipmentStatusMissingSCACCode"
        SET "RawDataStorageLink" = currentFile
        WHERE "Id" = currentID;
        
        UPDATE shipment_status."RawShipmentStatusMissingShipmentStatusCodeMap"
        SET "RawDataStorageLink" = currentFile
        WHERE "Id" = currentID;
        
        counter := counter + 1;
    END LOOP;
    -- 若需手动提交，可在循环结束后统一执行，或依赖外部事务控制
    -- COMMIT;
END;
$BODY$;

3. 可选：改用批量更新提升性能 #

循环更新效率较低，推荐用unnest将数组转为行数据，关联表进行批量更新，同时可以去掉冗余的start_with和end_with参数：

CREATE OR REPLACE PROCEDURE shipment_status.updatefilelocation(
    ids_to_update uuid[],
    files_to_update text[])
LANGUAGE 'plpgsql'
AS $BODY$
BEGIN
    -- 批量更新第一张表
    UPDATE shipment_status."RawShipmentStatusMissingSCACCode" t
    SET "RawDataStorageLink" = u.file_path
    FROM unnest(ids_to_update, files_to_update) AS u(id, file_path)
    WHERE t."Id" = u.id;
    
    -- 批量更新第二张表
    UPDATE shipment_status."RawShipmentStatusMissingShipmentStatusCodeMap" t
    SET "RawDataStorageLink" = u.file_path
    FROM unnest(ids_to_update, files_to_update) AS u(id, file_path)
    WHERE t."Id" = u.id;
END;
$BODY$;

对应的C#代码简化为：

cmd = new NpgsqlCommand("CALL shipment_status.updatefilelocation(@ids_to_update, @files_to_update)", con);

cmd.Parameters.Add("ids_to_update", NpgsqlDbType.Array | NpgsqlDbType.Uuid);
cmd.Parameters.Add("files_to_update", NpgsqlDbType.Array | NpgsqlDbType.Text);
cmd.Parameters["ids_to_update"].Value = rfi.Ids.ToArray();
cmd.Parameters["files_to_update"].Value = rfi.RawDataStorageLinks.ToArray();

验证方法 #

1. 在PostgreSQL客户端手动调用存储过程，传入1-based索引和测试数据，确认更新生效：

   CALL shipment_status.updatefilelocation('{uuid-1, uuid-2}'::uuid[], '{file-path-1, file-path-2}'::text[], 1, 2);
   

2. 检查C#代码中rfi.Ids和rfi.RawDataStorageLinks的长度是否一致，避免数组长度不匹配导致的部分数据无法更新

内容的提问来源于stack exchange，提问作者Chris