pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedERROR: type "my_user_type" does not exist on REFRESH MATERIALIZED VIEW
4+ messages / 4 participants
[nested] [flat]
* ERROR: type "my_user_type" does not exist on REFRESH MATERIALIZED VIEW
@ 2022-06-09 19:52 Pascal CROZET <pascal.crozet@metanext.com>
2022-06-10 11:28 ` Re: ERROR: type "my_user_type" does not exist on REFRESH MATERIALIZED VIEW Guillaume Lelarge <guillaume@lelarge.info>
2022-06-10 15:57 ` Re: ERROR: type "my_user_type" does not exist on REFRESH MATERIALIZED VIEW Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 2 replies; 4+ messages in thread
From: Pascal CROZET @ 2022-06-09 19:52 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: ERROR: type "my_user_type" does not exist on REFRESH MATERIALIZED VIEW
2022-06-09 19:52 ERROR: type "my_user_type" does not exist on REFRESH MATERIALIZED VIEW Pascal CROZET <pascal.crozet@metanext.com>
@ 2022-06-10 11:28 ` Guillaume Lelarge <guillaume@lelarge.info>
1 sibling, 0 replies; 4+ messages in thread
From: Guillaume Lelarge @ 2022-06-10 11:28 UTC (permalink / raw)
To: Pascal CROZET <pascal.crozet@metanext.com>; +Cc: pgsql-admin
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
https://dalibo.com
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: ERROR: type "my_user_type" does not exist on REFRESH MATERIALIZED VIEW
2022-06-09 19:52 ERROR: type "my_user_type" does not exist on REFRESH MATERIALIZED VIEW Pascal CROZET <pascal.crozet@metanext.com>
@ 2022-06-10 15:57 ` Laurenz Albe <laurenz.albe@cybertec.at>
2022-06-10 16:15 ` Re: ERROR: type "my_user_type" does not exist on REFRESH MATERIALIZED VIEW Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 4+ messages in thread
From: Laurenz Albe @ 2022-06-10 15:57 UTC (permalink / raw)
To: Pascal CROZET <pascal.crozet@metanext.com>; pgsql-admin
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: ERROR: type "my_user_type" does not exist on REFRESH MATERIALIZED VIEW
2022-06-09 19:52 ERROR: type "my_user_type" does not exist on REFRESH MATERIALIZED VIEW Pascal CROZET <pascal.crozet@metanext.com>
2022-06-10 15:57 ` Re: ERROR: type "my_user_type" does not exist on REFRESH MATERIALIZED VIEW Laurenz Albe <laurenz.albe@cybertec.at>
@ 2022-06-10 16:15 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2022-06-10 16:15 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: Pascal CROZET <pascal.crozet@metanext.com>; pgsql-admin
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2022-06-10 16:15 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-06-09 19:52 ERROR: type "my_user_type" does not exist on REFRESH MATERIALIZED VIEW Pascal CROZET <pascal.crozet@metanext.com>
2022-06-10 11:28 ` Re: ERROR: type "my_user_type" does not exist on REFRESH MATERIALIZED VIEW Guillaume Lelarge <guillaume@lelarge.info>
2022-06-10 15:57 ` Laurenz Albe <laurenz.albe@cybertec.at>
2022-06-10 16:15 ` Re: ERROR: type "my_user_type" does not exist on REFRESH MATERIALIZED VIEW Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox