Re: Cannot restore dump when using IS DISTINCT FROM on a HSTOREcolumn

On 9/5/19 7:16 AM, Lele Gaifax wrote:
> Hi all,
> 
> I'm hitting a problem very similar to the one described here[1]: one of my
> databases have the following trigger
> 
>     CREATE TRIGGER trg_dn_customer_contents_950_reset_usable
>       BEFORE UPDATE
>       ON dn.customer_contents
>       FOR EACH ROW
>           WHEN (OLD.usable IS NOT NULL AND OLD.usable = NEW.usable
>                 AND (OLD.customer_content_category_id IS DISTINCT FROM NEW.customer_content_category_id
>                      OR OLD.title IS DISTINCT FROM NEW.title
>                      OR OLD.summary IS DISTINCT FROM NEW.summary
>                      OR OLD.description IS DISTINCT FROM NEW.description
>                      OR OLD.active IS DISTINCT FROM NEW.active
>                      OR OLD.languages IS DISTINCT FROM NEW.languages
>                      OR OLD.address_id IS DISTINCT FROM NEW.address_id
>                      OR OLD.schedule IS DISTINCT FROM NEW.schedule
>                      OR OLD.price IS DISTINCT FROM NEW.price))
>           EXECUTE FUNCTION dn.reset_customer_content_usable()
> 
> where several of those columns are HSTOREs. Trying to restore a dump I get the
> same error: "ERROR:  operator does not exist: public.hstore = public.hstore".
> The source and target PG versions are the same, 11.5.
> 
> I followed the link[2] and read the related thread: as it is more that one
> year old, I wonder if there is any news on this, or alternatively if there is
> a recommended workaround: as that is the only place where I'm using IS
> DISTINCT FROM against an HSTORE field, I could easily replace those
> expressions with the more verbose equivalent like
> 
>     (OLD.x IS NULL AND NEW.x IS NOT NULL)
>     OR
>     (OLD.x IS NOT NULL AND NEW.x IS NULL)
>     OR
>     (OLD.x <> NEW.x)
> 
> lacking a better approach.
> 
> What would you suggest?

I don't know if progress has been made on this or not.

Are you able to use a plain text dump?

If so you might try changing:

SELECT pg_catalog.set_config('search_path', '', false);

to something that covers the path where you installed hstore.

> 
> Thanks in advance,
> ciao, lele.
> 
> [1]
https://www.postgresql-archive.org/BUG-15695-Failure-to-restore-a-dump-ERROR-operator-does-not-exist-public-hstore-public-hstore-td6077272.html
> [2] https://www.postgresql.org/message-id/flat/ffefc172-a487-aa87-a0e7-472bf29735c8%40gmail.com
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com