Обсуждение: Inserting into foreign table with sequences and default values

Hello,

   I have a few questions inserting data using Foreign Data Wrappers (FDW). Consider this simple example.

On PostgreSQL Database A (remote):

CREATE TABLE APP.TEST (
    ID BIGSERIAL NOT NULL,
    FIRST_NAME text,
    LAST_NAME text,
    STATUS integer NOT NULL DEFAULT 1,
    CONSTRAINT PK_USER PRIMARY KEY (ID)
);

When I connect directly to this database I can insert without any issues. Example:

insert into app.test(first_name) values('Peter');
INSERT 0 1

-------

On PostgreSQL Database B (local):

CREATE SERVER remote_server FOREIGN DATA WRAPPER postgres_fdw ...
IMPORT FOREIGN SCHEMA APP LIMIT TO (TEST)
    FROM SERVER remote_server INTO public;

-------

When I try to do a simple insert into the test table on database B I get an error:

insert into test(first_name) values('Mark');

ERROR:  null value in column "id" of relation "test" violates not-null constraint

If I manually set the id, I get another error

insert into "user"(id, first_name) values(2, 'Mark');

ERROR:  null value in column "status" of relation "test" violates not-null constraint

Only after I set both the id and status fields can I successfully insert.

insert into test(id, first_name, status) values(2, 'Mark', 1);
INSERT 0 1

I guess the FDW is not "seeing" the default value for status and the bigserial sequence for the id column. Is there anyway around this? Is there an option I missed when I called IMPORT FOREIGN SCHEMA? Or is there a hack using views or triggers?

Thanks in advance,

Peter

Thanks Tom,

   Your reply helped point me in the right direction. With a little trial and error I came up with a hack to solve my issue.

First off, I create a shared sequence for the user_id with a technique described here:

https://paquier.xyz/postgresql-2/global-sequences-with-postgres_fdw-and-postgres-core/

This involved creating a sequence on the remote server and a view of the sequence on the remote server. I also updated the "test" table on the remote server to populate the "id" column using a the sequence via a trigger.

On the local server, I created a foreign table that references the view on the remote server and a function that returns a sequence value from the foreign table.

Finally, on the local server I created a trigger on the remote "test" table that was imported earlier via the "IMPORT FOREIGN SCHEMA" command.

It's all a little hacky but it works :-)

Full steps below for anyone that's interested (sorry if it gets mangled via email).

Thanks Again,

Peter

--------------------
On Remote:
--------------------

CREATE SEQUENCE user_id;

CREATE VIEW user_id_view AS SELECT nextval('user_id') as a;

CREATE FUNCTION user_id_nextval() RETURNS bigint AS
'SELECT a FROM user_id_view;' LANGUAGE SQL;


CREATE TABLE APP.TEST (
    ID bigint NOT NULL,
    FIRST_NAME text,
    LAST_NAME text,
    STATUS integer NOT NULL DEFAULT 1,
    CONSTRAINT PK_USER PRIMARY KEY (ID)
);

 CREATE OR REPLACE FUNCTION test_function()
 RETURNS "trigger" AS
 $BODY$
 BEGIN
   New.id := case when New.id IS NULL then user_id_nextval() else New.id end;
   New.status := case when New.status IS NULL then 1 else New.status end;
   Return NEW;
 END;
 $BODY$
 LANGUAGE 'plpgsql' VOLATILE;


 CREATE TRIGGER test_trigger
 BEFORE INSERT
 ON app.test
 FOR EACH ROW
 EXECUTE PROCEDURE test_function();


--------------------
On Local
--------------------


CREATE FOREIGN TABLE user_id_foreign_table (a bigint)
SERVER culmen OPTIONS (table_name 'user_id_view');

CREATE FUNCTION user_id_nextval() RETURNS bigint AS
'SELECT a FROM user_id_foreign_table;' LANGUAGE SQL;


IMPORT FOREIGN SCHEMA APP LIMIT TO (TEST)
    FROM SERVER culmen INTO public;


 CREATE OR REPLACE FUNCTION test_function()
 RETURNS "trigger" AS
 $BODY$
 BEGIN
   New.id := case when New.id IS NULL then user_id_nextval() else New.id end;
   New.status := case when New.status IS NULL then 1 else New.status end;
   Return NEW;
 END;
 $BODY$
 LANGUAGE 'plpgsql' VOLATILE;


 CREATE TRIGGER test_trigger
 BEFORE INSERT
 ON test
 FOR EACH ROW
 EXECUTE PROCEDURE test_function();



 INSERT INTO test(first_name) VALUES ('Bob');

Correction: the trigger on the remote table isn't going to work. I was trying so many different things today that I confused myself. Sorry...

On the bright side the remote sequence works great and I can insert records from the my database to the remote database now which is a step forward.