postgresql 即使语句完成OK，仍有许多DELETE语句未完成

r9f1avp5 于 2023-11-18 发布在 PostgreSQL

关注(0)|答案(1)|浏览(94)

我有一个函数可以在一个循环中从许多表中删除行，多达130多个表。问题不在于DELETE语句本身，它们似乎能及时完成，但出于某种原因，LOOP需要更多的时间才能退出。
函数如下：

CREATE OR REPLACE FUNCTION myschema.delete_dependents(p_id smallint)
RETURNS void
LANGUAGE plpgsql
AS $function$
DECLARE
    v_query_delete          TEXT;
    i                       record;
BEGIN

DROP TABLE IF EXISTS tmp_dependents_table;
CREATE TEMP TABLE tmp_dependents_table (
    schema_name     varchar(255),
    table_oid       oid,
    table_name      varchar(255),
    level           smallint    
);

INSERT INTO tmp_dependents_table
SELECT DISTINCT
    ot.schema_name
    ,ot.table_oid
    ,ot.table_name
    ,ot.level
FROM related_tables_recursive(current_schema()) AS ot
INNER JOIN pg_attribute AS pga
ON pga.attrelid = ot.table_oid
WHERE   pga.attname = 'col_id'
    AND ot.level > 1
ORDER BY ot.level DESC NULLS LAST;

FOR i IN 
    SELECT * FROM tmp_dependents_table
LOOP
            
    v_query_delete := '';
    
    v_query_delete := 'DELETE FROM ' || i.schema_name || '.' || i.table_name
        || ' WHERE col_id = ' || p_id::TEXT || ';';
    
    RAISE NOTICE 'EXECUTING --> %', v_query_delete;
    EXECUTE v_query_delete;
    RAISE NOTICE 'EXECUTED DELETE.';
    
END LOOP;
    
END;
$function$
;

字符串
如果我单独执行SELECT语句来为temp.table提供数据，我会得到相关的数据，即包含col_id列的所有表
日志（RAISE NOTICE）显示该函数正在遍历所有表并快速执行删除，没有问题，但循环需要永远退出。
最后记录的EXECUTED DELETE是tmp_dependents_table中的最后一条记录。
我已经查找了触发器，其中一个表（在我的测试数据上运行函数时，它保存了22k条记录）在DELETE上定义了一个触发器，我已经删除了这个触发器。
我已经查找了规则。我找到的模式的唯一规则是SELECT规则。
有没有其他的东西可以自动触发，使我的代码需要更多的时间来完成？

postgresql

来源：https://stackoverflow.com/questions/77376347/loop-with-many-deletes-not-finishing-even-though-the-statements-complete-ok

1条答案

按热度按时间

bzzcjhmw1#

一个可能的解决方案：使用ON DELETE CASCADE-如果您希望同时删除依赖行，请定义FK约束。
参见：

在PostgreSQL中删除带有外键的行

在使用它的同时，您可以在很大程度上简化功能，并使其更便宜，更安全：

CREATE OR REPLACE FUNCTION myschema.delete_dependents(p_id smallint)
  RETURNS void
  LANGUAGE plpgsql AS
$func$
DECLARE
   _sql text;
   _tbl regclass;
BEGIN
   FOR _tbl IN 
      SELECT ot.table_oid  -- schema_name & table_name are redundant noise
      FROM   related_tables_recursive(current_schema()) ot  -- function can probably simplified, too
      WHERE  ot.level > 1  -- excludes null
      AND    EXISTS (
         SELECT FROM pg_attribute pga
         WHERE  pga.attrelid = ot.table_oid
         AND    pga.attname = 'col_id'
         AND    NOT pga.attisdropped  -- must be ruled out
         )
      GROUP BY ot.table_oid  -- why would you expect duplicates?
      ORDER BY max(ot.level) DESC, ot.table_oid
      -- nulls have been excluded, so no point in "NULLS LAST"
      -- make sort order unambiguous to avoid deadlocks with concurrent transactions
   LOOP
       -- _sql := '';  -- noise
       _sql := format('DELETE FROM %s WHERE col_id = $1', _tbl);

       RAISE NOTICE 'EXECUTING --> %', _sql;
       EXECUTE _sql USING p_id;  -- pass value as value
       RAISE NOTICE 'EXECUTED DELETE.';
   END LOOP;
END
$func$;

字符串
你不需要一个临时的table。
您的原始文件容易受到SQL注入的攻击。标识符必须被视为可能不安全的用户输入，并且必须在必要时使用双引号。关于这一点，以及regclass的作用：

表名作为PostgreSQL函数参数

赞(0）回复(0）举报 2023-11-18

我来回答

postgresql 即使语句完成OK，仍有许多DELETE语句未完成

1条答案

相关问题

热门标签

最新问答