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

From: Pascal CROZET <pascal(dot)crozet(at)metanext(dot)com>
To: pgsql-admin <pgsql-admin(at)postgresql(dot)org>
Subject: ERROR: type "my_user_type" does not exist on REFRESH MATERIALIZED VIEW
Date: 2022-06-09 19:52:53
Message-ID: PAXP251MB01782EE9FF9A844E7157F52698A79@PAXP251MB0178.EURP251.PROD.OUTLOOK.COM
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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

In response to

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Tim 2022-06-09 21:05:28 Re: pg_basebackup and pg_receivewal timing, missing WAL files
Previous Message Stephen Frost 2022-06-09 18:36:41 Re: pg_basebackup and pg_receivewal timing, missing WAL files