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