CODEWITHSUNDEEP
postgresqlpg-dumppostgresql-10
svoop
svoop

Reputation: 3464

Dump fails to re-create index over array of hstore column

I'm dumping a large database with pg_dump -O -U <user> <db> >dump.sql.

Here is the gist of dump.sql with everything irrelevant stripped.

When importing the dump into another Postgres instance (identical setup) with psql -f dump.sql -U <user> <db>, the following error happens on the CREATE INDEX line 147:

psql:dumped.sql:147: ERROR:  type "hstore" does not exist
LINE 5:           element hstore;
                          ^
QUERY:
        DECLARE
          arrHstore ALIAS FOR $1;
          key ALIAS FOR $2;
          element hstore;
          string text;
        BEGIN
          FOREACH element IN ARRAY arrHstore LOOP
            string := concat(string, ' ', element->key);
          END LOOP;
          RETURN trim(leading from string, ' ');
        END;

CONTEXT:  compilation of PL/pgSQL function "immutable_array_to_string" near line 5

So everything but the final CREATE INDEX has worked.

Now I connect to the database with psql -U <user> <db> and paste the previously failing CREATE INDEX command... the index is created without any problem.

A few things I've tried:

Any idea what's going on here? Thanks a bunch in advance!

Upvotes: 1

Views: 413

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247575

That's because of the security fixes concerning the public schema in the latest point release of PostgreSQL.

Either change the function so that it refers to the type hstore with its schema: public.hstore, or add SET search_path = public to the CREATE FUNCTION statement.

To schema-qualify the hstore operator ->, you can replace

element -> key

with

element OPERATOR(public.->) key

Similar for other operators.

Upvotes: 1

Related Questions