SELECT... with LIMIT, but iterate forward getting other records?

Question

I am doing a SELECT * FROM... with LIMIT query inside of a LOOP in my pgplsql function/procedure - something like this, e.g.

$$
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;
$$;

As you can see, the CTE expression

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

...is returning a batch of 10,000 records from the total amount of records, which is 169,246. Then, immediate after, I check if any ID fields in this cte_table are in some_other_table, and if they are, then I delete them, e.g.

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

This works fine on the 1st iteration because the get diagnostics n_rec_count = row_count; will return a n_rec_count == 10,000 because 10,000 records were just deleted.

However, the problem is on the 2nd iteration, the CTE that has the SELECT... LIMIT 10000 will just return the same 10000 records. All these records were already deleted, so n_rec_count == 0 and the LOOP exits. This is not what I want at all, because I have 169,246 - 10,000 = 159,246 records left that I need to delete.

How can iterate forward or "grab" the next set of 10000 records using LIMIT? Or do I need a different LOOP or to use a CURSOR? All the examples I see with a LOOP or CURSOR involve iterating thru individual (1 record) at a time and that is NOT WHAT I WANTED. (or is this ok?)

Answer 1

Without concurrent write operations, a simple solution would be to add an increasing 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$;

This works with 10k candidates for deletion in each iteration, which is potentially different from 10k actually deleted rows.

The function exits after the first empty DELETE (like your original). I'd rather exit when the SELECT in the CTE comes up empty. See below.

LIMIT / OFFSET is simple, but not the most efficient technique. Should be good enough for "just" 160k rows. See:

• Optimize query with OFFSET on large table

What would make more sense

Partitioning a DELETE in a function is of limited use, though. A function always runs inside a single transaction and accumulates locks until committed. You already mentioned a vague "function/procedure".

In Postgres 11 or later, a PROCEDURE (or a simple DO command using transaction control like procedures) makes more sense because we can COMMIT after deleting each partition. (Especially relevant with concurrent read or write access to the database!) See:

• Using a commit inside plpgsql block works in one case not another

Also, if you need it faster, remember the greatest id from each iteration to replace LIMIT / OFFSET. Like:

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$;

Also, this only exits after there are no more candidates for deletion.

Since it adds some overhead, it may be slower for relatively few rows to process. But it scales a lot better - assuming an index on each unique id column.

If there can be concurrent write operations, things get more complicated, and you'd first have to define what can happen concurrently, and how you want to deal with it.

Related:

• Postgres UPDATE ... LIMIT 1
• UPDATE with LIMIT / OFFSET in PL/pgSQL function

Answer 2

The logic in the query is backwards.

You are trying to delete 10,000 rows from some_other_table, not from the CTE, so there is no need to LIMIT the CTE.

Ideally, you need this kind of query, but I don't think it is supported in 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;

Instead, you can use ctid to get the row location, and then rejoin

   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
    );