Hello, I have an application that have 3 flavors of database: Oracle

Conversion between Oracle DATE and PostgreSQL TIMESTAMP about oracle_fdw HOT 9 CLOSED

adrianboangiu commented on August 14, 2024

Conversion between Oracle DATE and PostgreSQL TIMESTAMP

from oracle_fdw.

Comments (9)

laurenz commented on August 14, 2024

Please tell me the CREATE TABLE statement in Oracle and the corresponding CREATE FOREIGN TABLE statement in PostgreSQL.

from oracle_fdw.

adrianboangiu commented on August 14, 2024

Here are the create table in the Oracle and PostgreSQL (the equivalent of the Oracle table and the foreign table) with only the columns I use for insert and select (there are some others but not interesting for us in this example).
This is the Oracle table:

CREATE TABLE "COSWIN"."T_TABLE1"(
   "PK_TABLE1"          NUMBER( 38, 0 )
   , "TIMESTAMP"          NUMBER( 10, 0 )DEFAULT 1
      CONSTRAINT "NN_TABLE1_TS" NOT NULL ENABLE
   , "FRT1_CODE"          VARCHAR2( 20 BYTE )
      CONSTRAINT "NN_FRT1_CODE" NOT NULL ENABLE
   , "FRT1_DESCRIPTION"   VARCHAR2( 256 BYTE )
   , "FRT1_LAST_UPDATE"   DATE DEFAULT SYSDATE
      CONSTRAINT "NN_FRT1_LAST_UPDATE" NOT NULL ENABLE
   , "FRT1_CREATION_DATE" DATE DEFAULT SYSDATE
      CONSTRAINT "NN_FRT1_CREATION_DATE" NOT NULL ENABLE
   , "FRT1_REMARKS"       CLOB
   , "FRT1_OLE_COUNTER"   NUMBER( 10, 0 )
   , "FRT1_ENTITY"        VARCHAR2( 20 BYTE )
   , CONSTRAINT "CK_PK_TABLE1" CHECK(( PK_TABLE1 > 0 )
      AND( PK_TABLE1 < 2147483648 ))ENABLE
   , CONSTRAINT "CK_TABLE1_TS" CHECK( TIMESTAMP > 0 )ENABLE
   , CONSTRAINT "CK_FRT1_OLE_COUNTER" CHECK( FRT1_OLE_COUNTER >= 0 )ENABLE
   , CONSTRAINT "PK_TABLE1" PRIMARY KEY( "PK_TABLE1" )
      USING INDEX PCTFREE 5 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
         STORAGE( INITIAL 262144 NEXT 262144 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE
         DEFAULT )
      TABLESPACE "COSWIN_INDEX"
   ENABLE
   , CONSTRAINT "UN_FRT1_CODE" UNIQUE( "FRT1_CODE" )
      USING INDEX PCTFREE 5 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
         STORAGE( INITIAL 262144 NEXT 262144 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE
         DEFAULT )
      TABLESPACE "COSWIN_INDEX"
   ENABLE
   , CONSTRAINT "FK_FRT1_ENTITY" FOREIGN KEY( "FRT1_ENTITY" )
      REFERENCES "COSWIN"."T_ENTITY"( "CHEN_CODE" )
   DEFERRABLE ENABLE
)
SEGMENT CREATION IMMEDIATE
PCTFREE 5 PCTUSED 60 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
   STORAGE( INITIAL 524288 NEXT 524288 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE
   DEFAULT )
TABLESPACE "COSWIN_DATA"
   LOB( "FRT1_REMARKS" )STORE AS SECUREFILE(
      TABLESPACE "COSWIN_DATA"
      ENABLE STORAGE IN ROW
      CHUNK 8192
      NOCACHE LOGGING
      NOCOMPRESS
      KEEP_DUPLICATES
      STORAGE( INITIAL 106496 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT )
   );

This is the Postgres table equivalent to the Oracle one:

CREATE TABLE IF NOT EXISTS t_table1
(
    pk_table1 integer NOT NULL,
    "timestamp" integer NOT NULL DEFAULT 1,
    frt1_code character varying(20) COLLATE pg_catalog."default" NOT NULL,
    frt1_description character varying(256) COLLATE pg_catalog."default",
    frt1_last_update timestamp(0) without time zone NOT NULL DEFAULT now(),
    frt1_creation_date timestamp(0) without time zone NOT NULL DEFAULT now(),
    frt1_remarks text COLLATE pg_catalog."default",
    frt1_ole_counter integer,
    frt1_entity character varying(20) COLLATE pg_catalog."default",
    CONSTRAINT pk_table1 PRIMARY KEY (pk_table1),
    CONSTRAINT un_frt1_code UNIQUE (frt1_code),
    CONSTRAINT fk_frt1_entity FOREIGN KEY (frt1_entity)
        REFERENCES coswin.t_entity (chen_code) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        DEFERRABLE,
    CONSTRAINT ck_frt1_ole_counter CHECK (frt1_ole_counter >= 0),
    CONSTRAINT ck_pk_table1 CHECK (pk_table1 > 0 AND pk_table1 < '2147483648'::bigint),
    CONSTRAINT ck_table1_ts CHECK ("timestamp" > 0)
)

This is the foreign table:

CREATE FOREIGN TABLE IF NOT EXISTS ora_coswin.t_table1(
    pk_table1 numeric(38,0) OPTIONS (key 'true') NOT NULL,
    "timestamp" bigint NOT NULL,
    frt1_code character varying(20) NOT NULL COLLATE pg_catalog."default",
    frt1_description character varying(256) NULL COLLATE pg_catalog."default",
    frt1_last_update timestamp(0) without time zone NOT NULL,
    frt1_creation_date timestamp(0) without time zone NOT NULL,
    frt1_remarks text NULL COLLATE pg_catalog."default",
    frt1_ole_counter bigint NULL,
    frt1_entity character varying(20) NULL COLLATE pg_catalog."default",
)
    SERVER oracle_srv
    OPTIONS (schema 'COSWIN', table 'T_TABLE1');

In Oracle we do not use TIMESTAMP columns. We use only DATE columns (date and time) that were all "translated" in TIMESTAMP without timezone.

from oracle_fdw.

laurenz commented on August 14, 2024

I just tried that with a random row, and it worked without an error.
Can you give me an example of a row in the local t_table1 that will make the INSERT fail for you?
Best would be if you could write an INSERT statement for the local table.

from oracle_fdw.

laurenz commented on August 14, 2024

Aren't there any detail messages with the error? There should be something containing the actual Oracle error message.
Do you happen to have any login triggers in Oracle that mess with the date formats?

from oracle_fdw.

adrianboangiu commented on August 14, 2024

I have checked and what happens is that I have the following error when I try to transfer the full content of the table:

pid:24509 [12-1] xid:0 ip:::1(46006) coswin@coswin ERROR:  error executing query: OCIStmtExecute failed to execute remote query
pid:24509 [13-1] xid:0 ip:::1(46006) coswin@coswin DETAIL:  ORA-08177: can't serialize access for this transaction
pid:24509 [14-1] xid:0 ip:::1(46006) coswin@coswin STATEMENT:  insert into ora_coswin.t_table1(frt1_code,frt1_creation_date,frt1_description,frt1_entity,frt1_last_update,frt1_ole_counter,frt1_remarks,pk_table1,timestamp) select frt1_code,frt1_creation_date,frt1_description,frt1_entity,frt1_last_update,frt1_ole_counter,frt1_remarks,pk_table1,timestamp from t_table1;

but it works if I try to insert a single precise row.
I saw that I should use read_committed isolation level but I don't know how to check if there is a risk of inconsistency (the "check your execution plans if the foreign scan could be executed more than once part" from your advice).
I saw your comments about

from oracle_fdw.

laurenz commented on August 14, 2024

That is probably another instance of Oracle's shoddy implementation of the SERIALIZABLE isolation level: when an INSERT causes an index page split, you'll get a serialization error.

The workaround would be isolation_level = 'read_committed'. For the INSERT, that would never be a problem. For queries, it depends: if the foreign scan is on the inner side of a nested loop join or used in a parallel query, and there are concurrent data modifications on the Oracle side, there is a certain risk of seeing inconsistent data. You could show me an execution plan that you have doubts about, and I can tell you if you are at risk.

from oracle_fdw.

adrianboangiu commented on August 14, 2024

I have checked and it worked for this table. I still have some other +800 tables.
But all my statements are of the following type:
insert into foreign_schema.table( col1, ..., coln ) select col1, ..., coln from pg_schema.table; and for the actual statement the plan is

 Insert on t_table1  (cost=0.00..37.85 rows=710 width=3023)
   Oracle statement: INSERT INTO "COSWIN"."T_TABLE1" ("PK_TABLE1", "TIMESTAMP", "FRT1_CODE", "FRT1_DESCRIPTION", "FRT1_LAST_UPDATE", "FRT1_CREATION_DATE", "FRT1_REMARKS", "FRT1_OLE_COUNTER", "FRT1_ENTITY") VALUES (:p1, :p2, :p3, :p4, CAST (:p5 AS TIMESTAMP), CAST (:p6 AS TIMESTAMP), :p7, :p8, :p9)
   ->  Seq Scan on t_table1 t_table1_1  (cost=0.00..37.85 rows=710 width=3023)
(3 rows)

So I guess I will have the same plan no matter how "complex" a table is and how many rows it has.

from oracle_fdw.

laurenz commented on August 14, 2024

Yes, if all your statements are like that, you are safe: there is no join (let alone a nested loop join with a foreign table on the inner side), and INSERT never uses parallel query.

from oracle_fdw.

adrianboangiu commented on August 14, 2024

Thank you very much.
oracle_fdw proved to be "a must use" again. I don't want to tell you how I am doing the transfers Oracle <--> SQL Server. I didn't want to go in the same direction with the transfers Postgres --> Oracle/SQL Server

from oracle_fdw.

Conversion between Oracle DATE and PostgreSQL TIMESTAMP about oracle_fdw HOT 9 CLOSED

Comments (9)

Related Issues (20)

Recommend Projects

React

Vue.js

Typescript

TensorFlow

Django

Laravel

D3

Recommend Topics

javascript

web

server

Machine learning

Visualization

Game

Recommend Org

Facebook

Microsoft

Google

Alibaba

D3

Tencent