Re: Query on postgres_fdw extension
От | Bharath Rupireddy |
---|---|
Тема | Re: Query on postgres_fdw extension |
Дата | |
Msg-id | CALj2ACUejnxHERB4QNvKV3qVW4VQ=a8Y+kuuPTseuwq0prVe3Q@mail.gmail.com обсуждение исходный текст |
Ответ на | Query on postgres_fdw extension (Swathi P <swathi.bluepearl@gmail.com>) |
Ответы |
Re: Query on postgres_fdw extension
(Swathi P <swathi.bluepearl@gmail.com>)
Re: Query on postgres_fdw extension (Swathi P <swathi.bluepearl@gmail.com>) |
Список | 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 shardingsolution 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 helpus 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 valuesfor 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 onHost_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
В списке pgsql-general по дате отправления:
Следующее
От: Devrim GündüzДата:
Сообщение: Re: [RPM/CentOS7] Need to disable repo_gpgcheck on pgdg-common when using RPM version 42.0-17.1