文档章节

为PostgreSQL添加插件

PGSmith
 PGSmith
发布于 2015/12/23 23:15
字数 1765
阅读 4593
收藏 25

    我目前了解的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;

static
void welcome_to_pg(Port *port, int status)
{
	if(status == STATUS_OK && (strcmp(port->user_name, "brother") == 0))
		printf("Welcome to the world of PostgreSQL!\n");
}

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/Makefile

MODULE_big = brother
OBJS = brother.o

# uncomment the following two lines to enable cracklib support
# PG_CPPFLAGS = -DUSE_CRACKLIB '-DCRACKLIB_DICTPATH="/usr/lib/cracklib_dict"'
# SHLIB_LINK = -lcrack

ifdef USE_PGXS
PG_CONFIG = pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)
else
subdir = contrib/brother
top_builddir = ../..
include $(top_builddir)/src/Makefile.global
include $(top_srcdir)/contrib/contrib-global.mk
endif
    b.编译并安装
[postgres@localhost brother]$ make
gcc -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.c
gcc -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 start
server starting
[postgres@localhost bin]$ LOG:  database system was shut down at 2015-12-23 06:32:38 PST
LOG:  MultiXact member wraparound protections are now enabled
LOG:  database system is ready to accept connections
LOG:  autovacuum launcher started

[postgres@localhost bin]$ ./psql
psql (9.4.5)
Type "help" for help.

postgres=# create user brother;
CREATE ROLE

[postgres@localhost bin]$ ./psql postgres brother
Welcome 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);

Datum
get_current_user_id(PG_FUNCTION_ARGS)
{
	PG_RETURN_OID(GetSessionUserId());
}
Makefile:
# contrib/userid/Makefile

MODULE_big = userid
OBJS = userid.o

EXTENSION = userid
DATA = userid--1.0.sql userid--unpackaged--1.0.sql

ifdef USE_PGXS
PG_CONFIG = pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)
else
subdir = contrib/userid
top_builddir = ../..
include $(top_builddir)/src/Makefile.global
include $(top_srcdir)/contrib/contrib-global.mk
endif

    (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 EXTENSION
\echo Use "CREATE EXTENSION userid" to load this file. \quit

-- Register the function.
CREATE SCHEMA brother;
CREATE FUNCTION get_current_userid()
RETURNS OID
AS '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 EXTENSION
\echo Use "CREATE EXTENSION userid FROM unpackaged" to load this file. \quit

ALTER EXTENSION userid ADD schema brother;
ALTER EXTENSION userid ADD function get_current_userid();
    (4)control文件是在create extension加载
的文件,根据这个文件,PG去寻找对应的sql文件,内容如下:
# userid extension
comment = 'display current session user oid'
default_version = '1.0'
module_pathname = '$libdir/userid'
relocatable = true
    b.编译安装
[postgres@localhost userid]$ make
gcc -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.c
gcc -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 EXTENSION
postgres=# select * from pg_extension ;
      extname       | extowner | extnamespace | extrelocatable | extversion | ex
tconfig | 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 SCHEMA
postgres=# CREATE FUNCTION get_current_userid()
RETURNS OID
AS '$libdir/userid', 'get_current_user_id'
LANGUAGE C;
CREATE FUNCTION
postgres=# 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.
 */
void
recordDependencyOnCurrentExtension(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)
		{
			Oid			oldext;

			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源码中学习到的知识。这里介绍的还比较简单,请大家多多关注,我后续还会继续整理的。

© 著作权归作者所有

PGSmith

PGSmith

粉丝 93
博文 55
码字总数 59804
作品 0
济南
后端工程师
私信 提问
加载中

评论(1)

Frank_mc
Frank_mc
可以用py写插件啊
PostgreSQL技术周刊第9期:PostgreSQL 11新特性解读之新增非空默认值字段不需要重写表

PostgreSQL(简称PG)的开发者们: 云栖社区已有5000位PG开发者,发布了3000+PG文章(文章列表),沉淀了700+的PG精品问答(问答列表)。 PostgreSQL技术周刊将会为大家介绍最新的PG技术与动...

萌萌怪兽
2018/12/16
0
0
PostgreSQL HooK 介绍

标签 PostgreSQL , hook 背景 PostgreSQL 的HOOK机制,结合PostgreSQL的PGinit与PGfini两个初始化函数(加载SO时自动load PGinit(), 退出会话时自动加载PGfini()),使得用户可以在不修改源码...

德哥
2018/06/29
0
0
PostgreSQL技术周刊第1期:PostgreSQL技术进阶群限时开放

PostgreSQL(简称PG)的开发者们: 云栖社区已有5000位PG开发者,发布了3000+PG文章(文章列表),沉淀了700+的PG精品问答(问答列表)。 从本周起我们开始发布PostgreSQL技术周刊,会介绍最...

不靠谱贝贝
2018/10/19
0
0
PostgreSQL 自动创建分区实践 - 写入触发器

标签 PostgreSQL , 自动创建分区 , 触发器 , 写入 , 动态创建分区 背景 数据写入时,自动创建分区。 目前pg_pathman这个分区插件,有这个功能,如果你不是用的这个插件,可以考虑一下用触发器...

德哥
2018/06/21
0
0
德哥PG系列课程直播(第6讲):用户画像系统实践

知识点 标签聚合计算,varbit插件, 异步消费, BIT位计算, dblink异步并行 学习资料 1、阿里云RDS for PostgreSQL varbitx插件与实时画像应用场景介绍 背景:PostgreSQL 内置的varbit, bit类型...

小白dora
2018/12/11
0
0

没有更多内容

加载失败,请刷新页面

加载更多

通过微服务来正确实施SOA

对于组织来说,能够构建、发展和扩展大型应用程序是至关重要的, 但所涉及的挑战使其成为一项艰巨的任务。正因为如此, 微服务凭借能够将单个组件拆分成围绕特定业务功能的独立服务,已成为构建...

Linux就该这么学
17分钟前
2
0
从 Spark 到 Kubernetes — MaxCompute 的云原生开源生态实践之路

2019年5月14日,喜提浙江省科学技术进步一等奖的 MaxCompute 是阿里巴巴自研的 EB 级大数据计算平台。该平台依托阿里云飞天基础架构,是阿里巴巴在10年前做飞天系统的三大件之分布式计算部分...

阿里云官方博客
21分钟前
1
0
使用python来操作redis用法详解

1、redis连接 redis提供两个类Redis和StrictRedis用于实现Redis的命令,StrictRedis用于实现大部分官方的命令,并使用官方的语法和命令,Redis是StrictRedis的子类,用于向后兼容旧版本的red...

dragon_tech
21分钟前
2
0
给研发工程师的代码质量利器 | SOFAChannel#5 直播整理

> SOFA:Channel,有趣实用的分布式架构频道。 > > 本文根据 SOFAChannel#5 直播分享整理,主题:给研发工程师的代码质量利器 —— 自动化测试框架 SOFAActs。 > > 回顾视频以及 PPT 查看地址...

SOFAStack
23分钟前
1
0
段错误总结

https://blog.csdn.net/e_road_by_u/article/details/61415732 一、段错误是什么 一句话来说,段错误是指访问的内存超出了系统给这个程序所设定的内存空间,例如访问了不存在的内存地址、访问...

悲催的古灵武士
24分钟前
1
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部