Обсуждение: pg_upgrade fails saying function unaccent(text) doesn't exist

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@momjian.us>        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 +

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@momjian.us 
> <mailto:bruce@momjian.us>> 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@momjian.us <mailto:bruce@momjian.us>>
>     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@aklaver.com

On 08/30/2018 09:03 AM, Gunnlaugur Thor Briem wrote:
> \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
> 

I could not replicate with simple case:

select version();
                                       version 

------------------------------------------------------------------------------------
  PostgreSQL 10.5 on x86_64-pc-linux-gnu, compiled by gcc (SUSE Linux) 
4.8.5, 64-bit

CREATE OR REPLACE FUNCTION public.semantic_normalize(title text)
  RETURNS text
  LANGUAGE sql
AS $function$
SELECT lower(unaccent(btrim(regexp_replace($1,
'\s+', ' ', 'g'), ' "')))
$function$

CREATE TABLE unaccent_test(title text);

INSERT INTO unaccent_test values ('Hukić'), ('Böttcher'), ('ÀÁÂÃÄÅ'), 
('électro');

CREATE INDEX ix_semantic_normalize_title on unaccent_test(title);

VACUUM ANALYZE unaccent_test;
VACUUM

vacuumdb -U postgres -z -t unaccent_test test
vacuumdb: vacuuming database "test"


The only thing I can think of is that you have an older version of 
vacuumdb that is not aware of the schema specification changes in the 
newer versions of Postgrse.



-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 08/30/2018 09:03 AM, Gunnlaugur Thor Briem wrote:
> \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
> 

Forgot to add to previous post:

\dx unaccent
                        List of installed extensions
    Name   | Version | Schema |                 Description
----------+---------+--------+---------------------------------------------
  unaccent | 1.1     | public | text search dictionary that removes accents



-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 09/05/2018 01:49 AM, Gunnlaugur Thor Briem wrote:
> OK, I found the cause of the unaccent dictionary problem, and a workaround.
> 
> It's not the vacuumdb version, not the unaccent version, and it's not 
> even a pg_upgrade problem: I get this error also with PG 9.4.18 running 
> on the old cluster, with both the 10.5 vacuumdb and the 9.4.18 vacuumdb, 
> and I get the same error in both.
> 
> And it's not strictly a vacuumdb problem, though vacuumdb triggers it.
> 
> Here's a very minimal test case, unrelated to my DB, that you ought to 
> be able to reproduce:
> 
> SET search_path = "$user"; SELECT public.unaccent('fóö');
> SET
> ERROR:  text search dictionary "unaccent" does not exist
> 
> and here's a workaround:
> 
> SET search_path = "$user"; SELECT public.unaccent(tsdict.oid, 'fóö')
> FROM pg_catalog.pg_ts_dict tsdict WHERE dictname='unaccent';
> SET
>   unaccent
> ----------
>   foo
> (1 row)
> 
> The workaround avoids the OID lookup of the dictionary ... that lookup 
> (in the single-argument unaccent function) is done by unqualified name:
> 
> https://github.com/postgres/postgres/blob/fb8697b31aaeebe6170c572739867dcaa01053c6/contrib/unaccent/unaccent.c#L377
> 
>          dictOid = 
> get_ts_dict_oid(stringToQualifiedNameList("unaccent"), false);
> 
> and that fails if the search path doesn't include public. >
> So it is indeed triggered by the security changes that Bruce mentioned; 
> those were backported into 9.4.17: 
> https://www.postgresql.org/docs/9.4/static/release-9-4-17.html ... and 
> so got pulled in by my Macports upgrades. So nothing to do with pg_upgrade.
> 
> So the workaround for my vacuumdb/function-index problem is to give 
> unaccent the OID of the text search dictionary, so that the search path 
> isn't in play:
> 
> CREATE OR REPLACE FUNCTION public.semantic_normalize(title text)
> RETURNS text
>   LANGUAGE sql
>   IMMUTABLE STRICT
> AS $function$
>    SELECT lower(public.unaccent(16603, btrim(regexp_replace($1, '\s+', ' 
> ', 'g'), ' "')))
> $function$;
> 
> and that makes vacuumdb -z work in both 9.4.18 and 10.5, and makes 
> ./analyze_new_cluster.sh complete without problems.


Nice investigation. Working off the above, I offer a suggestion:

SET search_path = "$user"; SELECT public.unaccent('unaccent', 'fóö');
SET
ERROR:  text search dictionary "unaccent" does not exist
LINE 1: SELECT public.unaccent('unaccent', 'fóö');


SET search_path = "$user"; SELECT public.unaccent('public.unaccent', 'fóö');
SET
  unaccent
----------
  foo

That eliminates hard wiring the OID.

> 
> The proper fix is, I suppose, to make the single-argument unaccent 
> function explicitly look up the dictionary in the same schema as the 
> function itself is in.
> 
> Cheers,
> Gulli
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Gunnlaugur Thor Briem <gunnlaugur@gmail.com> writes:
> SET search_path = "$user"; SELECT public.unaccent('foo');
> SET
> ERROR:  text search dictionary "unaccent" does not exist

Meh.  I think we need the attached, or something just about like it.

It's barely possible that there's somebody out there who's relying on
setting the search path to allow choosing among multiple "unaccent"
dictionaries.  But there are way more people whose functions are
broken due to the recent search-path-tightening changes.

            regards, tom lane

diff --git a/contrib/unaccent/unaccent.c b/contrib/unaccent/unaccent.c
index 247c202..5f6ad8a 100644
*** a/contrib/unaccent/unaccent.c
--- b/contrib/unaccent/unaccent.c
***************
*** 20,25 ****
--- 20,26 ----
  #include "tsearch/ts_locale.h"
  #include "tsearch/ts_public.h"
  #include "utils/builtins.h"
+ #include "utils/lsyscache.h"
  #include "utils/regproc.h"

  PG_MODULE_MAGIC;
*************** unaccent_dict(PG_FUNCTION_ARGS)
*** 376,382 ****

      if (PG_NARGS() == 1)
      {
!         dictOid = get_ts_dict_oid(stringToQualifiedNameList("unaccent"), false);
          strArg = 0;
      }
      else
--- 377,393 ----

      if (PG_NARGS() == 1)
      {
!         /*
!          * Use the "unaccent" dictionary that is in the same schema that this
!          * function is in.
!          */
!         Oid            procnspid = get_func_namespace(fcinfo->flinfo->fn_oid);
!         char       *procnsp = get_namespace_name(procnspid);
!         List       *dictname;
!
!         dictname = list_make2(makeString(procnsp),
!                               makeString(pstrdup("unaccent")));
!         dictOid = get_ts_dict_oid(dictname, false);
          strArg = 0;
      }
      else