PostgreSQL still lacks a LIMIT clause for DML statements. If you need to modify only a handful of rows, you have to build the limitation yourself — and the obvious approaches carry some non-obvious hazards. The same techniques apply to DELETE ... LIMIT.

Consider an UNLOGGED table holding 100 categories of 100 entries each:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

CREATE UNLOGGED TABLE upd (

   id bigint GENERATED ALWAYS AS IDENTITY,

   category integer NOT NULL,

   flag boolean DEFAULT FALSE NOT NULL

);

INSERT INTO upd (category)

SELECT (i - 1) / 100

FROM generate_series(1, 100000) AS i;

/* set hint bits, gather optimizer statistics */

VACUUM (ANALYZE) upd;

/* created after INSERT for performance */

ALTER TABLE upd ADD PRIMARY KEY (id);

CREATE INDEX ON upd (category);

Suppose you want to set flag on one row in category 42:

1

2

3

4

UPDATE upd

SET flag = TRUE

WHERE category = 42

LIMIT 1;

That statement is not valid, so a workaround is required.

A subquery with LIMIT, and why it breaks

Since SELECT supports LIMIT, a subquery looks like a natural fit:

1

2

3

4

5

6

UPDATE upd AS u

SET flag = TRUE

WHERE u.category = 42

  AND u.id IN (SELECT id FROM upd AS u2

               WHERE u2.category = u.category

               LIMIT 1);

In a single-user scenario this behaves as intended. Concurrent modifications expose the flaw. Start by deleting a row in another session:

1

2

3

BEGIN;

DELETE FROM upd WHERE id = 4201;

Run the proposed update in a concurrent session and it will hang. Commit the DELETE, and the UPDATE ends up changing nothing:

1

UPDATE 0

The whole query reads from one snapshot, and multiversioning lets the subquery see the row that is being deleted. But the UPDATE must acquire a FOR NO KEY UPDATE row lock first, and that is where it blocks. Once the deleting transaction commits, the row is gone and the updater has no work left. The documentation puts it this way:

If the first updater commits, the second updater will ignore the row if the first updater deleted it, otherwise it will attempt to apply its operation to the updated version of the row.

Locking in the subquery — and a surprising plan

Taking a row lock inside the subquery prevents the row from disappearing between the read and the update. Because any category-42 row will do, SKIP LOCKED avoids waiting on rows other sessions already hold:

1

2

3

4

5

6

7

UPDATE upd AS u

SET flag = TRUE

WHERE u.category = 42

  AND u.id IN (SELECT id FROM upd AS u2

               WHERE u2.category = u.category

               FOR UPDATE SKIP LOCKED

               LIMIT 1);

Executing this produces an unhappy result:

1

UPDATE 99

All 99 remaining rows in category 42 were updated, despite LIMIT 1. The execution plan explains why:

1

2

3

4

5

6

7

8

9

10

11

12

                                QUERY PLAN                                

---------------------------------------------------------------------------

Update on upd u

   ->  Nested Loop Semi Join

         ->  Index Scan using upd_category_idx on upd u

               Index Cond: (category = 42)

         ->  Subquery Scan on "ANY_subquery"

               Filter: (u.id = "ANY_subquery".id)

               ->  Limit

                     ->  LockRows

                           ->  Index Scan using upd_category_idx on upd u2

                                 Index Cond: (category = u.category)

PostgreSQL ran the subquery as the inner side of a nested loop join, with the LockRows node inside it. The outer index scan visits all 99 category-42 rows and re-executes the subquery for each. Since every update appends a new row version, each subquery execution finds a different first row — the one the outer scan is currently on. Concurrent sessions locking and unlocking rows between those executions can cause similar misbehavior.

The underlying cause is a single one: the subquery is executed more than once. Here it became a correlated subquery by referencing u.category from the outer query, which led the planner to that join.

Forcing a single evaluation

Rewriting the first WHERE condition removes the correlation in this case:

1

WHERE u2.category =  42

A materialized CTE is the more reliable way to guarantee the subquery is evaluated exactly once:

1

2

3

4

5

6

7

8

9

10

11

WITH u2 AS MATERIALIZED (

   SELECT id FROM upd

   WHERE upd.category = 42

   FOR UPDATE SKIP LOCKED

   LIMIT 1

)

UPDATE upd AS u

SET flag = TRUE

FROM u2

WHERE u.category = 42

  AND u.id = u2.id;

The resulting plan:

1

2

3

4

5

6

7

8

9

10

11

12

13

                          QUERY PLAN                          

--------------------------------------------------------------

Update on upd u

   CTE u2

     ->  Limit

           ->  LockRows

                 ->  Index Scan using upd_category_idx on upd

                       Index Cond: (category = 42)

   ->  Nested Loop

         ->  CTE Scan on u2

         ->  Index Scan using upd_pkey on upd u

               Index Cond: (id = u2.id)

               Filter: (category = 42)

With the subquery materialized, the race conditions and plan-dependent oddities are gone.

The right lock level

FOR UPDATE is heavier than this operation needs. Use FOR NO KEY UPDATE instead: it does not block concurrent INSERT statements into a table whose foreign key references the updated table. The final form of the statement:

1

2

3

4

5

6

7

8

9

10

11

WITH u2 AS MATERIALIZED (

   SELECT id FROM upd

   WHERE upd.category = 42

   FOR NO KEY UPDATE SKIP LOCKED

   LIMIT 1

)

UPDATE upd AS u

SET flag = TRUE

FROM u2

WHERE u.category = 42

  AND u.id = u2.id;

For a limited DELETE, the same pattern applies, except there FOR UPDATE is the correct row lock.