laurenz
commented
5 days ago Your problem report is very confused. You complain about orafce (which is unrelated to oracle_fdw), and you don't even tell me how you tried to upgrade.
Please describe what exactly you did, which version of oracle_fdw you have before and after the upgrade (use oracle_diag() to find the version; the extension version is not significant), and please show the complete, unmodified error message.
Yes, the limit for the prefetch option was reduced to 1000, but I undid that change again in commit 13ac9fb4e5ca5a515062b2c441d53079b0ec814d because of popular demand. So you could use the latest development version of oracle_fdw, and you should avoid that error.
Still, my recommendation is to lower prefetch to a more reasonable value before you upgrade. 10000 is crazy high.
Post-User2024
We have updated below extension on PostgreSQL 11 before proceeding the PostgreSQL 15 upgrade.
On PostgreSQL 11
Oracle FDW - 1.1 To 1.2 orafce - 3.7 To 4.9
While updating the orafce for one B-database getting below error. For other A-database it was updated successfully.
A - Database:
orafce - 4.9
B-database
orafce - 3.7 <<<<<<<< Unable to update the extension.
alter extension orafce update; ERROR: type "oracle.date" does not exist
drop extension orafce; ERROR: cache lookup failed for type XXXXXXX
drop extension orafce restrict; ERROR: cache lookup failed for type XXXXXXX
drop extension orafce cascade; ERROR: cache lookup failed for type XXXXXX
Since there was no update on the extension we took once to initialize the upgrade with dry run it went fine there was no issue with dry run.
When we start the actual upgrade it got failed with below error.
pg_restore: error: could not execute query: ERROR: invalid value for option "prefetch" HINT: Valid values in this context are integers between 1 and 1000.
CREATE FOREIGN TABLE XXXXXX()server "server_name" OPTIONS ( "prefetch" '10000',
_When we checked "prefetch" value form pgtype it was null
select oid,typname from pg_type where typname='prefetch'; oid | typname -----+--------- (0 rows)
_Can you please advise since prefetch value was null our upgrade was failed (or) orafce version 3.7 incompatible to the PG15 which is on B-database creating this problem?
Can we try as per the HINT to update the binary upgrade set values as below?_
-- For binary upgrade, must preserve pg_type oid SELECT pg_catalog.binary_upgrade_set_next_pg_type_oid('14535618'::pg_catalog.oid);
-- For binary upgrade, must preserve pg_type array oid SELECT pg_catalog.binary_upgrade_set_next_array_pg_type_oid('14535617'::pg_catalog.oid);
-- For binary upgrade, must preserve pg_class oids and relfilenodes SELECT pg_catalog.binary_upgrade_set_next_heap_pg_class_oid('14535616'::pg_catalog.oid); SELECT pg_catalog.binary_upgrade_set_next_heap_relfilenode('14535616'::pg_catalog.oid);