From: | "D(dot) Dante Lorenso" <dante(at)lorenso(dot)com> |
---|---|
To: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | User-specific sequences |
Date: | 2004-01-16 20:27:57 |
Message-ID: | 4008494D.8010601@lorenso.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
In a previous thread, it was determined that rather than
use a table sequence for my primary key, I might consider
using a compound primary key of customer_id and customer_seq
where the customer_seq would be a sequence starting at say
1 and counting for each user separately ... thereby making
it difficult to guess the maximum total number of rows in a
table by simply seeing the currval of the table's sequence.
My question is...what is the best way to implement this?
----
1) Have a table which stores the current sequence value for
each customer and perform an update each time an id is pulled?
...
SELECT customer_seq INTO my_customer_seq
FROM customer_seq_table
WHERE customer_id = in_customer_id
FOR UPDATE;
UPDATE customer_seq_table
SET customer_seq = customer_seq + 1
WHERE customer_id = in_customer_id;
RETURN (my_customer_seq);
...
I guess the FOR UPDATE gives me the row-level lock I need to
ensure that I avoid the race condition between read and update?
----
2) Use max value in table plus one...
INSERT INTO some_table (customer_id, customer_seq, ...)
VALUES (in_customer_id,
(
SELECT MAX(customer_seq)
FROM some_table
WHERE customer_id = in_customer_id
), ... );
I like this approach because I don't have to maintain a separate
table, but it might be a lot slower. I don't know if it's safe
from the race condition problem of simultaneous reads, though.
----
3) Something else I haven't thought of...
----
With the sequences built into PostgreSQL, I can use functions like
CURRVAL and NEXTVAL and I'm guaranteed thread-safety, right? I'd
like my solution to have some of these features as well since common
usage will have this PK (customer_id, customer_seq) be an
auto-generated field which I'll also be using as a foreign key and
so need to retrieve the value as soon as it gets created.
Anybody got a good, clean solution that doesn't involve a lot of
table/trigger/function surgery? If it's clean, I might employ the
technique on other tables as well and so I'd rather avoid having
5 functions, 3 triggers, and 2 new tables everytime I need to do
something like this.
----------
Dante
From | Date | Subject | |
---|---|---|---|
Next Message | Josh Berkus | 2004-01-16 20:37:49 | Potential Problem with PostgeSQL performance on SuSE Linux 9.0 |
Previous Message | Bill Moran | 2004-01-16 20:24:23 | Re: Problems with \copy and delimiters ',' |