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