Cherio <cherio@gmail.com> writes:
> I am exporting DB from 10.6 and importing into 10.7 and an UPDATE rule
> fails to restore:
> pg_restore: [archiver (db)] could not execute query: ERROR: operator does
> not exist: public.hstore = public.hstore
> From the log I can see that hstore was extension successfully created
It's probably not in the search_path that pg_restore was using.
This is one of the hard-to-fix consequences of the decision to band-aid
over CVE-2018-1058 by having pg_restore run with a minimal search_path.
pg_dump can't forestall the problem by schema-qualifying the operator
name, because there is no explicit operator name in IS DISTINCT FROM.
I complained at the time that there needed to be a way to relax the
restriction, but I lost the argument.
AFAIK the only workaround that exists at the moment is to hand-edit
the dump script to change the forced search_path setting to include
whereever you put hstore (and any other extensions you have similar
issues with).
There's a previous discussion here:
https://www.postgresql.org/message-id/flat/ffefc172-a487-aa87-a0e7-472bf29735c8%40gmail.com
but it seems like nobody's done any work on it since then.
regards, tom lane
