oracle_fdw cannnot load clobs larger than 21kbytes

sabineWalter commented 9 months ago

Hello,

create a small table in oracle as `drop table clob_sizes_2 /

create table clob_sizes_2 (ID int generated always as identity, myClob clob);

declare myText clob := ''; begin for i in 1 .. 1000 loop myText := concat(myText, lpad(1,1024,'0')); insert into clob_sizes_2 (myClob) values (myText); end loop; commit; end; / commit;

alter table clob_sizes_2 add constraint p_id primary key (id)



Setup in PostgreSQL 

`CREATE EXTENSION oracle_fdw;
CREATE SERVER newbisdev FOREIGN DATA WRAPPER oracle_fdw  OPTIONS (dbserver '//10.162.75.236:1521/NEWBISTEST');
GRANT USAGE ON FOREIGN SERVER newbisdev TO postgres;
CREATE USER MAPPING FOR postgres SERVER newbisdev OPTIONS (user 'EMATRIX', password 'xxxxx');

CREATE FOREIGN TABLE ft_clog_sizes_2 ( 
  id     integer NOT NULL,
  myclob text ) 
SERVER newbisdev 
OPTIONS (schema 'EMATRIX', table 'CLOB_SIZES_2', 
         readonly 'yes', 
         sample_percent '5', lob_prefetch '8388608');
`

Trying to select the myClob column in PostgreSQL 
a) if length(myClob) < 21900: everythink works fin
b) if length(myClob) > 21900: I get en empty clob from oracle_fdw

`pocnewbis=#  select length(myclob) from ft_clog_sizes_2 where id=20;
 length
--------
  20480
(1 row)

pocnewbis=#  select length(myclob) from ft_clog_sizes_2 where id=21;
 length
--------
  21504
(1 row)

pocnewbis=#  select length(myclob) from ft_clog_sizes_2 where id=22;
 length
--------
      0
(1 row)`

pls help me

I'm using PostgreSQL 14.10
Oracle DB is of Version = 19.20

`                          oracle_diag
---------------------------------------------------------------
 oracle_fdw 2.6.0, PostgreSQL 14.10, Oracle client 21.11.0.0.0
(1 row)
`

Regards

laurenz commented 9 months ago

@philflorent Thanks for testing, that matches what I observe. Are there any pertinent bug reports in the Oracle knowledge base for version 21?

philflorent commented 9 months ago

OK I will have a look but 21c seriously ?

"Yes, Oracle 23 is only available as "Free" release so far (well, it's only 2024, so why hurry). I looked at the page where they advertise their new features, and it's things like a boolean data type and CREATE/DROP IF [NOT] EXISTS :^)"

Very "modern" features indeed.

Best regards, Phil

sabineWalter commented 9 months ago

@laurenz

do you have a client version 23c ? yes, I can open a ticket at Oracle Support

I agree to @philflorent provided some helpful information. as database version you can focus on 19c, 21c will be out of support at about Q2/2024, 18c will not be used and all all older version are out of support Maybe you can pick 12.1 and 11.2.0.4 to increase list of supported database version 23c will be delivered in some months (when it is not for free)

what about the supported oracle client versions on postgres server?

did you install oracle 23c free full database software to have a 23c client ?
why do I get the errors using 21.13, 21.12 or 21.11 oracle instant client ?
using oracle instant client 19.21: all tests are passed

philflorent commented 9 months ago

Many bugs with this function, including wrong results ones but nothing specific to 21c. Function was probably modified for JSON type introduced in 21c and this is interesting:

-- content removed --

Is this related ? Was this bug already present in 21c and still present since "fix inclusion to earlier releases is pending" ? Possible.

Best regards, Phil

philflorent commented 9 months ago

@sabineWalter

what about the supported oracle client versions on postgres server?

Laurenz cannot support Oracle software. Theoretically any Oracle client compatible with your Oracle Database should work. Perhaps an advice could be added about recommended versions, the LTR ones (11.2.0.4, 19c, 23c) but it's not really the place for that.

Oracle Client 21c => Oracle Database 19c-23c is OK but it seems it does not work and I just wanted to point that 21c is an "innovation" release, not to be used in (critical) production. In fact I would not use it at all unless you are a paid beta tester. It's a bit strange to pay to test in fact. At least PostgreSQL community does not ask me to pay and they even send me cool coins for my tests.

Best regards, Phil

sabineWalter commented 9 months ago

@philflorent do you know the bug number ? => to get some information about history of this bug ?

It is be possible (often seen), that oracle will create socalled backport patches patches designed for port =23.c and another patch (with the same name/number) for an existing port like = 19c => ealier releases is pending": this bug occurred in earlier releases, but LTS releases will get a patch first ...

laurenz commented 9 months ago

@philflorent Thanks, that seems to be spot on. With that, I'll gladly stop worrying about it being my fault.

@sabineWalter I installed the complete Oracle 23 server on my test machine; there is no separate client installation (yet). With Oracle 21, I get the empty LOB symptom too. You don't have to open a service request with Oracle; I am satisfied that we can write off Oracle 21.

philflorent commented 9 months ago

@sabineWalter MOS note is 2954929. I am not sure it is related since it's a Windows, 23c, JSON bug. But I am puzzled by the symptoms and the explanations. If you really want to make oracle-fdw work with LOB and a 21c client, you can open a SR since 21c is under premier support.

@laurenz I will make further tests with 19c <=> 11.2.0.3-4 / 19c combos, I failed to reproduce the issue without the fix and I need to be 100% sure it works. Perhaps things work differently with basicfile and securefile lobs ,same name but very different types.

sabineWalter commented 9 months ago

@philflorent

21c is a "innovation" release: the database software: YES the client software: no

but will ask some guys @ oracle directly

and yes, I do expect any support by @laurenz for oracle software

for me: this issue is solved.

My Issue is

using Oracle client 19c: all tests passed
I must install Oracle client >= 21.11 => all tests failed

=> therefore I will open a Ticket at Oracle to get either a 23c client => or a patched Oracle client 21.2x

Question should I open another issue ? or track by myself ?

sabineWalter commented 9 months ago

@philflorent MOS note is 2954929. = OCILocRead2() Hangs On Windows When Reading Large JSON Into SQLT_CLOB (Doc ID 2954929.1)

Applies to: Oracle Database - Standard Edition - Version Beta 23c and later => not to 19c , not to 21c

Symptoms: The same code works on Linux.

I'm not sure if this Document realy matches

laurenz commented 9 months ago

@philflorent Yes, this bug is not reproducible everywhere. On my laptop, where I have Oracle 18 XE and develop oracle_fdw, the problem never manifested, so I couldn't reproduce #614, which is the same bug for sure.

@sabineWalter, I won't keep you from opening a service request, but I doubt that it will lead anywhere. My little test program, when fixed like I did oracle_fdw, works like it should with Oracle Client 21. Without a reproducer, Oracle won't lift a finger.

So yes, some doubt remains as to the correctness of oracle_fdw.

I'll merge my fix into the master branch, which will auto-close this issue, but I will leave it open for further comments if you find anything interesting.

philflorent commented 9 months ago

@sabineWalter

"21c is a "innovation" release: the database software: YES the client software: no"

It's 21c that is an innovation release, client software is just a subset of the database software and they are EOL at the same date. When 21c will be EOL you will see that any combo including 21c client or 21c server will immediately be mentioned as "WAS supported" in note 207303.1. You can check that with another innovation release, 18c it's currently NO or WAS everywhere, client and server are not considered differently in terms of support.

"I must install Oracle client >= 21.11 => all tests failed"

I don't get why you really want 21c but it's up to you to open a SR since it's under premier support. That said you mentioned ora2pg and I am 100% sure it does not require 21c client.

Best regards, Phil

philflorent commented 9 months ago

@sabineWalter

"I'm not sure if this Document realy matches"

Same here. I am not sure it's related. It just seems to indicate this function was modified for JSON type support which is new in 21c. I won't investigate further with 21c anyway...

philflorent commented 9 months ago

"client and server are not considered differently in terms of support."

Just rechecked note 207303.1 to be sure and the critical part just above the client-server interoperability matrix is : "New interoperability problems will only be investigated if BOTH releases involved are covered by a valid support contract at the time that the issue is reported." No distinction between client and server software for Oracle, 21c "client" won't be fixed any longer than 21c "database", 21c is globally a short-life innovation release. => Hence I would choose a LTR instant client with oracle-fdw exactly as I choose a LTR for Orcl db.

sabineWalter commented 9 months ago

@laurenz https://github.com/laurenz/oracle_fdw/issues/643#issuecomment-1934925068 and https://github.com/laurenz/oracle_fdw/issues/643#issuecomment-1936081777

I tested oracle_fdw with oracle client 21 => did not work different error messages

=> worked fine only compiled with 19 client

laurenz commented 9 months ago

Right, so we all observe the same behavior. I have added some documentation.

laurenz commented 5 months ago

Please open a new issue.

laurenz / oracle_fdw

oracle_fdw cannnot load clobs larger than 21kbytes #643