On Fri, May 14, 2021 at 12:37 PM Swathi P <swathi.bluepe...@gmail.com> wrote: > > Thanks Bharath for the details. > > In our sharding solution, we have multiple coodinator nodes. If we declare > the table column as serial data type, we might end up having duplicate values > for id column in the table_a in host_b (data node) as cconnections come from > multiple coordinatoor nodes and might end up in duplicate key violations. > > Hence we decided to have the coordinator nodes as stateless and hence > declared the column with no serial/sequence. Let me know if this makes sense.
Hm. > Have come across multiple articles on the same issue, i would like to know if > we are doing something wrong here or we have bette workaround for this issue, > > https://www.postgresql.org/message-id/CAP=oouH=FccW4V2zm1VjGit=nzdcxzu2tyboze88v3mxrea...@mail.gmail.com > https://stackoverflow.com/questions/66582694/how-to-get-the-generated-id-from-an-insert-to-a-postgres-foreign-table > https://paquier.xyz/postgresql-2/global-sequences-with-postgres_fdw-and-postgres-core/ Did you try using the approach specified by Michael in [1]? Won't that help? "As a conclusion, you can create tables using unique values across multiple nodes by associating for example foreign_seq_nextval() with DEFAULT for a column. =# CREATE TABLE tab (a int DEFAULT foreign_seq_nextval()); CREATE TABLE =# INSERT INTO tab VALUES (DEFAULT), (DEFAULT), (DEFAULT); INSERT 0 3 =# SELECT * FROM tab; a ---- 9 10 11 (3 rows) " [1] - https://paquier.xyz/postgresql-2/global-sequences-with-postgres_fdw-and-postgres-core/ With Regards, Bharath Rupireddy. EnterpriseDB: http://www.enterprisedb.com
