seo Question posted in
Some basic information to get you started with this can be found here.

Getting primary key sequence number in postgres – SEO

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
Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search