BUG #11617: issue with dump/restore involving view with hstore data type embedded in where condition

From: support(at)maerix(dot)com
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #11617: issue with dump/restore involving view with hstore data type embedded in where condition
Date: 2014-10-09 20:00:31
Message-ID: 20141009200031.25464.53769@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 11617
Logged by: Normand Desautels
Email address: support(at)maerix(dot)com
PostgreSQL version: 9.3.4
Operating system: Ubuntu 12.04 LTS


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

AS a workaround, we used instead COALESCE with a fake hstore value for NULL

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
This bug was listed on the 12th of May by Craig Ringer.

Or the use of "IS DISTINCT FROM" clause is irreconcilable with hstore
If that is the case, we will take note of this.

Can you clarify this.

Thanks for your looking it.



Browse pgsql-bugs by date

  From Date Subject
Next Message Josh Berkus 2014-10-09 21:19:18 Re: schema-only -n option in pg_restore fails
Previous Message Josh Berkus 2014-10-09 19:36:39 schema-only -n option in pg_restore fails