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
