Inserting tens of millions of rows of data into an foreign table takes too long.

[KingZhaoTPG]

Hi Laurenz,

Sorry to bother you. I encountered a problem when using oracle_fdw. I was going to insert 16000000 rows into an foreign table, and I encountered an Error(ERROR: error executing query: OCIStmtExecute failed to execute remote query
DETAIL: ORA-08177: can't serialize access for this transaction
) at the beginning. I looked up issue#728 and changed my isolation_level to read_committed. Then the insertion took a very long time. Of course, my environment is a local Centos7 virtual machine. But this time still shocked me. Can you give me any optimization suggestions?

Here is my execution statement.

`
--1, create hg_emp table in Local PG server
CREATE TABLE hg_emp (
emp_id SERIAL PRIMARY KEY,
name VARCHAR(100),
age INT,
salary numeric,
dept_id INT
);

--2, insert 16000000 rows into hg_emp
INSERT INTO hg_emp (name, age, salary, dept_id)
SELECT
'Employee_' || gs,
(random() * 40 + 20)::int,
(random() * 5000 + 3000)::numeric,
(gs % 10) + 1
FROM generate_series(1, 16000000) AS gs;

--3, create ora_emp in Oracle 21c
CREATE TABLE ora_emp (
emp_id NUMBER PRIMARY KEY,
name VARCHAR2(100),
age INT,
salary NUMBER,
dept_id INT
);

--4, create foreign server and user mapping

--5, create a foreign table in local PG server
CREATE FOREIGN TABLE ora_emp (
emp_id NUMBER ,
name VARCHAR2(100),
age INT,
salary NUMBER,
dept_id INT
) SERVER fixora21
OPTIONS (schema 'C##ZZHASHE', table 'ORA_EMP');

--6, insert ora_emp in PG server
insert into ora_emp select * from hg_emp ;

`

Result:

test=#
test=# insert into ora_emp select * from hg_emp ;
INSERT 0 16000000
Time: 85903765.078 ms (23:51:43.765)
test=#
test=#

Thanks!
/Shengbin

[laurenz]

I think this is a duplicate of #431.

oracle_fdw performs a round trip between PostgreSQL and Oracle for each row inserted (or updated or deleted). So this kind of bad performance is expected.
I have no plans to improve that.

[KingZhaoTPG]

I got it. thanks!