User: xzw

xzw

May 1 (edited May 1)

Link

I don't agree with this. In my opinion the key takeaway should be that if your conditional expression may return different results on different runs or contains side-effects, do not gamble with...

Exemplary

I don't agree with this. In my opinion the key takeaway should be that if your conditional expression may return different results on different runs or contains side-effects, do not gamble with whether the optimizer runs it once or multiple times. Ironically it is materialized CTE that can help you preventing that, see https://www.postgresql.org/docs/17/queries-with.html#QUERIES-WITH-CTE-MATERIALIZATION

A useful property of WITH queries is that they are normally evaluated only once per execution of the parent query, even if they are referred to more than once by the parent query or sibling WITH queries. Thus, expensive calculations that are needed in multiple places can be placed within a WITH query to avoid redundant work. Another possible application is to prevent unwanted multiple evaluations of functions with side-effects.

However, if a WITH query is non-recursive and side-effect-free (that is, it is a SELECT containing no volatile functions) then it can be folded into the parent query, allowing joint optimization of the two query levels. By default, this happens if the parent query references the WITH query just once, but not if it references the WITH query more than once. You can override that decision by specifying MATERIALIZED to force separate calculation of the WITH query, or by specifying NOT MATERIALIZED to force it to be merged into the parent query.

So something like this should work:

with rows_to_be_deleted as materialized (
    select * from task_queue
    where queue_group_id = 15
    fetch first 1 rows only
    for update skip locked 
)
delete from task_queue 
where id in (select id from rows_to_be_deleted)
returning item_id;

The point is, put the subquery with side-effects in CTE, not the whole delete expression, and use the materialized keyword to prevent the planner from optimizing it out. I'm not sure the materialized keyword is really needed there, as PostgreSQL should know that locking is a side-effect, and would materialize the CTE either way, but it is a good indicator for fellow developers.

As a side note: limit is not part of the SQL standard, fetch is.

5 votes

xzw's recent activity

Comment on A PostgreSQL planner semi-join gotcha with CTE, LIMIT, and RETURNING in ~comp

Comment on Fiction with great “plot devices” in ~misc

Comment on Anyone interested in trying out Kagi? (trial giveaway: round #2) in ~tech

Comment on <deleted topic> in ~tech