Is it possible to use a PG sequence on a per record label?

Question

Does PostgreSQL 9.2+ provide any functionality to make it possible to generate a sequence that is namespaced to a particular value? For example:

 .. | user_id | seq_id | body | ...
 ----------------------------------
  - |    4    |   1    |  "abc...."
  - |    4    |   2    |  "def...."
  - |    5    |   1    |  "ghi...."
  - |    5    |   2    |  "xyz...."
  - |    5    |   3    |  "123...."

This would be useful to generate custom urls for the user:

domain.me/username_4/posts/1    
domain.me/username_4/posts/2

domain.me/username_5/posts/1
domain.me/username_5/posts/2
domain.me/username_5/posts/3

I did not find anything in the PG docs (regarding sequence and sequence functions) to do this. Are sub-queries in the INSERT statement or with custom PG functions the only other options?

Answer 1

You can use a subquery in the INSERT statement like @Clodoaldo demonstrates. However, this defeats the nature of a sequence as being safe to use in concurrent transactions, it will result in race conditions and eventually duplicate key violations.

You should rather rethink your approach. Just one plain sequence for your table and combine it with user_id to get the sort order you want.

You can always generate the custom URLs with the desired numbers using row_number() with a simple query like:

SELECT format('domain.me/username_%s/posts/%s'
            , user_id
            , row_number() OVER (PARTITION BY user_id ORDER BY seq_id)
             )
FROM   tbl;

db<>fiddle here
_{Old sqlfiddle}

Answer 2

Maybe this answer is a little off-piste, but I would consider partitioning the data and giving each user their own partitioned table for posts.

There's a bit of overhead to the setup as you will need triggers for managing the DDL statements for the partitions, but would effectively result in each user having their own table of posts, along with their own sequence with the benefit of being able to treat all posts as one big table also.

General gist of the concept...

psql# CREATE TABLE posts (user_id integer, seq_id integer);
CREATE TABLE

psql# CREATE TABLE posts_001 (seq_id serial) INHERITS (posts);
CREATE TABLE

psql# CREATE TABLE posts_002 (seq_id serial) INHERITS (posts);
CREATE TABLE

psql# INSERT INTO posts_001 VALUES (1);
INSERT 0 1

psql# INSERT INTO posts_001 VALUES (1);
INSERT 0 1

psql# INSERT INTO posts_002 VALUES (2);
INSERT 0 1

psql# INSERT INTO posts_002 VALUES (2);
INSERT 0 1

psql# select * from posts;
 user_id | seq_id 
---------+--------
       1 |      1
       1 |      2
       2 |      1
       2 |      2
(4 rows)

I left out some rather important CHECK constraints in the above setup, make sure you read the docs for how these kinds of setups are used