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

On 10/09/2014 11:00 PM, support@maerix.com wrote:
> 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.

Yeah, this is essentially the same bug as with NULLIF.

In the catalogs, we store the OID of the equality operator used in a
NULLIF or IS DISTINCT FROM expression. But when you try to deparse that
back to an SQL statement, it's impossible to construct an equivalent SQL
statement that would refer to the same operator, when that operator is
not in search_path. In essence, it's not possible to schema-qualify the
equality operator used.

There are a whole bunch of expressions that have the same problem:
a IS DISTINCT FROM b
a IS NOT DISTINCT FROM b
a IN (...)
a NOT IN (...)
CASE a WHEN b ... ELSE d END
NULLIF(a, b)

I don't think this can be solved without some additional syntax, for
specifying the equality operator explicitly. I propose that we add an
optional USING <operator> after the problematic expressions:

a IS DISTINCT FROM b USING myschema.=
NULLIF(a, b) USING myschema.=
...

I gave that a quick try, but got a shift/reduce conflict. I don't have
the time to dig deeper right now, but it might be that that particular
syntax might not work out. But something like that.

(NULLIF, IS DISTINCT FROM and CASE are specified by the SQL standard, so
there's a small risk that the standard committee might extend the syntax
in a way that conflicts with this. But it seems highly unlikely.)

- Heikki