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@xxxxxxxxxxx
