insert - postgresql company id based sequence

Question

I have a database with companies and their products, I want for each company to have a separate product id sequence.

I know that postgresql can't do this, the only way is to have a separate sequence for each company but this is cumbersome.

I thought about a solution to have a separate table to hold the sequences

CREATE TABLE "sequence"
(
  "table" character varying(25),
  company_id integer DEFAULT 0,
  "value" integer
)

"table" will be holt the table name for the sequence, such as products, categories etc.

and value will hold the actual sequence data that will be used for product_id on inserts

I will use UPDATE ... RETURNING value; to get a product id

I was wondering is this solution efficient?

With row level locking, only users of same company adding rows in the same table will have to wait to get a lock and I think that reduces race condition problems.

Is there a better way to solve this problem?

I don't want to use a sequence for products table for all companies because the difference between product id's will be to big, I want to keep it simple for the users.

Answer 1

You could just embed a counter in your companies table:

CREATE TABLE companies (
    id          SERIAL PRIMARY KEY,
    name        TEXT,
    product_id  INT DEFAULT 0
);

CREATE TABLE products (
    company     INT REFERENCES companies(id),
    product_id  INT,
    PRIMARY KEY (company, product_id),

    name        TEXT
);

INSERT INTO companies (id, name) VALUES (1, 'Acme Corporation');
INSERT INTO companies (id, name) VALUES (2, 'Umbrella Corporation');

Then, use UPDATE ... RETURNING to get the next product ID for a given company:

> INSERT INTO products VALUES (1, (UPDATE companies SET product_id = product_id+1 WHERE id=$1 RETURNING product_id), 'Anvil');
ERROR:  syntax error at or near "companies"
LINE 1: INSERT INTO products VALUES (1, (UPDATE companies SET produc...
                                            ^

Oh noes! It seems you can't (as of PostgreSQL 9.1devel) use UPDATE ... RETURNING as a subquery.

The good news is, it's not a problem! Just create a stored procedure that does the increment/return part:

CREATE FUNCTION next_product_id(company INT) RETURNS INT
AS $$
    UPDATE companies SET product_id = product_id+1 WHERE id=$1 RETURNING product_id
$$ LANGUAGE 'sql';

Now insertion is a piece of cake:

INSERT INTO products VALUES (1, next_product_id(1), 'Anvil');
INSERT INTO products VALUES (1, next_product_id(1), 'Dynamite');
INSERT INTO products VALUES (2, next_product_id(2), 'Umbrella');
INSERT INTO products VALUES (1, next_product_id(1), 'Explosive tennis balls');

Be sure to use the same company ID in both the product value and the argument to next_product_id(company INT).