Обсуждение: pg_upgrade and missing loadable libraries

Поиск
Список
Период
Сортировка

pg_upgrade and missing loadable libraries

От
Arun Maddukuri
Дата:
Hi Postgresql team,

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

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
 

Re: pg_upgrade and missing loadable libraries

От
"David G. Johnston"
Дата:
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.

ERROR: type "my_user_type" does not exist on REFRESH MATERIALIZED VIEW

От
Pascal CROZET
Дата:
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;
                  ^
QUERY:  

DECLARE
    _row row_reporting_p;
BEGIN

    -- my code
    
END
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

От
Guillaume Lelarge
Дата:
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 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;
                  ^
QUERY:  

DECLARE
    _row row_reporting_p;
BEGIN

    -- my code
    
END
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

I don't think so. Pretty sure you need to qualify the type's name with its schema's name.


-- 
Guillaume

Re: ERROR: type "my_user_type" does not exist on REFRESH MATERIALIZED VIEW

От
Laurenz Albe
Дата:
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

Re: ERROR: type "my_user_type" does not exist on REFRESH MATERIALIZED VIEW

От
Tom Lane
Дата:
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