Re: hstore dump/restore bug in 9.3 - Mailing list pgsql-bugs

From Craig Ringer
Subject Re: hstore dump/restore bug in 9.3
Date
Msg-id 53702F92.50404@2ndquadrant.com
Whole thread Raw
In response to hstore dump/restore bug in 9.3  (Craig Ringer <craig@2ndquadrant.com>)
Responses Re: hstore dump/restore bug in 9.3
List pgsql-bugs
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

pgsql-bugs by date:

Previous
From: Craig Ringer
Date:
Subject: hstore dump/restore bug in 9.3
Next
From: Craig Ringer
Date:
Subject: Re: hstore dump/restore bug in 9.3