最近在做一些功能兼容的工作，做了一些数据类型的工作。其中一部分是添加新的基础类型，很多细节值得记录一下，在此进行步骤介绍以及代码介绍。
此次要添加一种新的数据类型，这种类型使用的算法和PostgreSQL使用相同的input和output函数。那么接下来就将所有步骤进行演示。
类型信息
类型名，newtype；
类型长度，pg中显示-1，定长，和bigint相同，实际占用8个字节；
使用bigint（int8或int64）的input、output、send、receive函数；
能够进行显示、排序、简单比较、建立索引；
实现方法
定义基本函数：
使用unused_oids，确定oid
newtypein，newtypeout，newtyperecv，newtypesend[1]
在系统表pg_proc中注册相关以上4个函数
- 定义基础类型：
在系统表pg_type中注册类型newtype
在bootstrap注册基础类型[2]
- 实验效果：
shawn@B-D53RLVDL-1650 bin % ./psql postgres
psql (13devel)
Type "help" for help.
postgres=# create table testnewtype(t1 newtype);
CREATE TABLE
postgres=# insert into testnewtype values ('1'),('2');
INSERT 2
postgres=# checkpoint ;
CHECKPOINT
postgres=# select * from testnewtype ;
t1
1
2
(2 rows)
postgres=#
- 排序问题
按照以上步骤建立基础类型后，是无法直接进行排序操作的。
postgres=# select * from testnewtype order by t1;
2020-04-12 21:43:21.710 CST [60897] ERROR: could not identify an ordering operator for type newtype at character 36
2020-04-12 21:43:21.710 CST [60897] HINT: Use an explicit ordering operator or modify the query.
2020-04-12 21:43:21.710 CST [60897] STATEMENT: select * from testnewtype order by t1;
ERROR: could not identify an ordering operator for type newtype
LINE 1: select * from testnewtype order by t1;
^HINT: Use an explicit ordering operator or modify the query.
postgres=#
那么接下来就需要支持排序。
4.1 根据以上报错信息可以找到以下函数堆：
(gdb) bt
0 get_sort_group_operators (argtype=argtype@entry=562, needLT=needLT@entry=true,
needEQ=needEQ@entry=true, needGT=needGT@entry=false, ltOpr=ltOpr@entry=0x7ffe6113a108,
eqOpr=eqOpr@entry=0x7ffe6113a10c, gtOpr=gtOpr@entry=0x0,
isHashable=isHashable@entry=0x7ffe6113a106) at parse_oper.c:1911 0x0000000000579b75 in addTargetToSortList (pstate=pstate@entry=0x216adf8, tle=0x216b4a0,
sortlist=sortlist@entry=0x0, targetlist=0x216b5b0, sortby=0x216acd8)
at parse_clause.c:32992 0x0000000000579d4e in transformSortClause (pstate=pstate@entry=0x216adf8,
orderlist=0x216ad28, targetlist=targetlist@entry=0x216af58,
exprKind=exprKind@entry=EXPR_KIND_ORDER_BY, useSQL99=useSQL99@entry=false)
at parse_clause.c:26073 0x000000000055b444 in transformSelectStmt (stmt=0x216ab10, pstate=0x216adf8)
at analyze.c:12444 transformStmt (pstate=pstate@entry=0x216adf8, parseTree=0x216ab10) at analyze.c:301
5 0x000000000055cdc8 in transformOptionalSelectInto (pstate=pstate@entry=0x216adf8,
parseTree=<optimized out>) at analyze.c:2466 0x000000000055cece in transformTopLevelStmt (parseTree=0x216ad78, pstate=0x216adf8)
at analyze.c:1967 parse_analyze (parseTree=0x216ad78,
sourceText=0x2169ef8 "select * from ridt order by r1;", paramTypes=0x0, numParams=0,
queryEnv=0x0) at analyze.c:1168 0x000000000077f77c in pg_analyze_and_rewrite (parsetree=parsetree@entry=0x216ad78,
query_string=query_string@entry=0x2169ef8 "select * from ridt order by r1;",
paramTypes=paramTypes@entry=0x0, numParams=numParams@entry=0, queryEnv=queryEnv@entry=0x0)
at postgres.c:6919 0x000000000077fc4c in exec_simple_query (
query_string=0x2169ef8 "select * from ridt order by r1;") at postgres.c:115510 0x00000000007810be in PostgresMain (argc=, argv=argv@entry=0x2194f90,
dbname=0x2194eb8 "postgres", username=<optimized out>) at postgres.c:429811 0x0000000000480ec6 in BackendRun (port=, port=)
at postmaster.c:451012 BackendStartup (port=0x218ce90) at postmaster.c:4202
13 ServerLoop () at postmaster.c:1727
14 0x000000000070b92e in PostmasterMain (argc=argc@entry=3, argv=argv@entry=0x2164ac0)
at postmaster.c:140015 0x0000000000481bff in main (argc=3, argv=0x2164ac0) at main.c:210
(gdb)
PostgreSQL会在函数get_sort_group_operators内进行操作符查找：
/ get_sort_group_operators代码片段 /
typentry = lookup_type_cache(argtype, cache_flags);
首先，函数经过lookup_type_cache，这个函数会查找pg_opclass，而这个表依赖于pg_opfamily的信息，还会查找pg_amop，pg_amop依赖于pg_operator的定义。而pg_operator又依赖于pg_proc的函数定义。
/ get_sort_group_operators代码片段 /
lt_opr = typentry->lt_opr;
eq_opr = typentry->eq_opr;
gt_opr = typentry->gt_opr;
以上表示需要三种操作符，lt，eq，gt，以及操作符对应的三种函数。
这就需要在newtype.c中添加函数体，以及在pg_proc中添加函数定义，在pg_operator添加操作符定义。
那么添加完以上代码后，会发现：
postgres=# select * from testnewtype where t1 < '2' order by t1;
2020-04-13 22:36:04.644 CST [31149] ERROR: XX000: missing support function 1(564,564) in opfamily 567
2020-04-13 22:36:04.644 CST [31149] LOCATION: FinishSortSupportFunction, sortsupport.c:119
2020-04-13 22:36:04.644 CST [31149] STATEMENT: select * from testnewtype where t1 < '2' order by t1;
ERROR: missing support function 1(564,564) in opfamily 567
postgres=#
首先根据定义找到函数栈：
(gdb) bt
0 FinishSortSupportFunction (opfamily=567, opcintype=564, ssup=0x206a9c8) at sortsupport.c:95
1 0x000000000089dc9d in PrepareSortSupportFromOrderingOp (orderingOp=570, ssup=0x206a9c8) at sortsupport.c:149
2 0x00000000008a35e9 in tuplesort_begin_heap (tupDesc=, nkeys=1, attNums=0x20b3a40,
sortOperators=0x20b3a58, sortCollations=0x20b3a70, nullsFirstFlags=0x20b3a88, workMem=4096,
coordinate=coordinate@entry=0x0, randomAccess=false) at tuplesort.c:8623 0x00000000006429b4 in ExecSort (pstate=0x20447a0) at nodeSort.c:89
4 0x0000000000618322 in ExecProcNode (node=0x20447a0) at ../../../src/include/executor/executor.h:245
5 ExecutePlan (execute_once=, dest=0x20b3f40, direction=, numberTuples=0,
sendTuples=true, operation=CMD_SELECT, use_parallel_mode=<optimized out>, planstate=0x20447a0, estate=0x2044578)
at execMain.c:16466 standard_ExecutorRun (queryDesc=0x200ef28, direction=, count=0, execute_once=)
at execMain.c:3647 0x000000000076838b in PortalRunSelect (portal=portal@entry=0x1fbbec8, forward=forward@entry=true, count=0,
count@entry=9223372036854775807, dest=dest@entry=0x20b3f40) at pquery.c:9128 0x00000000007695f8 in PortalRun (portal=portal@entry=0x1fbbec8, count=count@entry=9223372036854775807,
isTopLevel=isTopLevel@entry=true, run_once=run_once@entry=true, dest=dest@entry=0x20b3f40,
altdest=altdest@entry=0x20b3f40, qc=qc@entry=0x7ffd12185180) at pquery.c:7569 0x000000000076532c in exec_simple_query (query_string=0x1f54ef8 "select * from newt order by t1;")
at postgres.c:123910 0x00000000007666b7 in PostgresMain (argc=, argv=argv@entry=0x1f7ffa0,
dbname=0x1f7fec8 "postgres", username=<optimized out>) at postgres.c:4315---Type to continue, or q to quit---
11 0x00000000004815bb in BackendRun (port=, port=) at postmaster.c:4510
12 BackendStartup (port=0x1f77ea0) at postmaster.c:4202
13 ServerLoop () at postmaster.c:1727
14 0x00000000006f4533 in PostmasterMain (argc=argc@entry=3, argv=argv@entry=0x1f4fac0) at postmaster.c:1400
15 0x00000000004821fe in main (argc=3, argv=0x1f4fac0) at main.c:210
(gdb)
在函数FinishSortSupportFunction进行报错的。
而引起问题的部分代码为：
static void
FinishSortSupportFunction(Oid opfamily, Oid opcintype, SortSupport ssup)
{
Oid sortSupportFunction;
/* Look for a sort support function */
sortSupportFunction = get_opfamily_proc(opfamily, opcintype, opcintype,} BTSORTSUPPORT_PROC);
get_opfamily_proc的代码是：
Oid
get_opfamily_proc(Oid opfamily, Oid lefttype, Oid righttype, int16 procnum)
{
HeapTuple tp;
Form_pg_amproc amproc_tup;
RegProcedure result;
tp = SearchSysCache4(AMPROCNUM,
ObjectIdGetDatum(opfamily),
ObjectIdGetDatum(lefttype),
ObjectIdGetDatum(righttype),
Int16GetDatum(procnum));
if (!HeapTupleIsValid(tp))
return InvalidOid;
amproc_tup = (Form_pg_amproc) GETSTRUCT(tp);
result = amproc_tup->amproc;
ReleaseSysCache(tp);
return result;} BTSORTSUPPORT_PROC);
这里能看到会在系统表pg_amproc查找数据，在ctid排序时能看到查找的是bttidcmp，那么就需要增加QQ号转让平台相关函数以及定义。
- 完成以上步骤后的演示结果：
postgres=# create table testnewtype(t1 newtype);
CREATE TABLE
postgres=# insert into testnewtype values ('1'),('2'),('3'),('4'),('5'),('5'),('6');
INSERT 0 7
postgres=# select * from testnewtype where t1 < '6' order by t1;
t1
1
2
3
4
5
5
(6 rows)
postgres=#
- 总结[3]
在这里一共添加了一个文件，src/backend/utils/adt/newtype.c；
修改系统表pg_proc，增加基础类型函数定义；
修改系统表pg_type，增加基础类型定义；
增加比较函数体；
增加比较函数定义；
在系统表pg_opfamily增加操作符族；
在pg_opcalss增加操作符类；
在pg_operator增加操作符，便于使用where查询；
在pg_amop增加与访问方法操作符族相关的操作符信息；
增加cmp函数体；
在pg_amproc增加关于访问方法操作符族相关的支持函数btnewtype定义。
