REFRESH MATERIALIZED VIEW error
Greetings,
After an OS upgrade from CentOS 7 to RHEL 9.2, our Materialized Views are not getting refreshed. We are receiving this error:
From log...
2023-10-04 00:04:01.885 EDT [235717] ERROR: could not access file "$libdir/file_fdw": No such file or directory
2023-10-04 00:04:01.885 EDT [235717] CONTEXT: SQL statement "REFRESH MATERIALIZED VIEW xxxxxx_vw with data" PL/pgSQL function refresh_xxxxxx_mvw() line 3 at SQL statement
2023-10-04 00:04:01.885 EDT [235717] STATEMENT: select refresh_xxxxxx_mvw();
From console...
dbname=> REFRESH MATERIALIZED VIEW xxxxxx_vw;
ERROR: could not access file "$libdir/file_fdw": No such file or directory
There is a file_fdw in the database...
dbname=> select * from pg_foreign_data_wrapper;
oid | fdwname | fdwowner | fdwhandler | fdwvalidator | fdwacl | fdwoptions
-------+----------+----------+------------+--------------+--------+------------
16399 | file_fdw | 10 | 16397 | 16398 | |
(1 row)
But none in $libdir:
bash-5.1$ /usr/pgsql-12/bin/pg_config --libdir
/usr/pgsql-12/lib
bash-5.1$ cd /usr/pgsql-12/lib
bash-5.1$ ls -la file_fdw
ls: cannot access 'file_fdw': No such file or directory
We recreated the Materialized view but was still not able to refresh the Materialized View - Same Error.
How did the OS upgrade change the way the REFRESH MATERIALIZED VIEW executed, and how do we get the statement to find file_fdw?
TIA, Bob
"Given, Robert A" <bgiven@bu.edu> writes:
After an OS upgrade from CentOS 7 to RHEL 9.2, our Materialized Views are not getting refreshed. We are receiving this error:
From log...
2023-10-04 00:04:01.885 EDT [235717] ERROR: could not access file "$libdir/file_fdw": No such file or directory
You didn't install the file_fdw.so library that underlies the file_fdw
extension.
regards, tom lane
Thank you Tom! We downloaded the file_fdw.so library and recompiled the Materialized views and the application is running normally.
Thank you for your insight!
Bob
-----Original Message-----
From: Tom Lane <tgl@sss.pgh.pa.us>
Sent: Wednesday, October 4, 2023 5:04 PM
To: Given, Robert A <bgiven@bu.edu>
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: REFRESH MATERIALIZED VIEW error
"Given, Robert A" <bgiven@bu.edu> writes:
After an OS upgrade from CentOS 7 to RHEL 9.2, our Materialized Views are not getting refreshed. We are receiving this error:
From log...
2023-10-04 00:04:01.885 EDT [235717] ERROR: could not access file
"$libdir/file_fdw": No such file or directory
You didn't install the file_fdw.so library that underlies the file_fdw extension.
regards, tom lane