Reputation: 1898

Which is better - Throwing an Exception or Checking for errors beforehand

In my server, which is connected to postgresql, should I check if the username already exists in the table by doing "select * ..." and then getting the number of rows in the resultset and i the number of rows is equal to zero, then insert the username?
Or just insert the username in the table. If it already exists, then it will throw an error which can then be caught.
Note:The username is the primary key

Doing which of the above two is better?

Upvotes: 1

Answers (9)

Erwin Brandstetter

Reputation: 656714

In this case the answer is neither. Neither provoke an error, nor check beforehand. Well mostly, anyway.
It can be handled simpler - and safer and faster at the same time:

INSERT INTO users(username, col1)
SELECT 'max', 'val1'
WHERE  NOT EXISTS (SELECT * FROM users WHERE username = 'max')

This will insert the new user only if he does not exist already. PostgreSQL will set the command status to 0 rows affected or 1 row affected, depending on whether it was there already. Either way, it will be there after this statement.

If you want an answer back:

INSERT INTO users(username, col1)
SELECT 'max', 'val1'
WHERE  NOT EXISTS (SELECT * FROM users WHERE username = 'max')
RETURNING username;

This will return the username only if it did not already exist.
However, the operation is not atomic, so if you have a lot of concurrency, acquire a lock on the table like this:

BEGIN;
LOCK TABLE users IN SHARE MODE;

INSERT INTO users(username, col1)
SELECT 'max', 'val1'
WHERE  NOT EXISTS (SELECT * FROM users WHERE username = 'max')
RETURNING username;

COMMIT;

Note that this can still fail, even if very unlikely - for instance if another transaction locks the table and blocks you forever due to some error.
So, admittedly, you still need code to handle the error case. It just should never occur unless your database or applications has a problem.

Upvotes: 2

kandarp

Reputation: 5047

If you insert user name directly without any checking and suppose same user name exist in the database, at that time you will get an exception otherwise it will insert record successfully in the database. As per standard coding practice(in my point of view), you should check uniqueness first and if user name is not exist in the database then insert record in the database.

Upvotes: 0

user207421

Reputation: 310913

You will get a lot of answers along the lines of "exceptions should never be used to control the flow of the program" and "exceptions aren't for control flow". Indeed you already have several. You may, like me, find these statements completely meaningless. Exceptions do control the flow of the program, and when an API is designed to throw an exception, you don't have any choice but to use it accordingly. EOFException is a case in point. When calling the methods that throw it, you don't have any other way of detecting an EOS.

In this case a different principle applies. If you test and then set, you are introducing a timing window during which the subsequent set can fail anyway, and if the set operation can throw an exception you have to code for it anyway. In these situations you should just do the set and handle the exception accordingly. That way your operation is atomic, and you don't have to write the same code twice. In general the most reliable way to detect whether a resource is available is to try to use it (consider connecting to a network server), and the most reliable way to detect whether an operation will fail is to actually try it (consider this case, i.e. inserting a value into a data structure in which it is a unique key).

The rule about "exceptions should never be used to control the flow of the program" originally came from a much narrower context meaning that you shouldn't in general throw exceptions that you catch within the same method, i.e. use them as a kind of GOTO. However as is very common in this industry the original motivation has been entirely forgotten in favour of what I can only describe as mindless, parrot-fashion, reiteration.

Upvotes: 0

aioobe

Reputation: 420991

The usual consensus is to use exceptions only for exceptional cases and not as a control flow construct. Trying to use a username which happens to be taken should in my opinion be considered a valid, and not that uncommon use case.

In other words, I would check for existing usernames first.

As @paxdiablo points out however, if you are in a multithreaded environment, such as a web server, then you either need to add some locking scheme or use the try / catch approach anyway (considering two threads may be competing for adding the same username). That situation however can surly be considered an exceptional case.

Related questions (all with the same conclusion, don't use exceptions for non-exceptional cases):

Upvotes: 1

Jayan

Reputation: 18459

I would prefer a check for user exists done via query rather than to use exception. The logic for 'user exists' error could soon become a business rule. (well you can write such rules in SQL, but that is altogether different world)

Or just insert the username in the table. If it already exists, then it will throw an error which can then be caught

The problem is there are many other reasons for exception. You anyway have to handle them.

Upvotes: 0

paxdiablo

Reputation: 881443

You should do the "try-and-catch exception" method simply because you have to do it anyway.

If you check first, there's nothing to stop someone inserting a row for that user between your check and your insert, in which case the user will be in the table even though your check didn't find it.

Short of being able to run the check-and-insert within some sort of transaction (so that nobody else can insert that user in the interim). you can't be certain that the non-exception will work.

And although many DBMS' provide transactional support, I don't know of any that will lock a row you have yet to insert :-)

Of course, if your application is designed in such a way that only your process will be inserting users (and serialised), you can use the check-first method. But I'd be putting in copious comments to the effect that it will need to be revisited if ever you scale up.

Upvotes: 4