Thread: pg_restore: [archiver (db)] could not execute query: ERROR: operatordoes not exist: public.hstore = public.hstore
pg_restore: [archiver (db)] could not execute query: ERROR: operatordoes not exist: public.hstore = public.hstore
From
Cherio
Date:
I am exporting DB from 10.6 and importing into 10.7 and an UPDATE rule fails to restore. I have a view and a rule that starts like this
CREATE OR REPLACE RULE table_view__upd__rul AS ON UPDATE
TO cfg.table_view
DO INSTEAD (
UPDATE sch.tab
SET
updated_at = NEW.updated_at,
updated_by = NEW.updated_by,
hstore_field = NEW.hstore_field
WHERE id = OLD.id
AND
(
OLD.updated_at IS DISTINCT FROM NEW.updated_at OR
OLD.updated_by IS DISTINCT FROM NEW.updated_by OR
OLD.hstore_field IS DISTINCT FROM NEW.hstore_field
);
....
I export into a custom format as
pg_dump -F c -Z 0 -T bak.* -T tmp.* -h host -p port database > file.dump
and import with
pg_restore -v -d database -j 4 -p port file.dump
which results into an error
pg_restore: [archiver (db)] could not execute query: ERROR: operator does not exist: public.hstore = public.hstore
LINE 3: ...ISTINCT FROM (new.updated_by)::text) OR (old.hstore_field IS DISTINC...
with an arrow pointing to "old.hstore_field IS -->DISTINC..."
From the log I can see that hstore was extension successfully created and many other tables, views and functions successfully recreated prior to this error.
This looks like a bug to me :(
Thank you!
Re: pg_restore: [archiver (db)] could not execute query: ERROR: operator does not exist: public.hstore = public.hstore
From
Tom Lane
Date:
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