0

I'm following a simple youtube course on how to build a FastAPI app with a Postgres db. The Users table has the following schema (using SQLAlchemy):

class User(Base):
    """
    Class responsible for the `users` table in the PostgreSQL DB
    """

    __tablename__ = "users"
    id = Column(Integer, primary_key=True, index=True, nullable=False)
    email = Column(String, unique=True, nullable=False)
    password = Column(String, nullable=False)
    created_at = Column(TIMESTAMP(timezone=True), nullable=False, server_default=text("now()"))

I've noticed that when I try to create a user (a row in a table) with an already used mail, I get an error, which is expected. Then, if I change the mail to a different one, and commit again the change, all is fine... However, when I check in the table for all the elements, I see that the id column has skipped some values, corresponding to those tries for which I got sqlalchemy.exc.IntegrityError.

Given this situation, I wonder whether it's better to first check for an already existing mail, and only then try to commit... otherwise, the id column will have jumps in its values.

Improve this question

1 Answer 1

Reset to default
0

Never mind gaps in serial or IDENTITY columns. Those are to be expected. Numbers drawn from a sequence are never reused. ROLLBACK does not reset sequences.

Only if you would burn huge amounts of serial numbers so that you might eventually exhaust the number space, it could be prudent to bother. Then, yes, you can largely avoid burning serial numbers by checking first. There will still be the occasional unique violation under concurrent load due to race conditions,

Improve this answer
3
  • «Never mind gaps in serial not IDENTITY columns.» Since id is the primary key, I deduce from you, that I should worry about gaps. Email is not the primary key, even though I'm making it to be unique. Maybe this is poor design from my part...
    – An old man in the sea.
    May 30 at 8:53
  • You should not worry about gaps. But you should use bigint instead of integer.
    – Laurenz Albe
    May 31 at 6:51
  • @Anoldmaninthesea: A unique email besides a surrogate int PK can still make sense. That int is much simpler and cheaper as FK reference and the possibly many indexes involving it. You should never exhaust the key space of integer (= int4) with something like "users". Only if your use case might burn lots of numbers, consider taking measures or using bigint instead.
    – Erwin Brandstetter
    May 31 at 12:15

Your Answer

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

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