sql - pgplsql 存储过程的性能？

Question

我们有以下存储过程，最近在 postgres 数据库中对大量日期执行非常慢。问题:

我们解析一个字符串的性质(第一个数字是行的id，第二个是状态)

||2|0||3|1||4|0||

用java这样的高级语言解析、拆分字符串和循环是不是更好？循环在 Postgres 中可以更有效吗？如何在存储过程中处理事务？整个功能是一次交易？可能我们正在对数据库执行大量写入和删除操作。删除也需要很长时间。可以更有效地处理吗？

CREATE OR REPLACE FUNCTION verificaemitidos(entrada text, largo_mensaje integer)
  RETURNS character AS
$BODY$
    DECLARE                 
    texto_procesado text;       
    identificador bigint;
    estado_mensaje int;
    i int;
    existe_documento int;
    estado_documento text;
    rut numeric;
    tipo int;
    folio_doc numeric;
    otros_estados int;
    BEGIN
        --estado 1 insertado
        --estado 0 no insertado
        --mensaje id_documento|estado||id_documento|estado||


        i := 1;
        while (i <= largo_mensaje)
        loop            
            --Proceso el mensaje
            texto_procesado := split_part(entrada,'||', i) ;
            identificador := split_part(texto_procesado, '|', 1);   
            estado_mensaje := split_part(texto_procesado, '|', 2);              
            -- Se comienza a hacer la comparacion
            existe_documento := (select count (id) from uris_emitidos where id = identificador);
            select estado, emp_rut, tipo_doc, folio into estado_documento, rut, tipo, folio_doc from uris_emitidos where id = identificador;

            --si existe el documento            
            if (existe_documento > 0) then              
                --si el documento que se ingreso esta insertado
                if (estado_mensaje = 1) then
                    --si esta aceptado se eliminan todos los documentos con ese rut, tipo, folio
                    if (estado_documento = 'A') then
                        delete from uris_emitidos where folio = folio_doc and emp_rut = rut and tipo_doc = tipo;
                    end if;
                    --si esta aceptado con reparo se eliminan todos los documentos con ese rut, tipo, folio
                    if (estado_documento = 'B') then
                        delete from uris_emitidos where folio = folio_doc and emp_rut = rut and tipo_doc = tipo;
                    end if;
                    --si esta rechazado se elimina el rechazado y el publicado
                    if (estado_documento = 'R') then
                        delete from uris_emitidos where folio = folio_doc and emp_rut = rut and tipo_doc = tipo and estado in ('R', 'P');
                    end if;
                    --si esta publicado se elimina
                    if (estado_documento = 'P') then
                        delete from uris_emitidos where id = identificador;
                    end if;
                --si el documento que se ingreso no esta insertado              
                else
                    --si esta aceptado se actualiza para que el proceso lo re-encole
                    if (estado_documento = 'A') then 
                        update uris_emitidos set estado_envio = 0, cont = (cont + 1) where id = identificador;                      
                    end if;
                    --si esta aceptado con reparo se actualiza para que el proceso lo re-encole
                    if (estado_documento = 'B') then
                        update uris_emitidos set estado_envio = 0, cont = (cont + 1) where id = identificador;                      
                    end if;
                    --si esta rechazado se verifica que no existe un registro aceptado que se haya encolado o este en espera de encolar
                    if (estado_documento = 'R') then
                        otros_estados = (select count(id) from uris_emitidos ue where ue.folio = folio_doc and ue.emp_rut = rut and ue.tipo_doc = tipo and ue.estado in ('A', 'B'));
                        --si otros estados = 0 significa que el estado rechazado es el mejor estado que hay, por lo tanto se debe re-encolar
                        if (otros_estados = 0) then
                            update uris_emitidos set estado_envio = 0, cont = (cont + 1) where id = identificador;
                        end if;
                    end if;
                    --si esta rechazado se verifica que no existe un registro aceptado o rechazado que se haya encolado o este en espera de encolar
                    if (estado_documento = 'P') then
                        otros_estados = (select count(id) from uris_emitidos where folio = folio_doc and emp_rut = rut and tipo_doc = tipo and estado in ('A', 'B', 'R'));
                        --si otros estados = 0 significa que el estado rechazado es el mejor estado que hay, por lo tanto se debe re-encolar
                        if (otros_estados = 0) then
                            update uris_emitidos set estado_envio = 0, cont = (cont + 1) where id = identificador;
                        end if;
                    end if;

                end if;

            end if;

            i := i+1;
        end loop;
        return 'ok';


    END;
$BODY$
  LANGUAGE plpgsql VOLATILE;

Answer 1

Can the loop be more efficient in pgsql?

正如@wildplasser 所提到的，运行操作多行的 SQL 语句通常比单独操作每一行要快得多。循环只能在 plpgsql(或其他过程语言函数，或者以有限的方式，在递归 CTE)中使用，而不能在普通 SQL 中使用。他们的工作做得很好，但不是 PostgreSQL 的强项。

How are transactions handled in stored procedures? The whole function is one transaction?

是的，整个功能作为一个事务运行。它可以是更大交易的一部分，但不能拆分。

在此 related answer on dba.SE 中阅读有关 plpgsql 函数如何工作的更多信息.

Is it better to parse, split the string and loop in a higher language like java?

如果字符串不是很大(数千个元素)，那真的没关系，只要您的逻辑合理即可。减慢速度的不是字符串解析。这是表中行的“一次一行”操作。

更快的替代方法是在一条或几条 SQL 语句中完成所有操作。我会用 data modifying CTEs为此(在 PostgreSQL 9.1 中引入):解析字符串一次，并在此内部工作表上运行 DML 语句。

考虑以下演示(未经测试):

WITH a(t) AS (  -- split string into rows
    SELECT unnest(string_to_array(trim('||2|0||3|1||4|0||'::text, '|'), '||'))
    )
    , b AS (    -- split record into columns per row
    SELECT split_part(t, '|', 1) AS identificador 
          ,split_part(t, '|', 2) AS estado_mensaje 
    FROM   a
    )
    , c AS (    -- implements complete IF branch of your base loop
    DELETE FROM uris_emitidos u
    USING  b
    WHERE  u.id = b.identificador
    AND    u.estado IN ('A','B','R','P')
    AND    b.estado_mensaje = 1
    )
--  , d AS (    -- implements ELSE branch of your loop
--  DELETE ...
--  )
SELECT 'ok':

除了主要的设计缺陷之外，您的循环中的逻辑也是冗余且不一致的。我将整个 IF 分支合并到上面的第一个 DELETE 语句中。

关于手册中使用的函数的更多信息here .