在视图中进行hstore比较转储和还原PostgreSQL数据库失败 [英] Dump and restore of PostgreSQL database with hstore comparison in view fails

问题描述

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

当我转储并还原该数据库时，恢复失败并显示以下错误消息：

 导入/tmp/hstore_test_2014-05-12.backup ... 
 pg_restore：[archiver（db）]处理目录时出错：
 pg_restore：[archiver（db）]来自目录项172的错误； 1259 1358132查看hstore_test_view xxxx 
 pg_restore：[存档（db）]无法执行查询：错误：运算符不存在：public.hstore = public.hstore 
第2行：SELECT NULLIF（hstore_test_table.column1， hstore_test_table .... 
 ^ 
提示：没有运算符匹配给定的名称和参数类型，您可能需要添加显式类型强制转换
命令为：CREATE VIEW hstore_test_view AS 
选择NULLIF（hstore_test_table.column1，hstore_test_table.column2）AS nullif 
从hst ... 
 pg_restore：[存档（db）]无法执行查询：错误：关系 hstore_test_schema。 hstore_test_view不存在
命令是：ALTER TABLE hstore_test_schema.hstore_test_view所有者到xxxx; 
  

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

 创建数据库hstore_test; 
 
 \c hstore_test 
 
使用SCHEMA创建扩展hstore public; 
 
 CREATE SCHEMA hstore_test_schema; 
 
创建表hstore_test_schema.hstore_test_table（
 id int，
 column1 hstore，
 column2 hstore，
 PRIMARY KEY（id）
）; 
 
创建视图hstore_test_schema.hstore_test_view AS 
选择NULLIF（column1，column2）AS比较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删除数据库hstore_test 
 psql -U xxxx -h本地主机-d postgres -c创建数据库hstore_test 
 pg_restore -U xxxx -h本地主机-d hstore_test / tmp / hstore_test_2014-05- 12.备份
  

pg_restore -l / tmp / hstore_test_2014-05-12。 backup 建议在创建视图之前启用 hstore 扩展名：

 ; 
;存档创建于2014年5月12日星期一11:18:32 
; dbname：hstore_test 
; TOC条目：15 
;压缩：-1 
;转储版本：1.12-0 
;格式：CUSTOM 
;整数：4个字节
；偏移量：8个字节
；从数据库版本转储：9.3.0 
;由pg_dump版本转储：9.3.0 
; 
; 
;选定的TOC条目：
； 
 2074; 1262 1358002数据库-hstore_test xxxx 
 7; 2615 1358003模式-hstore_test_schema xxxx 
 5; 2615 2200 SCHEMA-公共邮政
 2075； 0 0评论-SCHEMA公共邮政
 2076; 0 0 ACL-公共邮政
 173； 3079 11787扩展-plpgsql 
 2077; 0 0评论-扩展plpgsql 
 174; 3079 1358004扩展名-hstore 
 2078; 0 0评论-扩展hstore 
 171; 1259 1358124表hstore_test_schema hstore_test_table xxxx 
 172; 1259 1358132查看hstore_test_schema hstore_test_view xxxx 
 2069; 0 1358124表数据hstore_test_schema hstore_test_table xxxx 
 1960; 2606 1358131 CONSTTRAINT hstore_test_schema hstore_test_table_pkey xxxx 
  

顺便说一下，替换 NULLIF（col1，col2 ）与 col1 = col2 似乎使错误消失了，尽管它是 pg_restore 抱怨。

解决方案

这是PostgreSQL的一个错误。 我已将您的报告转发给了pgsql-bugs列表。 / p>

正在发生的事情是 pg_dump 将 search_path 设置为在架构中创建表时，请排除 public 。这个是正常的。当它转储引用不在 search_path 上的东西的对象时，它将明确地对它们进行模式限定，以便它们起作用。

它适用于 = 的情况，因为 pg_dump 认为 = 实际上是 OPERATOR（public。=），并以这种形式转储它：

 创建视图hstore_test_view AS 
选择（hstore_test_table.column1 OPERATOR（public。=）hstore_test_table.column2）AS比较
从hstore_test_table; 
  

但是， pg_dump 不能这样做通过 nullif 伪函数隐式使用的运算符。这将导致以下虚假命令序列：

 如果不存在SCHEMA public，则创建扩展。 
 ... 
 SET search_path = hstore_test_schema，pg_catalog; 
 ... 
创建视图hstore_test_view AS 
选择NULLIF（hstore_test_table.column1，hstore_test_table.column2）AS比较
从hstore_test_table; 
  

pg_dump 仅使用 pg_catalog.pg_get_viewdef 函数来转储视图，因此这可能需要服务器后端修复。

最简单的解决方法是不使用 nullif ，用更详细但等效的 case 代替：

 当column1 = column2 THEN NULL ELSE column1 END;时； 
  

语法不提供对 nullif 伪函数的运算符就像我们对显式 OPERATOR（public。=）所做的那样，因此修复似乎并不简单。

我希望同一问题会影响 GREATEST 和最小，也许也是 DISTINCT ，但事实并非如此。即使它们在运行时不在 search_path 上，它们似乎都找到了所需的运算符，但是如果运算符在视图定义时不在search_path上，它们也不会失败。这表明他们可能正在使用该类型的b树运算符类，通过表的属性在目录中找到该类型的条目来查找这些运算符。 （更新：检查了源，是的，它们就是这样做的）。大概 nullif 也应该这样做，但是不是。

相反，它死于：

  hstore_test =＃\设置VERBOSITY verbose 
 hstore_test =＃CREATE VIEW hstore_test_schema.hstore_test_view AS 
 SELECT NULLIF（column1， column2）AS比较FROM hstore_test_schema.hstore_test_table; 
错误：42883：操作符不存在：public.hstore = public.hstore 
第2行：选择NULLIF（column1，column2）从hstore_te进行比较... 
 ^ 
提示：没有运算符匹配给定的名称和参数类型。您可能需要添加显式类型转换。 
位置：op_error，parse_oper.c：722 
  

在我在那里设置断点时，陷阱位于：

 断点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）在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）在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 ）在parse_oper.c：440 
＃2 0x000000000051ad34在make_op（pstate = pstate @ entry = 0x1189f38，opname = 0x1189c10，ltre e = ltree @ entry = 0x118a528，rtree = 0x118a590，location = 58）在parse_oper.c：770 
＃3 0x00000000005155e1在parse_expr.c：transformAExprNullIf（a = 0x1189bc0，pstate = 0x1189f38）：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在parse_expr.c上的EXPR_KIND_SELECT_TARGET）：116 
＃6 transformTargetEntry（pstate = pstate @ entry = 0x1189f38，node = 0x1189bc0，expr = expr @ entry = 0x0，exprKind = exprKind @ entry = EXPR_KIND_SELECT）中的0x000000000051ff30 colname = 0x1189ba0比较，resjunk = resjunk @ entry = 0'\000'）在parse_target.c：94 
＃7 0x00000000005212df在transformTargetList（pstate = pstate @ entry = 0x1189f38，targetlist = 在parse_target.c：167 
＃8 0x00000000004ef594在transformSelectStmt（stmt = 0x11899f0，pstate = 0x1189f38）中：942 
＃9 transformStmt（pstate = 0x1189f38，parseTree = 0x11899f0）at analytics.c：243 
＃10 0x00000000004f0a2d in parse_analyze（parseTree = 0x11899f0，
 sourceText = sourceText @ entry = 0x114e6b0创建视图hstore_test_schema.hstore_test_view AS\nSELECT NULLIF（column1，column2）来自hstore_test_schema.hstore_test_table;的AS比较；，paramTypes = paramTypes @ entry = 0x0，numParams = numParams @ entry = 0）位于analytics.c：100 
 ＃11 0x000000000057cc4e在DefineView中（stmt = stmt @ entry = 0x114f7e8，
 queryString = queryString @ entry = 0x114e6b0 CREATE VIEW hstore_test_schema.hstore_test_view AS\nSELECT NULLIF（column1，column2）从hstore_test_schema进行AS比较；在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比较hstore_test_schema.hstore_test_table;，
 context =<已优化out>，params = params @ entry = 0x0，complementTag = completionTag @ entry = 0x7fffc98c9990，dest =<已优化out>）在Utility.c：1207 
＃13 0x000000000065a54e在standard_ProcessUtility（parsetree = 0x114f7e8，queryString = <<最优化输出>，context = <最优化输出>，params = 0x0，dest = <<最优化输出>，
 completeTag = 0x7fffc98c9990）at utility.c：829 
  

，因此眼前的问题看起来像 transformAExprNullIf 无法使用通过b树opclass和类型缓存的操作数类型。

I have a view which compares two hstore columns.

When I dump and restore this database, the restore 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 was able 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 restore 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 suggests that the hstore extension is 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

Incidentally, replacing the NULLIF(col1, col2) with col1 = col2 seems to make the error disappear, despite the fact it's an explicit comparison of the type pg_restore was complaining of.

解决方案

This is a PostgreSQL bug. I have relayed your report to the pgsql-bugs list.

What's happening is that pg_dump is setting the search_path to exclude public when creating tables in your schema. This is normal. When it dumps objects that refer to things that aren't on the search_path, it explicitly schema-qualifies them so they work.

It works for the = case because pg_dump sees that = is actually OPERATOR(public.=) in this case, and dumps it in that 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 fails to do this for the operator implicitly used via the nullif pseudo-function. That results in the following bogus command sequence:

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 just uses the pg_catalog.pg_get_viewdef function to dump the view, so this probably requires a server backend fix.

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

CASE WHEN column1 = column2 THEN NULL ELSE column1 END;

The syntax doesn't provide a way to schema-qualify the nullif pseudo-function's operator like we do with explicit OPERATOR(public.=), so the fix doesn't appear to be trivial.

I expected the same issue to affect GREATEST and LEAST, perhaps also DISTINCT, but it doesn't. Both seem to find their required operators even when they aren't on the search_path at runtime, but don't fail if the operator isn't on the search_path at view definition time. That suggests they're probably using the type's b-tree operator class to look up the operators, via the type's entry in the catalogs as found via the table's attributes. (Update: checked the sources and yes, that's what they do). Presumably nullif should also be doing this, but isn't.

Instead it 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 at:

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 issue looks like transformAExprNullIf failing to look up the operator using the type of its operand via the b-tree opclass and the typecache.

这篇关于在视图中进行hstore比较转储和还原PostgreSQL数据库失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！