问题

I'm trying to update a partitioned PostgreSQL table using a PL/pgsql loop to restrict the update to looking at one partition at a time, because it contains a self join on a large table. I keep getting the error "too many range table entries," and I don't understand why. If I hardcode the partition to update in the WHERE clauses, it works just fine, even if I run 10 partitions at the same time using IN ().

我正在尝试使用PL/pgsql循环来更新一个分区的PostgreSQL表，以便一次只查看一个分区，因为它包含一个大表的自连接。我一直收到错误消息"too many range table entries"，我不明白为什么。如果我在WHERE子句中硬编码要更新的分区，那么它可以正常工作，即使我同时使用IN ()运行10个分区也可以。

I thought those two practically did the same thing; looping over the UPDATE for each value for city_id in turn. Is that wrong? How can I get it to do that?

我以为这两者实际上是在循环中依次对每个city_id的值进行更新的。这是不对的吗？我该如何才能做到这一点？

This doesn't work:

这个不起作用：

DO
$$
DECLARE
    ele record;
BEGIN
    FOR ele IN SELECT city_id 
           FROM dwh.city
    LOOP 
        UPDATE dwh.residence res1
        SET residence_count = res2.residence_count
        FROM (
            SELECT 
                block_id, 
                COUNT(*) as residence_count,
            FROM dwh.residence  
            WHERE 1=1
                AND city_id = ele.city_id
            GROUP BY block_id
        ) res2
        WHERE 1=1
            AND res1.city_id = ele.city_id
            AND res1.block_id = res2.block_id;
    END LOOP;
END;
$$;

But, this does work:

但是，这个可以正常工作：

UPDATE dwh.residence res1
SET residence_count = res2.residence_count
FROM (
    SELECT 
        block_id, 
        COUNT(*) as residence_count,
   FROM dwh.residence  
   WHERE 1=1
       AND city_id = 1
  GROUP BY block_id
) res2
WHERE 1=1
    AND res1.city_id = 1
    AND res1.block_id = res2.block_id;

DO
$$
DECLARE
    ele record;
BEGIN
    FOR ele IN SELECT city_id 
           FROM dwh.city
    LOOP 
        UPDATE dwh.residence res1
        SET residence_count = res2.residence_count
        FROM (
            SELECT 
                block_id, 
                COUNT(*) as residence_count,
            FROM dwh.residence  
            WHERE 1=1
                AND city_id = ele.city_id
            GROUP BY block_id
        ) res2
        WHERE 1=1
            AND res1.city_id = ele.city_id
            AND res1.block_id = res2.block_id;
    END LOOP;
END;
$$;

UPDATE dwh.residence res1
SET residence_count = res2.residence_count
FROM (
    SELECT 
        block_id, 
        COUNT(*) as residence_count,
   FROM dwh.residence  
   WHERE 1=1
       AND city_id = 1
  GROUP BY block_id
) res2
WHERE 1=1
    AND res1.city_id = 1
    AND res1.block_id = res2.block_id;

答案1

得分: 1

根据@LaurenzAlbe发布的评论，使用动态SQL分别引用表分区可以解决错误（我拆分了变量，因为我认为这样看起来更清晰）：

DO
$$
DECLARE
    var_city_id int;
    var_city_name varchar;
BEGIN
    FOR var_city_id, var_city_name IN 
        SELECT city_id, city_name
        FROM dwh.city
    LOOP 
        EXECUTE
            'UPDATE dwh.residence_' || var_city_name || ' res1
            SET residence_count = res2.residence_count
            FROM (
                SELECT 
                    block_id, 
                    COUNT(*) as residence_count,
                FROM dwh.residence_' || var_city_name || '
                WHERE 1=1
                    AND city_id = ' || var_city_id || '
                GROUP BY block_id
            ) res2
            WHERE 1=1
                AND res1.city_id = ' || var_city_id || '
                AND res1.block_id = res2.block_id;';
    END LOOP;
END;
$$;

DO
$$
DECLARE
    var_city_id int;
    var_city_name varchar;
BEGIN
    FOR var_city_id, var_city_name IN 
        SELECT city_id, city_name
        FROM dwh.city
    LOOP 
        EXECUTE
            'UPDATE dwh.residence_' || var_city_name || ' res1
            SET residence_count = res2.residence_count
            FROM (
                SELECT 
                    block_id, 
                    COUNT(*) as residence_count,
                FROM dwh.residence_' || var_city_name || '
                WHERE 1=1
                    AND city_id = ' || var_city_id || '
                GROUP BY block_id
            ) res2
            WHERE 1=1
                AND res1.city_id = ' || var_city_id || '
                AND res1.block_id = res2.block_id;';
    END LOOP;
END;
$$;