在视图中使用hstore比较转储和恢复PostgreSQL数据库失败

发布时间：2020-12-13 16:09:54 所属栏目：百科来源：网络整理

导读：我有一个比较两个hstore列的视图. 当我转储和还原此数据库时,还原失败并显示以下错误消息： Importing /tmp/hstore_test_2014-05-12.backup...pg_restore: [archiver (db)] Error while PROCESSING TOC:pg_restore: [archiver (db)] Error from TOC entry 17

我有一个比较两个hstore列的视图.

当我转储和还原此数据库时,还原失败并显示以下错误消息：

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;

我能够通过以下步骤在PostgreSQL 9.3.0中创建此错误：

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;

为完整起见,转储和恢复过程如下所示：

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建议在创建视图之前启用hstore扩展：

;
; 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

顺便说一句,用col1 = col2替换NULLIF(col1,col2)似乎会使错误消失,尽管它是pg_restore抱怨的类型的显式比较.

解决方法

这是一个PostgreSQL错误. I have relayed your report to the pgsql-bugs list.

发生的事情是pg_dump正在设置search_path以在模式中创建表时排除public.这个是正常的.当它转储引用不在search_path上的东西的对象时,它显式地对它们进行模式限定以使它们起作用.

它适用于= case,因为pg_dump在这种情况下看到=实际上是OPERATOR(public.=),并以该形式转储它：

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

但是,对于通过nullif伪函数隐式使用的运算符,pg_dump无法执行此操作.这导致以下伪造命令序列：

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只是使用pg_catalog.pg_get_viewdef函数来转储视图,因此这可能需要服务器后端修复.

最简单的解决方法是不使用nullif,将其替换为更冗长但等效的情况：

CASE WHEN column1 = column2 THEN NULL ELSE column1 END;

语法没有像使用显式的OPERATOR(public.=)那样提供对nullif伪函数运算符进行模式限定的方法,因此修复似乎并不简单.

我预计同样的问题会影响GREATEST和LEAST,也许也会影响DISTINCT,但事实并非如此.两者似乎都找到了他们所需的运算符,即使它们在运行时不在search_path上,但如果运算符在视图定义时不在search_path上,则不会失败.这表明他们可能正在使用类型的b-tree运算符类来查找运算符,通过表中的类型条目(通过表的属性找到). (更新：检查来源,是的,这就是他们所做的).大概nullif也应该这样做,但事实并非如此.

相反它死在：

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

当我在那里设置一个断点时,陷阱：

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,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 '00',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 '00') 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 ASnSELECT 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 ASnSELECT 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 ASnSELECT NULLIF(column1,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>,params=0x0,dest=<optimized out>,completionTag=0x7fffc98c9990 "") at utility.c:829

所以直接问题看起来像transformAExprNullIf无法通过b-tree opclass和类型标记使用其操作数的类型查找运算符.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!