Does INSERT INTO with Subqueries run as a single transaction (thread safe)?

Question

I have an insert query with subqueries which i want to run on a Postgres database. But i'm not sure if the query with its subqueries run in a single transaction.

We have two entities 'configs' and 'config_versions'. A 'config' can have multiple 'config_versions'. Every 'config_version' has a 'rolling_version_id' which is not the auto-incremented id (primary key) of the table. The 'rolling_version_id' increases per 'config_id' and starts with 1, that means there is a unique constraint on config_id + rolling_version_id. This is how i solved it:

INSERT INTO config_versions(rolling_version_id, config_id, note)
VALUES (
    CASE WHEN EXISTS(SELECT * FROM config_versions WHERE config_id=42) 
    THEN (SELECT MAX(rolling_version_id)+1 FROM config_versions WHERE config_id=42 GROUP BY config_id) ELSE 1 END, 
    42, 
    'SomeNote');

Question: Is this query thread safe / Does it run in a single transaction?

Answer 1

not sure what you mean by being safe, also sql transactions are ATOMIC, anyways , I'm not sure your query runs , but here how you can simplify it:

INSERT INTO config_versions(rolling_version_id, config_id, note)
SELECT coalesce(MAX(rolling_version_id)+1, 1),42,'SomeNote' 
FROM config_versions 
WHERE config_id=42

db<>fiddle here

Answer 2

Your query is inefficient, but safe (but this has nothing to do with multi-threading).

The complete SQL statement, including its subqueries, uses the same database snapshot, that is, they see the same state of the database.