Import Foreign Schema -> Error Duplicate column "id"

[JamesInform]

Hi Laurenz,

I experience an issue with "import foreign schema" in the "master" branch. This issues does NOT exist in V2_6_0 release.

My environment:

• oracle_fdw from "master" branch"
• PostgreSQL 16.3

Steps to reproduce:

1. Create a table in oracle that has the first column named "ID" with column datatype varchar2(36).
2. Compile oracle_fdw from "master" and use this in PostgreSQL
3. Now connect oracle_fdw from PostgreSQL to Oracle and use "import foreign schema"

An error is thrown that "id" is defined twice.

Greetings to Austria James

[laurenz]

Interesting. My regression tests sport importing a table that meets your specifications, and that passes without problems. I was using the current PostgreSQL development version, but I don't think that should make a difference.

On the other hand, some recent commits in oracle_fdw touched the IMPORT FOREIGN SCHEMA code.

Can you tell me

• the exact error message you are getting

• the CREATE TABLE statement in Oracle

• the exact IMPORT FOREIGN SCHEMA statement you are using

[JamesInform]

Statement: import foreign schema "TEST" from server test_ora into test_ora options (readonly 'false')

Message: column "id" specified more than once

Oracle Table:

CREATE TABLE "T_GET_TEST_SEQUENCE" 
   (    "ID" VARCHAR2(36 CHAR) NOT NULL ENABLE, 
    "TEST_ID_OLD" NUMBER NOT NULL ENABLE, 
    "KEY_NAME" VARCHAR2(32 CHAR) NOT NULL ENABLE, 
    "ID_OLD" NUMBER NOT NULL ENABLE, 
    "ID_NEW" NUMBER, 
    "PARENT_KEY_NAME" VARCHAR2(32 CHAR), 
    "PARENT_ID_OLD" NUMBER NOT NULL ENABLE, 
    "PARENT2_KEY_NAME" VARCHAR2(32 CHAR), 
    "PARENT2_ID_OLD" NUMBER NOT NULL ENABLE, 
     CONSTRAINT "T_GET_TEST_SEQUENCE" PRIMARY KEY ("ID", "TEST_ID_OLD", "KEY_NAME", "ID_OLD", "PARENT_ID_OLD", "PARENT2_ID_OLD")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "DATEN"  ENABLE
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "DATEN" ;

CREATE OR REPLACE EDITIONABLE TRIGGER "T_GET_TEST_TRIGGER" before insert on t_get_test_sequence for each row
begin
   :new.id_new := test_sequence.nextval;
end;
/
ALTER TRIGGER "T_GET_TEST_TRIGGER" ENABLE;

CREATE UNIQUE INDEX "T_GET_TEST_SEQUENCE" ON "T_GET_TEST_SEQUENCE" ("ID", "TEST_ID_OLD", "KEY_NAME", "ID_OLD", "PARENT_ID_OLD", "PARENT2_ID_OLD") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "DATEN" ;

[laurenz]

Thank you; I can reproduce the error now.

[laurenz]

Could you check if my commit solves the problem?

[JamesInform]

Have checked:

1. Table import works.
2. BUT now importing materialized views fails.

I have also checked the (2) issue using the "limit to" clause and it fails on every existing materialized view.

[laurenz]

Thanks for your diligent testing. I have fixed processing for materialized views. Does it work now?

[JamesInform]

Yep, works! Thanks!

[laurenz]

Thanks again for all your help!