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