postgresql/no-locking-subquery-with-limit

Why this matters

PostgreSQL only guarantees that a sub-`SELECT` is evaluated once when it lives in a `WITH` clause. An `IN (...)` sub-`SELECT` is planned as a join, so it is re-executed for every candidate row of the enclosing statement — and when it carries a row-locking clause, each re-execution silently skips the rows the statement has already modified. The window slides down the table and `UPDATE ... WHERE id IN (SELECT ... LIMIT 1 FOR UPDATE)` ends up modifying **every** candidate row, not one (PostgreSQL BUG #15715; Tom Lane's answer is that this is expected, not a bug). `SKIP LOCKED` is not the culprit: plain `FOR UPDATE` and `FOR SHARE` slide the same way. Move the sub-`SELECT` into a CTE, which is single-evaluated. A sub-`SELECT` without a locking clause is unaffected — re-running it returns the same rows — and so is one in `FROM` / `USING`, which the planner materialises as its own scan node.

Examples

UPDATE delayed_jobs SET locked_at = now()
WHERE id IN (
  SELECT id FROM delayed_jobs ORDER BY priority LIMIT 1 FOR UPDATE
);

DELETE FROM ledger_entries
WHERE id IN (
  SELECT id FROM ledger_entries LIMIT 100 FOR NO KEY UPDATE SKIP LOCKED
);

WITH c AS MATERIALIZED (
  SELECT id FROM delayed_jobs ORDER BY priority LIMIT 1 FOR UPDATE SKIP LOCKED
)
UPDATE delayed_jobs SET locked_at = now() WHERE id IN (SELECT id FROM c);

UPDATE jobs SET flag = TRUE
WHERE id IN (SELECT id FROM jobs LIMIT 1); -- no locking clause

Configure it

// eslint.config.js
import postgresql from "eslint-plugin-postgresql";

export default [
  {
    files: ["**/*.sql"],
    languageOptions: {
      parser: postgresql.configs.recommended.languageOptions.parser,
    },
    plugins: { postgresql },
    rules: {
      "postgresql/no-locking-subquery-with-limit": "error",
    },
  },
];

Options

This rule has no options.