weixin_39999222
weixin_39999222
2020-12-02 04:39

Internal oracle_fdw error: encountered unknown node type 144

When I execute the following insert statement

sql
insert into ea.program_course(program_id, course_id, direction_id, period_theory, period_experiment, period_weeks,
    is_compulsory, is_practical, property_id, assess_type, test_type,
    start_week, end_week, suggested_term, allowed_term, schedule_type,
    department_id)
select program_id, course_id, direction_id, period_theory, period_experiment, period_weeks,
    is_compulsory, is_practical, property_id, assess_type, test_type,
    start_week, end_week, suggested_term, allowed_term, schedule_type,
    department_id
from ea.sv_program_course;

oracle_fdw reports ERROR: Internal oracle_fdw error: encountered unknown node type 144.

oracle_diag output is: oracle_fdw 2.2.0, PostgreSQL 12.1 (Ubuntu 12.1-1.pgdg18.04+1), Oracle client 19.5.0.0.0, Oracle server 18.0.0.0.0

该提问来源于开源项目:laurenz/oracle_fdw

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

8条回答

  • weixin_39690105 weixin_39690105 5月前

    Node 144 is NextValueExpr, which is used for identity columns. This is unexpected.

    Errors like this sometimes happen if oracle_fdw is built with a different version or fork of PostgreSQL than the one where it is running. Does that ring any bells?

    To reproduce this, could you provide the following:

    • the CREATE TABLE statement on Oracle
    • the CREATE FOREIGN TABLE statement on PostgreSQL
    点赞 评论 复制链接分享
  • weixin_39999222 weixin_39999222 5月前

    Thanks. The view on oracle side is a complex view, it is difficult to reproduce.

    sql
    create foreign table ea.sv_program_course (
        program_id integer,
        course_id text,
        period_theory numeric(3, 1),
        period_experiment numeric(3, 1),
        period_weeks integer,
        is_compulsory boolean,
        is_practical boolean,
        property_id integer,
        assess_type integer,
        test_type integer,
        start_week integer,
        end_week integer,
        suggested_term integer,
        allowed_term integer,
        schedule_type integer,
        department_id text,
        direction_id integer
    ) server zf options (schema 'EA', table 'SV_PROGRAM_COURSE', readonly 'true');
    
    sql
    create or replace view ea.sv_program_course as
    with all_program as (
        select
            jxjhh || '0' as program_id,
            kcdm as course_id,
            case when regexp_like(zxs, '^\d+\.\d?')  then to_number(regexp_substr(zxs, '^\d+\.\d?')) else 0 end as period_theory,
            case when regexp_like(zxs, '-\d+\.\d?$') then to_number(regexp_substr(zxs, '\d+\.\d?$')) else 0 end as period_experiment,
            case when regexp_like(zxs, '^\+\d+$')    then to_number(regexp_substr(zxs, '\d+'))       else 0 end as period_weeks,
            decode(kclb, '必修', 1, '选修', 0, 1) as is_compulsory,
            case when regexp_like(zxs, '^\+\d+$') then 1 else 0 end as is_practical,
            to_number(kcxzdm) as property_id,
            decode(khfs, '考试', 1, '考查', 2, '毕业论文', 3, /*空*/ 9) as assess_type,
            decode(ksfs, '集中', 1, '分散', 2, /*缺省集中*/ 1) as test_type,
            to_number(regexp_substr(qsjsz, '^\d+')) as start_week,
            to_number(regexp_substr(qsjsz, '\d+$')) as end_week,
            jyxdxq as suggested_term, -- error > 8
            ea.util.csv_bit_to_number(kkkxq, jyxdxq) as allowed_term,
            1 as schedule_type,
            xydm as department_id,
            sv_direction.id as direction_id
        from zfxfzb.jxjhkcxxb
        join zfxfzb.kcxzdmb on kcxz = kcxzmc
        join zfxfzb.xydmb on kkxy = xymc
        join ea.sv_course on kcdm = sv_course.id
        left join ea.sv_direction on sv_direction.name = zyfx and sv_direction.program_id = jxjhh || 0
        union all
        select
            jxjhh || case
                when substr(jxjhh, 1, 4) < 2012 then '9'
                when substr(jxjhh, 1, 4) = 2012 then decode(fxbs, 1, '9', '1')
                when substr(jxjhh, 1, 4) > 2012 then decode(fxbs, 1, '2', '1')
            end as program_id,
            kcdm as course_id,
            case when regexp_like(zxs, '^\d+\.\d?')  then to_number(regexp_substr(zxs, '^\d+\.\d?')) else 0 end as period_theory,
            case when regexp_like(zxs, '-\d+\.\d?$') then to_number(regexp_substr(zxs, '\d+\.\d?$')) else 0 end as period_experiment,
            case when regexp_like(zxs, '^\+\d+$')    then to_number(regexp_substr(zxs, '\d+'))       else 0 end as period_weeks,
            decode(kclb, '必修', 1, '选修', 0, 1) as is_compulsory,
            case when regexp_like(zxs, '^\+\d+$') then 1 else 0 end as is_practical,
            to_number(kcxzdm) as property_id,
            decode(khfs, '考试', 1, '考查', 2, '毕业论文', 3, /*空*/ 9) as assess_type,
            decode(ksfs, '集中', 1, '分散', 2, /*缺省集中*/ 1) as test_type,
            to_number(regexp_substr(qsjsz, '^\d+')) as start_week,
            to_number(regexp_substr(qsjsz, '\d+$')) as end_week,
            jyxdxq as suggested_term,
            ea.util.csv_bit_to_number(kkkxq, jyxdxq) as allowed_term,
            1 as schedule_type,
            xydm as department_id,
            sv_direction.id as direction_id
        from zfxfzb.fxjxjhkcxxb
        join zfxfzb.kcxzdmb on kcxz = kcxzmc
        join zfxfzb.xydmb on kkxy = xymc
        join ea.sv_course on kcdm = sv_course.id
        left join ea.sv_direction on sv_direction.name = fxmkmc and sv_direction.program_id = jxjhh || 2 -- 辅修课从模块取专业方向
    )
    select to_number(program_id) as program_id, course_id, period_theory, period_experiment,
        case
            when period_weeks <> 0 then period_weeks
            else (end_week - start_week + 1)
        end as period_weeks, is_compulsory, is_practical,
        property_id, assess_type, test_type, start_week, end_week,
        suggested_term, allowed_term, schedule_type, department_id, direction_id
    from all_program
    order by program_id, suggested_term, course_id;
    
    点赞 评论 复制链接分享
  • weixin_39999222 weixin_39999222 5月前

    The PostgreSQL is installed by apt on Ubuntu, the select part can be executed without any error.

    点赞 评论 复制链接分享
  • weixin_39999222 weixin_39999222 5月前
    • the CREATE TABLE statement on Oracle:
    sql
    create table TMP_PROGRAM_COURSE (
      PROGRAM_ID        NUMBER,
      COURSE_ID         VARCHAR2(12),
      PERIOD_THEORY     NUMBER,
      PERIOD_EXPERIMENT NUMBER,
      PERIOD_WEEKS      NUMBER,
      IS_COMPULSORY     NUMBER,
      IS_PRACTICAL      NUMBER,
      PROPERTY_ID       NUMBER,
      ASSESS_TYPE       NUMBER,
      TEST_TYPE         NUMBER,
      START_WEEK        NUMBER,
      END_WEEK          NUMBER,
      SUGGESTED_TERM    NUMBER(2),
      ALLOWED_TERM      NUMBER,
      SCHEDULE_TYPE     NUMBER,
      DEPARTMENT_ID     VARCHAR2(3),
      DIRECTION_ID      NUMBER
    );
    
    • the CREATE FOREIGN TABLE statement on PostgreSQL
    sql
    create foreign table ea.sv_program_course2 (
        program_id integer,
        course_id text,
        period_theory numeric(3, 1),
        period_experiment numeric(3, 1),
        period_weeks integer,
        is_compulsory boolean,
        is_practical boolean,
        property_id integer,
        assess_type integer,
        test_type integer,
        start_week integer,
        end_week integer,
        suggested_term integer,
        allowed_term integer,
        schedule_type integer,
        department_id text,
        direction_id integer
    ) server zf options (schema 'EA', table 'TMP_PROGRAM_COURSE', readonly 'true');
    
    • execute insert
    sql
    insert into ea.program_course(program_id, course_id, direction_id, period_theory, period_experiment, period_weeks,
        is_compulsory, is_practical, property_id, assess_type, test_type,
        start_week, end_week, suggested_term, allowed_term, schedule_type,
        department_id)
    select program_id, course_id, direction_id, period_theory, period_experiment, period_weeks,
        is_compulsory, is_practical, property_id, assess_type, test_type,
        start_week, end_week, suggested_term, allowed_term, schedule_type,
        department_id
    from ea.sv_program_course2;
    
    
    ERROR:  Internal oracle_fdw error: encountered unknown node type 144.
    
    • execute select
    sql
    select program_id, course_id, direction_id, period_theory, period_experiment, period_weeks,
        is_compulsory, is_practical, property_id, assess_type, test_type,
        start_week, end_week, suggested_term, allowed_term, schedule_type,
        department_id
    from ea.sv_program_course2;
    

    Without error.

    点赞 评论 复制链接分享
  • weixin_39690105 weixin_39690105 5月前

    I get more and more confused.

    There are now two versions of ea.sv_program_course2 floating, one a view and one a foreign table. And I am still lacking a definition for ea.program_course.

    Pardon me if I am inquisitive. I don't want to stall or annoy, but I need to be able to reproduce the problem in order to debug it.

    点赞 评论 复制链接分享
  • weixin_39999222 weixin_39999222 5月前

    Yeah, there is an identity column in ea.program_course, that's the problem.

    sql
    CREATE TABLE ea.program_course (
        id bigint NOT NULL,
        allowed_term integer NOT NULL,
        assess_type integer NOT NULL,
        course_group integer DEFAULT 0 NOT NULL,
        end_week integer NOT NULL,
        has_multi_teachers boolean DEFAULT false NOT NULL,
        is_compulsory boolean DEFAULT true NOT NULL,
        is_practical boolean DEFAULT false NOT NULL,
        period_experiment numeric(3,1) DEFAULT 0 NOT NULL,
        period_theory numeric(3,1) DEFAULT 0 NOT NULL,
        period_weeks integer DEFAULT 0 NOT NULL,
        schedule_type integer DEFAULT 1 NOT NULL,
        start_week integer NOT NULL,
        suggested_term integer NOT NULL,
        test_type integer NOT NULL,
        course_id text NOT NULL,
        department_id text NOT NULL,
        direction_id integer,
        program_id integer NOT NULL,
        property_id integer NOT NULL
    );
    
    
    ALTER TABLE ea.program_course ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY (
        SEQUENCE NAME ea.program_course_id_seq
        START WITH 1
        INCREMENT BY 1
        NO MINVALUE
        NO MAXVALUE
        CACHE 1
    );
    
    点赞 评论 复制链接分享
  • weixin_39690105 weixin_39690105 5月前

    I could finally reproduce the bug, than you.

    Could you verify that the fix I pushed makes it work for you?

    点赞 评论 复制链接分享
  • weixin_39999222 weixin_39999222 5月前

    Yes, it works. Thank you very much!

    点赞 评论 复制链接分享

相关推荐