编写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 + '\0' */
char *buffer = palloc(7 * sizeof(char));
unsigned int offset = 7 * sizeof(char);
buffer[--offset] = '\0';
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调试代码，以及如何通过正确的测试来避免这些错误。
还真有人点开啊🤣随意随意😂
