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
