BUG #15695: Failure to restore a dump, ERROR: operator does not exist: public.hstore = public.hstore

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

BUG #15695: Failure to restore a dump, ERROR: operator does not exist: public.hstore = public.hstore

PG Bug reporting form
The following bug has been logged on the website:

Bug reference:      15695
Logged by:          Yuri Cherio
Email address:      [hidden email]
PostgreSQL version: 10.7
Operating system:   Ubuntu 18.04 64bit
Description:        

I am exporting DB from 10.6 and importing into 10.7 and an UPDATE rule fails
to restore. I have a view with a rule that starts like this

CREATE OR REPLACE RULE table_view__upd__rul AS ON UPDATE
        TO schema.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 host1 -p port database > file.dump

and import as
pg_restore -v -d database -j 4 -h host2 -p port file.dump

which at some point logs 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!

Reply | Threaded
Open this post in threaded view
|

Re: BUG #15695: Failure to restore a dump, ERROR: operator does not exist: public.hstore = public.hstore

Noah Misch-2
On Fri, Mar 15, 2019 at 09:20:38PM +0000, PG Bug reporting form wrote:

> I am exporting DB from 10.6 and importing into 10.7 and an UPDATE rule fails
> to restore. I have a view with a rule that starts like this
>
> CREATE OR REPLACE RULE table_view__upd__rul AS ON UPDATE
> TO schema.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
> );
> ....

> 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..."

> This looks like a bug to me :(

It is a bug:
https://www.postgresql.org/message-id/flat/ffefc172-a487-aa87-a0e7-472bf29735c8%40gmail.com