1

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?

suraj
  • 1,828
  • 12
  • 36
  • 64

9 Answers9

4

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.

paxdiablo
  • 854,327
  • 234
  • 1,573
  • 1,953
  • There are two scenarios: 1) Username is since long in the database and could be checked using select, and 2) Two threads are competing for the same username which causes a race-condition. I'd argue that race-conditions should be avoided by proper locking (on the application side if necessary), and that situation (1) is not exceptional at all. – aioobe Mar 15 '12 at 09:19
  • @paxdiablo: If I use only exceptions without checking the username, it will be faster right? Then why is everyone advising against using exceptions as the default checking mechanism. Mnual checking will result in accessingthe database two times(once for checking for username and other for inserting) – Ashwin Mar 15 '12 at 09:19
  • @user1139023, is inserting new users a bottle-neck of your application? – aioobe Mar 15 '12 at 09:20
  • @aioobe: if you mean something like a mutual exclusion semaphore stored in the database (a known row that you select for update while doing the check and insert), I'd argue that _that_ was the more abusive situation :-). But that (and any other scheme involing mutexes or IPC) assumes that everyone using the database is following the rules, a dangerous assumption. This _is_ actually the right situation for an exception since (1) you cannot detect it beforehand reliably and (2) you must fail if your precondition was invalid. – paxdiablo Mar 15 '12 at 09:21
  • @user1139023, they're right about exception abuse, it's often used for catching problems that can be detected. I just don't think it applies _in this case._ – paxdiablo Mar 15 '12 at 09:22
  • @aioobe: Yes it is the bottleneck. – Ashwin Mar 15 '12 at 09:30
  • @paxdiablo: Does throwing exceptions have performance issues? – Ashwin Mar 15 '12 at 09:37
  • 1
    @user1139023: yes, they can since there's possibly some stack unwinding that needs to take place but, of course, it depends on the implementation and, as I said, in this case it won't matter since you _have_ to allow for it. You should probably benchmark it and do some static analysis on the effects. For example, if you'll only try to insert an already-existing user name in 0.01% of the cases, don't waste time trying to optimise. If the vast majority of cases would be attempted re-insertion, engineer your code around that. – paxdiablo Mar 15 '12 at 10:45
2

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.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
1

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):

Community
  • 1
  • 1
aioobe
  • 413,195
  • 112
  • 811
  • 826
  • How uncommon does it have to be before it becomes exceptional? Is it possible to quantify this? How common is it really to insert a duplicate user into a database? 1 in 1000? – user207421 Mar 15 '12 at 09:56
  • 1
    As with many design decisions there is not really anything right or wrong. Here's a tip for you: Would you call the situation *exceptional*? (This is analogous to asking *is a fish a mammal* when deciding whether one class should extend another.) – aioobe Mar 15 '12 at 10:14
0

I would say that catching an exception in such case is abusing of the exception concept, if you can check it before, you should check it.

MByD
  • 135,866
  • 28
  • 264
  • 277
  • 1
    Why do you say that? Directly using exceptions as the checking mechanism will be faster instead of forst checking if the username exists(for that again the database will have to be accesses). – Ashwin Mar 15 '12 at 09:22
0

exceptions should never be used to control the flow of the program. It is best practice to avoid exceptions if its not a excpetional case.

Adreamus
  • 670
  • 7
  • 15
  • 1
    Please give reason for you statement. – Ashwin Mar 15 '12 at 09:49
  • I find this statement completely meaningless. What is an exception if not an exceptional case? – user207421 Mar 15 '12 at 09:53
  • @EJP, *What is an exception if not an exceptional case?* An exception can (if misused) be thrown in a non-exceptional case. I'm sure you would agree with this. You probably just misunderstood or misread the answer here, in any case, have a look [here](http://meta.stackexchange.com/questions/2451/why-do-you-cast-downvotes-on-answers) for the community view of proper reasons to downvote answers. – aioobe Mar 15 '12 at 10:22
0

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.

Jayan
  • 18,003
  • 15
  • 89
  • 143
  • Yes I will handle the exceptions. But using exceptions will be faster than first checking if the username exists and then inserting. Does throwing an exception each time have performance issues? – Ashwin Mar 15 '12 at 09:28
0

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.

user207421
  • 305,947
  • 44
  • 307
  • 483
  • Does throwing exceptions have performance issues? In that case I should avoid throwing exceptions, I guess. – Ashwin Mar 15 '12 at 11:11
  • Not sure why this was downvoted but I'm upvoting it since it makes sense. The mis-application of rules is why some people never use goto or break or have multiple return points from a function despite not understanding _why._ Where those things don't make the code unreadable, they're fine and, in some cases, they're even _more_ readable than the rubbish you can get by over-engineering `while` statements to avoid them :-) – paxdiablo Mar 15 '12 at 12:06
0

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.

kandarp
  • 4,979
  • 11
  • 34
  • 43
-1

The right thing to do is not about exception handling in your case, you should use a primary key that auto increment.

PostgreSQL Autoincrement

Community
  • 1
  • 1
Snicolas
  • 37,840
  • 15
  • 114
  • 173
  • And by the way, you can a select count(*) request to count lines, it is much faster than requesting each and every line and counting them. But an autoincrement primary key is a better option – Snicolas Mar 15 '12 at 09:52