0

I am using Flask-SQLAlchemy with Postgres. I noticed that when I delete a record, the next record will reuse that one's id, which is not ideal for my purposes. Another SO question that this is the default behavior. In particular, his SO question discussed the sql behind the scenes. However, when I tested the solution in this problem, it did not work. In fact, postgres was not using SERIAL for the primary key. I was having to edit it in pgadmin myself. Solutions in other programs mention using a Sequence but it is not shown where the sequence is coming from.

So I would hope this code:


class Test1(db.Model):
    __tablename__ = "test1"

    # id = ... this is what needs to change
    id = db.Column(db.Integer, primary_key=True)

would not reuse say 3 if record 3 was deleted and another was created like so:

i1 = Invoice()
db.session.add(i1)
i2 = Invoice()
db.session.add(i2)
i3 = Invoice()
db.session.add(i3)
db.session.commit()

invs = Invoice.query.all()
for i in invs:
    print(i.id)  # Should print 1,2,3

Invoice.query.filter(id=3).delete()  # no 3 now
db.session.commit()

i4 = Invoice()
db.session.add(i4)
db.session.commit()

invs = Invoice.query.all()
for i in invs:
    print(i.id)  # Should print 1,2,4

Other, solutions said to use autoincrement=False. Okay, but then how do I determine what the number to set the id to is? Is there a way to save a variable in the class without it being a column:

class Test2(db.Model)
    __tablename__ = 'test2'

    id = ...
    last_id = 3

    # code to set last_id when a record is deleted

Edit: So I could (although I do not think I should) use Python to do this. I think this more clearly tries to illustrate what I am trying to do.

class Test1(db.Model):
    __tablename__ = "test1"

    # id = ... this is what needs to change
    id = db.Column(db.Integer, primary_key=True) 
    last_used_id = 30

    def __init__(self):
        self.id = last_used_id + 1
        self.last_used_id +=1

# Not sure if this somehow messes with SQLAlchemy / the db making the id first.

This will make any new record not touch an id that was already used. However, with this I approach, I do encounter the class variable issue behavior of Python. See this SO question

Future self checking: See UUID per @net comment here:

David Frick
  • 641
  • 1
  • 9
  • 25

1 Answers1

0

You should use autoincrement=True. This will automatically increment the id everytime you add a new row.

class Test1(db.Model):
    __tablename__ = "test1"

    id = db.Column(db.Integer, primary_key=True, autoincrement=True, unique=True, nullable=False)
    ....

By default Postgres will not reuse ids due to performance issues. Attempting to avoid gaps or to re-use deleted IDs creates horrible performance problems. See the PostgreSQL wiki FAQ.

You don't need to keep track of the id. When you call db.session.add(i4) and db.session.commit() it will automatically insert with the incremented id.

net
  • 116
  • 4
  • See my code block above to test this. This did not work when deleting id=4 then making a new record. It would have 4 – David Frick Jul 03 '20 at 22:21
  • I see. If performance is not an issue for you. Consider using uuid version 4 as primary key. That way it will always be unique everytime. – net Jul 03 '20 at 22:29
  • Okay, I will look into that. That might be the solution. I think I should update my question to this. Let's say I have an invoice with id 10. We email it to a customer. If we then remove it from the system, it seems to renumber 11 to 10, probably also making 12 to the end point one incorrectly. So I guess this is more of a integrity question than anything? Does this change suggests any other solutions as well to look into. By the way, I see you are a new user. Welcome to SO friend! – David Frick Jul 03 '20 at 22:50
  • I didn't quit understand the question. If you delete an invoice the system creates two more? Is the system automatically creating new invoices to replace the deleted one? if I understand your question correctly, maybe you can have another boolen column such as `valid`. Instead of deleting an invoice you can toggle the `valid` column. Then your system can check the `valid` column before send out invoice reminders. Thanks for the welcome! – net Jul 03 '20 at 22:54
  • See my update. I explain it with python. Sorry, I am just making myself more confused at this point :( – David Frick Jul 03 '20 at 23:02
  • Does my update make it clearer? I do think I need UUID so I will check that out – David Frick Jul 03 '20 at 23:16
  • The update actually makes it more complicated. Just let the database take care of id. Don't keep track of it. The `id` is something you shouldn't have worry about. Just let the connection take care of it. Try setting the table id to `id = db.Column(db.Integer, primary_key=True, autoincrement=True, unique=True, nullable=False)`. – net Jul 03 '20 at 23:22
  • I agree but the issue that I am having with that is that if I delete a stale data row for legitamate purposes. It then seems to round the next id down when I create a new one and I do not want it to. Deleted record with id 12 would I hope not make the next created record have an id of 12. The uuid method does work, but I will explore that you are saying about just having the db track the last used number – David Frick Jul 03 '20 at 23:44
  • That is not they way Postgres should be indexing. It should never reuse old numbers even if the previous one was deleted. It should always increment. – net Jul 03 '20 at 23:51
  • So the edited answer is correct. I am pretty sure it is the `unique=True` I feel dumb for having forgotten this. :( Thanks so much net! – David Frick Jul 04 '20 at 00:18