Re: User-specific sequences

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" <dante(at)lorenso(dot)com>
To: <pgsql-general(at)postgresql(dot)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 majordomo(at)postgresql(dot)org)
>