Why `\connect mydb` prevents error `function unaccent(text) does not exists`?

84 views Asked by At

I restored my database successfully on one cluster, but when I try to do same on different cluster with same custom PostgreSQL database image I got errors:

ERROR:  function unaccent(text) does not exist
LINE 2:   SELECT unaccent(coalesce($1, ''));
                 ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
QUERY:
  SELECT unaccent(coalesce($1, ''));

CONTEXT:  SQL function "str_norm" during inlining
ERROR:  relation "banners_text_search" does not exist
LINE 1: SELECT text from banners_text_search
                         ^
QUERY:  SELECT text from banners_text_search
REFRESH MATERIALIZED VIEW
ERROR:  function unaccent(text) does not exist
LINE 2:   SELECT unaccent(coalesce($1, ''));
                 ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
QUERY:
  SELECT unaccent(coalesce($1, ''));

CONTEXT:  SQL function "str_norm" during inlining

When I connected to restored database I can find unaccent function and banners_text_search relation. This is expected, because I did not encounter any errors when they were created. So problem is only with REFRESH MATERIALIZED VIEW.

I supposed that this instance is too fast, so I added \connect mydb just before REFRESH MATERIALIZED VIEW statements:

docker exec old-postgres-node bash -c "pg_dumpall --clean --if-exists -U postgres             | \
egrep -v '^(CREATE|DROP) ROLE( IF EXISTS)? postgres;'                                         | \
sed '/^DROP ROLE.*datadog/ i REVOKE ALL ON TABLE pg_catalog.pg_stat_database FROM datadog;'   | \
sed '/connect userdb/ r create-extensions.sql'                                                | \
sed '/^REFRESH MATERIALIZED VIEW public.banners_text_search;/ i \\\\\\connect mydb'           | \
psql -h 172.17.42.1 -p 5433 -U postgres -d postgres; "

Now everything goes smooth:

...
ALTER TABLE
ALTER TABLE
GRANT
You are now connected to database "userdb" as user "postgres".
REFRESH MATERIALIZED VIEW
REFRESH MATERIALIZED VIEW
REFRESH MATERIALIZED VIEW

Does anybody know what was the actual problem with REFRESH MATERIALIZED VIEW without reconnection to the database\connect mydb?

0

There are 0 answers