Re: Query on postgres_fdw extension

On Fri, May 14, 2021 at 12:37 PM Swathi P <swathi(dot)bluepearl(at)gmail(dot)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=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