• 编写Postgres扩展之二：类型和运算符

    在上一篇关于编写Postgres Extensions的文章中，我们介绍了扩展PostgresQL的基础知识。现在是有趣的部分来了——开发我们自己的类型。

    一个小小的免责声明

    最好不要急于复制和粘贴本文中的代码。文中的代码有一些严重的bug，这些bug是为了说明解释的目的而故意留下的。如果您正在寻找可用于生产的base36类型定义，请查看这里。

    复习一下base36

    我们需要的是一个用于存储和检索base36数字的base36数据类型的可靠实现。我们已经为扩展创建了基本框架，包括base36、controler和Makefile，您可以在专门用于本系列博客文章的GitHub repo中找到它们。您可以查看我们在第1部分中得到的结果，本文中的代码可以在第2部分分支中找到。

    文件名:base36.control

    # base36 extension
comment = 'base36 datatype'
default_version = '0.0.1'
relocatable = true

    文件名：Makefile

    EXTENSION = base36              # 扩展名称
DATA      = base36--0.0.1.sql   # 用于安装的脚本文件
REGRESS   = base36_test         # 我们的测试脚本文件（没有后缀名）
MODULES   = base36              # 我们要构建的C模块文件

# Postgres build stuff
PG_CONFIG = pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)

    Postgres中的自定义数据类型

    让我们重写SQL脚本文件，以显示我们自己的数据类型

    文件名：base36-0.0.1.sql

    -- complain if script is sourced in psql, rather than via CREATE EXTENSION
echo Use "CREATE EXTENSION base36" to load this file. quit

CREATE FUNCTION base36_in(cstring)
RETURNS base36
AS '$libdir/base36'
LANGUAGE C IMMUTABLE STRICT;

CREATE FUNCTION base36_out(base36)
RETURNS cstring
AS '$libdir/base36'
LANGUAGE C IMMUTABLE STRICT;

CREATE TYPE base36 (
  INPUT          = base36_in,
  OUTPUT         = base36_out,
  LIKE           = integer
);

    这是在Postgres中创建基类型所需的最低要求：我们需要输入和输出两个函数，它们告诉Postgres如何将输入文本转换为内部表示(base36 in)，然后再从内部表示转换为文本(base36 out)。我们还需要告诉Postgres将我们的类型视为integer。这也可以通过在类型定义中指定这些附加参数来实现，如下例所示:

    INTERNALLENGTH = 4,     -- use 4 bytes to store data
ALIGNMENT      = int4,  -- align to 4 bytes
STORAGE        = PLAIN, -- always store data inline uncompressed (not toasted)
PASSEDBYVALUE           -- pass data by value rather than by reference

    现在我们来修改C语言部分：
    文件名：base36.c

    #include "postgres.h"
#include "fmgr.h"
#include "utils/builtins.h"

PG_MODULE_MAGIC;

PG_FUNCTION_INFO_V1(base36_in);
Datum
base36_in(PG_FUNCTION_ARGS)
{
    long result;
    char *str = PG_GETARG_CSTRING(0);
    result = strtol(str, NULL, 36);
    PG_RETURN_INT32((int32)result);
}

PG_FUNCTION_INFO_V1(base36_out);
Datum
base36_out(PG_FUNCTION_ARGS)
{
    int32 arg = PG_GETARG_INT32(0);
    if (arg < 0)
        ereport(ERROR,
            (
             errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
             errmsg("negative values are not allowed"),
             errdetail("value %d is negative", arg),
             errhint("make it positive")
            )
        );
    char base36[36] = "0123456789abcdefghijklmnopqrstuvwxyz";

    /* max 6 char + '' */
    char *buffer        = palloc(7 * sizeof(char));
    unsigned int offset = 7 * sizeof(char);
    buffer[--offset]    = '';

    do {
        buffer[--offset] = base36[arg % 36];
    } while (arg /= 36);

    PG_RETURN_CSTRING(&buffer[offset]);
}

    我们基本上只是重复使用base36_encode函数作为我们的OUTPUT并添加了INPUT解码功能 - So Easy!

    现在我们可以在数据库中存储和检索base36数字。 让我们构建并测试它。

    make clean && make && make install

    test=# CREATE TABLE base36_test(val base36);
CREATE TABLE
test=# INSERT INTO base36_test VALUES ('123'), ('3c'), ('5A'), ('zZz');
INSERT 0 4
test=# SELECT * FROM base36_test;
 val
-----
 123
 3c
 5a
 zzz
(4 rows)

    直到现在一切正常。让我们对输出进行排序。

    test=# SELECT * FROM base36_test ORDER BY val;
ERROR:  could not identify an ordering operator for type base36
LINE 1: SELECT * FROM base36_test ORDER BY val;
                                           ^
HINT:  Use an explicit ordering operator or modify the query.

    嗯……看来我们漏掉了什么。

    运算符

    请记住，我们正在处理一个完全空白原始的数据类型。为了进行排序，我们需要定义数据类型的实例小于另一个实例、大于另一个实例或两个实例相等的含义。

    这不应该太奇怪 - 实际上，它类似于如何在Ruby类中包含Enumerable mixin或者在Golang类型中实现sort.Interface来引入对象的排序规则。(或者对于一个python对象实现__eq__、__lt__等魔法方法，sort函数实现key-lamda)

    让我们将比较函数和操作符添加到SQL脚本中。

    文件名：base36–0.0.1.sql

    -- type definition omitted

CREATE FUNCTION base36_eq(base36, base36)
RETURNS boolean LANGUAGE internal IMMUTABLE AS 'int4eq';

CREATE FUNCTION base36_ne(base36, base36)
RETURNS boolean LANGUAGE internal IMMUTABLE AS 'int4ne';

CREATE FUNCTION base36_lt(base36, base36)
RETURNS boolean LANGUAGE internal IMMUTABLE AS 'int4lt';

CREATE FUNCTION base36_le(base36, base36)
RETURNS boolean LANGUAGE internal IMMUTABLE AS 'int4le';

CREATE FUNCTION base36_gt(base36, base36)
RETURNS boolean LANGUAGE internal IMMUTABLE AS 'int4gt';

CREATE FUNCTION base36_ge(base36, base36)
RETURNS boolean LANGUAGE internal IMMUTABLE AS 'int4ge';

CREATE FUNCTION base36_cmp(base36, base36)
RETURNS integer LANGUAGE internal IMMUTABLE AS 'btint4cmp';

CREATE FUNCTION hash_base36(base36)
RETURNS integer LANGUAGE internal IMMUTABLE AS 'hashint4';

CREATE OPERATOR = (
  LEFTARG = base36,
  RIGHTARG = base36,
  PROCEDURE = base36_eq,
  COMMUTATOR = '=',
  NEGATOR = '<>',
  RESTRICT = eqsel,
  JOIN = eqjoinsel,
  HASHES, MERGES
);

CREATE OPERATOR <> (
  LEFTARG = base36,
  RIGHTARG = base36,
  PROCEDURE = base36_ne,
  COMMUTATOR = '<>',
  NEGATOR = '=',
  RESTRICT = neqsel,
  JOIN = neqjoinsel
);

CREATE OPERATOR < (
  LEFTARG = base36,
  RIGHTARG = base36,
  PROCEDURE = base36_lt,
  COMMUTATOR = > ,
  NEGATOR = >= ,
  RESTRICT = scalarltsel,
  JOIN = scalarltjoinsel
);

CREATE OPERATOR <= (
  LEFTARG = base36,
  RIGHTARG = base36,
  PROCEDURE = base36_le,
  COMMUTATOR = >= ,
  NEGATOR = > ,
  RESTRICT = scalarltsel,
  JOIN = scalarltjoinsel
);

CREATE OPERATOR > (
  LEFTARG = base36,
  RIGHTARG = base36,
  PROCEDURE = base36_gt,
  COMMUTATOR = < ,
  NEGATOR = <= ,
  RESTRICT = scalargtsel,
  JOIN = scalargtjoinsel
);

CREATE OPERATOR >= (
  LEFTARG = base36,
  RIGHTARG = base36,
  PROCEDURE = base36_ge,
  COMMUTATOR = <= ,
  NEGATOR = < ,
  RESTRICT = scalargtsel,
  JOIN = scalargtjoinsel
);

CREATE OPERATOR CLASS btree_base36_ops
DEFAULT FOR TYPE base36 USING btree
AS
        OPERATOR        1       <  ,
        OPERATOR        2       <= ,
        OPERATOR        3       =  ,
        OPERATOR        4       >= ,
        OPERATOR        5       >  ,
        FUNCTION        1       base36_cmp(base36, base36);

CREATE OPERATOR CLASS hash_base36_ops
    DEFAULT FOR TYPE base36 USING hash AS
        OPERATOR        1       = ,
        FUNCTION        1       hash_base36(base36);

    哇…太多了。对其进行分解:首先，我们为每一个比较运算符定义了一个比较函数进行赋能（<, <=, =, >= 和 >）。然后我们将它们放在一个操作符类中，这个操作符类将使我们能够在新的数据类型上创建索引。

    对于函数本身，我们可以简单地为integer类型重用相应的内置函数：int4eq, int4ne, int4lt, int4le, int4gt, int4ge, btint4cmp 和 hashint4。

    现在让我们老看看运算符定义。

    每一个运算符都有一个左参数（LEFTARG），一个右参数（RIGHTARG）和 一个函数（PROCEDURE）。

    因此，如果我们进行下面的操作：

    SELECT 'larg'::base36 < 'rarg'::base36;
 ?column?
----------
 t
(1 row)

    Postgresql将会使用base36_lt函数暨base36_lt('larg','rarg')进行对两个base36类型的数据进行比较。

    COMMUTATOR 和 NEGATOR

    每个运算符还有一个COMMUTATOR和一个NEGATOR(参见第52-53行)。查询规划器使用它们进行优化。commutator是应该用于表示相同结果但是翻转参数的运算符。由于对于所有可能的值x和y ，(x < y) = (y > x)，所以操作符>是操作符<的commutator。同理，操作符<是操作符>的commutator。否定器是否定运算符布尔结果的运算符。也就是说，对于所有可能的值x和y， (x < y) = NOT(x >= y)。

    为什么这很重要呢?假设您已经索引了val列：

    EXPLAIN SELECT * FROM base36_test where 'c1'::base36 > val;
                                           QUERY PLAN
-------------------------------------------------------------------------------------------------
 Index Only Scan using base36_test_val_idx on base36_test  (cost=0.42..169.93 rows=5000 width=4)
   Index Cond: (val < 'c1'::base36)
(2 rows)

    可以看到，为了能够使用索引，Postgres必须将查询从'c1'::base36 > val重写为val < 'c1'::base36。

    否定也是如此。

    base36_test=# explain SELECT * FROM base36_test where NOT val > 'c1';
                                           QUERY PLAN
-------------------------------------------------------------------------------------------------
 Index Only Scan using base36_test_val_idx on base36_test  (cost=0.42..169.93 rows=5000 width=4)
   Index Cond: (val <= 'c1'::base36)
(2 rows)

    这里NOT val>'c1':: base36被重写为val <='c1':: base36。

    最后你可以看到它会将NOT'c1':: base36 <val重写为val <='c1'::

    base36_test=# explain SELECT * FROM base36_test where NOT 'c1' < val;
                                           QUERY PLAN
-------------------------------------------------------------------------------------------------
 Index Only Scan using base36_test_val_idx on base36_test  (cost=0.42..169.93 rows=5000 width=4)
   Index Cond: (val <= 'c1'::base36)
(2 rows)

    因此，虽然在自定义Postgres类型定义中并不严格要求COMMUTATOR和NEGATOR子句，但如果没有它们，则无法进行上述重写。 因此，各个查询将不会使用索引，并且在大多数情况下会失去性能。

    RESTRICT 和 JOIN

    幸运的是，我们不需要编写自己的RESTRICT函数（参见第54-55行），可以简单地使用它：

    eqsel for =
neqsel for <>
scalarltsel for < or <=
scalargtsel for > or >=

    这些是限制选择性估计函数，它给Postgres一个提示，即在给定常量作为右参数的情况下，有多少行满足WHERE子句。如果常数是左边的参数，我们可以用commutator把它翻转到右边。

    你可能已经知道，当你或autovacuum守护程序运行ANALYZE时，Postgres会收集每个表的一些统计信息。你还可以在pg stats视图中查看这些统计数据。

    SELECT * FROM pg_stats WHERE tablename = 'base36_test';

    所有估计函数都是给出介于0和1之间的值，表示基于这些统计的行的估计分数。这一点非常重要，因为通常=操作符满足的行数少于<>操作符。由于在命名和定义操作符方面相对比较自由，所以需要说明它们是如何工作的。

    如果你真的想知道估算函数是什么样子的，请看源代码。免责声明:你的眼睛可能会开始流血。

    因此，我们不需要编写自己的JOIN选择性估计函数，这非常好。这个是用于多表join查询的，但本质上是一样的：它估计操作将返回多少行以最终决定使用哪个可能的计划（即哪个连接顺序）。

    所以，如果你有：

    ELECT * FROM table1
JOIN table2 ON table1.c1 = table2.c1
JOIN table3 ON table2.c1 = table2.c1

    这类的查询，这里表3只有几行，而表1和表2非常大。因此，首先联接表3，积累一些行，然后联接其他表是有意义的。

    HASHES 和 MERGES

    对于等式运算符，我们还定义参数HASHES和MERGES（第35行）。这样做就是告诉Postgres，使用此函数进行散列分别合并连接操作是合适的。为了使散列连接真正起作用，我们还需要定义一个散列函数并将它们放在一个运算符类中。您可以在PostgreSQL文档中进一步阅读有关不同Operator Optimization子句的内容。

    更多内容

    到目前为止，你已经了解了如何使用INPUT和OUTPUT函数实现基本数据类型。最重要的是，我们通过重用Postgres内部功能来添加比较运算符的。这允许我们对表进行排序并使用索引。

    但是，如果你按上面的步骤在计算机上的进行实现，可能会发现上面提到的EXPLAIN命令不起作用：

    # EXPLAIN SELECT * FROM base36_test where 'c1'::base36 > val;
server closed the connection unexpectedly
  This probably means the server terminated abnormally
  before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
Time: 275,327 ms
!>

    那是因为我们做了最糟糕的事情:在某些情况下，我们的代码会导致整个服务器崩溃。

    在下一篇文章中，我们将看到如何使用LLDB调试代码，以及如何通过正确的测试来避免这些错误。
  • 相关阅读:
    JSP三大指令是什么？
    jsp和servlet的区别、共同点、各自应用的范围
    1.说一说Servlet的生命周期?
    .查询姓“李”的老师的个数；
    jquery 主要内容有两大部分：
    jQuery的优势：
    JDBC的PreparedStatement是什么？
    execute，executeQuery，executeUpdate的区别是什么？
    String和StringBuffer、StringBuilder的区别是什么？String为什么是不可变的
    数据维护不求人，一招搞定增删改
  • 原文地址：https://www.cnblogs.com/taceywong/p/11272242.html
Copyright © 2020-2023  润新知