search

laurenz / oracle_fdw

PostgreSQL Foreign Data Wrapper for Oracle
http://laurenz.github.io/oracle_fdw/
Other
496 stars 156 forks source link

cannot set prefetch to 10240 in oracle_fdw 2.7.0 #704

Closed checpg closed 3 months ago

checpg commented 3 months ago

Hi, i am using ora_migrator with oracle_fdw. Had oracle_fdw 2.6, dropped it and re-installed it with version 2.7 to be able to use a larger prefetch option for foreign tables:

doing the following steps with ora_migrator:

SELECT db_migrate_prepare( plugin => 'ora_migrator', server => 'ora123', only_schemas => '{SCHEMA1}' );

select db_migrate_mkforeign( plugin => 'ora_migrator', server => 'ora123' );

Afterwards i want to adjust the prefetch for the large tables: alter foreign table schema1.table1 options (prefetch '10240'); but i am getting: ERROR: invalid value for option "prefetch" HINT: Valid values in this context are integers between 1 and 1000.

versions used: SELECT oracle_diag('ora123');

oracle_fdw 2.7.0, PostgreSQL 15.7, Oracle client 19.23.0.0.0, Oracle server 19.0.0.0.0

laurenz commented 3 months ago

In oracle_fdw 2.6.0 and 2.7.0, the limit for prefetch is 1000.

I changed the limit back to 10240 in commit 13ac9fb4e5ca5a515062b2c441d53079b0ec814d, so you need the latest development version for that. Be warned that a huge limit like that will use lots of memory and is unlikely to improve the performance.

checpg commented 3 months ago

many thanks for the fast reply. I misunderstood it and thought it is in 2.7.0 . I believe i will keep it then to 1000 instead of using the latest development version as this improved already the performance a lot compared to the default of 50.