PostgreSQL doesn't take into account a DELETE in WITH before the request has ended

Question

I have two tables to handle attachments:

• "files" which contains an id and other info
• "attachments" which contains the id_file (the id in the table files is the foreign key) and other info of the attachments.

Two tables to be able to share a file without duplicating it on the server (for your information).

When I delete an attachment, I want to delete the file only if there is no attachement left referring to its id.

I have this request, to delete all the attachments linked to a particular item (the 26th item of the table TABLE_NAME:

WITH deleted_files AS (
  DELETE FROM public.attachments a
  WHERE a.id_in_table = 26
    AND a.table_name = 'TABLE_NAME'
  RETURNING id_file
)
DELETE FROM public.files f
WHERE f.id IN (SELECT id FROM deleted_files)
  AND NOT EXISTS (
    SELECT 1
    FROM public.attachments a
    WHERE a.id_file = f.id
);

The problem is:

• the attachments are deleted correctly, but not the files
• if I run the same request again nothing is deleted (this is normal)
• if I run the second DELETE with the ids returned by the first delete, they are correctly deleted.

Is there some mechanism that keeps a shallow copy of the table with the deleted items, thus making the deletion of items impossible?

Answer 1

As the documentation says,

The sub-statements in WITH are executed concurrently with each other and with the main query. Therefore, when using data-modifying statements in WITH, the order in which the specified updates actually happen is unpredictable. All the statements are executed with the same snapshot (see Chapter 13), so they cannot “see” one another's effects on the target tables. This alleviates the effects of the unpredictability of the actual order of row updates, and means that RETURNING data is the only way to communicate changes between different WITH sub-statements and the main query.

My advice is to run two separate DELETE statements. If you need them to share the same snapshot, run them in a REPEATABLE READ transaction. If the reason for using a single statement was a constraint, you can use a deferred constraint instead. Use a temporary table for the rows deleted by the first statement.