Conversion between Oracle DATE and PostgreSQL TIMESTAMP (lost time part)

[Zoynels]

Hello,

We have an application that have 2 databases: Oracle and Postgres, just like https://github.com/laurenz/oracle_fdw/issues/661. Using oracle_fdw we successfully transferred Oracle table into Postgres (we only read them). But noticed that the fields with type date in oracle which has date+time (2023-03-06 09:12:59) transfered into postgres only as date part (time part is loss) (2023-03-06 00:00:00). In postgres it has type as timestamp (tries different types, also text then 06/03/23).

When we connect directly to oracle (without postgres) then we see date+time (2023-03-06 09:12:59). So we define that user has permission for this fields and via dbeaver and python can connect and view full information.

We try (prod linux): oracle_fdw 2.5.0devel, PostgreSQL 14.10, Oracle client 19.16.0.0.0, Oracle server 19.0.0.0.0 On dev (windows 10): oracle_fdw 2.7.0devel, PostgreSQL 16.2, Oracle client 21.8.0.0.0, Oracle server 19.0.0.0.0 (latest commit)

[Zoynels]

SET LOCAL client_min_messages = debug2;
SELECT C_DATE_CHANGE FROM public.f_git WHERE id = 123;

debug:

oracle_fdw: plan foreign table scan
oracle_fdw: set NLS_LANG=RUSSIAN_RUSSIA.AL32UTF8
oracle_fdw: begin remote transaction
oracle_fdw: remote query is: SELECT /*c4c12a62*/ r1."ID", r1."C_DATE_CHANGE" FROM "TEST"."Z#GIT" r1 WHERE (r1."ID" = 123)
oracle_fdw: begin foreign table scan on 106235622
oracle_fdw: execute query in foreign table scan 
oracle_fdw: end foreign table scan
oracle_fdw: commit remote transaction

I renamed schema and table name.

[laurenz]

That should not happen. Can you provide the following for me to reproduce the problem:

1. the CREATE TABLE statement in Oracle

2. an INSERT statement for the Oracle table to produce a sample row

3. the CREATE FOREIGN TABLE statement in PostgreSQL

[Zoynels]

another table with less columns:

CREATE TABLE PUBLIC."Z#BCD_STATE" (
    ID NUMBER NOT NULL,
    CLASS_ID VARCHAR2(16) NULL,
    C_DOC_DATE DATE NULL,
    C_TEXT VARCHAR2(2000) NULL,
    SN NUMBER NULL,
    SU NUMBER NULL,
    CONSTRAINT "PK_Z#BCD_STATE_ID" PRIMARY KEY (ID),
    CONSTRAINT "FK_Z#BCD_STATE_ID" FOREIGN KEY (ID) REFERENCES PUBLIC."Z#BC_DOC"(ID)
);
CREATE UNIQUE INDEX "PK_Z#BCD_STATE_ID" ON PUBLIC."Z#BCD_STATE" (ID);

INSERT INTO PUBLIC."Z#BCD_STATE"
(ID, CLASS_ID, C_DOC_DATE, C_TEXT, SN, SU)
VALUES(20913200327, '', to_date('2023-12-19 16:23:20', 'YYYY-MM-DD HH24:MI:SS'), 'test', 0, 0);

CREATE FOREIGN TABLE public.f_bcd_state (
    ID int8 NOT NULL,
    CLASS_ID text NULL,
    C_DOC_DATE timestamp NULL,
    C_TEXT text NULL,
    SN INT8 NULL,
    SU INT8 NULL
)
SERVER ora_srv
OPTIONS (SCHEMA 'PUBLIC', table 'Z#BCD_STATE', readonly 'true');

[laurenz]

I tried your example and queried with psql:

TABLE f_bcd_state;
     id      │ class_id │     c_doc_date      │ c_text │ sn │ su 
═════════════╪══════════╪═════════════════════╪════════╪════╪════
 20913200327 │ ∅        │ 2023-12-19 16:23:20 │ test   │  0 │  0
(1 row)

So that is working fine. Perhaps you are using a client that doesn't display timestamp values properly?

[Zoynels]

We tried with dev-server -- there we can see time part. On prod-server -- no time part. We try dbeaver and python. All of them display postgres tables with timestamp as normal date+time. When we set text as type for this column, then it also display 06/03/23 (only date without time).

[laurenz]

Try to spot the difference between the servers, in particular the foreign table definitions. If I cannot reproduce the problem, I cannot fix it.

[Zoynels]

We created fdw for query in oracle SELECT * FROM NLS_SESSION_PARAMETERS; And we understood, that oracle do not change e.i. NLS_DATE_FORMAT=YYYY-MM-DD HH24:MI:SS BC and other parameters (in function setOracleEnvironment ). We don't understand why this happens and how to repair normal behavior. So we decide to recreate user (we create new user on same server and all works fine).

It will be good if you add check that after change parameters, its was really changed.

[laurenz]

Ah, I think I have a clue. There was probably a logon trigger defined that messed up NLS_TIMESTAMP_FORMAT.

[laurenz]

Another possibility is that the data are transferred correctly, but the client you use to look at the PostgreSQL data doesn't display everything. Did you manage to figure out what is going on here?

[Zoynels]

We can't understand why NLS_* parameters is not changed with env for this user. So our DBA create new user and drop old. After this all work as expected.

[laurenz]

Thanks for the feedback. So I guess we should close the issue, because it will be hard to figure out the cause after the Oracle user has been dropped.