Double inserting records in flask SqlAlchemy connected with PostgreSql?

Question

Very rarely, I meet a problem that the record that I inserted into Table Tbl_CUSTOMER was double with auto ID from Postgres.

I have no idea, but I suspected that it could be caused from postgres vacuum running time. To confirm that, I tried to run postgres vacuum at the same with inserting record, but could not found this problem happened, therefore, I could not duplicate the issue to find what was the root cause and fix the problem.

models.py

class Tbl_CUSTOMER():
    ID              =   db.Column(db.Numeric(25, 9), primary_key=True, autoincrement=True)
    PotentialCustomer   =   db.Column(db.String(12))
    FirstNameEn     =   db.Column(db.String(35))
    LastNameEn      =   db.Column(db.String(35))
    FirstNameKh     =   db.Column(db.String(35))
    LastNameKh      =   db.Column(db.String(35))
    Salutation      =   db.Column(db.String(4))
    Gender          =   db.Column(db.String(6))
    DateOfBirth     =   db.Column(db.String(10))
    CountryOfBirth  =   db.Column(db.String(2))
    Nationality     =   db.Column(db.String(2))
    ProvinceOfBirth =   db.Column(db.String(3))

views.py

dataInsert =Tbl_CUSTOMER(
                PotentialCustomer   =   request.form['PotentialCustomer'],
                FirstNameEn     =   request.form['FirstNameEn'],
                LastNameEn      =   request.form['LastNameEn'],
                FirstNameKh     =   request.form['FirstNameKh'],
                LastNameKh      =   request.form['LastNameKh'],
                Salutation      =   request.form['Salutation'],
                Gender          =   request.form['Gender'],
                DateOfBirth     =   request.form['DateOfBirth'],
                CountryOfBirth  =   request.form['CountryOfBirth'],
                Nationality     =   request.form['Nationality'],
                ProvinceOfBirth =   request.form['ProvinceOfBirth']
            )

db.session.add(dataInsert)
db.session.commit()

This problem does not happen frequently. So, what is the problem, and how can I fix this to prevent it happen in future? Thanks.

I am also not sure why you facing the issue. On the side note, you can remove autoincrement=True it is shipped along with primary_key if in the case table has only one primary_key. Also, I am curious if the type of ID should be string or Integer and if it is a string what it is expected to increment. — mad_
– mad_, Commented Sep 6, 2018 at 14:00
@mad_ thanks for contribution, it is quite strange for me and my client. On the other hand, I could not remove auto id as our app implement id pattern separately. Cannot manually enter ID. — Houy Narun
– Houy Narun, Commented Sep 6, 2018 at 14:04
I cannot understand if you have your own ID then what do you expect db should do with autoincrement behavior on this column? — mad_
– mad_, Commented Sep 6, 2018 at 14:16
@mad_, my app depend on postgres to manage auto ID for record that is to save. The ID pattern that I said is config as the sequence of date/time with two specific prefix letter, eg. 'CUyymmdd`. there are could be many record to be save per day. it is not possible to do it manually. Thanks — Houy Narun
– Houy Narun, Commented Sep 6, 2018 at 14:47
You are getting confused in autoincrement and your uuid generation which is generated from your app. autoincrement is something which db deals with and not your app. Not suggesting to create a manual ID it will still be generated from your app function. Hope it all make sense — mad_
– mad_, Commented Sep 6, 2018 at 14:55

Thiago Mata · Accepted Answer · 2018-09-13 05:53:23Z

1

If you create a unique key ( or replace your primary key ) with some hashing function value based on all the values of your row, that may help you to see when this problem is happening. Using this hashing column you will be able to decide what you should happen when your system get the same value ( same hash ). One option, for example, just ignores the new row, keeping the old one. Other, is to rewrite, etc.

The chance of getting the same hash value from different rows is so small that I would not even consider that. Look this thread https://crypto.stackexchange.com/questions/1170/best-way-to-reduce-chance-of-hash-collisions-multiple-hashes-or-larger-hash if you want to see more details about that.

answered Sep 13, 2018 at 5:53

Thiago Mata

2,9801 gold badge36 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Houy Narun Over a year ago

Thanks, is it concluded the root cause is because of primary key? I'm not using hashing function for my primary key.

Thiago Mata Over a year ago

Probably the root is some threading into another layer that is adding the same line twice. Very hard to figure out the cause of the problem just using the information that you send. I would guess that this issue is not related to the database itself. If you force the unique over the hash and keep the insert as it is, you will get some errors trace that may help you to figure out the cause. What I am proposing is more about dealing with the repeated insert than avoiding it.

Stephan Schielke Over a year ago

For me, it was a "watched" debug expression in my IDE. The expression got executed on each breakpoint...

Collectives™ on Stack Overflow

Double inserting records in flask SqlAlchemy connected with PostgreSql?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related