Inserting row based on condition

Question

I have a postgres table that is used to hold users files. Two users can have a file with the same name, but a user isn't allowed to have two files with the same name. Currently, if a user tries to upload a file with a name they already used, the database will spit out the error below as it should.

IntegrityError: duplicate key value violates unique constraint "file_user_id_title_key"

What I would like to do is first query the database with the file name and user ID to see if the file name is being used by the user. If the name is already being used, return an error, otherwise write the row.

cur.execute('INSERT INTO files(user_id, title, share)'
            'VALUES (%s, %s, %s) RETURNING id;',
            (user.id, file.title, file.share))

But that is what the unique constraint does. You have put the error in your message. You just need to trap it and process it as you like. — Gordon Linoff, Jul 01 '16 at 01:18
I was under the impression that although the unique constraint is there, you still want your backend to catch the error before the database does. — Brosef, Jul 01 '16 at 01:35
Reset your primary key sequence - your sequence is broken http://stackoverflow.com/questions/244243/how-to-reset-postgres-primary-key-sequence-when-it-falls-out-of-sync — dmitryro, Jul 01 '16 at 02:09

score 0 · Answer 1 · answered Jul 01 '16 at 05:13

The problem is that you cannot really do that without opening a race condition:
There is nothing to keep somebody else from inserting a conflicting row between the time you query the table and when you try to insert the row, so the error could still happen (unless you go to extreme measures like locking the table before you do that, which would affect concurrency badly).

Moreover, your proposed technique incurs extra load on the database by adding a superfluous second query.

You are right that you should not confront the user with a database error message, but the correct way to handle this is as follows:

You INSERT the new row like you showed.
You check if you get an error.
If the SQLSTATE of the error is the SQL standard value 23505 (unique_violation), you know that there is already such a file for the user and show the appropriate error to the user.

So you can consider the INSERT statement as an atomic operation check if there is already a matching entry, and if not, add the row.

Inserting row based on condition

1 Answers1