Обсуждение: 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.