Getting primary key sequence number in postgres

Question

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

Is it safe to get the value like so ... -- with currval: as long as you use the DEFAULT for your primary key (or omit it in the INSERT), or use nextval(...) directly at insertion for this primary key, it is safe. -- with nextval: always safe, but the the generated value won't be in sync with the primary key. — pozs
– pozs, Commented Apr 11, 2017 at 13:30

Gary - Stand with Ukraine · Accepted Answer · 2017-04-11 12:37:19Z

3

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

answered Apr 11, 2017 at 12:37

Gary - Stand with Ukraine

7,6291 gold badge33 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Lisa Anna Over a year ago

thanks for the suggestion. I used currval because if you run this sample, I'm getting id+1 as my value as example above.

Vao Tsun · Accepted Answer · 2017-04-11 12:37:41Z

1

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:

To avoid blocking concurrent transactions that obtain numbers from the same sequence, a nextval operation is never rolled back; that is, once a value has been fetched it is considered used and will not be returned again. This is true even if the surrounding transaction later aborts, or if the calling query ends up not using the value.

answered Apr 11, 2017 at 12:37

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

Collectives™ on Stack Overflow

Getting primary key sequence number in postgres

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related