The application should limit access to a certain resource to a maximum number of accesses every 24 hours. To keep track of those, we have the following table:

CREATE TABLE accesses (
    user_id         INTEGER NOT NULL,
    dt              TIMESTAMPZ
);

Each time the resource is accessed, we record it in the table. However, we also need to check if the access limit was reached. To that end, we need to execute this query:

SELECT count(*) 
FROM accesses 
WHERE dt >= timezone('utc', now()) - interval '24 hours' AND user_id = $1

and we need to check the result against the user limit.

Now, if two accesses are requested at the same time we may have problems:

• if we first read, compare, and the insert we may grant more accesses than the limit;
• if we first insert, then read and compare, we may disallow legitimate accesses.

The two ways that I know are to either use a SERIALIZABLE transaction or SELECT ... FOR UPDATE. I am not sure if the latter actually works in this case, because we will not update the rows that are read, rather we will insert new ones. So I think that it'll be a useless lock and the result will probably be still incorrect.

However, with a SERIALIZABLE transaction I have retry it in case it fails.

What should be done in this case, to ensure proper access limits?

The application should limit each user's access to a certain resource to a maximum number of accesses every 24 hours. To keep track of those, we have the following table:

CREATE TABLE accesses (
    user_id         INTEGER NOT NULL,
    dt              TIMESTAMPZ
);

Each time the resource is accessed, we record it in the table. However, we also need to check if the access limit was reached. To that end, we need to execute this query:

SELECT count(*) 
FROM accesses 
WHERE dt >= timezone('utc', now()) - interval '24 hours' AND user_id = $1

and we need to check the result against the user limit.

Now, if two accesses are requested at the same time we may have problems:

• if we first read, compare, and the insert we may grant more accesses than the limit;
• if we first insert, then read and compare, we may disallow legitimate accesses.

The two ways that I know are to either use a SERIALIZABLE transaction or SELECT ... FOR UPDATE. I am not sure if the latter actually works in this case, because we will not update the rows that are read, rather we will insert new ones. So I think that it'll be a useless lock and the result will probably be still incorrect.

However, with a SERIALIZABLE transaction I have retry it in case it fails.

What should be done in this case, to ensure proper access limits?