为PostgreSQL添加插件

为PostgreSQL添加插件

我目前了解的PG插件大约有两种,一种是利用hook,另一种是建立C函数,然后在数据库中进行关联。PG本身就是最好的老师,大家有兴趣可以看一下contrib目录下的插件。

下面将对两种方式进行介绍:

1、利用hook建立插件,hook是PG中可以对PG运行机制进行修改的一种方式,大家可以看一下我之前对PG hook的介绍:

a.在contrib目录下建立brother目录,建立brother.c文件和Makefile文件。具体内容见下:

brother.c(在用户brother登录验证成功后,显示信息Welcome to the world of PostgreSQL!):

/*------------------------------------------------------------------------- * * brother.c *  welcome to the world of PostgreSQL * *  contrib/brother/brother.c *------------------------------------------------------------------------- */#include "postgres.h"#include "libpq/auth.h"#include "miscadmin.h"#include "utils/guc.h"PG_MODULE_MAGIC;void _PG_init(void);void _PG_fini(void);static ClientAuthentication_hook_type pre_ClientAuthentication_hook = NULL;staticvoid welcome_to_pg(Port *port, int status){if(status == STATUS_OK && (strcmp(port->user_name, "brother") == 0))printf("Welcome to the world of PostgreSQL!");}void_PG_init(void){        pre_ClientAuthentication_hook = ClientAuthentication_hook;        ClientAuthentication_hook = welcome_to_pg;}void_PG_fini(void){        ClientAuthentication_hook = pre_ClientAuthentication_hook;}
Makefile文件:
# contrib/brother/MakefileMODULE_big = brotherOBJS = brother.o# uncomment the following two lines to enable cracklib support# PG_CPPFLAGS = -DUSE_CRACKLIB '-DCRACKLIB_DICTPATH="/usr/lib/cracklib_dict"'# SHLIB_LINK = -lcrackifdef USE_PGXSPG_CONFIG = pg_configPGXS := $(shell $(PG_CONFIG) --pgxs)include $(PGXS)elsesubdir = contrib/brothertop_builddir = ../..include $(top_builddir)/src/Makefile.globalinclude $(top_srcdir)/contrib/contrib-global.mkendif
b.编译并安装
[postgres@localhost brother]$ makegcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -g -O2 -fpic -I. -I. -I../../src/include -D_GNU_SOURCE   -c -o brother.o brother.cgcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -g -O2 -fpic -shared -o brother.so brother.o -L../../src/port -L../../src/common -Wl,--as-needed -Wl,-rpath,'/opt/hg3.0/lib',--enable-new-dtags  [postgres@localhost brother]$ make install/bin/mkdir -p '/opt/hg3.0/lib/postgresql'/usr/bin/install -c -m 755  brother.so '/opt/hg3.0/lib/postgresql/brother.so'
c.文件安装位置
[postgres@localhost postgresql]$ pwd/opt/hg3.0/lib/postgresql[postgres@localhost postgresql]$ ll brother.so -rwxr-xr-x. 1 postgres postgres 21523 Dec 23 06:27 brother.so
d.在postgresql.conf中配置
shared_preload_libraries = 'brother'
e.运行,查看效果
[postgres@localhost bin]$ ./pg_ctl -D ../data startserver starting[postgres@localhost bin]$ LOG:  database system was shut down at 2015-12-23 06:32:38 PSTLOG:  MultiXact member wraparound protections are now enabledLOG:  database system is ready to accept connectionsLOG:  autovacuum launcher started[postgres@localhost bin]$ ./psqlpsql (9.4.5)Type "help" for help.postgres=# create user brother;CREATE ROLE[postgres@localhost bin]$ ./psql postgres brotherWelcome to the world of PostgreSQL!psql (9.4.5)Type "help" for help.postgres=>

2、建立C函数,并建立关联

a.在contrib下建立目录userid,建立C文件userid.c,建立Makefile文件,建立两个SQL文件,userid--1.0.sql,userid--unpackaged--1.0.sql,以及一个control文件,userid.control。

(1)C文件和Makefile就不介绍了,内容如下:
userid.c:

/*------------------------------------------------------------------------- * * userid.c *  display current session user oid * *  contrib/userid/userid.c *------------------------------------------------------------------------- */#include "postgres.h"#include "libpq/auth.h"#include "utils/guc.h"#include "miscadmin.h"PG_MODULE_MAGIC;PG_FUNCTION_INFO_V1(get_current_user_id);Datumget_current_user_id(PG_FUNCTION_ARGS){PG_RETURN_OID(GetSessionUserId());}
Makefile:
# contrib/userid/MakefileMODULE_big = useridOBJS = userid.oEXTENSION = useridDATA = userid--1.0.sql userid--unpackaged--1.0.sqlifdef USE_PGXSPG_CONFIG = pg_configPGXS := $(shell $(PG_CONFIG) --pgxs)include $(PGXS)elsesubdir = contrib/useridtop_builddir = ../..include $(top_builddir)/src/Makefile.globalinclude $(top_srcdir)/contrib/contrib-global.mkendif

(2)userid--1.0.sql是在create extension时加载的SQL文件,会执行SQL文件中的sql,内容如下:

/* contrib/userid/userid--1.0.sql */-- complain if script is sourced in psql, rather than via CREATE EXTENSIONecho Use "CREATE EXTENSION userid" to load this file. quit-- Register the function.CREATE SCHEMA brother;CREATE FUNCTION get_current_userid()RETURNS OIDAS 'MODULE_PATHNAME', 'get_current_user_id'LANGUAGE C;
(3) userid--unpackaged--1.0.sql是CREATE EXTENSION userid FROM unpackaged时调用的SQL文件,这个文件是在更新9.1之前版本的插件函数时使用的,后面会介绍用法。
/* contrib/userid/userid--unpackaged--1.0.sql */-- complain if script is sourced in psql, rather than via CREATE EXTENSIONecho Use "CREATE EXTENSION userid FROM unpackaged" to load this file. quitALTER EXTENSION userid ADD schema brother;ALTER EXTENSION userid ADD function get_current_userid();
(4)control文件是在create extension加载
的文件,根据这个文件,PG去寻找对应的sql文件,内容如下:
# userid extensioncomment = 'display current session user oid'default_version = '1.0'module_pathname = '$libdir/userid'relocatable = true
b.编译安装
[postgres@localhost userid]$ makegcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -g -O2 -fpic -I. -I. -I../../src/include -D_GNU_SOURCE   -c -o userid.o userid.cgcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -g -O2 -fpic -shared -o userid.so userid.o -L../../src/port -L../../src/common -Wl,--as-needed -Wl,-rpath,'/opt/hg3.0/lib',--enable-new-dtags  [postgres@localhost userid]$ make install/bin/mkdir -p '/opt/hg3.0/lib/postgresql'/bin/mkdir -p '/opt/hg3.0/share/postgresql/extension'/bin/mkdir -p '/opt/hg3.0/share/postgresql/extension'/usr/bin/install -c -m 755  userid.so '/opt/hg3.0/lib/postgresql/userid.so'/usr/bin/install -c -m 644 userid.control '/opt/hg3.0/share/postgresql/extension/'/usr/bin/install -c -m 644 userid--1.0.sql userid--unpackaged--1.0.sql '/opt/hg3.0/share/postgresql/extension/'
c.文件安装位置
[postgres@localhost postgresql]$ pwd/opt/hg3.0/lib/postgresql[postgres@localhost postgresql]$ ll userid.so -rwxr-xr-x. 1 postgres postgres 18665 Dec 23 06:48 userid.so[postgres@localhost extension]$ pwd/opt/hg3.0/share/postgresql/extension[postgres@localhost extension]$ ll userid*-rw-r--r--. 1 postgres postgres 329 Dec 23 06:48 userid--1.0.sql-rw-r--r--. 1 postgres postgres 142 Dec 23 06:48 userid.control-rw-r--r--. 1 postgres postgres 304 Dec 23 06:48 userid--unpackaged--1.0.sql
d.配置
[postgres@localhost bin]$ ./psql psql (9.4.5)Type "help" for help.postgres=# create extension userid;CREATE EXTENSIONpostgres=# select * from pg_extension ;      extname       | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition --------------------+----------+--------------+----------------+------------+-----------+-------------- plpgsql            |       10 |           11 | f              | 1.0        |           |  pgfincore          |       10 |         2200 | t              | 1.1.1      |           |  pg_stat_statements |       10 |         2200 | t              | 1.2        |           |  pg_freespacemap    |       10 |         2200 | t              | 1.0        |           |  pgstattuple        |       10 |         2200 | t              | 1.2        |           |  userid             |       10 |         2200 | t              | 1.0        |           | (6 rows)
e.运行,查看效果
postgres=# select SESSION_USER; session_user -------------- postgres(1 row)postgres=# select oid from pg_authid where rolname = 'postgres'; oid -----  10(1 row)postgres=# select get_current_userid(); get_current_userid --------------------                 10(1 row)postgres=# dn  List of schemas  Name   |  Owner   ---------+---------- brother | postgres public  | postgres(2 rows)postgres=#
f.使用unpackaged.sql,这里是更新9.1之前的插件使用的,现在来模拟一下:
postgres=# create schema brother;CREATE SCHEMApostgres=# CREATE FUNCTION get_current_userid()RETURNS OIDAS '$libdir/userid', 'get_current_user_id'LANGUAGE C;CREATE FUNCTIONpostgres=# create extension userid with schema public from unpackaged;CREATE EXTENSION
因为在9.1之前都是利用SQL安装的插件,没有和extension进行关联,so,在这里可以关联一下。那什么是关联呢?

g.extension关联的意思,首先大家需要知道PG的表,pg_depend。这张表是当我们进行drop时,添加参数CASCADE会调用查询的一张表,这张表主要存储对象oid依赖的对象oid等等。下面来看一下:

postgres=# select oid from pg_proc where proname = 'get_current_userid';  oid  ------- 24715(1 row)postgres=# select oid from pg_namespace where nspname = 'brother';   oid  ------- 24714(1 row)postgres=# select oid from pg_extension where extname = 'userid';  oid  ------- 24716(1 row)postgres=# select * from pg_depend where refobjid = 24716; classid | objid | objsubid | refclassid | refobjid | refobjsubid | deptype ---------+-------+----------+------------+----------+-------------+---------    2615 | 24714 |        0 |       3079 |    24716 |           0 | e    1255 | 24715 |        0 |       3079 |    24716 |           0 | e(2 rows)
这个地方就是当你drop extension时,同时会删除你插件中SQL文件建立的对象的原因。那当create extension时,又是什么地方去写pg_depend信息的呢?

h.当你进行create extension时,下面这段代码就是自动建立关联的代码:

/* * If we are executing a CREATE EXTENSION operation, mark the given object * as being a member of the extension.  Otherwise, do nothing. * * This must be called during creation of any user-definable object type * that could be a member of an extension. * * If isReplace is true, the object already existed (or might have already * existed), so we must check for a pre-existing extension membership entry. * Passing false is a guarantee that the object is newly created, and so * could not already be a member of any extension. */voidrecordDependencyOnCurrentExtension(const ObjectAddress *object,   bool isReplace){/* Only whole objects can be extension members */Assert(object->objectSubId == 0);if (creating_extension){ObjectAddress extension;/* Only need to check for existing membership if isReplace */if (isReplace){Oidoldext;oldext = getExtensionOfObject(object->classId, object->objectId);if (OidIsValid(oldext)){/* If already a member of this extension, nothing to do */if (oldext == CurrentExtensionObject)return;/* Already a member of some other extension, so reject */ereport(ERROR,(errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE), errmsg("%s is already a member of extension "%s"",getObjectDescription(object),get_extension_name(oldext))));}}/* OK, record it as a member of CurrentExtensionObject */extension.classId = ExtensionRelationId;extension.objectId = CurrentExtensionObject;extension.objectSubId = 0;recordDependencyOn(object, &extension, DEPENDENCY_EXTENSION);}}

至此,就是我在学习PG源码中学习到的知识。这里介绍的还比较简单,请大家多多关注,我后续还会继续整理的。

推荐阅读

    excel怎么用乘法函数

    excel怎么用乘法函数,乘法,函数,哪个,excel乘法函数怎么用?1、首先用鼠标选中要计算的单元格。2、然后选中单元格后点击左上方工具栏的fx公

    鼠标不能拖动文件了

    鼠标不能拖动文件了,鼠标,拖动,本文目录鼠标不能拖动文件了电脑鼠标无法拖动软件图标了,怎么办鼠标不能滚动怎么调整电脑鼠标不能进行任何

    wps如何修改页眉上边距

    wps如何修改页眉上边距,WPS教程,1.WPS版word怎样设置页眉边距WPS中word设置页眉边距技巧“页边距”:页面上打印区域之外的空白空间。1、设

    excel中乘法函数是什么?

    excel中乘法函数是什么?,乘法,函数,什么,打开表格,在C1单元格中输入“=A1*B1”乘法公式。以此类推到多个单元。1、A1*B1=C1的Excel乘法公式

    标准差excel用什么函数?

    标准差excel用什么函数?,函数,标准,什么,在数据单元格的下方输入l标准差公式函数公式“=STDEVPA(C2:C6)”。按下回车,求出标准公差值。详细