Re: Query on postgres_fdw extension

[Swathi P <swathi.bluepearl@xxxxxxxxx>]

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. 

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@xxxxxxxxxxxxxx
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/

Thanks again for your time. 

- Swathi

On Thu, May 13, 2021 at 5:34 PM Bharath Rupireddy <bharath.rupireddyforpostgres@xxxxxxxxx> wrote:

On Thu, May 13, 2021 at 4:58 PM Swathi P <swathi.bluepearl@xxxxxxxxx> wrote:
>
> Hello EveryOne,
>
>  Hope you are all doing well and staying safe.
>
> Am Swathi, have been working with postgres for last 3 years. Currently we are working on a project to build a sharding solution with the help of native postgres_fdw extension. During this process, we have encountered an issue with postgres_fdw. I tried to give as much as details below on the issue we are facing, it would be of great help if you can help us overcome this issue.
>
> - We have Host_A and Host_B , where Host_A is out coordinator node and Host_B is used as our data node.
>
> - Host_B has a table "Table_B" with a sequence id column which auto generates the series by default
>   CREATE TABLE public.table_a
>  (
>      id bigint NOT NULL DEFAULT nextval('table_a_id_seq'::regclass),
>      topic character varying(50) NOT NULL,
>      CONSTRAINT table_a_pk PRIMARY KEY (id)
>  )
>
> - on Host_A we have a foreign table created with the ddl below
>  CREATE FOREIGN TABLE public.table_a
>  (
>       id bigint ,
>       topic character varying(50) NOT NULL,
>   ) SERVER Host_A OPTIONS (schema_name 'public', table_name 'table_a');
>
> - When we try to insert data directly on the table_a while connected to Host_B, works fine with the auto incremented values for the id column
>
> - But the same insert fails when run from the coordinator node with below error.
> poc=> insert into table_a(topic) values ('test');
> ERROR:  null value in column "id" of relation "table_a" violates not-null constraint
> DETAIL:  Failing row contains (null, test).
> CONTEXT:  remote SQL command: INSERT INTO public.table_a(id, topic) VALUES ($1, $2)
>
> - If we omit the primary key and not null constraints on the table_a on remote host (Host_B) , inserts will work fine on Host_A but a NULL value insertedd for the id column instead of sequence
>
> We are looking for some help on understanding the issue here and the best possible workaround for the same.
>
> Your help will be greatly appreciated

I think you need to declare your foreign table column id as "serial"
type instead of "bigint". Below is what I tried from my end.

On remote server:
CREATE USER foreign_user;
DROP TABLE table_a;
CREATE TABLE table_a
 (
     id serial NOT NULL,
     topic character varying(50) NOT NULL,
     CONSTRAINT table_a_pk PRIMARY KEY (id)
 );
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO foreign_user;

On local server:
DROP EXTENSION postgres_fdw CASCADE;
CREATE EXTENSION postgres_fdw;
CREATE SERVER foreign_server
        FOREIGN DATA WRAPPER postgres_fdw
        OPTIONS (host 'localhost', port '5433', dbname 'postgres');
CREATE USER MAPPING FOR public
        SERVER foreign_server
        OPTIONS (user 'foreign_user', password '');
CREATE FOREIGN TABLE table_a (id serial NOT NULL,
     topic character varying(50) NOT NULL)
SERVER foreign_server
OPTIONS (schema_name 'public', table_name 'table_a');
SELECT * FROM table_a;
INSERT INTO table_a(topic) VALUES('row1');
INSERT INTO table_a(topic) VALUES('row2');
INSERT INTO table_a(topic) VALUES('row3');

With Regards,
Bharath Rupireddy.
EnterpriseDB: http://www.enterprisedb.com