The following bug has been logged on the website:
Bug reference: 11617
Logged by: Normand Desautels
Email address: support@maerix.com
PostgreSQL version: 9.3.4
Operating system: Ubuntu 12.04 LTS
Description:
------------------------
Specs:
Distributor ID: Ubuntu
Description: Ubuntu 12.04 LTS
Release: 12.04
Codename: precise
PG Version: PostgreSQL 9.3.4 on x86_64-unknown-linux-gnu, compiled by gcc
(Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bi
-------------------------
To whom it may concern,
in restoring a dump followed by a restore, the restore partially work's but
does not restore a specific view with a hstore component
The error message is:
pg_restore: [archiver (db)] could not execute query: ERROR: operator does
not exist: public.hstore = public.hstore
LINE 30: ...me <> 'fk_formation_id'::text)) AND (h1.old_value IS DISTINC...
^
HINT: No operator matches the given name and argument type(s). You might
need to add explicit type casts.
The condition in the WHERE clause looks like this:
"WHERE (h1.old_value IS DISTINCT FROM h1.new_value)"
Where the columns old_value and new_value are of the HSTORE datatype, and
are optionnal.
That is why we called upon "IS DISTINCT FROM " to manage possible NULL
values.
AS a workaround, we used instead COALESCE with a fake hstore value for NULL
cases
Something along the line of:
where COALESCE( h1.old_value,'"e"=>"1", "f"=>"2"'::hstore) <>
COALESCE(h2.new_value,'"e"=>"1", "f"=>"2"'::hstore);
And it resolved the issue when restoring. Everything goes through normally.
Now, either it is a bug similar to the NULLIF issue (see attached
http://stackoverflow.com/q/23599926/398670)
This bug was listed on the 12th of May by Craig Ringer.
Or the use of "IS DISTINCT FROM" clause is irreconcilable with hstore
datatypes.
If that is the case, we will take note of this.
Can you clarify this.
Thanks for your looking it.
Regards
