PostgreSQL: Is it possible to limit inserts per user based on time difference between timestamp colu-CodePudding

I have an issue when two almost concurrent requests ( - 10ms difference) by the same user (unintentionally duplicated by client side) successfully execute whole use case logic twice. I can't really solve this situation in code of my API, so I've been thinking about how to limit one user_id to be able to insert row into table order max. once every second for example.

I want to achieve this: If in table order exists row with user_id X and that row was created (inserted) less than 1 second ago, insert with user_id X would fail.

This could be effective way of avoiding unintentionally duplicated requests by client side. Because I can't imagine situation when user could send two complex requests less than 1 second between intentionally. I'm also interested in any other ideas, for example what's the proper way to deal with similar situations in API's.

CodePudding user response：

There is one problem with your idea. If the server becomes really slow for just a second, the orders will arrive more than one second apart in the database and will be inserted.

I'd recommend generating a unique ID, like a UUID, in the front-end, and sending that with the request. You could, for example, generate a new one every page load. Then, if the server sees that the received UUID already exists in the database, the order is skipped.

This avoids any potential timing issues, but also retains the possibility of someone re-ordering the exact same products.

CodePudding user response：

You can do it with an EXCLUDE constraint. You need to create your own immutable helper function, and use an extension.

create extension btree_gist;
create function addsec(timestamptz) returns tstzrange immutable language sql as $$ 
    select tstzrange($1,$1 interval '1 second')
$$;
create table orders (
    userid int, 
    t timestamptz, 
    exclude using gist (userid with =, addsec(t) with &&)
);

But you should probably change the front end anyway to include a validation token, as currently it may be subject to CSRF attacks.

Note that EXCLUDE constraints may be much less efficient than UNIQUE constraints. Also, I'm not 100% sure that addsec really is immutable. There might be weird things with leap seconds or something that messes it up.