Re: pg_upgrade fails saying function unaccent(text) doesn't exist

[Gunnlaugur Thor Briem <gunnlaugur@xxxxxxxxx>]

\dFd unaccent
List of text search dictionaries
 Schema |   Name   | Description
--------+----------+-------------
 public | unaccent |
(1 row)

\dFd+ unaccent
                    List of text search dictionaries
 Schema |   Name   |    Template     |    Init options    | Description
--------+----------+-----------------+--------------------+-------------
 public | unaccent | public.unaccent | rules = 'unaccent' |
(1 row)

Cheers,

Gulli

On Thu, Aug 30, 2018 at 1:48 PM Adrian Klaver <adrian.klaver@xxxxxxxxxxx> wrote:

On 08/30/2018 02:24 AM, Gunnlaugur Thor Briem wrote:
> Thank you! That got the pg_upgrade to completion. But then during
> ./analyze_new_cluster.sh vacuum fails thus:
>
> vacuumdb: processing database "dm_test": Generating minimal optimizer
> statistics (1 target)
> vacuumdb: vacuuming of database "dm_test" failed: ERROR:  text search
> dictionary "unaccent" does not exist
> CONTEXT:  SQL function "semantic_normalize" statement 1
>
> And yet a text search dictionary with that name does exist:
>
> $ psql -d dm_test -c '\dFd+ unaccent'
>                      List of text search dictionaries
>   Schema |   Name   |    Template     |    Init options    | Description
> --------+----------+-----------------+--------------------+-------------
>   public | unaccent | public.unaccent | rules = 'unaccent' |
> (1 row)
>
> Running VACUUM ANALYZE semantic_mapping in psql works:
>
> $ psql -d dm_test -c 'VACUUM ANALYZE semantic_mapping'
> VACUUM
> Time: 1231,767 ms (00:01,232)
>
> But running it with the vacuumdb command doesn't:
>
> vacuumdb -z -t semantic_mapping dm_test
> vacuumdb: vacuuming database "dm_test"
> vacuumdb: vacuuming of table "semantic_mapping" in database "dm_test"
> failed: ERROR:  text search dictionary "unaccent" does not exist
> CONTEXT:  SQL function "semantic_normalize" statement 1
>
> This is presumably a similar search path problem, because I can
> reproduce this in psql by setting the search path to exclude public:
>
> set search_path to "$user";
> vacuum analyze public.semantic_mapping;
> ERROR:  text search dictionary "unaccent" does not exist
> CONTEXT:  SQL function "semantic_normalize" statement 1
> Time: 851,562 ms
>
> Can't find a place to poke the "public." prefix in to work around this
> ... I can't even see where it's getting the link to the text search
> dictionary from. Is that in native code in the unaccent extension?

Since the semantic_normalize function is tripping it and it uses
unaccent I would say it is native to the extension.

What does:

\dFd unaccent

show?

>
> The unaccent definition looks like this:
>
> \df+ public.unaccent
>                                                                                List of functions
>   Schema |   Name   | Result data type | Argument data types |  Type  |
> Volatility | Parallel | Owner | Security | Access privileges | Language
> |  Source code  | Description
> --------+----------+------------------+---------------------+--------+------------+----------+-------+----------+-------------------+----------+---------------+-------------
>   public | unaccent | text             | regdictionary, text | normal |
> stable     | safe     | gthb  | invoker  |                   | c       
> | unaccent_dict |
>   public | unaccent | text             | text                | normal |
> stable     | safe     | gthb  | invoker  |                   | c       
> | unaccent_dict |
> (2 rows)
>
> Any tips?
>
> Cheers,
> Gulli
>
> On Wed, Aug 29, 2018 at 7:06 PM Bruce Momjian <bruce@xxxxxxxxxx
> <mailto:bruce@xxxxxxxxxx>> wrote:
>
>     On Wed, Aug 29, 2018 at 06:09:53PM +0000, Gunnlaugur Thor Briem wrote:
>      > Hi,
>      >
>      > I'm trying to upgrade an old PG 9.4 cluster to PG 10, and it's
>     failing on
>      > creating an index that uses the unaccent(text) function.
>      >
>      > That function is part of the unaccent extension, which is
>     installed in the old
>      > DB cluster. I expect pg_upgrade to create that extension as part
>     of the
>      > upgrade. It does create other extensions that are installed in
>     the old DB
>      > cluster. I don't get why this one isn't included.
>
>     This is caused by security changes made in PG 10.3 and other minor
>     releases.  Please see this thread for an outline of the issue:
>
>     https://www.postgresql.org/message-id/flat/152106914669.1223.5104148605998271987%40wrigleys.postgresql.org
>
>     I think you have to change your index function to specify the schema
>     name before the unacces function call, e.g.
>
>              SELECT lower(public.unaccent(btrim(regexp_replace(
>
>     --
>        Bruce Momjian  <bruce@xxxxxxxxxx <mailto:bruce@xxxxxxxxxx>>
>     http://momjian.us
>        EnterpriseDB http://enterprisedb.com
>
>     + As you are, so once was I.  As I am, so you will be. +
>     +                      Ancient Roman grave inscription +
>


--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx