Received: from localhost (unknown [200.46.204.183]) by developer.postgresql.org (Postfix) with ESMTP id DD2692E0054 for ; Tue, 1 Apr 2008 20:27:03 -0300 (ADT) Received: from developer.postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 34663-03 for ; Tue, 1 Apr 2008 20:27:00 -0300 (ADT) X-Greylist: delayed 00:32:58.387618 by SQLgrey-1.7.5 Received: from mx.myintervals.com (mx.myintervals.com [209.85.90.111]) by developer.postgresql.org (Postfix) with ESMTP id B61A82E004A for ; Tue, 1 Apr 2008 20:27:00 -0300 (ADT) Received: from mx.myintervals.com (localhost [127.0.0.1]) by mx.myintervals.com (Postfix) with ESMTP id 883CD1F6C6F8 for ; Tue, 1 Apr 2008 15:53:59 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; h=Received:X-MimeOLE:Content-class:MIME-Version:Content-Type:Subject:Date:Message-ID:X-MS-Has-Attach:X-MS-TNEF-Correlator:Thread-Topic:Thread-Index:From:To; b=GivcLSFWbfE31iGDhxuPJ16ZdusI+pDtAI98Z+zhLtM9hNFOwlsF5jCePC81LfrQVvYlg0qvVdlO2UCugVOijQ==; c=nofws; d=pelagodesign.com; q=dns; s=mx Received: from 2exchange.pelagodesign.com (wsip-72-215-162-97.sb.sd.cox.net [72.215.162.97]) by mx.myintervals.com (Postfix) with ESMTP id 28F871F6C6EC for ; Tue, 1 Apr 2008 15:53:59 -0700 (PDT) X-MimeOLE: Produced By Microsoft Exchange V6.5 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C8944B.4540BF0A" Subject: Sequential non unique IDs Date: Tue, 1 Apr 2008 15:49:46 -0700 Message-ID: X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: Sequential non unique IDs Thread-Index: AciUSq9ObiMH7a8/ScS+k53/72xhYQ== From: "John Reeve" To: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200804/5 X-Sequence-Number: 30544 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 temporary 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

I have = the following=20 scenario:

A = 'task' table that=20 has the fields:

id =3D> primary key, updated on each insert using a=20 sequence

customerid =3D> integer

localid =3D> integer

I need = the localid=20 to be sequential and unique per unique customerid. The data needs to = look like=20 this:

=20 1, 92, 1

2, 92, 2

3, 93, 1

4, 93, 2

5, 93, 3

6, 92, 3

and so on

I am = presently doing=20 this on the INSERT using an INNER SELECT, like this:

INSERT = INTO task=20 (id, customerid, localid) VALUES (nextval('task_id'), 92, (SELECT = MAX(localid) +=20 1 FROM task WHERE customerid =3D 92));

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.

I've = already=20 considered:

1. I = can't lock the=20 table, because there are too many inserts happening and it will slow = down the=20 app.

2. I = can't use=20 temporary sequence tables because they only exist per session, and each = insert=20 statement belongs to a seperate session.

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.

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)

Anyone = know a=20 solution for this? This can't be the first time anyone has ever tried to = do=20 this. Thanks!

- John =

------_=_NextPart_001_01C8944B.4540BF0A--