在PostgreSQL的SQL查询中使用ANY运算符的奇怪问题

0sgqnhkj  于 2022-11-04  发布在  PostgreSQL
关注(0)|答案(1)|浏览(224)

让我解释一下我的设想。
我有一个包含550万条记录的简单表,结构很简单。

relations
------------
id
parent_id
name

我有一个递归函数来获取一个节点的所有子节点id;这个函数在任何情况下都能正常工作,这不是问题所在

CREATE OR REPLACE FUNCTION children_ids(id INTEGER)
RETURNS INT[]
LANGUAGE plpgsql
AS
$$
DECLARE
    ids INT[];
    r RECORD;
BEGIN
    FOR r IN WITH RECURSIVE t
    AS
    (
        SELECT * 
          FROM relations sa
         WHERE sa.id = id
         UNION ALL
        SELECT next.*
          FROM t prev
          JOIN relations next ON (next.parent_id = prev.id)
    )
    SELECT t.id FROM t
    LOOP
        ids := ids || r.id;
    END LOOP;

    RETURN ids;
END
$$;

然后,我需要查询另一个表,以获取函数返回的任何子id中的记录数:
我要执行的查询是:

SELECT COUNT(p.*) FROM books AS p WHERE p.id = ANY(children_ids(20));

但由于超时,它失败了。奇怪的是,在我的localhost中,这个相同的查询工作正常,数据库被克隆,PostgreSQL版本是相同的。
我已经测试了另一种变体,结果如下:

SELECT children_ids(20);
-- WORKS: {20}

SELECT COUNT(p.*) FROM books AS p WHERE p.id = ANY(children_ids(20)); 
-- FAILS: TIMEOUT

SELECT COUNT(*) FROM books AS p WHERE p.id = ANY(children_ids(20)); 
-- FAILS: TIMEOUT

SELECT * FROM books AS p WHERE p.id = ANY(children_ids(20));
-- FAILS: TIMEOUT

SELECT COUNT(p.*) FROM books AS p WHERE p.id = ANY(ARRAY[20]);
-- WORKS: 0

SELECT COUNT(p.*) FROM books AS p WHERE p.id IN (20);
-- WORKS: 0

SELECT COUNT(*) FROM books AS p WHERE p.id IN (20);
-- WORKS: 0

你知道这里会发生什么吗

krcsximq

krcsximq1#

您的数据中可能存在循环,因此您的递归会进入无限循环。有关如何打破循环的信息,请参阅文档。使用PostgreSQL v14或更高版本,最简单的方法是

WITH RECURSIVE t AS (
   SELECT ...
   UNION ALL
   SELECT ...
) CYCLE id SET is_cycle USING path

相关问题