EnterpriseDB中有意思的特性（二）

1.数组类型的字段

定义一个包含字段的表：

CREATE TABLE empsal (
    ename          VARCHAR2(10),
    paybyqrtr      NUMBER [6],
    schedule        VARCHAR2 [2][2]
);

上述表中定义了paybyqrtr一维数组，以及一个schedule二维数组字段。

1)用字符串形式输入数组数据：用{}包含，用,分隔

INSERT INTO empsal
    VALUES ('JONES',
    '{10000, 10000, 10000, 10000}',
    '{{"MEETING", "LUNCH"}, {"TRAINING", "PRESENTATION"}}');

2)使用用ARRAY构造器建立数组

INSERT INTO empsal
    VALUES ('Bill',
    ARRAY[10000, 10000, 10000, 10000],
    ARRAY[['meeting', 'lunch'], ['training', 'presentation']]);

注意多维数组中，数组每个元素（也是一个数组）的维度必须一致，既不能出现锯齿形的多维数组。

3)用[]访问数组字段中的元素：

 SELECT ename FROM empsal WHERE paybyqrtr[1] <> paybyqrtr[2]; 

  ename
-------
 SCOTT
 Carol
(2 rows)

SELECT paybyqrtr[3] FROM empsal;

 paybyqrtr
-----------
     10000
     25000
     10000
     25000
(4 rows)

4)使用[lower-bound:upper-bound]方式获取数组的切片，或者子数组：

SELECT schedule[1:2][1:1] FROM empsal WHERE ename = 'Bill';
          schedule
------------------------
 {{meeting},{training}}
(1 row)

5)用array_dims 函数获取数组的维度

SELECT array_dims(schedule) FROM empsal WHERE ename = 'Carol';

 array_dims
------------
 [1:2][1:2]
(1 row)

6)更新整个数组字段

UPDATE empsal SET paybyqrtr = '{25000,25000,27000,27000}'
    WHERE ename = 'Carol';

7)更新数组字段中的一个元素

UPDATE empsal SET paybyqrtr[4] = 15000
    WHERE ename = 'Bill';

8)更新数组切片

UPDATE empsal SET paybyqrtr[1:2] = '{27000,27000}'
    WHERE ename = 'Carol';

2.索引

2.1基于表达式的索引

CREATE INDEX test1_lower_col1_idx ON test1 (lower(col1));

  建立了一个lower(col1)表达式的索引。

2.2 局部索引

局部索引是建立在一部分记录（满足一个过滤条件）上的索引。
假设有以下的表

CREATE TABLE access_log (
    url varchar,
    client_ip inet,
    
);

建立一个局部索引

CREATE INDEX access_log_client_ip_ix ON access_log (client_ip)
    WHERE NOT (client_ip > inet '192.168.100.0' AND client_ip < inet '192.168.100.255');

3. 规则系统

    EnterpriseDB的规则系统（准确的说，查询重写规则系统），和Trigger的概念有点类似，但是实现方式完全不同。规则系统通过修改查询来引入规则，然后把修改后的查询传递给后续的执行计划系统。
    规则系统介于语法分析器和执行计划之间，它接受分析器的输出（一个查询树），以及用户定义的查询重写规则（也是一个查询树），然后创建一个或者多个语法树作为输出传递给执行计划系统。
   那么，什么是查询树？查询数是SQL的语法树（从这一点来说，是SQL树而不是查询树，因为查询树容易误解为SELECT语句的语法树。）EnterpriseDB提供了输出语法树的函数：debug_print_parse, debug_print_rewritten, debug_print_plan。
  语法树中的重要部分：
1)命令类型，标识SQL语句的类型，SELECT, UPDATE或DELETE等
2)范围表。既SQL语句中用到的关系（表、视图），如果为SELECT语句，就是FROM和WHERE之间的表或视图。
3)结果表。标识SQL执行后产生的结果表。SELECT INTO语句会产生结果表。对于UPDATE，INSERT，DELETE，结果表为受影响的表。
4)目标列表：一系列表达式，定义了结果表的字段。
  a)对于SELECT语句，是SELECT和FROM关键字之间的内容。
  b)DELETE语句没有目标类表（但实际上，规则系统会在目标类表中增加一个CTID）。
  c)对于INSERT语句，目标列表描述插入到目标表中的内容，包括VALUES后的内容，或INSERT ... SELECT... 方式中的SELECT语句中的内容。重写过程的第一步是在目标列表中增加在SQL中没有出现，但包含默认值的字段，其他字段将有执行器添加，其值为Null。
  d)对于UPDATE命令，目标列表为将替换老记录的新纪录表达式，既SET column = expression部分。执行器将添加其他字段（值为老记录值）。规则系统将和DELETE语句类似的添加CTID。
  目标列表中的每个元素都是表达式，由常量、指向范围表的一个字段名，参数，或一个函数调用子树。
5)过滤条件(原文为Qualification)
  过滤条件和目标列表中的一个元素类似，是一个表达式，其结果类型为BOOL类型。对应于SQL中的WHERE子句的内容。
6)连接树
  当多表连接使用了ON、USING子句时，用连接树来表现不同表之间的连接关系。实际上，WHERE中的某些部分可能也和多表连接有关，因此WHERE中的某些内容也会出现在连接树中。
7)其它部分，如ORDER BY
  

3.1 视图和规则系统

EnterpriseDB中的视图使用规则系统实现。实际上，视图和规则系统没有本质的区别。比如

CREATE VIEW myview AS SELECT * FROM mytab;

所创建的视图和下列规则所做相同：

CREATE TABLE myview (same column list as mytab);
CREATE RULE "_RETURN" AS ON SELECT TO myview DO INSTEAD
    SELECT * FROM mytab;

对于解析器来说，它们并没有不同。

3.1.1 SELECT规则如何起作用 

    规则ON SELECT作用到查询上，规则的命令可以不仅仅是SELECT，也可以是INSERT，UPDATE，DELETE。和其他命令不同，它修改了原有的语法树，而不是创建一个新的语法树。
    目前的版本ON SELECT规则的动作（Action）只能有一个，并只能是INSTEAD SELECT。

 例子：
先创建一个min函数，返回两个数中的小值：

CREATE FUNCTION min(integer, integer) RETURNS integer AS $$
    SELECT CASE WHEN $1 < $2 THEN $1 ELSE $2 END
$$ LANGUAGE SQL STRICT;

规则中涉及的表--鞋、鞋带和换算单位：

CREATE TABLE shoe_data (
    shoename   text,          -- primary key
    sh_avail   integer,       -- available number of pairs
    slcolor    text,          -- preferred shoelace color
    slminlen   real,          -- minimum shoelace length
    slmaxlen   real,          -- maximum shoelace length
    slunit     text           -- length unit
);

CREATE TABLE shoelace_data (
    sl_name    text,          -- primary key
    sl_avail   integer,       -- available number of pairs
    sl_color   text,          -- shoelace color
    sl_len     real,          -- shoelace length
    sl_unit    text           -- length unit
);

CREATE TABLE unit (
    un_name    text,          -- primary key
    un_fact    real           -- factor to transform to cm
);

涉及到的几个视图

CREATE VIEW shoe AS
    SELECT sh.shoename,
           sh.sh_avail,
           sh.slcolor,
           sh.slminlen,
           sh.slminlen * un.un_fact AS slminlen_cm,
           sh.slmaxlen,
           sh.slmaxlen * un.un_fact AS slmaxlen_cm,
           sh.slunit
      FROM shoe_data sh, unit un
     WHERE sh.slunit = un.un_name;

CREATE VIEW shoelace AS
    SELECT s.sl_name,
           s.sl_avail,
           s.sl_color,
           s.sl_len,
           s.sl_unit,
           s.sl_len * u.un_fact AS sl_len_cm
      FROM shoelace_data s, unit u
     WHERE s.sl_unit = u.un_name;

CREATE VIEW shoe_ready AS
    SELECT rsh.shoename,
           rsh.sh_avail,
           rsl.sl_name,
           rsl.sl_avail,
           min(rsh.sh_avail, rsl.sl_avail) AS total_avail
      FROM shoe rsh, shoelace rsl
     WHERE rsl.sl_color = rsh.slcolor
       AND rsl.sl_len_cm >= rsh.slminlen_cm
       AND rsl.sl_len_cm <= rsh.slmaxlen_cm;

     CREATE VIEW命令shoelace创建了一个关系shoelace，以及pg_rewrite中的一个实体来说明，当引用到shoelace视图时，执行一个重写规则，这个规则没有过滤条件，没有SELECT规则，是一个替换(INSTEAD)规则。

    需要注意规则的过滤条件不同于查询的过滤条件。规则的动作(action)包含查询过滤条件。规则的动作的查询树是查询定义命令的拷贝。
   下面是对涉及到的表和视图的操作：

INSERT INTO unit VALUES ('cm', 1.0);
INSERT INTO unit VALUES ('m', 100.0);
INSERT INTO unit VALUES ('inch', 2.54);
INSERT INTO shoe_data VALUES ('sh1', 2, 'black', 70.0, 90.0, 'cm');
INSERT INTO shoe_data VALUES ('sh2', 0, 'black', 30.0, 40.0, 'inch');
INSERT INTO shoe_data VALUES ('sh3', 4, 'brown', 50.0, 65.0, 'cm');
INSERT INTO shoe_data VALUES ('sh4', 3, 'brown', 40.0, 50.0, 'inch');
INSERT INTO shoelace_data VALUES ('sl1', 5, 'black', 80.0, 'cm');
INSERT INTO shoelace_data VALUES ('sl2', 6, 'black', 100.0, 'cm');
INSERT INTO shoelace_data VALUES ('sl3', 0, 'black', 35.0 , 'inch');
INSERT INTO shoelace_data VALUES ('sl4', 8, 'black', 40.0 , 'inch');
INSERT INTO shoelace_data VALUES ('sl5', 4, 'brown', 1.0 , 'm');
INSERT INTO shoelace_data VALUES ('sl6', 0, 'brown', 0.9 , 'm');
INSERT INTO shoelace_data VALUES ('sl7', 7, 'brown', 60 , 'cm');
INSERT INTO shoelace_data VALUES ('sl8', 1, 'brown', 40 , 'inch');
SELECT * FROM shoelace;
sl_name   | sl_avail | sl_color | sl_len | sl_unit | sl_len_cm
-----------+----------+----------+--------+---------+-----------
sl1       |        5 | black    |     80 | cm      |        80
sl2       |        6 | black    |    100 | cm      |       100
sl7       |        7 | brown    |     60 | cm      |        60
sl3       |        0 | black    |     35 | inch    |      88.9
sl4       |        8 | black    |     40 | inch    |     101.6
sl8       |        1 | brown    |     40 | inch    |     101.6
sl5       |        4 | brown    |      1 | m       |       100
sl6       |        0 | brown    |    0.9 | m       |        90
(8 rows)

其中的SELECT命令被解析器解析为：

SELECT shoelace.sl_name, shoelace.sl_avail,

       shoelace.sl_color, shoelace.sl_len,

       shoelace.sl_unit, shoelace.sl_len_cm

  FROM shoelace shoelace;

所产生的语法树输出到规则系统后，对shoelace视图进行展开：

SELECT s.sl_name, s.sl_avail,

       s.sl_color, s.sl_len, s.sl_unit,

       s.sl_len * u.un_fact AS sl_len_cm

  FROM shoelace *OLD*, shoelace *NEW*,

       shoelace_data s, unit u

 WHERE s.sl_unit = u.un_name;


这和下列查询的结果类似

SELECT shoelace.sl_name, shoelace.sl_avail,

       shoelace.sl_color, shoelace.sl_len,

       shoelace.sl_unit, shoelace.sl_len_cm

  FROM (SELECT s.sl_name,

               s.sl_avail,

               s.sl_color,

               s.sl_len,

               s.sl_unit,

               s.sl_len * u.un_fact AS sl_len_cm

          FROM shoelace_data s, unit u

         WHERE s.sl_unit = u.un_name) shoelace;

然而，两者之间还是有些区别：基于视图的查询包含两个特殊的实体shoelace *OLD* 和shoelace *NEW*。这两个实体并不直接参与查询，因为它们没有出现在连接树或者目标列表节点中。规则系统使用这两个实体来引用在系统表中的权限信息，来确保SQL执行用户有合适的权限。

第二个例子，查询和鞋带配对、库存量大于2的鞋：

SELECT * FROM shoe_ready WHERE total_avail >= 2;
shoename | sh_avail | sl_name | sl_avail | total_avail
----------+----------+---------+----------+-------------
sh1      |        2 | sl1     |        5 |           2
sh3      |        4 | sl7     |        7 |           4
(2 rows)

解析器生成的结果类似于下面的查询：

SELECT shoe_ready.shoename, shoe_ready.sh_avail,
       shoe_ready.sl_name, shoe_ready.sl_avail,
       shoe_ready.total_avail
  FROM shoe_ready shoe_ready
 WHERE shoe_ready.total_avail >= 2;

sheo_ready视图规则作用于查询语法树后生成：

SELECT shoe_ready.shoename, shoe_ready.sh_avail,
       shoe_ready.sl_name, shoe_ready.sl_avail,
       shoe_ready.total_avail
  FROM (SELECT rsh.shoename,
               rsh.sh_avail,
               rsl.sl_name,
               rsl.sl_avail,
               min(rsh.sh_avail, rsl.sl_avail) AS total_avail
          FROM shoe rsh, shoelace rsl
         WHERE rsl.sl_color = rsh.slcolor
           AND rsl.sl_len_cm >= rsh.slminlen_cm
           AND rsl.sl_len_cm <= rsh.slmaxlen_cm) shoe_ready
 WHERE shoe_ready.total_avail >= 2;

再次展开shoe和shoelace后生成一个三层的语法树：

SELECT shoe_ready.shoename, shoe_ready.sh_avail,
       shoe_ready.sl_name, shoe_ready.sl_avail,
       shoe_ready.total_avail
  FROM (SELECT rsh.shoename,
               rsh.sh_avail,
               rsl.sl_name,
               rsl.sl_avail,
               min(rsh.sh_avail, rsl.sl_avail) AS total_avail
          FROM (SELECT sh.shoename,
                       sh.sh_avail,
                       sh.slcolor,
                       sh.slminlen,
                       sh.slminlen * un.un_fact AS slminlen_cm,
                       sh.slmaxlen,
                       sh.slmaxlen * un.un_fact AS slmaxlen_cm,
                       sh.slunit
                  FROM shoe_data sh, unit un
                 WHERE sh.slunit = un.un_name) rsh,
               (SELECT s.sl_name,
                       s.sl_avail,
                       s.sl_color,
                       s.sl_len,
                       s.sl_unit,
                       s.sl_len * u.un_fact AS sl_len_cm
                  FROM shoelace_data s, unit u
                 WHERE s.sl_unit = u.un_name) rsl
         WHERE rsl.sl_color = rsh.slcolor
           AND rsl.sl_len_cm >= rsh.slminlen_cm
           AND rsl.sl_len_cm <= rsh.slmaxlen_cm) shoe_ready
 WHERE shoe_ready.total_avail > 2;

在执行计划中，这个树会被优化为一个两层的树：最底层的SELECT将被合并到中间一层的SELECT，但不会和第一层合并，因为包含了一个min聚合函数，提升这一层将会改变顶层SELECT的行为。这个优化动作实在执行计划中进行的，与规则系统无关。

3.1.2 非SELECT语句的规则

查询树有两个特性在上述例子中还没有涉及。它们是命令类型和结果表。实际上，视图规则不需要这些信息。SELECT命令和其他命令的语法树有几点不同。很明显，命令类型和SELECT命令不同，同时，结果表标明命令运行结果实体在系统实体表中的位置。查询树中的其余部分相同。比如，下列两个SQL：

SELECT t2.b FROM t1, t2 WHERE t1.a = t2.a;

UPDATE t1 SET b = t2.b WHERE t1.a = t2.a;

它们的语法树基本相同。
1)范围表为t1和t2
2)目标列表包含t2的b字段
3)过滤条件表达式比较t1,t2的a字段
4)连接树用a字段相同方式连接t1, t2

4 关于INSERT, UPDATE, DELETE的规则

    定义在INSERT、UPDATE和DELETE的规则和视图规则差异很大。
    首先，它们的CREATE RULE命令支持更多的语法：

• 可以是INSTEAD或ALSO(默认) ；
• 两个伪表NEW、OLD可以发生作用；
• 它们可以包括规则过滤条件。

   其次，它们创建一个或多个新的语法树来取代原有语法树，而不是修改原有语法树。

4.1 更新规则如何起作用

    创建规则的语法：  

CREATE [ OR REPLACE ] RULE name AS ON event
    TO table [ WHERE condition ]
    DO [ ALSO | INSTEAD ] { NOTHING | command | ( command ; command  ) }

    当规则系统判断将被执行的语法树的表和事件和规则匹配时，规则将被启用，所产生的语法树被传递到执行器进行执行。
   规则的过滤条件用来判断什么情况下规则可以运行，什么时候不需要。规则的过滤条件只引用两个伪表NEW和OLD。同时，原语法树的过滤条件会被添加到规则的过滤条件中。
   ALSO关键字意味着原语法树会被增加到规则语法树列表中。
   对于ON INSERT规则，ALSO关键字下，原语法树在规则的语法树之前执行。这时规则可以看到新增加的数据。对于ON UPDATE和ON DELETE规则，原语法树在规则的语法树之后执行，这保证规则能够看到被修改的记录。

例子

 

 

CREATE TABLE shoelace_log (
    sl_name    text,          -- shoelace changed
    sl_avail   integer,       -- new available value
    log_who    text,          -- who did it
    log_when   timestamp      -- when
);

CREATE RULE log_shoelace AS ON UPDATE TO shoelace_data
    WHERE NEW.sl_avail <> OLD.sl_avail
    DO INSERT INTO shoelace_log VALUES (
                                    NEW.sl_name,
                                    NEW.sl_avail,
                                    current_user,
                                    current_timestamp
                                );

我们建立一个规则，来跟踪对shoelace_data的sl_avail字段的修改，并记录到shoelace_log中。在进行了以下更新后：

UPDATE shoelace_data SET sl_avail = 6 WHERE sl_name = 'sl7';

log表中的数据为：

SELECT * FROM shoelace_log;

 sl_name | sl_avail | log_who | log_when                        
---------+----------+---------+----------------------------------
 sl7     |        6 | Al      | Tue Oct 20 16:14:45 1998 MET DST
(1 row)

执行的步骤：
1.解析器创建的查询树:

UPDATE shoelace_data SET sl_avail = 6
  FROM shoelace_data shoelace_data
 WHERE shoelace_data.sl_name = 'sl7';

2.ON UPDATE的规则

INSERT INTO shoelace_log VALUES (
       *NEW*.sl_name, *NEW*.sl_avail,
       current_user, current_timestamp )
  FROM shoelace_data *NEW*, shoelace_data *OLD*;

3.原SQL命令的范围表加入到规则中：

INSERT INTO shoelace_log VALUES (
       *NEW*.sl_name, *NEW*.sl_avail,
       current_user, current_timestamp )
  FROM shoelace_data *NEW*, shoelace_data *OLD*,
       shoelace_data shoelace_data;

4.考虑到规则的过滤条件：

INSERT INTO shoelace_log VALUES (
       *NEW*.sl_name, *NEW*.sl_avail,
       current_user, current_timestamp )
  FROM shoelace_data *NEW*, shoelace_data *OLD*,
       shoelace_data shoelace_data
 WHERE *NEW*.sl_avail <> *OLD*.sl_avail;

SQL的INSERT命令并没有WHERE子句，但对于执行器来说，这并不存在问题。
5.加入原SQL的过滤条件

INSERT INTO shoelace_log VALUES (
       *NEW*.sl_name, *NEW*.sl_avail,
       current_user, current_timestamp )
  FROM shoelace_data *NEW*, shoelace_data *OLD*,
       shoelace_data shoelace_data
 WHERE *NEW*.sl_avail <> *OLD*.sl_avail
   AND shoelace_data.sl_name = 'sl7';

6.替换NEW表的变量。这里是使用原SQL命令中的表达式：

INSERT INTO shoelace_log VALUES (
       shoelace_data.sl_name, 6,
       current_user, current_timestamp )
  FROM shoelace_data *NEW*, shoelace_data *OLD*,
       shoelace_data shoelace_data
 WHERE 6 <> *OLD*.sl_avail
   AND shoelace_data.sl_name = 'sl7';

7.用原表替换OLD表字段的引用：

INSERT INTO shoelace_log VALUES (
       shoelace_data.sl_name, 6,
       current_user, current_timestamp )
  FROM shoelace_data *NEW*, shoelace_data *OLD*,
       shoelace_data shoelace_data
 WHERE 6 <> *OLD*.sl_avail
   AND shoelace_data.sl_name = 'sl7';

8.因为这是一个ALSO类型的规则，加上原SQL命令

INSERT INTO shoelace_log VALUES (
       shoelace_data.sl_name, 6,
       current_user, current_timestamp )
  FROM shoelace_data
 WHERE 6 <> shoelace_data.sl_avail
   AND shoelace_data.sl_name = 'sl7';

UPDATE shoelace_data SET sl_avail = 6
 WHERE sl_name = 'sl7';