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] /messages/by-id/ffefc172-a487-aa87-a0e7-472bf29735c8@gmail.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.