Dump and restore a PostgreSQL database using hstore comparison

Question

I have a view that compares two hstore columns.

When I dump and restore this database, the recovery fails with the following error message:

 Importing /tmp/hstore_test_2014-05-12.backup... pg_restore: [archiver (db)] Error while PROCESSING TOC: pg_restore: [archiver (db)] Error from TOC entry 172; 1259 1358132 VIEW hstore_test_view xxxx 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.... ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. Command was: CREATE VIEW hstore_test_view AS SELECT NULLIF(hstore_test_table.column1, hstore_test_table.column2) AS "nullif" FROM hst... pg_restore: [archiver (db)] could not execute query: ERROR: relation "hstore_test_schema.hstore_test_view" does not exist Command was: ALTER TABLE hstore_test_schema.hstore_test_view OWNER TO xxxx; 

I managed to create this error in PostgreSQL 9.3.0 with the following steps:

 CREATE DATABASE hstore_test; \c hstore_test CREATE EXTENSION hstore WITH SCHEMA public; CREATE SCHEMA hstore_test_schema; CREATE TABLE hstore_test_schema.hstore_test_table( id int, column1 hstore, column2 hstore, PRIMARY KEY( id ) ); CREATE VIEW hstore_test_schema.hstore_test_view AS SELECT NULLIF(column1, column2) AS comparison FROM hstore_test_schema.hstore_test_table; 

For completeness, the dump and recovery process looked like this:

 pg_dump -U xxxx -h localhost -f /tmp/hstore_test_2014-05-12.backup -Fc hstore_test psql -U xxxx -h localhost -d postgres -c "DROP DATABASE hstore_test" psql -U xxxx -h localhost -d postgres -c "CREATE DATABASE hstore_test" pg_restore -U xxxx -h localhost -d hstore_test /tmp/hstore_test_2014-05-12.backup 

pg_restore -l /tmp/hstore_test_2014-05-12.backup assumes that the hstore extension hstore enabled before the view is created:

 ; ; Archive created at Mon May 12 11:18:32 2014 ; dbname: hstore_test ; TOC Entries: 15 ; Compression: -1 ; Dump Version: 1.12-0 ; Format: CUSTOM ; Integer: 4 bytes ; Offset: 8 bytes ; Dumped from database version: 9.3.0 ; Dumped by pg_dump version: 9.3.0 ; ; ; Selected TOC Entries: ; 2074; 1262 1358002 DATABASE - hstore_test xxxx 7; 2615 1358003 SCHEMA - hstore_test_schema xxxx 5; 2615 2200 SCHEMA - public postgres 2075; 0 0 COMMENT - SCHEMA public postgres 2076; 0 0 ACL - public postgres 173; 3079 11787 EXTENSION - plpgsql 2077; 0 0 COMMENT - EXTENSION plpgsql 174; 3079 1358004 EXTENSION - hstore 2078; 0 0 COMMENT - EXTENSION hstore 171; 1259 1358124 TABLE hstore_test_schema hstore_test_table xxxx 172; 1259 1358132 VIEW hstore_test_schema hstore_test_view xxxx 2069; 0 1358124 TABLE DATA hstore_test_schema hstore_test_table xxxx 1960; 2606 1358131 CONSTRAINT hstore_test_schema hstore_test_table_pkey xxxx 

By the way, replacing NULLIF(col1, col2) with col1 = col2 seems to make the error disappear, despite the fact that an explicit comparison of type pg_restore complained.

Answer 1

This is a PostgreSQL error. I submitted your report to the pgsql-bugs list .

What happens when pg_dump sets search_path to public exception when creating tables in your schema. This is normal. When it discards objects that are related to things that are not related to search_path , it explicitly schematizes them for them to work.

It works for the case = , because pg_dump sees that = is actually OPERATOR(public.=) In this case and unloads it in this form:

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

however, pg_dump does not do this for a statement implicitly used with the nullif nullif . This leads to the following sequence of dummy commands:

 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; 

pg_dump simply uses the pg_catalog.pg_get_viewdef function to dump the output, so this probably requires a fix for the server.

The simplest workaround is to use nullif , replacing it with a more verbose but equivalent case :

 CASE WHEN column1 = column2 THEN NULL ELSE column1 END; 

The syntax does not provide a way for the circuit to qualify the pseudo-functional nullif operator, as we do with explicit OPERATOR(public.=) , So the correction does not seem trivial.

I expected the same problem to affect GREATEST and LEAST , possibly also DISTINCT , but it is not. Both seem to find their required operators, even if they are not on the search_path at run time, but fail if the operator is not on the search_path during the definition of the view. This suggests that they are probably using a b-tree operator class to search for operators through a type entry in directories found through table attributes. (Update: sources checked and yes what they do). Presumably nullif should also do this, but it is not.

Instead, he dies in:

 hstore_test=# \set VERBOSITY verbose hstore_test=# CREATE VIEW hstore_test_schema.hstore_test_view AS SELECT NULLIF(column1, column2) AS comparison FROM hstore_test_schema.hstore_test_table; ERROR: 42883: operator does not exist: public.hstore = public.hstore LINE 2: SELECT NULLIF(column1, column2) AS comparison FROM hstore_te... ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. LOCATION: op_error, parse_oper.c:722 

which, when I set a breakpoint there, traps when:

 Breakpoint 1, op_error ( pstate=pstate@entry =0x1189f38, op=op@entry =0x1189c10, oprkind=oprkind@entry =98 'b', arg1=arg1@entry =97207, arg2=arg2@entry =97207, fdresult=FUNCDETAIL_NOTFOUND, location=location@entry =58) at parse_oper.c:706 706 { (gdb) bt #0 op_error ( pstate=pstate@entry =0x1189f38, op=op@entry =0x1189c10, oprkind=oprkind@entry =98 'b', arg1=arg1@entry =97207, arg2=arg2@entry =97207, fdresult=FUNCDETAIL_NOTFOUND, location=location@entry =58) at parse_oper.c:706 #1 0x000000000051a81b in oper ( pstate=pstate@entry =0x1189f38, opname=opname@entry =0x1189c10, ltypeId=ltypeId@entry =97207, rtypeId=rtypeId@entry =97207, noError=noError@entry =0 '\000', location=location@entry =58) at parse_oper.c:440 #2 0x000000000051ad34 in make_op ( pstate=pstate@entry =0x1189f38, opname=0x1189c10, ltree=ltree@entry =0x118a528, rtree=0x118a590, location=58) at parse_oper.c:770 #3 0x00000000005155e1 in transformAExprNullIf (a=0x1189bc0, pstate=0x1189f38) at parse_expr.c:1021 #4 transformExprRecurse ( pstate=pstate@entry =0x1189f38, expr=0x1189bc0) at parse_expr.c:244 #5 0x0000000000517484 in transformExpr (pstate=0x1189f38, expr=<optimized out>, exprKind=exprKind@entry =EXPR_KIND_SELECT_TARGET) at parse_expr.c:116 #6 0x000000000051ff30 in transformTargetEntry ( pstate=pstate@entry =0x1189f38, node=0x1189bc0, expr=expr@entry =0x0, exprKind=exprKind@entry =EXPR_KIND_SELECT_TARGET, colname=0x1189ba0 "comparison", resjunk=resjunk@entry =0 '\000') at parse_target.c:94 #7 0x00000000005212df in transformTargetList ( pstate=pstate@entry =0x1189f38, targetlist=<optimized out>, exprKind=exprKind@entry =EXPR_KIND_SELECT_TARGET) at parse_target.c:167 #8 0x00000000004ef594 in transformSelectStmt (stmt=0x11899f0, pstate=0x1189f38) at analyze.c:942 #9 transformStmt (pstate=0x1189f38, parseTree=0x11899f0) at analyze.c:243 #10 0x00000000004f0a2d in parse_analyze (parseTree=0x11899f0, sourceText=sourceText@entry =0x114e6b0 "CREATE VIEW hstore_test_schema.hstore_test_view AS\nSELECT NULLIF(column1, column2) AS comparison FROM hstore_test_schema.hstore_test_table;", paramTypes=paramTypes@entry =0x0, numParams=numParams@entry =0) at analyze.c:100 #11 0x000000000057cc4e in DefineView ( stmt=stmt@entry =0x114f7e8, queryString=queryString@entry =0x114e6b0 "CREATE VIEW hstore_test_schema.hstore_test_view AS\nSELECT NULLIF(column1, column2) AS comparison FROM hstore_test_schema.hstore_test_table;") at view.c:385 #12 0x000000000065b1cf in ProcessUtilitySlow ( parsetree=parsetree@entry =0x114f7e8, queryString=0x114e6b0 "CREATE VIEW hstore_test_schema.hstore_test_view AS\nSELECT NULLIF(column1, column2) AS comparison FROM hstore_test_schema.hstore_test_table;", context=<optimized out>, params=params@entry =0x0, completionTag=completionTag@entry =0x7fffc98c9990 "", dest=<optimized out>) at utility.c:1207 #13 0x000000000065a54e in standard_ProcessUtility (parsetree=0x114f7e8, queryString=<optimized out>, context=<optimized out>, params=0x0, dest=<optimized out>, completionTag=0x7fffc98c9990 "") at utility.c:829 

so the immediate problem looks like this: transformAExprNullIf cannot find the statement using its operand type via b-tree opclass and typecache.