Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rBkWE-00CBnh-TL for pgsql-general@arkaria.postgresql.org; Fri, 08 Dec 2023 23:44:31 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1rBkWD-00Cvah-Kg for pgsql-general@arkaria.postgresql.org; Fri, 08 Dec 2023 23:44:29 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rBkWD-00CvaY-95 for pgsql-general@lists.postgresql.org; Fri, 08 Dec 2023 23:44:29 +0000 Received: from smtp83.iad3a.emailsrvr.com ([173.203.187.83]) by magus.postgresql.org with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rBkW9-00Atf6-MO for pgsql-general@lists.postgresql.org; Fri, 08 Dec 2023 23:44:28 +0000 Received: from app69.wa-webapps.iad3a (relay-webapps.rsapps.net [172.27.255.140]) by smtp19.relay.iad3a.emailsrvr.com (SMTP Server) with ESMTP id AC9E04DBE; Fri, 8 Dec 2023 18:44:23 -0500 (EST) Received: from kartographia.com (localhost.localdomain [127.0.0.1]) by app69.wa-webapps.iad3a (Postfix) with ESMTP id 96ABF211AB; Fri, 8 Dec 2023 18:44:23 -0500 (EST) Received: by apps.rackspace.com (Authenticated sender: peter.borissow@kartographia.com, from: peter.borissow@kartographia.com) with HTTP; Fri, 8 Dec 2023 18:44:23 -0500 (EST) X-Auth-ID: peter.borissow@kartographia.com Date: Fri, 8 Dec 2023 18:44:23 -0500 (EST) Subject: Re: Inserting into foreign table with sequences and default values From: "peter.borissow@kartographia.com" To: "Tom Lane" Cc: pgsql-general@lists.postgresql.org MIME-Version: 1.0 Content-Type: multipart/alternative;boundary="----=_20231208184423000000_84838" Importance: Normal X-Priority: 3 (Normal) X-Type: html In-Reply-To: <1468017.1702060306@sss.pgh.pa.us> References: <1702058638.91064408@apps.rackspace.com> <1468017.1702060306@sss.pgh.pa.us> X-Client-IP: 170.250.244.158 Message-ID: <1702079063.614617754@apps.rackspace.com> X-Mailer: webmail/19.0.24-RC X-Classification-ID: 7140c433-d5d3-4080-b243-394975cbe29e-1-1 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk ------=_20231208184423000000_84838 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable =0AThanks Tom,=0A 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.=0A =0AFi= rst off, I create a shared sequence for the user_id with a technique descri= bed here:=0A[ https://paquier.xyz/postgresql-2/global-sequences-with-postgr= es_fdw-and-postgres-core/ ]( https://paquier.xyz/postgresql-2/global-sequen= ces-with-postgres_fdw-and-postgres-core/ )=0A =0AThis involved creating a s= equence on the remote server and a view of the sequence on the remote serve= r. I also updated the "test" table on the remote server to populate the "id= " column using a the sequence via a trigger.=0A =0AOn the local server, I c= reated a foreign table that references the view on the remote server and a = function that returns a sequence value from the foreign table.=0A =0AFinall= y, on the local server I created a trigger on the remote "test" table that = was imported earlier via the "IMPORT FOREIGN SCHEMA" command.=0A =0AIt's al= l a little hacky but it works :-)=0A =0AFull steps below for anyone that's = interested (sorry if it gets mangled via email).=0A =0AThanks Again,=0APete= r=0A =0A =0A--------------------=0AOn Remote:=0A--------------------=0A=0AC= REATE SEQUENCE user_id;=0A=0ACREATE VIEW user_id_view AS SELECT nextval('us= er_id') as a;=0A=0ACREATE FUNCTION user_id_nextval() RETURNS bigint AS=0A'S= ELECT a FROM user_id_view;' LANGUAGE SQL;=0A=0A=0ACREATE TABLE APP.TEST (= =0A ID bigint NOT NULL,=0A FIRST_NAME text,=0A LAST_NAME text,=0A = STATUS integer NOT NULL DEFAULT 1,=0A CONSTRAINT PK_USER PRIMARY KEY = (ID)=0A);=0A=0A CREATE OR REPLACE FUNCTION test_function()=0A RETURNS "trig= ger" AS=0A $BODY$=0A BEGIN=0A New.id :=3D case when New.id IS NULL then u= ser_id_nextval() else New.id end;=0A New.status :=3D case when New.status= IS NULL then 1 else New.status end;=0A Return NEW;=0A END;=0A $BODY$=0A = LANGUAGE 'plpgsql' VOLATILE;=0A=0A=0A CREATE TRIGGER test_trigger=0A BEFORE= INSERT=0A ON app.test=0A FOR EACH ROW=0A EXECUTE PROCEDURE test_function()= ;=0A=0A=0A--------------------=0AOn Local=0A--------------------=0A=0A=0ACR= EATE FOREIGN TABLE user_id_foreign_table (a bigint)=0ASERVER culmen OPTIONS= (table_name 'user_id_view');=0A=0ACREATE FUNCTION user_id_nextval() RETURN= S bigint AS=0A'SELECT a FROM user_id_foreign_table;' LANGUAGE SQL;=0A=0A=0A= IMPORT FOREIGN SCHEMA APP LIMIT TO (TEST)=0A FROM SERVER culmen INTO pub= lic;=0A=0A=0A CREATE OR REPLACE FUNCTION test_function()=0A RETURNS "trigge= r" AS=0A $BODY$=0A BEGIN=0A New.id :=3D case when New.id IS NULL then use= r_id_nextval() else New.id end;=0A New.status :=3D case when New.status I= S NULL then 1 else New.status end;=0A Return NEW;=0A END;=0A $BODY$=0A LA= NGUAGE 'plpgsql' VOLATILE;=0A=0A=0A CREATE TRIGGER test_trigger=0A BEFORE I= NSERT=0A ON test=0A FOR EACH ROW=0A EXECUTE PROCEDURE test_function();=0A= =0A=0A=0A INSERT INTO test(first_name) VALUES ('Bob');=0A=0A=0A =0A =0A ------=_20231208184423000000_84838 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

Thanks Tom,

=0A

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

=0A

 

=0A

First off, I= create a shared sequence for the user_id with a technique described here:<= /p>=0A

https://paquier.xyz/post= gresql-2/global-sequences-with-postgres_fdw-and-postgres-core/

=0A 

=0A

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

=0A

 

=0A

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

=0A

&= nbsp;

=0A

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

=0A

 

=0A

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

=0A

 

=0A

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

=0A

 

=0A

Thanks Again,

=0A

Peter

=0A

 

=0A

 <= /p>=0A

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

CREATE SEQUENCE user_id;

CREATE VIEW u= ser_id_view AS SELECT nextval('user_id') as a;

CREATE FUNCTION u= ser_id_nextval() RETURNS bigint AS
'SELECT a FROM user_id_view;' LANGU= AGE SQL;


CREATE TABLE APP.TEST (
    I= D bigint NOT NULL,
    FIRST_NAME text,
 &nbs= p;  LAST_NAME text,
    STATUS integer NOT NULL DE= FAULT 1,
    CONSTRAINT PK_USER PRIMARY KEY (ID)
)= ;

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


 CREATE T= RIGGER test_trigger
 BEFORE INSERT
 ON app.test
&n= bsp;FOR EACH ROW
 EXECUTE PROCEDURE test_function();

<= br />--------------------
On Local
--------------------


CREATE FOREIGN TABLE user_id_foreign_table (a bigint)
SERVER c= ulmen 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;


&nbs= p;CREATE OR REPLACE FUNCTION test_function()
 RETURNS "trigger" A= S
 $BODY$
 BEGIN
   New.id :=3D case whe= n New.id IS NULL then user_id_nextval() else New.id end;
   = New.status :=3D case when New.status IS NULL then 1 else New.status end;   Return NEW;
 END;
 $BODY$
 LA= NGUAGE 'plpgsql' VOLATILE;


 CREATE TRIGGER test_trigg= er
 BEFORE INSERT
 ON test
 FOR EACH ROW
 EXECUTE PROCEDURE test_function();



 INSE= RT INTO test(first_name) VALUES ('Bob');

=0A

 

=0A

 

=0A

&n= bsp;

------=_20231208184423000000_84838--