Distributed Sequence generation - Citus

Question

We are using sequence generator to generate the sequence. Ex: Every time when a row is inserted there is one more column which uses sequence to create strings like R0001, R0002 etc.
We want this sequence to be re-started for every shard. We are sharding by Org_Id which is from organisation table.
Ex: If Org Table has two entries with Ids 1,2 and Role Table has two entries with Ids 1 and 2, from Org Id 1 and Org Id 2 respectively, RoleNumbers should be R0001 and R0001 for both rows 1 and 2 in Role table. Currently since the sequence is not distributed, generated sequence is R0001 and R0002.
Is there a solution in citus for the above problem?

Answer 1

So Citus is an extension of postgres, meaning it shares and has most of the same code as postgresql; however, it has limitiations.

Specifically, and what affects you, is that sequence generated by the node inserts its node id in front of everyone so if your node ids are 001 and 002 you would get 001R0001 and 002R0001 from my reading of it and your sequence needs to be of at least size BIGINT.

That being said, you can have two works arounds

1. You can have the client query for the current id/count and have the client create the field
2. you could do a sql command to get the count and generate a new id without the sequencer I understand now. You want to insert a new row into a table, and if a specific field is missing in your object, you want to automatically add a value that represents the count of rows in the table plus 1.

something akin to this

INSERT INTO your_table (field1, field2, field3)
VALUES ('value1', 'value2', COALESCE('value3', 'COUNT-' || (SELECT COUNT(*) + 1 FROM your_table)));