We’ve got a table where we’re exposing a ShortID to our consumers that generates a Youtube like identifier (heavily inspired by this SO post). We use the primary KEY of the row to generate it (in the view). For example
SELECT title,description,imageuri,generateseoid(id)as seo FROM broadcastimage WHERE...
The generateseoid
functionis essentially doing the stringify_bigint
call (from the above SO answers) with some minor changes.
Our need is to do this at insertion time. Is it safe to get the value like so:
generateseoid(currval(pg_get_serial_sequence('broadcastimage', 'id')))
within the insert call? We have lots of threads (via SQS queues in AWS) inserting records into the broadcast stream table. I’m a bit concerned that with multiple postgres connections, we’d get the same sequence number being generated. There’s a UNIQUE CONSTRAINT on that column.
Alternative is to add another random() number to it I guess?
EDIT:
Here’s a simple example that tests the nextval vs currval:
CREATE TABLE test (
id serial PRIMARY KEY,
text text
)
insert into test (text) VALUES ('test ' || nextval(pg_get_serial_sequence('test ', 'id')))
insert into test (text) VALUES ('test ' || currval(pg_get_serial_sequence('test ', 'id')))
select * From test
The results point to:
1 | test 2
3 | test 3
2
Answers
Postgres will generate new ids for sequences each time. You might get gaps in the sequence caused by rollbacks etc, but if that isn’t important, then you should be good to go.
You should be using
nextval
to get a new value.currval
reports the last generated value.Sequence documentation.
https://www.postgresql.org/docs/current/static/sql-createsequence.html
I’d rather use
nextval
to roll forward the value and return it.Regarding your concern – it is safe to use
nextval
for multiple concurrent transactions: