PL/pgSQL循环产生意外的“太多范围表条目”错误。

huangapple
go评论79阅读模式
英文:

PL/pgSQL loop produces unexpected "too many range table entries" error

问题

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:

这个不起作用：

  1. DO
  2. $$
  3. DECLARE
  4.     ele record;
  5. BEGIN
  6.     FOR ele IN SELECT city_id 
  7.            FROM dwh.city
  8.     LOOP 
  9.         UPDATE dwh.residence res1
  10.         SET residence_count = res2.residence_count
  11.         FROM (
  12.             SELECT 
  13.                 block_id, 
  14.                 COUNT(*) as residence_count,
  15.             FROM dwh.residence  
  16.             WHERE 1=1
  17.                 AND city_id = ele.city_id
  18.             GROUP BY block_id
  19.         ) res2
  20.         WHERE 1=1
  21.             AND res1.city_id = ele.city_id
  22.             AND res1.block_id = res2.block_id;
  23.     END LOOP;
  24. END;
  25. $$;

But, this does work:

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

  1. UPDATE dwh.residence res1
  2. SET residence_count = res2.residence_count
  3. FROM (
  4.     SELECT 
  5.         block_id, 
  6.         COUNT(*) as residence_count,
  7.    FROM dwh.residence  
  8.    WHERE 1=1
  9.        AND city_id = 1
  10.   GROUP BY block_id
  11. ) res2
  12. WHERE 1=1
  13.     AND res1.city_id = 1
  14.     AND res1.block_id = res2.block_id;
英文:

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 ().

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?

This doesn't work:

  1. DO
  2. $$
  3. DECLARE
  4.     ele record;
  5. BEGIN
  6.     FOR ele IN SELECT city_id 
  7.            FROM dwh.city
  8.     LOOP 
  9.         UPDATE dwh.residence res1
  10.         SET residence_count = res2.residence_count
  11.         FROM (
  12.             SELECT 
  13.                 block_id, 
  14.                 COUNT(*) as residence_count,
  15.             FROM dwh.residence  
  16.             WHERE 1=1
  17.                 AND city_id = ele.city_id
  18.             GROUP BY block_id
  19.         ) res2
  20.         WHERE 1=1
  21.             AND res1.city_id = ele.city_id
  22.             AND res1.block_id = res2.block_id;
  23.     END LOOP;
  24. END;
  25. $$;

But, this does work:

  1. UPDATE dwh.residence res1
  2. SET residence_count = res2.residence_count
  3. FROM (
  4.     SELECT 
  5.         block_id, 
  6.         COUNT(*) as residence_count,
  7.    FROM dwh.residence  
  8.    WHERE 1=1
  9.        AND city_id = 1
  10.   GROUP BY block_id
  11. ) res2
  12. WHERE 1=1
  13.     AND res1.city_id = 1
  14.     AND res1.block_id = res2.block_id;

答案1

得分: 1

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

  1. DO
  2. $$
  3. DECLARE
  4.     var_city_id int;
  5.     var_city_name varchar;
  6. BEGIN
  7.     FOR var_city_id, var_city_name IN 
  8.         SELECT city_id, city_name
  9.         FROM dwh.city
  10.     LOOP 
  11.         EXECUTE
  12.             'UPDATE dwh.residence_' || var_city_name || ' res1
  13.             SET residence_count = res2.residence_count
  14.             FROM (
  15.                 SELECT 
  16.                     block_id, 
  17.                     COUNT(*) as residence_count,
  18.                 FROM dwh.residence_' || var_city_name || '
  19.                 WHERE 1=1
  20.                     AND city_id = ' || var_city_id || '
  21.                 GROUP BY block_id
  22.             ) res2
  23.             WHERE 1=1
  24.                 AND res1.city_id = ' || var_city_id || '
  25.                 AND res1.block_id = res2.block_id;';
  26.     END LOOP;
  27. END;
  28. $$;
英文:

Per the comment posted by @LaurenzAlbe, using dynamic SQL to refer to table partitions individually solves the error (I broke out the variables because I thought it looks clearer this way):

  1. DO
  2. $$
  3. DECLARE
  4.     var_city_id int;
  5.     var_city_name varchar;
  6. BEGIN
  7.     FOR var_city_id, var_city_name IN 
  8.         SELECT city_id, city_name
  9.         FROM dwh.city
  10.     LOOP 
  11.         EXECUTE
  12.             'UPDATE dwh.residence_' || var_city_name || ' res1
  13.             SET residence_count = res2.residence_count
  14.             FROM (
  15.                 SELECT 
  16.                     block_id, 
  17.                     COUNT(*) as residence_count,
  18.                 FROM dwh.residence_' || var_city_name || '
  19.                 WHERE 1=1
  20.                     AND city_id = ' || var_city_id || '
  21.                 GROUP BY block_id
  22.             ) res2
  23.             WHERE 1=1
  24.                 AND res1.city_id = ' || var_city_id || '
  25.                 AND res1.block_id = res2.block_id;';
  26.     END LOOP;
  27. END;
  28. $$;

huangapple
  • 本文由 发表于 2023年5月24日 20:14:05
  • 转载请务必保留本文链接：https://go.coder-hub.com/76323424.html
  • plpgsql
  • postgresql
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定