Re: hstore dump/restore bug in 9.3

On 05/12/2014 10:08 AM, Craig Ringer wrote:
> Hi all
>
> A user has reported a bug where a simple view using hstore does not dump
> and restore correctly. I've reproduced the detailed test case they
> supplied below.
>
> The original report is by Stack Overflow user 'aidan', here:
> http://stackoverflow.com/q/23599926/398670
>
>
> The error is:
>
> pg_restore: [archiver (db)] could not execute query: ERROR:  operator
> does not exist: public.hstore = public.hstore
> LINE 2:  SELECT NULLIF(hstore_test_table.column1, hstore_test_table....

When running pg_restore without a DB to get an SQL dump, it's clear why
this happens - the dump sets the search_path to exclude the public
schema, which contains the hstore operators required.



CREATE EXTENSION IF NOT EXISTS hstore WITH SCHEMA public;

...

SET search_path = hstore_test_schema, pg_catalog;

...


CREATE VIEW hstore_test_view AS
 SELECT NULLIF(hstore_test_table.column1, hstore_test_table.column2) AS
comparison
   FROM hstore_test_table;



Using a different view definition makes this go away, as the original
reporter noted:

CREATE VIEW hstore_test_schema.hstore_test_view AS
SELECT column1 =  column2 AS comparison
FROM hstore_test_schema.hstore_test_table;

because the view is dumped with an explicit operator schema:

CREATE VIEW hstore_test_view AS
 SELECT (hstore_test_table.column1 OPERATOR(public.=)
hstore_test_table.column2) AS comparison
   FROM hstore_test_table;



It looks like pg_dump expects to be able to explicitly qualify operators
so it doesn't worry about setting the search_path to include them, but
it doesn't cope with operators that're used indirectly by the nullif
pseudofunction.

Do we need a way to schema-qualify the operator used in NULLIF, or to
provide an operator alias that it gets dumped as?

--
 Craig Ringer                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services