Horton

Thread

Commits

  1. Make contrib/unaccent's unaccent() function work when not in search path.

  2. Avoid unnecessary use of pg_strcasecmp for already-downcased identifiers.

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

    Gunnlaugur Thor Briem <gunnlaugur@gmail.com> — 2018-08-29T18:09:53Z

    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:

\x \df+ semantic_normalize
Expanded display is on.
List of functions
-[ RECORD 1
]-------+---------------------------------------------------------------------------
Schema              | public
Name                | semantic_normalize
Result data type    | text
Argument data types | title text
Type                | normal
Volatility          | immutable
Owner               | dm_admin
Security            | invoker
Access privileges   |
Language            | sql
Source code
|
+
                    |   SELECT lower(unaccent(btrim(regexp_replace($1,
'\s+', ' ', 'g'), ' "')))+
                    |
Description         |

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

Cheers,
Gulli

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

    Bruce Momjian <bruce@momjian.us> — 2018-08-29T19:06:35Z

    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 +

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

    Gunnlaugur Thor Briem <gunnlaugur@gmail.com> — 2018-08-30T09:24:13Z

    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?

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

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

    Adrian Klaver <adrian.klaver@aklaver.com> — 2018-08-30T13:48:26Z

    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

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

    Gunnlaugur Thor Briem <gunnlaugur@gmail.com> — 2018-08-30T16:03:11Z

    \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@aklaver.com>
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@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
>
>

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

    Adrian Klaver <adrian.klaver@aklaver.com> — 2018-08-31T14:14:18Z

    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

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

    Adrian Klaver <adrian.klaver@aklaver.com> — 2018-08-31T14:17:06Z

    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

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

    Gunnlaugur Thor Briem <gunnlaugur@gmail.com> — 2018-09-05T08:49:50Z

    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

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

    Adrian Klaver <adrian.klaver@aklaver.com> — 2018-09-05T14:00:03Z

    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. 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> — 2018-09-05T14:06:17Z

    Yep, a neater workaround for sure!

Cheers,
Gulli

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

> 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. Re: unaccent(text) fails depending on search_path (WAS: pg_upgrade fails saying function unaccent(text) doesn't exist)

    Tom Lane <tgl@sss.pgh.pa.us> — 2018-09-05T19:03:33Z

    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

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

    Tom Lane <tgl@sss.pgh.pa.us> — 2018-09-05T22:37:00Z

    [ 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

  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> — 2018-09-07T22:32:47Z

    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?

	https://www.postgresql.org/message-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. Re: unaccent(text) fails depending on search_path (WAS: pg_upgrade fails saying function unaccent(text) doesn't exist)

    Tom Lane <tgl@sss.pgh.pa.us> — 2018-09-07T22:43:52Z

    Bruce Momjian <bruce@momjian.us> writes:
> If we are going down this route, is there any thought of handling
> earchdistance the same way?
> 	https://www.postgresql.org/message-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. Re: unaccent(text) fails depending on search_path (WAS: pg_upgrade fails saying function unaccent(text) doesn't exist)

    Bruce Momjian <bruce@momjian.us> — 2018-09-07T22:56:43Z

    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?
> > 	https://www.postgresql.org/message-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. Re: unaccent(text) fails depending on search_path (WAS: pg_upgrade fails saying function unaccent(text) doesn't exist)

    Tom Lane <tgl@sss.pgh.pa.us> — 2018-09-07T23:05:39Z

    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