1

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?

ManojAnavatti
  • 604
  • 1
  • 7
  • 18
  • Too clarify, your asking if there is a solution in citrus to allow you to do the sequence in a distributed setting as your asking? or are you asking can citrus fix the issues you are currently having as it is not distributed? Im reading this as you want convert this to a distributed context and if citrus can handle the sequencing? – Lucas Hendren Aug 01 '23 at 10:54
  • @LucasHendren, thanks for your comment. For your questions, a) May be this problem is already solved by citus which I am not aware of b) Is there a simple workaround without for us to do any design changes on how we generate sequence. This is an important feature for us as we generate this similar sequence for many different tables. – ManojAnavatti Aug 02 '23 at 07:19

1 Answers1

0

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)));
Lucas Hendren
  • 2,786
  • 2
  • 18
  • 33