自定义postgresql9.0

PostgreSQL内核扩展入门培训digoal目录PostgreSQLflowchart如何分析代码瓶颈如何自定义CUDF如何自定义数据类型如何自定义操作符开放索引接口介绍如何自定义索引PostgreSQL内核扩展接口总结PostgreSQL插件打包、发布GPU,FPGA如何与PostGIS深度整合pg_strom介绍目标CASEPostgreSQLflowcharthttps://wiki.
postgresql.
org/wiki/Backend_flowcharthttps://www.
postgresql.
org/developer/backend/bootstrap:初始化数据库集群main:程序入口postmaster:监听,forklibpq:通信库tcop:解包,分发请求到适当的模块,backendprocess入口parser:词法分析(输出优化器或执行器需要的结构)rewrite:view,ruleoptimizer:基于执行计划优化算法,生成执行树executor:执行QUERYcommands:DDL,DCL相关catalog:元数据access:索引、堆表、事务相关(common,gin,gist,hash,heap,index,nbtree,spgist,transam)(公共代码,索引,堆表,事务)storage:存储接口(buffer,file,freespace,ipc,large_object,lmgr,page,smgr)(缓存,文件,FSM,内部进程通信,大对象,锁,页,磁盘)utils:工具包(adtbuild-in数据类型相关,cache元数据,函数,类型等缓存)(error,fmgr,hash,init,mb,misc,mmgr,resowner,sort,time)(错误处理,内部/外部自定义函数接口,内部公用的hash算法库如扫描cache,初始化数据库,多字节字符,context内存管理,资源owner跟踪,内部公用排序算法库,MVCC相关row可见性管理)include,lib,snowball,tsearch:port:平台兼容性相关regex:正则replication:流复制相关如何分析代码瓶颈OProfile–http://oprofile.
sourceforge.
net/–mkdir/tmp/optest;cd/tmp/optest;operf-lpostgres-B2GB-cport=$i-clisten_addresses='0.
0.
0.
0'-csynchronous_commit=off-cfull_page_writes=off-cwal_buffers=1900MB-cwal_writer_delay=10ms-cmax_connections=100-cmax_wal_size=4GB-clog_destination='csvlog'-clogging_collector=on-D$PGDATA-k$PGDATA–进行一些压测perf–perftop如何分析代码瓶颈OProfilecd/tmp/optest;opreport-l-f-w-x-t1如何分析代码瓶颈OProfileopreport-l-f-g-w-x-t1/opt/pgsql/bin/postgres如何分析代码瓶颈OProfileopannotate-x-s-t1/opt/pgsql/bin/postgres-iHeapTupleSatisfiesVacuum|less如何分析代码瓶颈perftop如何自定义CUDFC与SQL的类型对应关系如何获取SQL函数的args如何从C函数返回结果给SQL函数示例自定义普通UDF自定义聚合函数自定义窗口函数类型对应关系SQL-C类型对应关系头文件不一定准确获取argsmacro(Datum->ctype)contrib/*/*.
h插件新建类型相关argMACRO–contrib/hstore/hstore.
h:#definePG_GETARG_HS(x)DatumGetHStoreP(PG_GETARG_DATUM(x))–contrib/cube/cubedata.
h:#definePG_GETARG_NDBOX(x)DatumGetNDBOX(PG_GETARG_DATUM(x))–contrib/ltree/ltree.
h:#definePG_GETARG_LTREE(x)((ltree*)DatumGetPointer(PG_DETOAST_DATUM(PG_GETARG_DATUM(x))))–contrib/ltree/ltree.
h:#definePG_GETARG_LTREE_COPY(x)((ltree*)DatumGetPointer(PG_DETOAST_DATUM_COPY(PG_GETARG_DATUM(x))))–contrib/ltree/ltree.
h:#definePG_GETARG_LQUERY(x)((lquery*)DatumGetPointer(PG_DETOAST_DATUM(PG_GETARG_DATUM(x))))–contrib/ltree/ltree.
h:#definePG_GETARG_LQUERY_COPY(x)((lquery*)DatumGetPointer(PG_DETOAST_DATUM_COPY(PG_GETARG_DATUM(x))))–contrib/ltree/ltree.
h:#definePG_GETARG_LTXTQUERY(x)((ltxtquery*)DatumGetPointer(PG_DETOAST_DATUM(PG_GETARG_DATUM(x))))–contrib/ltree/ltree.
h:#definePG_GETARG_LTXTQUERY_COPY(x)((ltxtquery*)DatumGetPointer(PG_DETOAST_DATUM_COPY(PG_GETARG_DATUM(x))))–contrib/isn/isn.
h:#definePG_GETARG_EAN13(n)PG_GETARG_INT64(n)–contrib/intarray/_int.
h:#definePG_GETARG_QUERYTYPE_P(n)DatumGetQueryTypeP(PG_GETARG_DATUM(n))–contrib/intarray/_int.
h:#definePG_GETARG_QUERYTYPE_P_COPY(n)DatumGetQueryTypePCopy(PG_GETARG_DATUM(n))获取argsmacro(Datum->ctype)全文检索相关类型argMACRO–src/include/tsearch/ts_utils.
h:#definePG_GETARG_TSQUERYSIGN(n)DatumGetTSQuerySign(PG_GETARG_DATUM(n))–src/include/tsearch/ts_type.
h:#definePG_GETARG_TSVECTOR(n)DatumGetTSVector(PG_GETARG_DATUM(n))–src/include/tsearch/ts_type.
h:#definePG_GETARG_TSVECTOR_COPY(n)DatumGetTSVectorCopy(PG_GETARG_DATUM(n))–src/include/tsearch/ts_type.
h:#definePG_GETARG_TSQUERY(n)DatumGetTSQuery(PG_GETARG_DATUM(n))–src/include/tsearch/ts_type.
h:#definePG_GETARG_TSQUERY_COPY(n)DatumGetTSQueryCopy(PG_GETARG_DATUM(n))获取argsmacro(Datum->ctype)相关类型argMACRO–src/include/utils/timestamp.
h:#definePG_GETARG_TIMESTAMP(n)DatumGetTimestamp(PG_GETARG_DATUM(n))–src/include/utils/timestamp.
h:#definePG_GETARG_TIMESTAMPTZ(n)DatumGetTimestampTz(PG_GETARG_DATUM(n))–src/include/utils/timestamp.
h:#definePG_GETARG_INTERVAL_P(n)DatumGetIntervalP(PG_GETARG_DATUM(n))–src/include/utils/timestamp.
h:#definePG_GETARG_TIMESTAMP(n)DatumGetTimestamp(PG_GETARG_DATUM(n))–src/include/utils/timestamp.
h:#definePG_GETARG_TIMESTAMPTZ(n)DatumGetTimestampTz(PG_GETARG_DATUM(n))–src/include/utils/timestamp.
h:#definePG_GETARG_INTERVAL_P(n)DatumGetIntervalP(PG_GETARG_DATUM(n))–src/include/utils/nabstime.
h:#definePG_GETARG_ABSOLUTETIME(n)DatumGetAbsoluteTime(PG_GETARG_DATUM(n))–src/include/utils/nabstime.
h:#definePG_GETARG_RELATIVETIME(n)DatumGetRelativeTime(PG_GETARG_DATUM(n))–src/include/utils/nabstime.
h:#definePG_GETARG_TIMEINTERVAL(n)DatumGetTimeInterval(PG_GETARG_DATUM(n))–src/include/utils/date.
h:#definePG_GETARG_DATEADT(n)DatumGetDateADT(PG_GETARG_DATUM(n))–src/include/utils/date.
h:#definePG_GETARG_TIMEADT(n)DatumGetTimeADT(PG_GETARG_DATUM(n))–src/include/utils/date.
h:#definePG_GETARG_TIMETZADT_P(n)DatumGetTimeTzADTP(PG_GETARG_DATUM(n))–src/include/utils/xml.
h:#definePG_GETARG_XML_P(n)DatumGetXmlP(PG_GETARG_DATUM(n))–src/include/utils/varbit.
h:#definePG_GETARG_VARBIT_P(n)DatumGetVarBitP(PG_GETARG_DATUM(n))–src/include/utils/varbit.
h:#definePG_GETARG_VARBIT_P_COPY(n)DatumGetVarBitPCopy(PG_GETARG_DATUM(n))–src/include/utils/uuid.
h:#definePG_GETARG_UUID_P(X)DatumGetUUIDP(PG_GETARG_DATUM(X))–src/include/utils/numeric.
h:#definePG_GETARG_NUMERIC(n)DatumGetNumeric(PG_GETARG_DATUM(n))–src/include/utils/numeric.
h:#definePG_GETARG_NUMERIC_COPY(n)DatumGetNumericCopy(PG_GETARG_DATUM(n))–src/include/utils/acl.
h:#definePG_GETARG_ACLITEM_P(n)DatumGetAclItemP(PG_GETARG_DATUM(n))–src/include/utils/acl.
h:#definePG_GETARG_ACL_P(n)DatumGetAclP(PG_GETARG_DATUM(n))–src/include/utils/acl.
h:#definePG_GETARG_ACL_P_COPY(n)DatumGetAclPCopy(PG_GETARG_DATUM(n))相关类型argMACRO–src/include/utils/geo_decls.
h:#definePG_GETARG_POINT_P(n)DatumGetPointP(PG_GETARG_DATUM(n))–src/include/utils/geo_decls.
h:#definePG_GETARG_LSEG_P(n)DatumGetLsegP(PG_GETARG_DATUM(n))–src/include/utils/geo_decls.
h:#definePG_GETARG_PATH_P(n)DatumGetPathP(PG_GETARG_DATUM(n))–src/include/utils/geo_decls.
h:#definePG_GETARG_PATH_P_COPY(n)DatumGetPathPCopy(PG_GETARG_DATUM(n))–src/include/utils/geo_decls.
h:#definePG_GETARG_LINE_P(n)DatumGetLineP(PG_GETARG_DATUM(n))–src/include/utils/geo_decls.
h:#definePG_GETARG_BOX_P(n)DatumGetBoxP(PG_GETARG_DATUM(n))–src/include/utils/geo_decls.
h:#definePG_GETARG_POLYGON_P(n)DatumGetPolygonP(PG_GETARG_DATUM(n))–src/include/utils/geo_decls.
h:#definePG_GETARG_POLYGON_P_COPY(n)DatumGetPolygonPCopy(PG_GETARG_DATUM(n))–src/include/utils/geo_decls.
h:#definePG_GETARG_CIRCLE_P(n)DatumGetCircleP(PG_GETARG_DATUM(n))–src/include/utils/inet.
h:#definePG_GETARG_INET_P(n)DatumGetInetP(PG_GETARG_DATUM(n))–src/include/utils/inet.
h:#definePG_GETARG_INET_PP(n)DatumGetInetPP(PG_GETARG_DATUM(n))–src/include/utils/inet.
h:#definePG_GETARG_MACADDR_P(n)DatumGetMacaddrP(PG_GETARG_DATUM(n))–src/include/utils/array.
h:#definePG_GETARG_ARRAYTYPE_P(n)DatumGetArrayTypeP(PG_GETARG_DATUM(n))–src/include/utils/array.
h:#definePG_GETARG_ARRAYTYPE_P_COPY(n)DatumGetArrayTypePCopy(PG_GETARG_DATUM(n))–src/include/utils/array.
h:#definePG_GETARG_EXPANDED_ARRAY(n)DatumGetExpandedArray(PG_GETARG_DATUM(n))–src/include/utils/array.
h:#definePG_GETARG_EXPANDED_ARRAYX(n,metacache)\–src/include/utils/array.
h:DatumGetExpandedArrayX(PG_GETARG_DATUM(n),metacache)–src/include/utils/array.
h:#definePG_GETARG_ANY_ARRAY(n)DatumGetAnyArray(PG_GETARG_DATUM(n))–src/include/utils/pg_lsn.
h:#definePG_GETARG_LSN(n)DatumGetLSN(PG_GETARG_DATUM(n))–src/include/utils/cash.
h:#definePG_GETARG_CASH(n)DatumGetCash(PG_GETARG_DATUM(n))–src/include/utils/rangetypes.
h:#definePG_GETARG_RANGE(n)DatumGetRangeType(PG_GETARG_DATUM(n))–src/include/utils/rangetypes.
h:#definePG_GETARG_RANGE_COPY(n)DatumGetRangeTypeCopy(PG_GETARG_DATUM(n))–src/include/utils/jsonb.
h:#definePG_GETARG_JSONB(x)DatumGetJsonb(PG_GETARG_DATUM(x))获取argsmacro(Datum->ctype)相关类型argMACRO–src/include/fmgr.
h:#definePG_GETARG_DATUM(n)(fcinfo->arg[n])–src/include/fmgr.
h:#definePG_GETARG_INT32(n)DatumGetInt32(PG_GETARG_DATUM(n))–src/include/fmgr.
h:#definePG_GETARG_UINT32(n)DatumGetUInt32(PG_GETARG_DATUM(n))–src/include/fmgr.
h:#definePG_GETARG_INT16(n)DatumGetInt16(PG_GETARG_DATUM(n))–src/include/fmgr.
h:#definePG_GETARG_UINT16(n)DatumGetUInt16(PG_GETARG_DATUM(n))–src/include/fmgr.
h:#definePG_GETARG_CHAR(n)DatumGetChar(PG_GETARG_DATUM(n))–src/include/fmgr.
h:#definePG_GETARG_BOOL(n)DatumGetBool(PG_GETARG_DATUM(n))–src/include/fmgr.
h:#definePG_GETARG_OID(n)DatumGetObjectId(PG_GETARG_DATUM(n))–src/include/fmgr.
h:#definePG_GETARG_POINTER(n)DatumGetPointer(PG_GETARG_DATUM(n))–src/include/fmgr.
h:#definePG_GETARG_CSTRING(n)DatumGetCString(PG_GETARG_DATUM(n))–src/include/fmgr.
h:#definePG_GETARG_NAME(n)DatumGetName(PG_GETARG_DATUM(n))–src/include/fmgr.
h:#definePG_GETARG_FLOAT4(n)DatumGetFloat4(PG_GETARG_DATUM(n))–src/include/fmgr.
h:#definePG_GETARG_FLOAT8(n)DatumGetFloat8(PG_GETARG_DATUM(n))–src/include/fmgr.
h:#definePG_GETARG_INT64(n)DatumGetInt64(PG_GETARG_DATUM(n))–src/include/fmgr.
h:#definePG_GETARG_RAW_VARLENA_P(n)((structvarlena*)PG_GETARG_POINTER(n))–src/include/fmgr.
h:#definePG_GETARG_VARLENA_P(n)PG_DETOAST_DATUM(PG_GETARG_DATUM(n))–src/include/fmgr.
h:#definePG_GETARG_VARLENA_PP(n)PG_DETOAST_DATUM_PACKED(PG_GETARG_DATUM(n))–src/include/fmgr.
h:#definePG_GETARG_BYTEA_P(n)DatumGetByteaP(PG_GETARG_DATUM(n))相关类型argMACRO–src/include/fmgr.
h:#definePG_GETARG_BYTEA_PP(n)DatumGetByteaPP(PG_GETARG_DATUM(n))–src/include/fmgr.
h:#definePG_GETARG_TEXT_P(n)DatumGetTextP(PG_GETARG_DATUM(n))–src/include/fmgr.
h:#definePG_GETARG_TEXT_PP(n)DatumGetTextPP(PG_GETARG_DATUM(n))–src/include/fmgr.
h:#definePG_GETARG_BPCHAR_P(n)DatumGetBpCharP(PG_GETARG_DATUM(n))–src/include/fmgr.
h:#definePG_GETARG_BPCHAR_PP(n)DatumGetBpCharPP(PG_GETARG_DATUM(n))–src/include/fmgr.
h:#definePG_GETARG_VARCHAR_P(n)DatumGetVarCharP(PG_GETARG_DATUM(n))–src/include/fmgr.
h:#definePG_GETARG_VARCHAR_PP(n)DatumGetVarCharPP(PG_GETARG_DATUM(n))–src/include/fmgr.
h:#definePG_GETARG_HEAPTUPLEHEADER(n)DatumGetHeapTupleHeader(PG_GETARG_DATUM(n))–src/include/fmgr.
h:#definePG_GETARG_BYTEA_P_COPY(n)DatumGetByteaPCopy(PG_GETARG_DATUM(n))–src/include/fmgr.
h:#definePG_GETARG_TEXT_P_COPY(n)DatumGetTextPCopy(PG_GETARG_DATUM(n))–src/include/fmgr.
h:#definePG_GETARG_BPCHAR_P_COPY(n)DatumGetBpCharPCopy(PG_GETARG_DATUM(n))–src/include/fmgr.
h:#definePG_GETARG_VARCHAR_P_COPY(n)DatumGetVarCharPCopy(PG_GETARG_DATUM(n))–src/include/fmgr.
h:#definePG_GETARG_HEAPTUPLEHEADER_COPY(n)DatumGetHeapTupleHeaderCopy(PG_GETARG_DATUM(n))–src/include/fmgr.
h:#definePG_GETARG_BYTEA_P_SLICE(n,a,b)DatumGetByteaPSlice(PG_GETARG_DATUM(n),a,b)–src/include/fmgr.
h:#definePG_GETARG_TEXT_P_SLICE(n,a,b)DatumGetTextPSlice(PG_GETARG_DATUM(n),a,b)–src/include/fmgr.
h:#definePG_GETARG_BPCHAR_P_SLICE(n,a,b)DatumGetBpCharPSlice(PG_GETARG_DATUM(n),a,b)–src/include/fmgr.
h:#definePG_GETARG_VARCHAR_P_SLICE(n,a,b)DatumGetVarCharPSlice(PG_GETARG_DATUM(n),a,b)返回结果macro(ctype->Datum)contrib/cube/cubedata.
h:#definePG_RETURN_NDBOX(x)PG_RETURN_POINTER(x)contrib/isn/isn.
h:#definePG_RETURN_EAN13(x)PG_RETURN_INT64(x)src/include/access/gin.
h:#definePG_RETURN_GIN_TERNARY_VALUE(x)returnGinTernaryValueGetDatum(x)src/include/tsearch/ts_utils.
h:#definePG_RETURN_TSQUERYSIGN(X)returnTSQuerySignGetDatum(X)src/include/tsearch/ts_type.
h:#definePG_RETURN_TSVECTOR(x)returnTSVectorGetDatum(x)src/include/tsearch/ts_type.
h:#definePG_RETURN_TSQUERY(x)returnTSQueryGetDatum(x)src/include/utils/timestamp.
h:#definePG_RETURN_TIMESTAMP(x)returnTimestampGetDatum(x)src/include/utils/timestamp.
h:#definePG_RETURN_TIMESTAMPTZ(x)returnTimestampTzGetDatum(x)src/include/utils/timestamp.
h:#definePG_RETURN_INTERVAL_P(x)returnIntervalPGetDatum(x)src/include/utils/timestamp.
h:#definePG_RETURN_TIMESTAMP(x)returnTimestampGetDatum(x)src/include/utils/timestamp.
h:#definePG_RETURN_TIMESTAMPTZ(x)returnTimestampTzGetDatum(x)src/include/utils/timestamp.
h:#definePG_RETURN_INTERVAL_P(x)returnIntervalPGetDatum(x)返回结果macro(ctype->Datum)src/include/utils/nabstime.
h:#definePG_RETURN_ABSOLUTETIME(x)returnAbsoluteTimeGetDatum(x)src/include/utils/nabstime.
h:#definePG_RETURN_RELATIVETIME(x)returnRelativeTimeGetDatum(x)src/include/utils/nabstime.
h:#definePG_RETURN_TIMEINTERVAL(x)returnTimeIntervalGetDatum(x)src/include/utils/date.
h:#definePG_RETURN_DATEADT(x)returnDateADTGetDatum(x)src/include/utils/date.
h:#definePG_RETURN_TIMEADT(x)returnTimeADTGetDatum(x)src/include/utils/date.
h:#definePG_RETURN_TIMETZADT_P(x)returnTimeTzADTPGetDatum(x)src/include/utils/xml.
h:#definePG_RETURN_XML_P(x)PG_RETURN_POINTER(x)src/include/utils/varbit.
h:#definePG_RETURN_VARBIT_P(x)returnVarBitPGetDatum(x)src/include/utils/uuid.
h:#definePG_RETURN_UUID_P(X)returnUUIDPGetDatum(X)src/include/utils/numeric.
h:#definePG_RETURN_NUMERIC(x)returnNumericGetDatum(x)src/include/utils/acl.
h:#definePG_RETURN_ACLITEM_P(x)PG_RETURN_POINTER(x)src/include/utils/acl.
h:#definePG_RETURN_ACL_P(x)PG_RETURN_POINTER(x)src/include/utils/geo_decls.
h:#definePG_RETURN_POINT_P(x)returnPointPGetDatum(x)src/include/utils/geo_decls.
h:#definePG_RETURN_LSEG_P(x)returnLsegPGetDatum(x)返回结果macro(ctype->Datum)src/include/utils/geo_decls.
h:#definePG_RETURN_PATH_P(x)returnPathPGetDatum(x)src/include/utils/geo_decls.
h:#definePG_RETURN_LINE_P(x)returnLinePGetDatum(x)src/include/utils/geo_decls.
h:#definePG_RETURN_BOX_P(x)returnBoxPGetDatum(x)src/include/utils/geo_decls.
h:#definePG_RETURN_POLYGON_P(x)returnPolygonPGetDatum(x)src/include/utils/geo_decls.
h:#definePG_RETURN_CIRCLE_P(x)returnCirclePGetDatum(x)src/include/utils/inet.
h:#definePG_RETURN_INET_P(x)returnInetPGetDatum(x)src/include/utils/inet.
h:#definePG_RETURN_MACADDR_P(x)returnMacaddrPGetDatum(x)src/include/utils/array.
h:#definePG_RETURN_ARRAYTYPE_P(x)PG_RETURN_POINTER(x)src/include/utils/array.
h:#definePG_RETURN_EXPANDED_ARRAY(x)PG_RETURN_DATUM(EOHPGetRWDatum(&(x)->hdr))src/include/utils/pg_lsn.
h:#definePG_RETURN_LSN(x)returnLSNGetDatum(x)src/include/utils/cash.
h:#definePG_RETURN_CASH(x)returnCashGetDatum(x)src/include/utils/rangetypes.
h:#definePG_RETURN_RANGE(x)returnRangeTypeGetDatum(x)src/include/utils/jsonb.
h:#definePG_RETURN_JSONB(x)PG_RETURN_POINTER(x)返回结果macro(ctype->Datum)/*ToreturnaNULLdothis:*/#definePG_RETURN_NULL()\do{fcinfo->isnull=true;return(Datum)0;}while(0)src/include/fmgr.
h:#definePG_RETURN_VOID()return(Datum)0src/include/fmgr.
h:#definePG_RETURN_DATUM(x)return(x)src/include/fmgr.
h:#definePG_RETURN_INT32(x)returnInt32GetDatum(x)src/include/fmgr.
h:#definePG_RETURN_UINT32(x)returnUInt32GetDatum(x)src/include/fmgr.
h:#definePG_RETURN_INT16(x)returnInt16GetDatum(x)src/include/fmgr.
h:#definePG_RETURN_UINT16(x)returnUInt16GetDatum(x)src/include/fmgr.
h:#definePG_RETURN_CHAR(x)returnCharGetDatum(x)src/include/fmgr.
h:#definePG_RETURN_BOOL(x)returnBoolGetDatum(x)src/include/fmgr.
h:#definePG_RETURN_OID(x)returnObjectIdGetDatum(x)src/include/fmgr.
h:#definePG_RETURN_POINTER(x)returnPointerGetDatum(x)src/include/fmgr.
h:#definePG_RETURN_CSTRING(x)returnCStringGetDatum(x)src/include/fmgr.
h:#definePG_RETURN_NAME(x)returnNameGetDatum(x)src/include/fmgr.
h:#definePG_RETURN_FLOAT4(x)returnFloat4GetDatum(x)src/include/fmgr.
h:#definePG_RETURN_FLOAT8(x)returnFloat8GetDatum(x)src/include/fmgr.
h:#definePG_RETURN_INT64(x)returnInt64GetDatum(x)src/include/fmgr.
h:#definePG_RETURN_BYTEA_P(x)PG_RETURN_POINTER(x)src/include/fmgr.
h:#definePG_RETURN_TEXT_P(x)PG_RETURN_POINTER(x)src/include/fmgr.
h:#definePG_RETURN_BPCHAR_P(x)PG_RETURN_POINTER(x)src/include/fmgr.
h:#definePG_RETURN_VARCHAR_P(x)PG_RETURN_POINTER(x)src/include/fmgr.
h:#definePG_RETURN_HEAPTUPLEHEADER(x)returnHeapTupleHeaderGetDatum(x)compositetypearg例子#include"postgres.
h"#include"fmgr.
h"#include"executor/executor.
h"/*forGetAttributeByName()根据composite元素名读取Datum*/#ifdefPG_MODULE_MAGICPG_MODULE_MAGIC;#endifPG_FUNCTION_INFO_V1(c_overpaid);Datumc_overpaid(PG_FUNCTION_ARGS){HeapTupleHeadert=PG_GETARG_HEAPTUPLEHEADER(0);//获取参数1compositeDatum转成复合类型对应ctypeint32limit=PG_GETARG_INT32(1);//获取参数2boolisnull;Datumsalary;salary=GetAttributeByName(t,"salary",&isnull);//获取composite中的某个元素的Datumif(isnull)PG_RETURN_BOOL(false);/*Alternatively,wemightprefertodoPG_RETURN_NULL()fornullsalary.
*/PG_RETURN_BOOL(DatumGetInt32(salary)>limit);//Datum转ctype进行比较,通过PG_RETURN*输出Datum}返回record例子将参数反转输出fun(复合(a,b,c))输出c,b,a,c*b+aDatumc_reverse_tuple(PG_FUNCTION_ARGS){HeapTupleHeaderth;//复合类型对应的Ctypeint32a,b,c;//复合类型对应的三个子类型boolaisnull,bisnull,cisnull;//是否为空TupleDescresultTupleDesc;//返回record值的描述类型OidresultTypeId;//返回值的OID(如果有的话)Datumretvals[4];//返回值子类boolretnulls[4];//返回值子类是否为空HeapTuplerettuple;//返回tuple//getthetupleheaderof1stargumentth=PG_GETARG_HEAPTUPLEHEADER(0);//getargumentDatum'sandconvertthemtoint32a=DatumGetInt32(GetAttributeByName(th,"a",&aisnull));//从compositectype获取子类Datum并转换为Ctypeb=DatumGetInt32(GetAttributeByName(th,"b",&bisnull));c=DatumGetInt32(GetAttributeByName(th,"c",&cisnull));返回record例子//debug:reporttheextractedfieldvaluesereport(INFO,(errmsg("arg:(a:%d,b:%d,c:%d)",a,b,c)));//setuptupledescriptorforresultinfoget_call_result_type(fcinfo,&resultTypeId,&resultTupleDesc);//checkthatSQLfunctiondefinitionissetuptoreturnarecordAssert(resultTypeId==TYPEFUNC_COMPOSITE);//makethetupledescriptorknowntopostgresasvalidreturntypeBlessTupleDesc(resultTupleDesc);retvals[0]=Int32GetDatum(c);//构造返回值子集retvals[1]=Int32GetDatum(b);retvals[2]=Int32GetDatum(a);retvals[3]=Int32GetDatum(retvals[0]*retvals[1]+retvals[2]);retnulls[0]=aisnull;retnulls[1]=bisnull;retnulls[2]=cisnull;retnulls[3]=aisnull||bisnull||cisnull;rettuple=heap_form_tuple(resultTupleDesc,retvals,retnulls);//构造tuplePG_RETURN_DATUM(HeapTupleGetDatum(rettuple));//返回Datum}返回表(SRF)例子伪代码Datummy_set_returning_function(PG_FUNCTION_ARGS){FuncCallContext*funcctx;Datumresult;furtherdeclarationsasneededif(SRF_IS_FIRSTCALL())//判断该函数是否在该会话第一次被调用{MemoryContextoldcontext;funcctx=SRF_FIRSTCALL_INIT();//初始化FuncCallContextoldcontext=MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);/*One-timesetupcodeappearshere:*/usercodeifreturningcompositebuildTupleDesc,andperhapsAttInMetadataendifreturningcompositeusercodeMemoryContextSwitchTo(oldcontext);}返回表(SRF)例子/*Each-timesetupcodeappearshere:*/usercodefuncctx=SRF_PERCALL_SETUP();//清除之前调用产生的结果usercode/*thisisjustonewaywemighttestwhetherwearedone:*/if(funcctx->call_cntrmax_calls){/*Herewewanttoreturnanotheritem:*/usercodeobtainresultDatumSRF_RETURN_NEXT(funcctx,result);//返回一条记录,循环往复}else{/*Herewearedonereturningitemsandjustneedtocleanup:*/usercodeSRF_RETURN_DONE(funcctx);//调用结束,返回}}示例#include#include"postgres.
h"#include"fmgr.
h"PG_MODULE_MAGIC;//V1CUDFPG_FUNCTION_INFO_V1(text_reverse);//声明/**Returnreversedstring*/Datumtext_reverse(PG_FUNCTION_ARGS){text*str=PG_GETARG_TEXT_PP(0);constchar*p=VARDATA_ANY(str);intlen=VARSIZE_ANY_EXHDR(str);constchar*endp=p+len;text*result;char*dst;result=palloc(len+VARHDRSZ);dst=(char*)VARDATA(result)+len;SET_VARSIZE(result,len+VARHDRSZ);示例if(pg_database_encoding_max_length()>1){/*multibyteversion*/while(p1和1就是操作符时指定了它的commutator是是它的commutator.
另外需要注意,有commutator操作符的操作符的左右两侧的参数类型必须一致,这样才能满足xop1y等价于yop2x.
优化器如何利用commutator呢例如索引扫描,列必须在操作符的左侧才能使用索引.
1>tbl.
c这个条件,如果>没有commutator的话,是不能使用索引的.
如何自定义操作符2.
negator,指xop1y等价于not(yop2x),或者xop1等价于not(yop2),或者op1x等价于not(op2y),因此negator支持一元和二元操作符.
例子:如果=和是一对negator操作符,NOT(x=y)可以替换为xy.
同样,操作符两侧参数x,y的类型必须一致.
并且仅适用于返回布尔逻辑类型的操作符.
如何自定义操作符3.
restrict,是用于评估选择性的函数,仅适用于二元操作符,例如wherecol>100,这个查询条件,如何评估选择性呢是通过操作符的restrict来指定的,选择性乘以pg_class.
reltuples就可以评估得到这个查询条件的行数.
如何自定义操作符4.
join,是joinsel即join的选择性计算函数.
对应pg_operator.
oprjoin5.
hashes6.
mergeshashes和merges表示该操作符是否允许hashjoin和mergejoin,只有返回布尔逻辑值的二元操作符满足这个要求.
自定义操作符例子Datumcitext_ne(PG_FUNCTION_ARGS){text*left=PG_GETARG_TEXT_PP(0);text*right=PG_GETARG_TEXT_PP(1);char*lcstr,*rcstr;boolresult;/*Wecan'tcomparelengthsinadvanceofdowncasing.
.
.
*/lcstr=str_tolower(VARDATA_ANY(left),VARSIZE_ANY_EXHDR(left),DEFAULT_COLLATION_OID);rcstr=str_tolower(VARDATA_ANY(right),VARSIZE_ANY_EXHDR(right),DEFAULT_COLLATION_OID);/**Sinceweonlycareaboutequalityornot-equality,wecanavoidallthe*expenseofstrcoll()here,andjustdobitwisecomparison.
*/result=(strcmp(lcstr,rcstr)!
=0);pfree(lcstr);pfree(rcstr);PG_FREE_IF_COPY(left,0);PG_FREE_IF_COPY(right,1);PG_RETURN_BOOL(result);}自定义操作符例子CREATEFUNCTIONcitext_ne(citext,citext)RETURNSboolAS'MODULE_PATHNAME'LANGUAGECIMMUTABLESTRICT;CREATEOPERATOR(LEFTARG=CITEXT,RIGHTARG=CITEXT,NEGATOR==,COMMUTATOR=,PROCEDURE=citext_ne,RESTRICT=neqsel,JOIN=neqjoinsel);等效优化https://yq.
aliyun.
com/articles/51131xy等价于not(x=y)自定义索引语法扩展索引语法CREATEOPERATORCLASSname[DEFAULT]FORTYPEdata_typeUSINGindex_method[FAMILYfamily_name]AS{OPERATORstrategy_numberoperator_name[(op_type,op_type)][FORSEARCH|FORORDERBYsort_family_name]|FUNCTIONsupport_number[(op_type[,op_type])]function_name(argument_type|STORAGEstorage_type操作符strategy_number、函数support_numberhttps://www.
postgresql.
org/docs/9.
5/static/xindex.
html开放索引接口介绍GIN–索引结构value:(ctid1,ctid2,.
.
.
.
)–https://www.
postgresql.
org/docs/9.
5/static/gin.
html–开发接口–https://www.
postgresql.
org/docs/9.
5/static/gin-extensibility.
html–intcompare(Datuma,Datumb)比较两个element–Datum*extractValue(DatumitemValue,int32*nkeys,bool**nullFlags)输入ctid返回对应行所在列存储的elements–Datum*extractQuery(Datumquery,int32*nkeys,StrategyNumbern,bool**pmatch,Pointer**extra_data,bool**nullFlags,int32*searchMode)columnopquery返回对应行(s)所在列存储的elements–boolconsistent(boolcheck[],StrategyNumbern,Datumquery,int32nkeys,Pointerextra_data[],bool*recheck,DatumqueryKeys[],boolnullFlags[])columnopquery返回trueorfalse–GinTernaryValuetriConsistent(GinTernaryValuecheck[],StrategyNumbern,Datumquery,int32nkeys,Pointerextra_data[],DatumqueryKeys[],boolnullFlags[])GIN_TRUE,GIN_FALSEandGIN_MAYBE(需要recheck,lossy部分).
例子–array,ts,hstore开放索引接口介绍operatorstrategynumber不固定策略号、视数据类型IndexMethodSupportRoutines开放索引接口介绍GiST(balanced,tree结构索引)–https://www.
postgresql.
org/docs/9.
5/static/gist-intro.
html–开发接口–https://www.
postgresql.
org/docs/9.
5/static/gist-extensibility.
html–consistentcolumnopquery返回trueorfalse(recheck表示是否为lossy)–union输入一批entry转换成一个entry,基于结果entry创建索引–compress将被索引的entry压缩成适合在indexpage中存储的Datum–decompress解压–penalty计算并返回entry插入索引branch的cost–picksplit当索引页需要分裂时,决定哪些entry需要保留在原地page,哪些entry需要移到新的page.
–same比较两个entry是否相等–distancecolumnopquery返回"距离",需要排序的话必须实现distance接口函数–fetch获取索引entry对应的columnvalue例子–range,point,box开放索引接口介绍operatorstrategynumber不固定策略号、视数据类型IndexMethodSupportRoutines开放索引接口介绍SP-GiST(non-balanced数据结构,quad-trees,k-dtrees,andradixtrees(tries))repeatedlydividesearchspaceintopartitionsthatneednotbeofequalsize–https://www.
postgresql.
org/docs/9.
5/static/spgist.
html–开发接口–https://www.
postgresql.
org/docs/9.
5/static/spgist-extensibility.
html例子–range,point开放索引接口介绍operatorstrategynumber不固定策略号、视数据类型IndexMethodSupportRoutines开放索引接口介绍Operatorstrategynumberbtreehash固定策略号brin不固定策略号、视数据类型开放索引接口介绍IndexMethodSupportRoutines–btree–hash–brin自定义GIN索引例子Operator(s)functionPG_FUNCTION_INFO_V1(hstore_contains);Datumhstore_contains(PG_FUNCTION_ARGS){HStore*val=PG_GETARG_HS(0);HStore*tmpl=PG_GETARG_HS(1);boolres=true;HEntry*te=ARRPTR(tmpl);char*tstr=STRPTR(tmpl);HEntry*ve=ARRPTR(val);char*vstr=STRPTR(val);inttcount=HS_COUNT(tmpl);intlastidx=0;inti;/**weexploitthefactthatkeysin"tmpl"areinstrictlyincreasing*ordertonarrowthehstoreFindKeysearch;eachsearchcanstartone*entrypasttheprevious"found"entry,oratthelowerboundofthe*search*/自定义GIN索引例子for(i=0;res&&i=0){boolnullval=HSTORE_VALISNULL(te,i);intvallen=HSTORE_VALLEN(te,i);if(nullval!
=HSTORE_VALISNULL(ve,idx)||(!
nullval&&(vallen!
=HSTORE_VALLEN(ve,idx)||memcmp(HSTORE_VAL(te,tstr,i),HSTORE_VAL(ve,vstr,idx),vallen)!
=0)))res=false;}elseres=false;}PG_RETURN_BOOL(res);}自定义GIN索引例子CREATEFUNCTIONhs_contains(hstore,hstore)RETURNSboolAS'MODULE_PATHNAME','hstore_contains'LANGUAGECSTRICTIMMUTABLE;CREATEOPERATOR@>(LEFTARG=hstore,RIGHTARG=hstore,PROCEDURE=hs_contains,COMMUTATOR=',OPERATOR9(hstore,text),OPERATOR10|(hstore,text[]),OPERATOR11&(hstore,text[]),FUNCTION1bttextcmp(text,text),FUNCTION2gin_extract_hstore(internal,internal),FUNCTION3gin_extract_hstore_query(internal,internal,int2,internal,internal),FUNCTION4gin_consistent_hstore(internal,int2,internal,int4,internal,internal),STORAGEtext;自定义GiST索引例子略PostgreSQL内核扩展接口总结PostgreSQL有哪些开放接口–UDF(包括聚合、窗口以及普通的函数)https://www.
postgresql.
org/docs/9.
5/static/xfunc-c.
html–GiST,SP-GiST,GIN,BRIN自定义索引接口https://www.
postgresql.
org/docs/9.
5/static/gist.
html.
.
.
.
.
.
–扩展索引接口(bloom例子)https://www.
postgresql.
org/docs/9.
6/static/bloom.
htmlhttps://www.
postgresql.
org/docs/9.
6/static/xindex.
html–操作符https://www.
postgresql.
org/docs/9.
5/static/sql-createoperator.
html–数据类型https://www.
postgresql.
org/docs/9.
5/static/sql-createtype.
html–FDWhttps://www.
postgresql.
org/docs/9.
5/static/fdwhandler.
html–函数语言handlerhttps://www.
postgresql.
org/docs/9.
5/static/plhandler.
html–SPIhttps://www.
postgresql.
org/docs/9.
5/static/spi.
html–动态fork进程,动态创建共享内存段https://www.
postgresql.
org/docs/9.
5/static/bgworker.
html–tablesamplingmethodhttps://www.
postgresql.
org/docs/9.
5/static/tablesample-method.
html–customscanproviderhttps://www.
postgresql.
org/docs/9.
5/static/custom-scan.
html–自定义REDO日志encode,decode接口https://www.
postgresql.
org/docs/9.
6/static/generic-wal.
htmlPostgreSQL插件打包、发布https://www.
postgresql.
org/docs/9.
6/static/extend-extensions.
htmlhttps://www.
postgresql.
org/docs/9.
6/static/extend-pgxs.
htmlhttp://pgxn.
org/about/PostgreSQL插件打包、发布MakefilePostgreSQL插件打包、发布controlfile–#hstoreextension–comment='datatypeforstoringsetsof(key,value)pairs'–default_version='1.
3'–module_pathname='$libdir/hstore'–relocatable=trueGPU,FPGA如何与PostGIS深度整合customscanproviderAPIhttps://www.
postgresql.
org/docs/9.
5/static/custom-scan.
htmlpg_strom介绍https://github.
com/pg-strom/develhttps://wiki.
postgresql.
org/wiki/PGStrompg_strom加速例子src/backend/optimizer/plan/planner.
cPlannedStmt*planner(Query*parse,intcursorOptions,ParamListInfoboundParams){PlannedStmt*result;if(planner_hook)result=(*planner_hook)(parse,cursorOptions,boundParams);elseresult=standard_planner(parse,cursorOptions,boundParams);returnresult;}pg_strom加速例子_PG_initpg_strom加速例子pg_strom加速例子pg_strom加速例子pg_strom加速例子pg_strom加速例子plannerhook,将plan的工作旁路到用户定制的分支处理.
用户定制的planner分支将生成基于GPU的plantree(例如dma的数据访问,基于CUDA库的并行计算等)executor执行这个plantree路径动态规划pgroutinghttp://pgrouting.
org/http://workshop.
pgrouting.
org/bit逻辑运算bit运算/**bit_and*performalogicalANDontwobitstrings.
*/Datumbit_and(PG_FUNCTION_ARGS){VarBit*arg1=PG_GETARG_VARBIT_P(0);VarBit*arg2=PG_GETARG_VARBIT_P(1);VarBit*result;intlen,bitlen1,bitlen2,i;bits8*p1,*p2,*r;bit逻辑运算bitlen1=VARBITLEN(arg1);bitlen2=VARBITLEN(arg2);if(bitlen1!
=bitlen2)ereport(ERROR,(errcode(ERRCODE_STRING_DATA_LENGTH_MISMATCH),errmsg("cannotANDbitstringsofdifferentsizes")));len=VARSIZE(arg1);result=(VarBit*)palloc(len);SET_VARSIZE(result,len);VARBITLEN(result)=bitlen1;p1=VARBITS(arg1);p2=VARBITS(arg2);r=VARBITS(result);for(i=0;inet/docs/manual-2.
2/ST_Contains.
html–booleanST_Contains(geometrygeomA,geometrygeomB);–lwgeom_geos.
c参考资料范例–contrib,.
.
.
–pgxn,github书籍–PostgreSQL数据库内核分析–PostgreSQL数据库服务端编程网站资料–http://blog.
163.
com/digoal@126/blog/static/163877040201172183022203/