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

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(dot)klaver(at)aklaver(dot)com