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?

Single statement runs as single transactuon unless there's a distributed transaction or explicit call to new transaction (for example, in some nested function of procedure). Because it should be ACID-compliant. — astentx, Apr 21 '21 at 14:56
Also, SQL is declarative where you specify the result, not the way to calculate it. It knows nothing about threads because they are too low-level. You can check this [doc article](https://www.postgresql.org/docs/9.5/transaction-iso.html) about transaction isolation levels and their description. — astentx, Apr 21 '21 at 15:09

eshirvana · Answer 1 · 2021-04-21T19:16:29.187

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

edited Apr 21 '21 at 19:16

answered Apr 21 '21 at 14:57

eshirvana

23,227
3
22
38

If I add 'GROUP BY config_id' at the end, the query runs. But unfortunately it does not insert anything in case no config_version with config_id=42 exist in the config_versions table. If there is already a config_version with a config_id=42 in the table, the insert works and the rolling_version_id is incremented for the new record. – thebigone12 Apr 21 '21 at 19:14
it works for me , check the fiddle out : *db<>fiddle [here](https://dbfiddle.uk/?rdbms=postgres_13&fiddle=4bf45b6e0bb14ba502af6739bfd324af)* – eshirvana Apr 21 '21 at 19:20

score 1 · Answer 2 · answered Apr 21 '21 at 15:32

1

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.

answered Apr 21 '21 at 15:32

Laurenz Albe

209,280
17
206
263

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

2 Answers2