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

Started by Gunnlaugur Thor Briemover 7 years ago16 messages

gunnlaugur@gmail.com

over 7 years ago

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.

Here are the commands I run, and their output:

----- snip -----
$ sudo rm -rf /opt/local/var/db/postgresql10/defaultdb

$ sudo mkdir -p /opt/local/var/db/postgresql10/defaultdb

$ sudo chown postgres:postgres /opt/local/var/db/postgresql10/defaultdb

$ sudo su postgres -c '/opt/local/lib/postgresql10/bin/initdb -D
/opt/local/var/db/postgresql10/defaultdb --locale en_US.UTF-8'
The files belonging to this database system will be owned by user
"postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory
/opt/local/var/db/postgresql10/defaultdb ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

/opt/local/lib/postgresql10/bin/pg_ctl -D
/opt/local/var/db/postgresql10/defaultdb -l logfile start

$ sudo su postgres -c '/opt/local/lib/postgresql10/bin/pg_upgrade
--old-bindir /opt/local/lib/postgresql94/bin --old-datadir
/opt/local/var/db/postgresql94/defaultdb --new-bindir
/opt/local/lib/postgresql10/bin --new-datadir
/opt/local/var/db/postgresql10/defaultdb'Performing Consistency Checks
-----------------------------
Checking cluster versions ok
Checking database user is the install user ok
Checking database connection settings ok
Checking for prepared transactions ok
Checking for reg* data types in user tables ok
Checking for contrib/isn with bigint-passing mismatch ok
Checking for invalid "unknown" user columns ok
Checking for roles starting with "pg_" ok
Creating dump of global objects ok
Creating dump of database schemas
ok
Checking for presence of required libraries ok
Checking database user is the install user ok
Checking for prepared transactions ok

If pg_upgrade fails after this point, you must re-initdb the
new cluster before continuing.

Performing Upgrade
------------------
Analyzing all rows in the new cluster ok
Freezing all rows in the new cluster ok
Deleting files from new pg_xact ok
Copying old pg_clog to new server ok
Setting next transaction ID and epoch for new cluster ok
Deleting files from new pg_multixact/offsets ok
Copying old pg_multixact/offsets to new server ok
Deleting files from new pg_multixact/members ok
Copying old pg_multixact/members to new server ok
Setting next multixact ID and offset for new cluster ok
Resetting WAL archives ok
Setting frozenxid and minmxid counters in new cluster ok
Restoring global objects in the new cluster ok
Restoring database schemas in the new cluster
dm_test
*failure*

Consult the last few lines of "pg_upgrade_dump_409041.log" for
the probable cause of the failure.
Failure, exiting
----- snip -----

And here is that pg_upgrade_dump_409041.log file:

----- snip -----
command: "/opt/local/lib/postgresql10/bin/pg_dump" --host /private/tmp
--port 50432 --username postgres --schema-only --quote-all-identifiers
--binary-upgrade --format=custom --file="pg_upgrade_dump_409041.custom"
'dbname=dm_test' >> "pg_upgrade_dump_409041.log" 2>&1

command: "/opt/local/lib/postgresql10/bin/pg_restore" --host /private/tmp
--port 50432 --username postgres --exit-on-error --verbose --dbname
'dbname=dm_test' "pg_upgrade_dump_409041.custom" >>
"pg_upgrade_dump_409041.log" 2>&1
pg_restore: connecting to database for restore
pg_restore: creating pg_largeobject "pg_largeobject"
pg_restore: creating pg_largeobject_metadata "pg_largeobject_metadata"
pg_restore: creating SCHEMA "acl_admin"
pg_restore: creating COMMENT "SCHEMA "acl_admin""
pg_restore: creating SCHEMA "public"
pg_restore: creating COMMENT "SCHEMA "public""
pg_restore: creating EXTENSION "fuzzystrmatch"
pg_restore: creating COMMENT "EXTENSION "fuzzystrmatch""
pg_restore: creating EXTENSION "hstore"
pg_restore: creating COMMENT "EXTENSION "hstore""
pg_restore: creating EXTENSION "sslinfo"
pg_restore: creating COMMENT "EXTENSION "sslinfo""
pg_restore: creating TYPE "public.dataimport_job_state"
pg_restore: creating TYPE "public.dblink_pkey_results"...
[...]
pg_restore: creating INDEX "public.ix_semantic_mapping_lower_title"
pg_restore: creating INDEX "public.ix_semantic_mapping_normalize_title"
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 5240; 1259 427215 INDEX
ix_semantic_mapping_normalize_title dm_admin
pg_restore: [archiver (db)] could not execute query: ERROR: function
unaccent(text) does not exist
LINE 2: SELECT lower(unaccent(btrim(regexp_replace($1, '\s+', ' ',...
^
HINT: No function matches the given name and argument types. You might
need to add explicit type casts.
QUERY:
SELECT lower(unaccent(btrim(regexp_replace($1, '\s+', ' ', 'g'), ' "')))

CONTEXT: SQL function "semantic_normalize" during inlining
Command was:
-- For binary upgrade, must preserve pg_class oids
SELECT
pg_catalog.binary_upgrade_set_next_index_pg_class_oid('427215'::pg_catalog.oid);

CREATE INDEX "ix_semantic_mapping_normalize_title" ON
"public"."semantic_mapping" USING "btree"
("public"."semantic_normalize"("title"));
----- snip -----

These occurrences of unaccent at the end are the only ones in the file:

$ sudo grep -n unaccent pg_upgrade_dump_409041.log g
1713:pg_restore: [archiver (db)] could not execute query: ERROR: function
unaccent(text) does not exist
1714:LINE 2: SELECT lower(unaccent(btrim(regexp_replace($1, '\s+', ' ',...
1718: SELECT lower(unaccent(btrim(regexp_replace($1, '\s+', ' ', 'g'), '
"')))

Here is the definition of that index in the old DB:

\d ix_semantic_mapping_normalize_title
Index "public.ix_semantic_mapping_normalize_title"
Column | Type | Definition
--------------------+------+---------------------------
semantic_normalize | text | semantic_normalize(title)
btree, for table "public.semantic_mapping"

and the semantic_normalize function it uses:

Any more information I can provide, to help troubleshoot this?

Cheers,
Gulli

Bruce Momjian

bruce@momjian.us

over 7 years ago

In reply to: Gunnlaugur Thor Briem (#1)

Re: 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:

/messages/by-id/152106914669.1223.5104148605998271987@wrigleys.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 +

Gunnlaugur Thor Briem

gunnlaugur@gmail.com

over 7 years ago

In reply to: Bruce Momjian (#2)

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

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:

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?

The unaccent definition looks like this:

\df+ public.unaccent

Any tips?

Cheers,
Gulli

On Wed, Aug 29, 2018 at 7:06 PM Bruce Momjian <bruce@momjian.us> wrote:

Show quoted text

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:

/messages/by-id/152106914669.1223.5104148605998271987@wrigleys.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 +

Adrian Klaver

adrian.klaver@aklaver.com

over 7 years ago

In reply to: Gunnlaugur Thor Briem (#3)

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

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:

/messages/by-id/152106914669.1223.5104148605998271987@wrigleys.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

Gunnlaugur Thor Briem

gunnlaugur@gmail.com

over 7 years ago

In reply to: Adrian Klaver (#4)

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

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

Cheers,
Gulli

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

Show quoted text

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:

/messages/by-id/152106914669.1223.5104148605998271987@wrigleys.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

Adrian Klaver

adrian.klaver@aklaver.com

over 7 years ago

In reply to: Gunnlaugur Thor Briem (#5)

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

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

Adrian Klaver

adrian.klaver@aklaver.com

over 7 years ago

In reply to: Gunnlaugur Thor Briem (#5)

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

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:

--
Adrian Klaver
adrian.klaver@aklaver.com

Gunnlaugur Thor Briem

gunnlaugur@gmail.com

over 7 years ago

In reply to: Adrian Klaver (#7)

unaccent(text) fails depending on search_path (WAS: pg_upgrade fails saying function unaccent(text) doesn't exist)

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.

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

over 7 years ago

In reply to: Gunnlaugur Thor Briem (#8)

Re: unaccent(text) fails depending on search_path (WAS: pg_upgrade fails saying function unaccent(text) doesn't exist)

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

#10

Gunnlaugur Thor Briem

gunnlaugur@gmail.com

over 7 years ago

In reply to: Adrian Klaver (#9)

Re: unaccent(text) fails depending on search_path (WAS: pg_upgrade fails saying function unaccent(text) doesn't exist)

Yep, a neater workaround for sure!

Cheers,
Gulli

On Wed, Sep 5, 2018 at 2:00 PM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

Show quoted text

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

#11

Tom Lane

tgl@sss.pgh.pa.us

over 7 years ago

In reply to: Gunnlaugur Thor Briem (#8)

1 attachment(s)

Re: unaccent(text) fails depending on search_path (WAS: pg_upgrade fails saying function unaccent(text) doesn't exist)

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

Attachments:

avoid-depending-on-search-path-in-unaccent.patchtext/x-diff; charset=us-ascii; name=avoid-depending-on-search-path-in-unaccent.patchDownload

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

#12

Tom Lane

tgl@sss.pgh.pa.us

over 7 years ago

In reply to: Tom Lane (#11)

1 attachment(s)

Re: unaccent(text) fails depending on search_path (WAS: pg_upgrade fails saying function unaccent(text) doesn't exist)

[ redirecting to pgsql-hackers ]

I wrote:

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.

Here's a slightly more efficient version.

regards, tom lane

Attachments:

avoid-depending-on-search-path-in-unaccent-2.patchtext/x-diff; charset=us-ascii; name=avoid-depending-on-search-path-in-unaccent-2.patchDownload

diff --git a/contrib/unaccent/unaccent.c b/contrib/unaccent/unaccent.c
index 247c202..dbf2bb9 100644
*** a/contrib/unaccent/unaccent.c
--- b/contrib/unaccent/unaccent.c
***************
*** 20,26 ****
--- 20,28 ----
  #include "tsearch/ts_locale.h"
  #include "tsearch/ts_public.h"
  #include "utils/builtins.h"
+ #include "utils/lsyscache.h"
  #include "utils/regproc.h"
+ #include "utils/syscache.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
--- 378,398 ----
  
  	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);
! 		const char *dictname = "unaccent";
! 
! 		dictOid = GetSysCacheOid2(TSDICTNAMENSP,
! 								  PointerGetDatum(dictname),
! 								  ObjectIdGetDatum(procnspid));
! 		if (!OidIsValid(dictOid))
! 			ereport(ERROR,
! 					(errcode(ERRCODE_UNDEFINED_OBJECT),
! 					 errmsg("text search dictionary \"%s.%s\" does not exist",
! 							get_namespace_name(procnspid), dictname)));
  		strArg = 0;
  	}
  	else

#13

Bruce Momjian

bruce@momjian.us

over 7 years ago

In reply to: Tom Lane (#12)

Re: unaccent(text) fails depending on search_path (WAS: pg_upgrade fails saying function unaccent(text) doesn't exist)

On Wed, Sep 5, 2018 at 06:37:00PM -0400, Tom Lane wrote:

[ redirecting to pgsql-hackers ]

I wrote:

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.

Here's a slightly more efficient version.

If we are going down this route, is there any thought of handling
earchdistance the same way?

/messages/by-id/20180330205229.GS8476@momjian.us

--
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 +

#14

Tom Lane

tgl@sss.pgh.pa.us

over 7 years ago

In reply to: Bruce Momjian (#13)

Re: unaccent(text) fails depending on search_path (WAS: pg_upgrade fails saying function unaccent(text) doesn't exist)

Bruce Momjian <bruce@momjian.us> writes:

If we are going down this route, is there any thought of handling
earchdistance the same way?
/messages/by-id/20180330205229.GS8476@momjian.us

AFAICS there are no internal-to-the-C-code search path dependencies
in earthdistance.c, so it's not the same problem.

regards, tom lane

#15

Bruce Momjian

bruce@momjian.us

over 7 years ago

In reply to: Tom Lane (#14)

Re: unaccent(text) fails depending on search_path (WAS: pg_upgrade fails saying function unaccent(text) doesn't exist)

On Fri, Sep 7, 2018 at 06:43:52PM -0400, Tom Lane wrote:

Bruce Momjian <bruce@momjian.us> writes:

If we are going down this route, is there any thought of handling
earchdistance the same way?
/messages/by-id/20180330205229.GS8476@momjian.us

AFAICS there are no internal-to-the-C-code search path dependencies
in earthdistance.c, so it's not the same problem.

Uh, there is an SQL function that calls functions from the module that
fail. It would be a CREATE FUNCTION patch, I think, but I thought the
issue was the same.

--
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 +

#16

Tom Lane

tgl@sss.pgh.pa.us

over 7 years ago

In reply to: Bruce Momjian (#15)

Re: unaccent(text) fails depending on search_path (WAS: pg_upgrade fails saying function unaccent(text) doesn't exist)

Bruce Momjian <bruce@momjian.us> writes:

On Fri, Sep 7, 2018 at 06:43:52PM -0400, Tom Lane wrote:

AFAICS there are no internal-to-the-C-code search path dependencies
in earthdistance.c, so it's not the same problem.

Uh, there is an SQL function that calls functions from the module that
fail. It would be a CREATE FUNCTION patch, I think, but I thought the
issue was the same.

Not really. You could either interpolate @extschema@ into the text
of the referencing function, or (though much inferior for performance)
have it SET SEARCH_PATH FROM CURRENT. Either of those changes would
involve an extension version bump since they're changing the extension
script. What's more of a problem is that we could no longer claim
the extension is relocatable. My unaccent fix dodged that by looking
up the C function's current schema, but I don't think there's any
equivalent functionality available at SQL level.

regards, tom lane