SELECT … 使用 LIMIIT，但向前迭代獲取其他記錄？

我正在執行SELECT * FROM …在我的 pgplsql 函式/過程中的LOOP內使用****LIMIT查詢- 類似這樣的事情，例如

$$
DECLARE

BEGIN
LOOP
  exit when get diagnostics n_rec_count = 0;

  WITH cte_table AS (
   SELECT *
   FROM my_table t1
   INNER JOIN another_table t2
   ON t1.id = t2.id
   LIMIT 10000
  );

   -- take the batch of 10,000 records from above 
   -- and check if any IDs exist in another table
   DELETE FROM some_other_table t WHERE t.id IN (SELECT * FROM 
   cte_table);

get diagnostics n_rec_count = row_count;

END LOOP;
END;
$$;

如您所見，CTE 表達式

WITH cte_table AS (
   SELECT *
   FROM my_table t1
   INNER JOIN another_table t2
   ON t1.id = t2.id
   LIMIT 10000
  );

…正在從記錄總數中返回一批 10,000 條記錄，即 169,246。然後，緊接著，我檢查其中是否有任何 ID 欄位在cte_table中some_other_table，如果是，則刪除它們，例如

DELETE FROM some_other_table t 
WHERE t.id IN (
  SELECT * 
  FROM cte_table
  );

***這在第一次迭代時工作正常，***因為剛剛刪除了 10,000 條記錄，get diagnostics n_rec_count = row_count;將返回 an_rec_count == 10,000

但是，問題出在第 2 次迭代中，具有SELECT… LIMIT 10000的CTE只會返回相同的 10000 條記錄。所有這些記錄都已被刪除，因此LOOP退出。這根本不是我想要的，因為我還有 169,246 - 10,000 = 159,246 條記錄需要刪除。n_rec_count == 0

如何使用LIMIT向前迭代或“抓取”下一組 10000 條記錄？還是我需要不同的LOOP或使用CURSOR？我看到的所有帶有LOOP或CURSOR的範例都涉及一次遍歷單個（1 條記錄），這不是我想要的。（或者這樣可以嗎？）

查詢中的邏輯是向後的。

您正在嘗試從 中刪除 10,000 行some_other_table，而不是從 CTE 中，因此不需要LIMITCTE。

理想情況下，您需要這種查詢，但我認為 PostgreSQL 不支持它。

  WITH cte_table AS (
   SELECT t1.id
   FROM my_table t1
   INNER JOIN another_table t2
   ON t1.id = t2.id
  )

   -- take the batch of 10,000 records from above 
   -- and check if any IDs exist in another table
   DELETE FROM some_other_table t
   WHERE t.id IN
       (SELECT id FROM cte_table)
   LIMIT 10000;

相反，您可以使用ctid獲取行位置，然後重新加入

  WITH cte_table AS (
   SELECT t1.id
   FROM my_table t1
   INNER JOIN another_table t2
   ON t1.id = t2.id
  )

   -- take the batch of 10,000 records from above 
   -- and check if any IDs exist in another table
   DELETE FROM some_other_table t
   WHERE t.id IN (
       SELECT t2.id FROM some_other_table t2
       WHERE t2.id IN
           (SELECT id FROM cte_table)
       LIMIT 10000
   );

如果沒有並發寫操作，一個簡單的解決方案是添加一個遞增的OFFSET：

CREATE OR REPLACE FUNCTION foo()
 RETURNS void 
 LANGUAGE plpgsql AS
$func$
DECLARE
  _offset integer := 0;
BEGIN
LOOP
  WITH cte AS (
     SELECT id  -- don't use *, we only need id
     FROM   my_table      t1
     JOIN   another_table t2 USING (id)
     ORDER  BY id
     LIMIT  10000
     OFFSET _offset
     )
  DELETE FROM some_other_table t
  USING  cte c
  WHERE  t.id = c.id;

  EXIT WHEN NOT FOUND;
  _offset := _offset + 10000;
END LOOP;
END
$func$;

這適用於每次迭代中刪除的 10k個候選對象，這可能與 10k 個實際刪除的行不同。

該函式在第一個空之後退出DELETE（就像你原來的一樣）。我寧願SELECT在 CTE 中的空白出現時退出。見下文。

LIMIT/OFFSET很簡單，但不是最有效的技術。對於“僅”160k 行應該足夠了。看：

• 在大表上使用 OFFSET 優化查詢

什麼更有意義

但是，在函式中對 a 進行分區的DELETE用途有限。函式總是在單個事務中執行並累積鎖直到送出。您已經提到了一個模糊的“功能/程序”。

在 Postgres 11 或更高版本中，一個PROCEDURE（或使用類似過程的事務控制的簡單DO命令）更有意義，因為我們可以COMMIT在刪除每個分區之後。（特別是與對數據庫的並發讀取或寫入訪問相關！）請參閱：

• 在 plpgsql 塊中使用送出在一種情況下有效，而不是另一種情況

此外，如果您需要更快，請記住id每次迭代中最大的替換LIMIT/ OFFSET。像：

DO
$do$
DECLARE
  _max_id integer := -1;  -- assuming positive, unique IDs!
BEGIN
LOOP
  WITH cte AS (
     SELECT id  -- don't use *, we only need id
     FROM   my_table      t1
     JOIN   another_table t2 USING (id)
     WHERE  t1.id > _max_id
     ORDER  BY id
     LIMIT  10000
     )
  , del AS (
     DELETE FROM some_other_table t
     USING  cte c
     WHERE  t.id = c.id
     )
  SELECT max(id) FROM cte
  INTO _max_id;

  EXIT WHEN _max_id IS NULL;
  COMMIT;  -- make delete visible and permanent; release locks
END LOOP;
END
$do$;

此外，只有在沒有更多候選刪除後才會退出。

由於它增加了一些成本，因此處理相對較少的行可能會更慢。但它的擴展性要好得多——假設每個唯一列上都有一個索引。id

如果可以有並發的寫操作，事情會變得更加複雜，你首先必須定義可以同時發生的事情，以及你想如何處理它。

有關的：

• Postgres 更新…限制 1
• 在 PL/pgSQL 函式中使用 LIMIT / OFFSET 進行更新

引用自：https://dba.stackexchange.com/questions/297323