This is a multi-part message in MIME format.
------_=_NextPart_001_01C8944B.4540BF0A
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
I have the following scenario:
=20
A 'task' table that has the fields:
id =3D> primary key, updated on each insert using a sequence
customerid =3D> integer
localid =3D> integer
=20
I need the localid to be sequential and unique per unique customerid.
The data needs to look like this:
1, 92, 1
2, 92, 2
3, 93, 1
4, 93, 2
5, 93, 3
6, 92, 3
and so on
=20
I am presently doing this on the INSERT using an INNER SELECT, like
this:
=20
INSERT INTO task (id, customerid, localid) VALUES (nextval('task_id'),
92, (SELECT MAX(localid) + 1 FROM task WHERE customerid =3D 92));
=20
The problem with this query is that if two INSERTS are performed at the
same time for the same customerid (which is happening more than you
would think), than both records end up with the same localid.=20
=20
I've already considered:
1. I can't lock the table, because there are too many inserts happening
and it will slow down the app.
2. I can't use tem****ary sequence tables because they only exist per
session, and each insert statement belongs to a seperate session.
3. I could create a sequence table for every customerid (i.e. CREATE
SEQUENCE task_id_92) but then I would end up with thousands of sequence
tables.
4. Sequence tables wont' rollback with the transaction, so any db error
would create a gap in the localid sequence. This is not ideal, but I
suppose I could live with it (but would prefer not to)
=20
Anyone know a solution for this? This can't be the first time anyone has
ever tried to do this. Thanks!
=20
- John=20
=20
------_=_NextPart_001_01C8944B.4540BF0A
Content-Type: text/html;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Dus-ascii">
<META content=3D"MSHTML 6.00.6000.16608" name=3DGENERATOR></HEAD>
<BODY>
<DIV><SPAN class=3D306233722-01042008><FONT face=3DArial size=3D2>I have =
the following=20
scenario:</FONT></SPAN></DIV>
<DIV><SPAN class=3D306233722-01042008><FONT face=3DArial=20
size=3D2></FONT></SPAN> </DIV>
<DIV><SPAN class=3D306233722-01042008><FONT face=3DArial size=3D2>A =
'task' table that=20
has the fields:</FONT></SPAN></DIV>
<DIV><SPAN class=3D306233722-01042008> <FONT =
face=3DArial=20
size=3D2>id =3D> primary key, updated on each insert using a=20
sequence</FONT></SPAN></DIV>
<DIV><SPAN class=3D306233722-01042008> <FONT =
face=3DArial=20
size=3D2>customerid =3D> integer</FONT></SPAN></DIV>
<DIV><SPAN class=3D306233722-01042008> <FONT =
face=3DArial=20
size=3D2>localid =3D> integer</FONT></SPAN></DIV>
<DIV><SPAN class=3D306233722-01042008><FONT face=3DArial=20
size=3D2></FONT></SPAN> </DIV>
<DIV><SPAN class=3D306233722-01042008><FONT face=3DArial size=3D2>I need =
the localid=20
to be sequential and unique per unique customerid. The data needs to =
look like=20
this:</FONT></SPAN></DIV>
<DIV><SPAN class=3D306233722-01042008><FONT face=3DArial =
size=3D2> =20
1, 92, 1</FONT></SPAN></DIV>
<DIV><SPAN class=3D306233722-01042008> <FONT =
face=3DArial=20
size=3D2>2, 92, 2</FONT></SPAN></DIV>
<DIV><SPAN class=3D306233722-01042008> <FONT =
face=3DArial=20
size=3D2>3, 93, 1</FONT></SPAN></DIV>
<DIV><SPAN class=3D306233722-01042008> <FONT =
face=3DArial=20
size=3D2>4, 93, 2</FONT></SPAN></DIV>
<DIV><SPAN class=3D306233722-01042008> <FONT =
face=3DArial=20
size=3D2>5, 93, 3</FONT></SPAN></DIV>
<DIV><SPAN class=3D306233722-01042008> <FONT =
face=3DArial=20
size=3D2>6, 92, 3</FONT></SPAN></DIV>
<DIV><SPAN class=3D306233722-01042008> <FONT =
face=3DArial=20
size=3D2>and so on</FONT></SPAN></DIV>
<DIV><SPAN class=3D306233722-01042008><FONT face=3DArial=20
size=3D2></FONT></SPAN> </DIV>
<DIV><SPAN class=3D306233722-01042008><FONT face=3DArial size=3D2>I am =
presently doing=20
this on the INSERT using an INNER SELECT, like this:</FONT></SPAN></DIV>
<DIV><SPAN class=3D306233722-01042008><FONT face=3DArial=20
size=3D2></FONT></SPAN> </DIV>
<DIV><SPAN class=3D306233722-01042008><FONT face=3DArial size=3D2>INSERT =
INTO task=20
(id, customerid, localid) VALUES (nextval('task_id'), 92, (SELECT =
MAX(localid) +=20
1 FROM task WHERE customerid =3D 92));</FONT></SPAN></DIV>
<DIV><SPAN class=3D306233722-01042008><FONT face=3DArial=20
size=3D2></FONT></SPAN> </DIV>
<DIV><SPAN class=3D306233722-01042008><FONT face=3DArial size=3D2>The =
problem with=20
this query is that if two INSERTS are performed at the same time for the =
same=20
customerid (which is happening more than you would think), than =
both=20
records end up with the same localid. </FONT></SPAN></DIV>
<DIV><SPAN class=3D306233722-01042008><FONT face=3DArial=20
size=3D2></FONT></SPAN> </DIV>
<DIV><SPAN class=3D306233722-01042008><FONT face=3DArial size=3D2>I've =
already=20
considered:</FONT></SPAN></DIV>
<DIV><SPAN class=3D306233722-01042008><FONT face=3DArial size=3D2>1. I =
can't lock the=20
table, because there are too many inserts happening and it will slow =
down the=20
app.</FONT></SPAN></DIV>
<DIV><SPAN class=3D306233722-01042008><FONT face=3DArial size=3D2>2. I =
can't use=20
tem****ary sequence tables because they only exist per session, and each =
insert=20
statement belongs to a seperate session.</FONT></SPAN></DIV>
<DIV><SPAN class=3D306233722-01042008><FONT face=3DArial size=3D2>3. I =
could create a=20
sequence table for every customerid (i.e. CREATE SEQUENCE task_id_92) =
but then I=20
would end up with thousands of sequence tables.</FONT></SPAN></DIV>
<DIV><SPAN class=3D306233722-01042008><FONT face=3DArial size=3D2>4. =
Sequence tables=20
wont' rollback with the transaction, so any db error would create a gap =
in the=20
localid sequence. This is not ideal, but I suppose I could live with it =
(but=20
would prefer not to)</FONT></SPAN></DIV>
<DIV><SPAN class=3D306233722-01042008></SPAN><SPAN =
class=3D306233722-01042008><FONT=20
face=3DArial size=3D2></FONT></SPAN> </DIV>
<DIV><SPAN class=3D306233722-01042008><FONT face=3DArial size=3D2>Anyone =
know a=20
solution for this? This can't be the first time anyone has ever tried to =
do=20
this. Thanks!</FONT></SPAN><FONT face=3DArial =
size=3D2></FONT></DIV><FONT=20
size=3D2></FONT>
<DIV align=3Dleft><FONT face=3DArial></FONT> </DIV>
<DIV align=3Dleft><FONT face=3DArial size=3D2> - John =
<BR></FONT></DIV>
<DIV align=3Dleft><FONT face=3DArial =
size=3D2></FONT> </DIV></BODY></HTML>
------_=_NextPart_001_01C8944B.4540BF0A--
|
