Search code examples
postgresqlsessionrandomgenerated-columns

Is this a stupid way to handle session storage with PostgreSQL? Should I use GENERATED values?

I'm making a login system for a small application - that is, there will not be many users. I figured I'd store sessions as 4-byte integers, as that'd be far more than enough.

My basic idea is this:

session_id INTEGER NOT NULL DEFAULT (random() * 4294967295 - 2147483648)

Ie., the database creates a more or less random number, which is used as primary key, and identifier for the session.

Is there an obvious pitfall with this sort of approach? What happens if there's a collision? I'd imagine it tries again, but does it? And there should be no risk of the table filling up, but what'd happen if it were about to run out of unique numbers?

I also looked at definitions like GENERATED ALWAYS AS IDENTITY or GENERATED ALWAYS AS(expression) STORED, but I don't really want a sequence, and those seem to require immutable functions, and random() isn't apparently immutable. Is there a way to implement them with this sort of approach?

I did some preliminary testing and my basic idea seems to work. But I'm unsure if it's good on the long run.

Solution
    1. session_id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY - this strictly guarantees and enforces uniqueness. If someone attempts to insert the same session_id for the second time or update...set another one to introduce a duplicate, it'll get rejected with an error.
    2. Random() with two constants only make uniqueness likely. Given enough time/attempts, it's therefore guaranteed to break.
    3. Without adding unique or primary key, you're not enforcing it at all - nothing's watching out for collisions, nothing reports them, they are free to occur.
    4. You can always run out of numbers, but with bigint and uuid there's a lot to run out of. Unless that's a seriously large project, other things will typically break sooner than you manage to exhaust them.
    5. If you want non-sequential identity, use uuid: 
      session_id uuid default gen_random_uuid() PRIMARY KEY
    6. If you're worried about identifiers being sequential, it might be because you're exposing them, which you shouldn't do even if they are not sequential.