Re: Delete from locking ordering differences

On 9/23/22 01:31, Peter Hendriks wrote:
> We are wondering if anyone can explain the difference we are having in
> production with the following queries:
>
> DELETE FROM store
> WHERE id IN (
>   SELECT id FROM store
>   FOR UPDATE SKIP LOCKED
>   ORDER BY ID
>   LIMIT 1000
> )
> RETURNING id, payload
>
> This query is sometimes executed with high concurrency, and then can
> hang indefinitely, we assume because of a locking problem that
> postgresql is not detecting as a deadlock.
>
> This alternative query does not have the hanging problem:
>
> WITH store_ids AS (
>   SELECT id FROM store
>   FOR UPDATE SKIP LOCKED
>   ORDER BY ID
>   LIMIT 1000
> )
> DELETE FROM store s
> USING store_ids si
> WHERE s.id <http://s.id> = si.id <http://si.id>
> RETURNING s.id <http://s.id>, s.payload
>
> Can anyone explain why the first query is expected to fail (hang), and
> the second query does not have this problem? We would be interested in
> more understanding on this. Thanks!

The "optimizatin fence" nature of CTEs appears to be a win in this
case.  Why the "order by"?  I assume these are down within a transaction?