Re: delete statement returning too many results

On Mon, Nov 28, 2022 at 12:11:53PM -0500, Tom Lane wrote:

> So basically it's unsafe to run the sub-select more than once,
> but the query as written leaves it up to the planner whether
> to do that.  I'd suggest rephrasing as
> 
> WITH target_rows AS MATERIALIZED (
>      SELECT id
>      FROM queue
>      ORDER BY id
>      LIMIT 1
>      FOR UPDATE
>      SKIP LOCKED
> )
> DELETE FROM queue
>   WHERE id IN (SELECT * FROM target_rows)
> RETURNING *;

Thanks for the explanation and suggested fix, Tom.

I'm not the original poster, but I do use similar constructions for simple
postgres queues. I've been trying for a while, but I don't understand where the
extra rows come from, or what's "silent" about SKIP LOCKED.

Because we get different results depending on the plan postgres picks, I can
see two options: either the query is broken, or postgres is broken. Assuming it's
the former, would there be a way to make it clearer that the "obvious" (to me)
way to use SKIP LOCKED is wrong?

Thanks!
Harmen