Thread: pg_upgrade and missing loadable libraries
Hi Postgresql team,
cat loadable_libraries.txt
I have noticed pg_upgrade and missing loadable libraries error. A request to provide a solution to fix it.
This is Ubuntu (18.04), Upgrade from 9.5 to 13.7 version.
Error received during pre-checks of upgrade:
"Checking for presence of required libraries: fatal
Your installation references Loadable that are missing from the new installation. You can add these libraries to the new installation, or remove the functions using them from the old installation. A list of problem libraries is in the file
Your installation references Loadable that are missing from the new installation. You can add these libraries to the new installation, or remove the functions using them from the old installation. A list of problem libraries is in the file
loadable_libraries.txt
cat loadable_libraries.txt
could not load library "$libdir/pgpool-recovery": Error: could not access file "$libdir/pgpool-recovery": no such file or directory.
Please help
Kind regards,
Arun Maddukuri
On Thu, Jun 9, 2022 at 9:44 AM Arun Maddukuri <arun.maddukuri07@gmail.com> wrote:
I have noticed pg_upgrade and missing loadable libraries error. A request to provide a solution to fix it.
You were given two:
You can add these libraries to the new installation, or remove the functions using them from the old installation.
David J.
Hi DBA's (PG 12.10 on Ubuntu 20.04)
In a restoration from a full plain text database ( xzcat my_database.sql.xz | psql ), I've this issue at the end.
REFRESH MATERIALIZED VIEW
END
ERROR: type "row_reporting_p" does not exist
LINE 3: _row row_reporting_p;
^
QUERY:
DECLARE
_row row_reporting_p;
BEGIN
-- my code
END
CONTEXT: compilation of PL/pgSQL function "get_row_reporting_new" near line 3
ERROR: type "row_reporting_p" does not exist
LINE 23: _row row_reporting_p;
^
QUERY:
DECLARE
_row row_reporting_p;
BEGIN
-- my code
CONTEXT: compilation of PL/pgSQL function "fs_get_row_reporting" near line 23
Of course, the user type is present and well restored before, 'cause the last SQL commands from the file are for REFRESH MATERIALZED VIEW vm_my_materialized_view;
When I refresh the materialized view manually, from psql, the refresh is well. This issue is only present on databases using user type. On other databases restoration, not using user type, I haven't this isssue.
Is it a PostGreSql bug ? Thanks
_________________________________
Cordialement, Pascal CROZET
Re: ERROR: type "my_user_type" does not exist on REFRESH MATERIALIZED VIEW
From
Guillaume Lelarge
Date:
Hi,
Le ven. 10 juin 2022, 12:40, Pascal CROZET <pascal.crozet@metanext.com> a écrit :
Hi DBA's (PG 12.10 on Ubuntu 20.04)In a restoration from a full plain text database ( xzcat my_database.sql.xz | psql ), I've this issue at the end.REFRESH MATERIALIZED VIEWERROR: type "row_reporting_p" does not existLINE 3: _row row_reporting_p;^QUERY:DECLARE_row row_reporting_p;BEGIN-- my codeENDCONTEXT: compilation of PL/pgSQL function "get_row_reporting_new" near line 3ERROR: type "row_reporting_p" does not existLINE 23: _row row_reporting_p;^QUERY:DECLARE_row row_reporting_p;BEGIN-- my codeENDCONTEXT: compilation of PL/pgSQL function "fs_get_row_reporting" near line 23Of course, the user type is present and well restored before, 'cause the last SQL commands from the file are for REFRESH MATERIALZED VIEW vm_my_materialized_view;When I refresh the materialized view manually, from psql, the refresh is well. This issue is only present on databases using user type. On other databases restoration, not using user type, I haven't this isssue.Is it a PostGreSql bug ? Thanks
I don't think so. Pretty sure you need to qualify the type's name with its schema's name.
--
Guillaume
On Thu, 2022-06-09 at 19:52 +0000, Pascal CROZET wrote: > Hi DBA's (PG 12.10 on Ubuntu 20.04) > > In a restoration from a full plain text database ( xzcat my_database.sql.xz | psql ), I've this issue at the end. > > REFRESH MATERIALIZED VIEW > ERROR: type "row_reporting_p" does not exist > LINE 3: _row row_reporting_p; > ^ > QUERY: > DECLARE > _row row_reporting_p; > > BEGIN > > -- my code > > END > > CONTEXT: compilation of PL/pgSQL function "get_row_reporting_new" near line 3 > ERROR: type "row_reporting_p" does not exist > LINE 23: _row row_reporting_p; > > Of course, the user type is present and well restored before, 'cause the last SQL commands > from the file are forREFRESH MATERIALZED VIEW vm_my_materialized_view; > > When I refresh the materialized view manually, from psql, the refresh is well. > This issue is only present on databases using user type. On other databases restoration, > not using user type, I haven't this isssue. > > Is it a PostGreSql bug ? Thanks I would have to see the dump to be certain, but my guess is that you need to make the function independent from the current setting of "search_path" by schema-qualifying the data type: DECLARE _row my_schema.row_reporting_p; Yours, Laurenz Albe
Laurenz Albe <laurenz.albe@cybertec.at> writes: > I would have to see the dump to be certain, but my guess is that you need to make the function > independent from the current setting of "search_path" by schema-qualifying the data type: > DECLARE > _row my_schema.row_reporting_p; Yeah, that seems likely to be the explanation. The restore will be run under a restrictive search_path and functions that are not ready for that will fail. It may well be that this isn't the only search_path dependency in the function, in which case you might be better advised to add a "SET search_path" option to the function, instead of trying to fix all the references manually. regards, tom lane