Repeatable reads in case protection from duplicated inserts in POSTGRES

Question

I would need to perform this kind of logic in a serializable way:

account table has columns: 'accountName', 'location', 'worth', ...
Assume no unique key exists.

Some accounts are logically (according to an application) in a same group.

Example:

accountNameOne - group1 accountNameTwo - group1

accountNewOne - group2 accountNewTwo - group2

There are many clients application trying to insert one account member of their group concurrenlty. I want to have the guarantee to insert only one account per group.

BEGIN;

select *
from account 
where accountName in (list of account names inside a group)

-- if result is not empty exit there's already an account of the group
-- otherwise we want to insert it

INSERT INTO account (accountName, column2, column3, ...)
VALUES ('accountInAGroup', value2, value3, ...);

COMMIT;

As far as I know Repeatable reads isolation level cannot help in this, I am basically left with using SERIALIAZABLE I guess.

Can you help me understand if my reasoning here is correct ? Is Repeatable reads really not enough for this ?

Answer 1

Yes, the SERIALIZABLE isolation level is the only way for the database to guarantee that no anomalies can occur.

Serializing the execution of the function using advisory locks would only help if that function is the only place where data are inserted into this table.