How thread-safe is the SQL operation DELETE FROM my_table WHERE ctid IN (SELECT ctid FROM my_table WHERE some_condition)?

Question

Motivation

I am using a database table as a persistent queue. The purpose is to implement a pop operation on a queue: take any record fulfilling a condition from the queue, remove it from the queue, and process it. The whole procedure is run within a transaction, so if anything fails, the database performs a rollback so the record is not lost.

My Implementation

Following the answers to How do I delete a fixed number of rows with sorting in PostgreSQL?, I am using the following SQL code (in Postgres).

DELETE FROM my_table 
WHERE ctid IN (
    SELECT ctid FROM my_table 
    WHERE some_condition
    LIMIT 1
)
RETURNING *

The same code can be run in parallel in multiple threads, or even in multiple Kubernetes pods, on the same database.

Please notice that I cannot use just simply DELETE FROM my_table WHERE some_condition, as Postgres unfortunately does not support the LIMIT clause for DELETE and UPDATE.

Problem Statement

I am wondering, if the construct DELETE FROM my_table WHERE ctid IN (SELECT ctid FROM my_table WHERE some_condition) is "atomic enough" in Postgres SQL, i.e. if it is thread-safe, or if there can be situations that two threads start fighting for the same record.

I am considering the following fix, but I am not sure if it is necessary, or maybe if there is something even better to reach the same goal:

DELETE FROM my_table 
WHERE ctid IN (
    SELECT ctid FROM my_table 
    WHERE some_condition
    LIMIT 1
    FOR UPDATE
    SKIP LOCKED
)
RETURNING *

Answer 1

I found the following answers:

• Atomic UPDATE .. SELECT in Postgres
• https://dba.stackexchange.com/q/69471/55104
• https://dba.stackexchange.com/a/214443/55104

The result is that in the concurrent setup the FOR UPDATE SKIP LOCKED clause is necessary to avoid clashes between the threads.