From: | Lele Gaifax <lele(at)metapensiero(dot)it> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Cannot restore dump when using IS DISTINCT FROM on a HSTORE column |
Date: | 2019-09-05 14:16:19 |
Message-ID: | 87woem4ymk.fsf@metapensiero.it |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(at)metapensiero(dot)it | -- Fortunato Depero, 1929.
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2019-09-05 14:30:39 | Re: Cannot restore dump when using IS DISTINCT FROM on a HSTORE column |
Previous Message | Tom Lane | 2019-09-05 13:53:51 | Re: Rebuild pg_toast from scratch? |