Message-ID: <ff953d5f710168f2af17d510a847d079e94a98c7.camel@cybertec.at>
Subject: Re: ERROR:  type "my_user_type" does not exist on REFRESH
 MATERIALIZED VIEW
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Pascal CROZET <pascal.crozet@metanext.com>, pgsql-admin
	 <pgsql-admin@postgresql.org>
Date: Fri, 10 Jun 2022 17:57:04 +0200
In-Reply-To: 
 <PAXP251MB01782EE9FF9A844E7157F52698A79@PAXP251MB0178.EURP251.PROD.OUTLOOK.COM>
References: 
	<CAH59SE=GKvZxWa++n+D33GV7xk1pyYu=jYzCtsJnXNsdaSSb=w@mail.gmail.com>
	 <CAKFQuwZoK=gZ_n48djihvgFhgTC4o0Popu--Yak-ntnsTP5DKg@mail.gmail.com>
	 <PAXP251MB01782EE9FF9A844E7157F52698A79@PAXP251MB0178.EURP251.PROD.OUTLOOK.COM>
Content-Type: text/plain; charset="UTF-8"
User-Agent: Evolution 3.42.4 (3.42.4-2.fc35) 
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/ff953d5f710168f2af17d510a847d079e94a98c7.camel%40cybertec.at>
Precedence: bulk

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