Upgrade From Postgresql 11 to Postgresql 15 was failed due to prefetch - oid null value.

[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 pg_type 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);

[laurenz]

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 13ac9fb 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]

Hi Laurenz,

Sorry for the confusion let me summarize the step by step what we did.

last week we tried upgrade as below.

1. Pg version 11 - Oracle FDW was 1.1 and orafce 3.7
2. Pg version 15 was installed after that orafce & oracle fdw latest version was installed using the make and make install.

then we started dry run it was success.

/usr/pgsql-15/bin/pg_upgrade --old-bindir /usr/pgsql-11/bin --new-bindir /usr/pgsql-15/bin --old-datadir /pgDATA/data --new-datadir /pgDATA/15/data --link --check
Performing Consistency Checks

Checking cluster versions ok
Checking database user is the install user ok
Checking database connection settings ok
Checking for prepared transactions ok
Checking for system-defined composite types in user tables ok
Checking for reg* data types in user tables ok
Checking for contrib/isn with bigint-passing mismatch ok
Checking for removed "abstime" data type in user tables ok
Checking for removed "reltime" data type in user tables ok
Checking for removed "tinterval" data type in user tables ok
Checking for user-defined encoding conversions ok
Checking for user-defined postfix operators ok
Checking for incompatible polymorphic functions ok
Checking for tables WITH OIDS ok
Checking for invalid "sql_identifier" user columns ok
Checking for presence of required libraries ok
Checking database user is the install user ok
Checking for prepared transactions ok
Checking for new cluster tablespace directories ok

Clusters are compatible

Then started actual upgrade it failed with below error.

/usr/pgsql-15/bin/pg_upgrade --old-bindir /usr/pgsql-11/bin --new-bindir /usr/pgsql-15/bin --old-datadir /pgDATA/data --new-datadir /pgDATA/15/data --link

pg_restore: creating FUNCTION "public.nanvl(numeric, character varying)"
pg_restore: creating FUNCTION "public.normal_rand(integer, double precision, double precision)"
pg_restore: creating FUNCTION "public.nvarchar2_transform("internal")"
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 10787; 1255 541860000 FUNCTION nvarchar2_transform("internal") abc
pg_restore: error: could not execute query: ERROR: there is no built-in function named "varchar_transform"
Command was: CREATE FUNC.

This week again we tested the upgrade as below

To fix above nvarchar2_transform issue we updated oracle fdw (From 1.1. to 1.2) and orafce (3.7 To 4.9) on PG version 11 then started upgrade again it failed and reporting the below error

Actually after install the Postgresql version 15 we have not done oracle fdw and orafce extension make && make install after upgrade will plan for the extension make && make install then update the extension on specific database.

pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 1674; 1259 14535616 FOREIGN TABLE product_full abc
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.
Command was:
-- 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);

[Post-User2024]

We used latest development version of oracle_fdw,

[Post-User2024]

@laurenz

Could you please share inputs on this issue?

[Post-User2024]

We used latest development version of oracle_fdw - 2.7.0

[Post-User2024]

select oracle_diag();
oracle_diag

oracle_fdw 2.7.0, PostgreSQL 11.21, Oracle client 18.3.0.0.0,

[laurenz]

I understand the problem now, thanks.

The nvarchar_transform error must be from orafce. It plays no role here.

The error about prefetch above 1000 actually is an oracle_fdw problem. I didn't think of the dump/restore case.

The solution is to use the latest development version. That is not 2.7.0, but this.

[Post-User2024]

Thanks for the quick reply.

alter extension orafce update; --- failed to update on PostgreSQL V11
ERROR: type "oracle.date" does not exist

can we ignore above ERROR and proceed for the upgrade-15 please advise.

[laurenz]

I don't know, and I won't investigate an orafce error. I have nothing to do with orafce.

[Post-User2024]

Could you please advise who can help us on the orafce update issue?

[laurenz]

Sure, that project is here. You can open an issue there.

[Post-User2024]

Thank you so much. Opened an issue.

[laurenz]

Please let me know when we can close this issue.

[Post-User2024]

Could you please give me some time till this weekend?

[laurenz]

Sure, no problem at all.

[Post-User2024]

Hi Laurenz

As per your advise we updated oracle fdw before the PostgreSQL, Post that upgrade was completed successfully.
Oracle fdw 1.2 as per the master file which you shared.
orafce - 4.13

On the PostgreSQL 15 we have updated oracle fdw and orafce extension with same level extensions aswell.

Just to verify the oracle fdw and orafce ran below select queries there were no output. That means extension having any issues?

Please share your inputs.

select orafce.rownum from my_foreign_table;
ERROR: relation "my_foreign_table" does not exist
LINE 1: select orafce.rownum from my_foreign_table;

select orafce.to_char(current_date,'YYYY-MM-DD');
ERROR: schema "orafce" does not exist
LINE 1: select orafce.to_char(current_date,'YYYY-MM-DD');

select * from my_foreign_tables limit 10;
ERROR: relation "my_foreign_tables" does not exist
LINE 1: select * from my_foreign_tables limit 10;

[Post-User2024]

select oracle_diag();
oracle_diag

oracle_fdw 2.8.0devel, PostgreSQL 15.8, Oracle client 18.3.0.0.0

[Post-User2024]

select * from pg_extension;
oid | extname | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition
-----------+--------------------+----------+--------------+----------------+------------+-------------+--------------
740337946 | oracle_fdw | 10 | 2200 | t | 1.2 | |
740337953 | orafce | 10 | 2200 | f | 4.13 | {740338294} | {""}

[Post-User2024]

Hi
Could you please share your inputs.

[Post-User2024]

Can you please advise how to validate extension is working or not as expected after the upgrade?

[laurenz]

If you get the error "relation X does not exist", that may mean that the table actually does not exist, but also that it could not be found on your search_path.

You can look for the schema as follows:

BEGIN;

SET LOCAL search_path = '';

SELECT oid::regclass
FROM pg_class
WHERE lower(relname) = 'my_foreign_table';

ROLLBACK;

That should show you where the foreign table really is.

[Post-User2024]

That means PostgreSQL upgrade went fine?

[Post-User2024]

if i run the "select * from pg_foreign_table" it is listing the output of the foreign tables.

[laurenz]

If it is visible in pg_foreign_table, great.

Run SELECT ftrelid::regclass FROM pg_foreign_table; to see where the table is, then try to select from that.

If that works, I'd say that everything is fine. If you didn't get errors during the upgrade process, it should be fine.

[Post-User2024]

Thanks for the quick reply.

[Post-User2024]

Let me try that.

[Post-User2024]

Can share you view on below scenario

On PostgreSQL 11 oracle fdw update to 2.8 then after upgrade to PostgreSQL 15.
Update the oracle fdw with master zip like make && make install then connect to the respect to the database run the alter extension oracle_fdw update;

Stating that it already updated.

In case if we plan make && make install on PostgreSQL 15 for oracle fdw before upgrade what will happen is it same output (or) different while update the extension?

what is your approach on this? kindly share you inputs

[laurenz]

That's the way you should do it: install the latest oracle_fdw version on both v11 and v15.
You have to make sure that you have the PATH set so that it points to the correct pg_config, and you have to make clean in between:

PATH=/usr/pgsql-11/bin:$PATH make clean
PATH=/usr/pgsql-11/bin:$PATH make
PATH=/usr/pgsql-11/bin:$PATH make install
PATH=/usr/pgsql-15/bin:$PATH make clean
PATH=/usr/pgsql-15/bin:$PATH make
PATH=/usr/pgsql-15/bin:$PATH make install

[Post-User2024]

can we restore v11 specific database backup on the v15?

[laurenz]

You should be able to. For best results, dump the v11 database with pg_dump from v15.

Anyway, is the issue solved?

[Post-User2024]

==> You should be able to. For best results, dump the v11 database with pg_dump from v15.

Are you referring below way to restore v11 specific database on v15? Please review and suggest.

On v11:

pg_dump -U postgres -F c test_db > test_db.tar

On v15:

pg_dump -h v11 -U postgres -Ft test_db1 < test_db.tar

[Post-User2024]

Getting below error when i tried to restore the v11 database dump on v15.

Please review and advise. (orafce extension version is 4.13 on v15).

nohup: ignoring input
pg_restore: error: could not execute query: ERROR: extension "pg_hint_plan" is not available
DETAIL: Could not open extension control file "/usr/pgsql-15/share/extension/pg_hint_plan.control": No such file or directory.
HINT: The extension must first be installed on the system where PostgreSQL is running.
Command was: CREATE EXTENSION IF NOT EXISTS pg_hint_plan WITH SCHEMA hint_plan;

pg_restore: error: could not execute query: ERROR: function to_char(integer) does not exist

[laurenz]

Please stop asking about unrelated extensions like pg_hint_plan. Either install it in the new system too, or drop it in the old system before upgrading.

The second error smells of orafce.

Nothing of that has anything to do with oracle_fdw.
If you have no more oracle_fdw problems, let's close the issue.

[Post-User2024]

Before closing this issue could you please review and advise.

==> You should be able to. For best results, dump the v11 database with pg_dump from v15.

Are you referring below way to restore v11 specific database on v15? Please review and suggest.

On v11:
pg_dump -U postgres -F c test_db > test_db.tar

On v15:
pg_dump -h v11 -U postgres -Ft test_db1 < test_db.tar