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

On Thu, May 13, 2021 at 4:58 PM Swathi P <swathi.bluepearl@gmail.com> 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