Re: Query on postgres_fdw extension - Mailing list pgsql-general

On Fri, May 14, 2021 at 12:37 PM Swathi P <swathi.bluepearl@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
mightend up having duplicate values for id column in the table_a in host_b (data node) as cconnections come from
multiplecoordinatoor 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.
Letme 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
havebette workaround for this issue, 
>
> https://www.postgresql.org/message-id/CAP=oouH=FccW4V2zm1VjGit=NZDCXzU2tYBoZe88v3mXrEA9Qg@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