CODEWITHSUNDEEP
postgresqlconcurrencysequences
Ritesh Kadmawala
Ritesh Kadmawala

Reputation: 743

How to specify list of values for a postgresql sequence

I have a list of integer values X and I want a primary key for my table Y to come from that list of integer values. I was wondering if this could be done using sequences. In other words: is there a way to tell a PostgreSQL sequence to use this list X to generate primary keys for my table Y?

One way of doing this would be to use a sequence that contains the index of the last used integer from the list X, call setval() on the sequence, get the next value and try to insert it into my table Y. In case of concurrent requests there will be an error, in which case I need to try with the next value from the list X. I would like to know what other (better) ways there are to achieve what I intend to do.

Upvotes: 1

Views: 677

Answers (3)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658102

Could work like this:

-- DROP SCHEMA x CASCADE;
CREATE SCHEMA x;
CREATE TABLE x.priv_id(seq_id int primary key, id int);

INSERT INTO x.priv_id
SELECT generate_series(1,100,1), (random() * 1000)::int;

CREATE SEQUENCE x.priv_seq;

SELECT id
FROM   x.priv_id
WHERE  seq_id = (SELECT nextval('x.priv_seq'));

Major points:

1) Create a lookup table with two numbers
- seq_id is counting from 1 and your primary key.
- id is your numbers in sequence (I substituted random numbers here).
2) Create a helper sequence.
3) Get your numbers with a SELECT like above.
You need the subselect, or all values will be returned at once.

This solution gives all the security nextval() has to offer for concurrency.
Create a unique index on priv_id(id) if you want to make sure your custom id's are unique.

Upvotes: 2

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95692

The most general way to provide your own set of valid numbers is to store them in a table, and set a foreign key reference to it.

That solves the problem of using your own list, but it doesn't solve the problem of fetching the next number from the list. To do that, you'd need to write your own function. (You can't compel a PostgreSQL sequence to operate on your table instead of using its internals.) It's not particularly hard to do, though.

Another alternative that's often overlooked--perhaps with good reason--is writing your own sequence. PostgreSQL is open source; you can write your own sequencer based on existing sequence code. Compile it, test it, and you're off to the races. (I don't recommend doing that, though.)

Upvotes: 0

Bohemian
Bohemian

Reputation: 425238

generate_series can be used to produce a list of all numbers every generated by a sequence:

select * 
from generate_series(1, (select last_value from my_sequence), 1) as x

Note: This assumes the sequence (here my_sequence) started at 1 and incremented by 1. To change these assumptions, change the parameters appropriately.

Upvotes: 0

Related Questions