From: John Reeve <jreeve@pelagodesign.com>
To: pgsql-sql@postgresql.org
Subject: Sequential non unique IDs
Date: Tue, 1 Apr 2008 15:49:46 -0700
Message-ID: <E3BA5CEC8F665345BF4993E64A5F5C26C2556B@2exchange.pelagodesign.com> (raw)
I have the following scenario:
A 'task' table that has the fields:
id => primary key, updated on each insert using a sequence
customerid => integer
localid => integer
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
I am presently doing this on the INSERT using an INNER SELECT, like
this:
INSERT INTO task (id, customerid, localid) VALUES (nextval('task_id'),
92, (SELECT MAX(localid) + 1 FROM task WHERE customerid = 92));
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.
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)
Anyone know a solution for this? This can't be the first time anyone has
ever tried to do this. Thanks!
- John
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jreeve@pelagodesign.com
Subject: Re: Sequential non unique IDs
In-Reply-To: <E3BA5CEC8F665345BF4993E64A5F5C26C2556B@2exchange.pelagodesign.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox