REFRESH MATERIALIZED VIEW error

From: "Given, Robert A" <bgiven(at)bu(dot)edu>
To: "pgsql-bugs(at)lists(dot)postgresql(dot)org" <pgsql-bugs(at)lists(dot)postgresql(dot)org>
Cc: "Given, Robert A" <bgiven(at)bu(dot)edu>
Subject: REFRESH MATERIALIZED VIEW error
Date: 2023-10-04 17:46:10
Message-ID: DM6PR03MB3497453D31E1A5B65EA6C2ABA6CBA@DM6PR03MB3497.namprd03.prod.outlook.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Thomas Munro 2023-10-04 21:12:27 Re: BUG #18146: Rows reappearing in Tables after Auto-Vacuum Failure in PostgreSQL on Windows
Previous Message Tom Lane 2023-10-04 14:24:01 Re: BUG #18146: Rows reappearing in Tables after Auto-Vacuum Failure in PostgreSQL on Windows