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

[Guillaume Lelarge <guillaume@xxxxxxxxxxxx>]

Hi,

Le ven. 10 juin 2022, 12:40, Pascal CROZET <pascal.crozet@xxxxxxxxxxxx> 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

https://dalibo.com