PostgreSQL的类型扩展相关的几个系统表(包含pg的操作符类和操作
PostgreSQL中的数据类型,以及与之相关的操作符和索引都是可以自由扩展的,因此可支持不同领域应用的定制。而且PG的扩展非常自由,自定义的数据类型,操作符以及索引类型和原生的具有相同的能力。其秘密在于,自定义的和原生的都基于相同的设计架构,它们作为元数据平等地存储在几个系统表里。下面看看这几个系统表简单了解一下PG类型扩展的设计理念(以下很多内容摘自PG手册)。
(作为对比,简单看了下Oracle 12c的手册。Oracle也可以支持类型定制,但看上去比PG弱。Oracle的自定义数据类型依赖于已有的原生数据类型,自定义操作符的使用方法也比较怪异更像是函数调用,自定义索引类型看上去很复杂没多看) 1. 数据类型pg_type http://58.58.27.50:8079/doc/html/9.3.1_zh/catalog-pg-type.html---------------------------------------------------------------- 47.51.pg_typepg_type存储有关数据类型的信息。基本类型和枚举类型(标量类型)是用CREATE TYPE创建的,域是使用CREATE DOMAIN创建的。 同时还为数据库中每个表自动创建一个复合类型,以表示该表的行结构。还可以用CREATE TYPE AS创建复合类型。 Table 47-51.pg_type字段
Table 47-52列出了系统定义的typcategory的值。 任何未来添加到这个列表的也是大写的ASCII字母。所有其他ASCII字符为用户定义的范畴保留。 Table 47-52.typcategory代码
---------------------------------------------------------------- 比较关键是4个输入输出函数:typinput,typoutput,typreceive,typsend。PG通过这4个函数读写数据,所以扩展数据类型时,只要按要求实现好这4个函数,可以自由定义数据的内部存储格式。 例: jsonb类型 postgres=# select oid,* from pg_type where typname='jsonb'; -[ RECORD 1 ]--+----------- oid | 3802 typname | jsonb typnamespace | 11 typowner | 10 typlen | -1 typbyval | f typtype | b typcategory | U typispreferred | f typisdefined | t typdelim |,typrelid | 0 typelem | 0 typarray | 3807 typinput | jsonb_in typoutput | jsonb_out typreceive | jsonb_recv typsend | jsonb_send typmodin | - typmodout | - typanalyze | - typalign | i typstorage | x typnotnull | f typbasetype | 0 typtypmod | -1 typndims | 0 typcollation | 0 typdefaultbin | typdefault | typacl | 创建新的数据类型的SQL: CREATE TYPE --定义一个新数据类型 详细用法参考手册:http://58.58.27.50:8079/doc/html/9.3.1_zh/sql-createtype.html 2. 操作符
pg_operator
http://58.58.27.50:8079/doc/html/9.3.1_zh/catalog-pg-operator.html
---------------------------------------------------------------- 47.33.pg_operatorpg_operator存储有关操作符的信息。参阅 CREATE OPERATOR 和 节33.12 获取这些操作符参数的细节。 Table 47-33.pg_operator字段
未用的字段包含零。比如,oprleft对于前缀操作符而言是零。 例: jsonb的@>操作符 postgres=# select oid,* from pg_operator where oprleft=3802 and oprname='@>'; -[ RECORD 1 ]+--------------- oid | 3246 oprname | @> oprnamespace | 11 oprowner | 10 oprkind | b oprcanmerge | f oprcanhash | f oprleft | 3802 oprright | 3802 oprresult | 16 oprcom | 3250 oprnegate | 0 oprcode | jsonb_contains oprrest | contsel oprjoin | contjoinsel 创建新的操作符的SQL: CREATE OPERATOR --定义一个新操作符 3. 索引扩展
索引扩展相关的系统表有5个,它们通过oid相互关联。关于PG的扩展索引,有几个比较重要的概念要先说明一下。
索引访问方法 索引访问方法代表了索引算法, 目前PG提供了5种 索引访问方法,btree,hash,gist,gin和spgist。 其中 gist 和 spgist可以 支持多种多维数据的索引 算法。比如基于gist的R-tree,RD-tree,基于spgist的quad-trees,k-d trees和 radix trees。索引访问方法相关的信息存储在系统表pg_am中。 操作符类 一个索引方法的过程并不直接知道任何该索引方法将要操作的数据类型的信息。 而是 操作符类 表明索引方法在操作特定数据类型的时候需要使用的操作集合。这么说比较抽象,实际一点,create index的using子句中可以指定的索引字段数据类型的某个操作符类作为具体的索引实现(不指定的话,使用该数据类型的默认操作符类)。索引访问方法相关的信息存储在系统表pg_am中。操作符类相关的信息存储在系统表pg_opclass中。 索引方法策略 索引方法策略是操作符在操作符类环境里的内部表示。比如,B-tree索引的”等于“操作对应的策略号为3。因为PostgreSQL允许用户定义操作符,PostgreSQL 无法仅通过查看操作符的名字(比如<或>=)就明白它进行的比较是什么,所以索引方法定义了一套"策略",它可以看作一般性的操作符。比如,你可以把你的数据类型的等于操作符的名字定义成"="也可以定义成"==",只要把它的策略号设定为3,B-tree就可以知道它的语义是"等于"了。 操作符类包含的操作符集合和索引方法策略号的对应关系存储在系统表 pg_amop 中 。 索引方法支持函数 有时候,策略的信息还不足以让系统决定如何使用某个索引。在实际中, 索引方法需要附加的一些过程来保证正常工作。例如, B-tree 索引方法必须能够比较两个键字以决定其中一个是大于、等于、还是小于另外一个。与策略号相似,索引方法通过“支持号”识别支持函数。 操作符类包含的支持函数集合和支持号的对应关系存储在系统表 pg_amproc 中 。 操作符族 一个操作符类代表一种数据类型,而一个操作符族则可以包含一个或多个操作符类,它们代表一类相似的数据类型,比如int2,int4和int8。这样可以在操作符族上定义一些交叉数据类型操作符(比如int4和int8的比较)。操作符族相关的信息都存储在系统表pg_opfamily中,并且pg_opclass,pg_amop和pg_amproc通过pg_opfamily的oid聚集成一个整体。 为了说明这索引扩展相关的几个系统表的关系,后面的例子采用下面的 操作符类(族)gist__int_ops2进行说明。 下面这个例子,为int4数组创建一个 操作符 类gist__int_ops2(pg_opclass),并且由于没有指定 操作符族,所以创建一个同名的操作符族 gist__int_ops ( pg_opfamily ) 。OPERATOR相关的定义会存到 pg_amop ,FUNCTION相关的定义会存到 pg_amproc 。 CREATE OPERATOR CLASS gist__int_ops2 FOR TYPE _int4 USING gist AS OPERATOR 3 &&,OPERATOR 6 = (anyarray,anyarray),OPERATOR 7 @>,OPERATOR 8 <@,OPERATOR 13 @,OPERATOR 14 ~,OPERATOR 20 @@ (_int4,query_int),FUNCTION 1 g_int_consistent (internal,_int4,int,oid,internal),FUNCTION 2 g_int_union (internal,FUNCTION 3 g_int_compress (internal),FUNCTION 4 g_int_decompress (internal),FUNCTION 5 g_int_penalty (internal,internal,FUNCTION 6 g_int_picksplit (internal,FUNCTION 7 g_int_same (_int4,internal); pg_am
http://58.58.27.50:8079/doc/html/9.3.1_zh/catalog-pg-am.html
---------------------------------------------------------------- 47.3.pg_ampg_am存储有关索引访问方法的信息。系统支持的每种索引访问方法都有一行。 这个表的内容在Chapter 54详细讨论。 Table 47-3.pg_am字段
目前PG提供了5种索引访问方法。PG没有提供扩展索引访问方法的SQL语句,但通过往pg_am中插入记录也不是不可以扩展出新的索引访问方法。 postgres=# select oid,amname,amstrategies,amsupport from pg_am; oid | amname | amstrategies | amsupport ------+--------+--------------+----------- 403 | btree | 5 | 2 405 | hash | 1 | 1 783 | gist | 0 | 8 2742 | gin | 0 | 6 4000 | spgist | 0 | 5 (5 rows) pg_opfamily http://58.58.27.50:8079/doc/html/9.3.1_zh/catalog-pg-opfamily.html ---------------------------------------------------------------- 47.34.pg_opfamilypg_opfamily表定义操作符族。每个操作符族是一个操作符和相关支持例程的集合, 其中的例程实现为一个特定的索引访问方式指定的语义。另外,族中的操作符都是"兼容的", 通过由访问方式指定的方法。操作符族的概念允许交叉数据类型操作符和索引一起使用, 并且合理的使用访问方式的语义的知识。 操作符族在Section 35.14里面描述。 Table 47-34.pg_opfamily字段
定义一个操作符族的大多数信息不在它的pg_opfamily行里面, 而是在相关的行pg_amop,pg_amproc和pg_opclass里。 ----------------------------------------------------------------例:查询操作符族gist__int_ops2 postgres=# select oid,* from pg_opfamily where opfname = 'gist__int_ops2'; oid | opfmethod | opfname | opfnamespace | opfowner -------+-----------+----------------+--------------+---------- 32904 | 783 | gist__int_ops2 | 2200 | 10 (1 row) 创建新的操作符族的SQL: CREATE OPERATOR FAMILY--定义一个新操作符族 pg_opclass http://58.58.27.50:8079/doc/html/9.3.1_zh/catalog-pg-opclass.html ---------------------------------------------------------------- 47.32.pg_opclasspg_opclass定义索引访问方法操作符类。 每个操作符类为一种特定数据类型和一种特定索引访问方法定义索引字段的语义。 一个操作符类本质上指定一个特定的操作符族适用于一个特定的可索引的字段数据类型。 索引的字段实际可用的族中的操作符集是接受字段的数据类型作为它们的左边的输入的那个。 操作符类在Section 35.14里有比较详细的描述。 Table 47-32.pg_opclass字段
一个操作符类的opcmethod必须匹配包含它的操作符族的opfmethod。 同样,对于任意给定的opcmethod和opcintype的组合, 不能有超过一个pg_opclass行有opcdefault为真。 ----------------------------------------------------------------例:查询操作符族gist__int_ops2包含的操作符类 postgres=# select oid,* from pg_opclass where opcfamily=32904; oid | opcmethod | opcname | opcnamespace | opcowner | opcfamily | opcintype | opcdefault | opckeytype -------+-----------+----------------+--------------+----------+-----------+-----------+------------+------------ 32905 | 783 | gist__int_ops2 | 2200 | 10 | 32904 | 1007 | f | 0 (1 row) CREATE OPERATOR CLASS--定义一个新操作符类 pg_amop http://58.58.27.50:8079/doc/html/9.3.1_zh/catalog-pg-amop.html ---------------------------------------------------------------- 47.4.pg_amoppg_amop表存储有关和访问方法操作符族关联的信息。 如果一个操作符是一个操作符族中的成员,那么在这个表中会占据一行。 一个族成员是一个search操作符或一个ordering操作符。 一个操作符可以在多个族中出现,但是不能在一个族中的多个搜索位置或多个排序位置中出现。 (尽管不太可能,但这是允许的,一个操作符可以被搜索和排序目的使用。) Table 47-4.pg_amop字段
"搜索"操作符表明这个操作符族的一个索引可以被搜索,找到所有满足WHEREindexed_columnoperatorconstant的行。 显然,这样的操作符必须返回布尔值,并且它的左输入类型必须匹配索引的字段数据类型。 "排序"操作符表明这个操作符族的一个索引可以被扫描,返回以ORDER BYindexed_columnoperatorconstant顺序表示的行。这样的操作符可以返回任意可排序的数据类型,它的左输入类型也必须匹配索引的字段数据类型。ORDER BY的确切的语义是由amopsortfamily字段指定的, 该字段必须为操作符的返回类型引用一个btree操作符族。
一个项的amopmethod必须匹配它包含的操作符族的opfmethod(包括amopmethod是故意违反性能原因的表结构的规范化)。同样,amoplefttype和amoprighttype必须匹配引用的pg_operator的oprleft和oprright。 ----------------------------------------------------------------例: 查询操作符族gist__int_ops2 包含的所有 操作符 postgres=# select oid,(select oprname from pg_operator where oid=amopopr),* from pg_amop where amopfamily=32904; oid | oprname | amopfamily | amoplefttype | amoprighttype | amopstrategy | amoppurpose | amopopr | amopmethod | amopsortfamily -------+---------+------------+--------------+---------------+--------------+-------------+---------+------------+---------------- 32906 | && | 32904 | 1007 | 1007 | 3 | s | 32809 | 783 | 0 32908 | @> | 32904 | 1007 | 1007 | 7 | s | 32811 | 783 | 0 32909 | <@ | 32904 | 1007 | 1007 | 8 | s | 32810 | 783 | 0 32910 | @ | 32904 | 1007 | 1007 | 13 | s | 32813 | 783 | 0 32911 | ~ | 32904 | 1007 | 1007 | 14 | s | 32812 | 783 | 0 32912 | @@ | 32904 | 1007 | 32793 | 20 | s | 32801 | 783 | 0 32907 | = | 32904 | 2277 | 2277 | 6 | s | 1070 | 783 | 0 (7 rows) pg_amproc http://58.58.27.50:8079/doc/html/9.3.1_zh/catalog-pg-amproc.html ---------------------------------------------------------------- 47.5.pg_amprocpg_amproc存储有关与访问方法操作符族相关联的支持过程的信息。 每个属于某个操作符族的支持过程都占有一行。 Table 47-5.pg_amproc字段
amproclefttype和amprocrighttype字段的习惯解释, 他们标识一个特定支持过程支持的操作符的左和右输入类型。对于某些访问方式, 他们匹配支持过程本身的输入数据类型,对其他的则不这样。有一个对索引的"缺省"支持过程的概念,amproclefttype和amprocrighttype都等于索引操作符类的opcintype。 ----------------------------------------------------------------例: 查询操作符族gist__int_ops2 包含的所有支持函数 postgres=# select oid,* from pg_amproc where amprocfamily=32904; oid | amprocfamily | amproclefttype | amprocrighttype | amprocnum | amproc -------+--------------+----------------+-----------------+-----------+------------------ 32913 | 32904 | 1007 | 1007 | 1 | g_int_consistent 32914 | 32904 | 1007 | 1007 | 2 | g_int_union 32915 | 32904 | 1007 | 1007 | 3 | g_int_compress 32916 | 32904 | 1007 | 1007 | 4 | g_int_decompress 32917 | 32904 | 1007 | 1007 | 5 | g_int_penalty 32918 | 32904 | 1007 | 1007 | 6 | g_int_picksplit 32919 | 32904 | 1007 | 1007 | 7 | g_int_same (7 rows 4. 参考http://58.58.27.50:8079/doc/html/9.3.1_zh/xindex.htmlhttp://blog.chinaunix.net/uid-20726500-id-4884626.html http://58.58.27.50:8079/doc/html/9.3.1_zh/catalogs.html
原文:http://blog.chinaunix.net/uid-20726500-id-4926919.html (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |