Обсуждение: Cannot restore dump when using IS DISTINCT FROM on a HSTORE column

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?

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
-- 
nickname: Lele Gaifax | Quando vivrò di quello che ho pensato ieri
real: Emanuele Gaifas | comincerò ad aver paura di chi mi copia.
lele@metapensiero.it  |                 -- Fortunato Depero, 1929.

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

Lele Gaifax <lele@metapensiero.it> writes:
> I'm hitting a problem very similar to the one described here[1]

Yeah, nothing's been done about that yet :-(

> 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:

Probably the simplest solution is to override pg_dump's forcing of the
search_path.  It's going to look like this:

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

If you have the dump in a text file you could just edit it and remove
that line (it's only about a dozen lines in, typically).  Otherwise
you could delete it with sed or the like, roughly

pg_restore dumpfile | sed s'/^SELECT pg_catalog.set_config('search_path', '', false);//' | psql targetdb

There's been some discussion of providing a simpler way to do this,
but nothing's been done about that either.

Disclaimer: in principle, doing this leaves you open to SQL-injection-like
attacks during the restore, if some malicious user has had access to
either the original source database or your restore target DB.  That's
why we put in the search_path restriction.  But it doesn't help to be
secure if you can't get your work done ...

            regards, tom lane

On 9/5/19 7:35 AM, Tom Lane wrote:
> Lele Gaifax <lele@metapensiero.it> writes:
>> I'm hitting a problem very similar to the one described here[1]

> 
> pg_restore dumpfile | sed s'/^SELECT pg_catalog.set_config('search_path', '', false);//' | psql targetdb
> 
> There's been some discussion of providing a simpler way to do this,
> but nothing's been done about that either.

My 2 cents would be for a --unsecure-path option to pg_dump/restore that 
would drop the pg_catalog.set_config('search_path', '', false).

>             regards, tom lane
> 
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Tom Lane <tgl@sss.pgh.pa.us> writes:

> If you have the dump in a text file you could just edit it and remove
> that line (it's only about a dozen lines in, typically).  Otherwise
> you could delete it with sed or the like, roughly
>
> pg_restore dumpfile | sed s'/^SELECT pg_catalog.set_config('search_path', '', false);//' | psql targetdb

Great, I can easily do that! I missed the "rewrite-dump-as-sql-script"
functionality of pg_restore.

Thanks a lot, also to Adrian!

bye, lele.
-- 
nickname: Lele Gaifax | Quando vivrò di quello che ho pensato ieri
real: Emanuele Gaifas | comincerò ad aver paura di chi mi copia.
lele@metapensiero.it  |                 -- Fortunato Depero, 1929.