0

I have table with user sessions:

CREATE TABLE user_sessions (
    id SERIAL PRIMARY KEY,
    user_id INT NOT NULL
);

Each user can have only 5 sessions. if a user has 5 sessions and starts a new session, then his oldest session is deleted. For example, for user_id == 1

BEIGN;

INSERT INTO user_sessions (user_id) VALUES (1);

DELETE FROM user_sessions us WHERE us.user_id=1 AND us.id NOT IN (
    SELECT us2.id FROM user_sessions us2 WHERE us2.user_id=1 ORDER BY ID DESC LIMIT 5
)

COMMIT;

I am using READ COMMITED isolation level. I'm not sure about concurrency requests. When one user starts two session at same time. Could it be that the DELETE statement deletes the newest row, because it doesn't exist when the SELECT is executed, but exists when the DELETE is executed? Then NOT IN will be true and newest row will be deleted.

2 Answers 2

1

I found a solution:

DELETE FROM user_sessions us WHERE us.id IN (
    SELECT us2.id FROM user_sessions us2 WHERE us2.user_id=1
    ORDER BY id DESC OFFSET 5
);

This ensures that the newest line is not removed.

But I'm still not sure about the first delete variant. Can the newest line be deleted there? Or postgres uses the same snapshot for DELETE and SELECT, and that's not possible?

1
  • 1
    The SELECT and the DELETE share a snapshot, so nothing can go wrong. However, you should never use NOT IN (SELECT ...). Commented Jul 20, 2023 at 7:26
-1

In this scenario, I'd say there is no guarantee, that you could avoid such a race condition. Depending on how tight the 5-session requirement is, you may need to consider adding a column to the table, I'd avoid the user of counters as they aren't precise and aren't idempotent, but you could have a session column to insert timestamps. Make it part of the PK so you can check for 5 DISTINCT values, then delete the oldest by timestamp value once 5 are found and perform the insert. This may not be perfect, but usually getting creative with the data model, you can find an acceptable solution.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.