User-specific sequences

[D. Dante Lorenso]

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




---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Chris Ochs]

Here is a function I use to generate unique order id's. In a multi -
merchant system we prefix the order id with the merchant's id. This gives
us system wide and per merchant unique order id's. Sequences are nice but
not when you don't want possible skips in sequence. This function
initializes the order id if it doesn't exist also.

CREATE OR REPLACE FUNCTION get_orderid(varchar) returns numeric AS '
DECLARE
in_mer_id ALIAS FOR $1;
next_order_id numeric;
new_order_id numeric;
BEGIN
next_order_id := order_id FROM last_orderid WHERE mer_id = in_mer_id FOR
UPDATE;
IF next_order_id < 1 THEN
new_order_id := 10000000;
INSERT into last_orderid(mer_id,order_id)
VALUES(in_mer_id,new_order_id);
RETURN new_order_id;
ELSE
UPDATE last_orderid SET order_id=(order_id + 1) WHERE mer_id =
in_mer_id;
RETURN next_order_id;
END IF;
END
' LANGUAGE 'plpgsql';

----- Original Message -----
From: "D. Dante Lorenso" <da***@lorenso.com>
To: <pg***********@postgresql.org>
Sent: Friday, January 16, 2004 12:27 PM
Subject: [GENERAL] User-specific sequences

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




---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend