Thread: how to prevent generating same clipids

Hi All,

I am having the clipid field in my table.In my application  i am taking
Max(clipid) and inserting it into the table by incrementing that max
value. so there is a problem when 2 users click sumbit button at a
time. we r getiing same value . so hoe to solve the problem

On Mon, 2006-04-10 at 07:02, v.suryaprabha@gmail.com wrote:
> Hi All,
>
> I am having the clipid field in my table.In my application  i am taking
> Max(clipid) and inserting it into the table by incrementing that max
> value. so there is a problem when 2 users click sumbit button at a
> time. we r getiing same value . so hoe to solve the problem

You need to use a sequence.

You can either have the database assign the value automatically by
declaring the field type to be serial (instead of int) or bigserial
(instead of int8) or you can assign the value yourself using the nextval
/ currval functions.

Hi
   Now we cannot change the field type. Because already our application
is running and thousands of records are already entered. we are getting
same no for clipid when 2 users are entering data at a time. so how to
solve the problem

On Tue, 2006-04-11 at 00:43, v.suryaprabha@gmail.com wrote:
> Hi
>    Now we cannot change the field type. Because already our application
> is running and thousands of records are already entered. we are getting
> same no for clipid when 2 users are entering data at a time. so how to
> solve the problem

Create a sequence.
Set it's next id to something like "select max(clidid)+100" and start
using the sequence to get the next clipid from.

As long as the +100 is a big enough number that you won't have overrun
the sequence with the max(clipid) before you implement the code change.

You should be able to deploy the change to your app and the sequence in
the database at about the same time, or during a maintenance window I'd
assume.

Read up on nextval() and currval() before you set this in motion.
They're really quite an elegant solution to such issues.

On Tue, 2006-04-11 at 00:43, v.suryaprabha@gmail.com wrote:
> Hi
>    Now we cannot change the field type. Because already our application
> is running and thousands of records are already entered. we are getting
> same no for clipid when 2 users are entering data at a time. so how to
> solve the problem

Addendum:

Note that you can also set the default for the clipid column to be the
nextval('sequencename') at the same time, then if anyone DOES insert a
row without getting the nextval() first, it'll still give them the
proper value.

Note that a "serial" type is actually just an int with a default and a
dependency.

Also, you really should have a primary key or unique index with not null
attribute on the clipid column as well, just to be safe.  PK is about
the same as unique & not null, just that foreign references aren't
automatic, but I get the feeling foreign keys aren't playing a role in
your schema just now.

On mán, 2006-04-10 at 22:43 -0700, v.suryaprabha@gmail.com wrote:
> Hi
>    Now we cannot change the field type. Because already our application
> is running and thousands of records are already entered. we are getting
> same no for clipid when 2 users are entering data at a time. so how to
> solve the problem

create a sequence:
CREATE SEQUENCE clipid_seq
   START WITH somehighenoughnumber

now use that when you allocate ids.
i.e. instead of
  SELECT 1+max(clipid) FROM yourtable
do:
  SELECT nexval(clipid_seq)

gnari

On Mon, Apr 10, 2006 at 10:43:16PM -0700, v.suryaprabha@gmail.com wrote:
> Hi
>    Now we cannot change the field type. Because already our application
> is running and thousands of records are already entered. we are getting
> same no for clipid when 2 users are entering data at a time. so how to
> solve the problem

You don't need to change the field type, you just need to use a
sequence. i.e. nextval/currval. Wherever you're doing max()+1 do a
nextval() instead (after reading the documentation ofcourse).

Have a nice day,

--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

This problem has been solved, by the use of sequences.  If you can't
use them as a default, you can use them instead of

MAX(clipid)

You would use

NEXTVAL(clipid_seq)

assuming you had first done

CREATE SEQUENCE clipid_seq;
SELECT SETVAL('clipid_seq', (select MAX(clipid) from whatevertable));

This will guarantee no duplicates.  It will not guarantee no missing values.

On 10 Apr 2006 22:43:16 -0700, v.suryaprabha@gmail.com
<v.suryaprabha@gmail.com> wrote:
> Hi
>    Now we cannot change the field type. Because already our application
> is running and thousands of records are already entered. we are getting
> same no for clipid when 2 users are entering data at a time. so how to
> solve the problem
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>