PostgreSQL编写记录删除表格信息的Extension扩展
摘要:因为作者水平有限,暂且只是测试Extension扩展用例,
一个典型的extension包含多个SQL对象。举个例子，如果我们创建一个新的数据类型(data type)，那可能同时还需要与之相关的新函数(function)、新的操作符(operator)以及新的索引类型(index operator class)等，为了简化数据库管理，我们可以把这个新的数据类型以及所有与之相关的对象都封装到一个包(package)里。在Postgresql里，这样的包(package)就是extension。Extension至少包含一个脚本文件和一个控制文件，脚本文件(包含SQL命令)用来创建相关对象，控制文件(control file)用来指定extension的基本属性。有些extension中还包含一些C语言实现的共享库。如果具备了这些文件，一条简单的CREATE EXTENSION命令就可以将所有属于同一个extensioin的相关对象加载到数据库中。
用extension的好处是可以让原本处于“松散”状态但实际相关的SQL对象在数据库中可以以一个整体的形式存在。一条简单的DROP EXTENSION命令就可以删除所有的相关对象，而不需要一个个的去删。除此之外，pg_dump命令在执行时仅会备份CREATE EXTENSION命令，而不备份extension中的对象，这样极大的简化了不同版本之间的相互备份和恢复。注意，在使用extension时，必须具备相关的权限(创建SQL对象、相关文件)。不允许删除extension中的单个对象，要删除必须删除整个extension，而且对单个对象的修改(如CREATE OR REPLACE FUNCTION)也不会被pg_dump备份，除非将相同的修改命令写到extension中的脚本文件中。
extension机制也支持添加修改脚本来对某些SQL对象进行重新定义。举个例子，1.1版本的extension在1.0的基础上增加了一个函数，并修改了另一个函数的部分代码，那创建extension的用户就可以添加一个更新脚本来做这些修改。 ALTER EXTENSION UPDATE命令可以用来使这些修改生效，同时还能知道当前添加到数据库的extension到底是哪一个版本。想知道extension所支持的SQL对象的类型，请看ALTER EXTENSION命令的相关文档。
注意，由于extension是属于具体的某个数据库的，诸如数据库、角色以及表空间这些所有数据库共享的对象是不能作为extension的成员对象的，虽然extension中的脚本也可以创建这些对象，但即使创建了这些对象，它们也不会被当作当前extension的一部分。另外，虽然表可以作为extension的成员，但这个表的子对象并不是extension的成员，比如表的索引。除此之外还要注意，模式(schema)可以属于extension，extension并不属于一个schema。
begin: 当然首先移步,对Extension进行基础了解.https://blog.csdn.net/liguangxianbin/article/details/79865747
然后移步,对Hook做基础了解:hook机制
1.源代码:test.c
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include "postgres.h"
#include "fmgr.h"
#include "commands/tablecmds.h"
#include "libpq-fe.h"
#include "utils/timestamp.h"
#include "utils/lsyscache.h"
#include "postgres.h"
#include "commands/dbcommands.h"
#include "utils/builtins.h"
#include "postgres_ext.h"
#include "miscadmin.h"
#include "postgres_ext.h"
#include "commands/extension.h"
#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif
void _PG_init(void);
void _PG_fini(void);
/* Original Hook */
static ProcessUtility_hook_type prev_processutility_hook = NULL;
static void record_deltable_info(Oid relOid,Oid namespace_oid);
static
void record_deltable_info(Oid relOid,Oid namespace_oid)
{
PGresult *res;
PGconn *conn;
char cmd[256];
char conninfo[256];
//char search_name[256];
Oid ex_oid;
//char* tuple;
Name db;
char db_name[NAMEDATALEN];
char *user_name;
char *table_name;
char *extension_name = "delrelation_record";
//char* search_text = " select relname from pg_class where relname = \'pg_removetable_record\'";
ex_oid = get_extension_oid(extension_name,true);
//res = PQexec(conn, search_text);
//tuple = PQcmdTuples(res);
if(ex_oid != 0)
{
db = (Name) palloc(NAMEDATALEN);
/* get current database name */
namestrcpy(db, get_database_name(MyDatabaseId));
strcpy(db_name, db->data);
/* get current database user name */
user_name = GetUserNameFromId(GetUserId(), true);
/* connect pg_database */
sprintf(conninfo, " dbname = %s user = %s", db_name, user_name);
conn = PQconnectdb(conninfo);
/* given reloid ,get tablename */
table_name = get_rel_name(relOid);
/* From information of delete table, insert into pg_removetable_table */
sprintf(cmd, " insert into pg_removetable_record(user_name,oid,table_name,namespace_oid,time) values(\'%s\',%d,\'%s\',%d,now())", user_name, relOid, table_name, namespace_oid);
res = PQexec(conn, cmd);
//tuple = PQcmdTuples(res);
/* charge insert is not success */
if(!res || PQresultStatus(res) != PGRES_COMMAND_OK)
ereport(ERROR,
(errcode(ERRCODE_TRIGGERED_ACTION_EXCEPTION),
errmsg("Record from remove relations \"%s\", insert into pg_removetable_record Failed! ", user_name)));
PQclear(res);
}
/*
else
{
if(PQresultStatus(res) == PGRES_FATAL_ERROR)
ereport(ERROR,
(errcode(ERRCODE_TRIGGERED_ACTION_EXCEPTION),
errmsg("Record from remove relations \"%s\", Or \"%s\" is Failed! ", db_name, user_name)));
}
*/
}
/*
* Module Load Callback
*/
void
_PG_init(void)
{
/* Install Hooks */
prev_processutility_hook = my_ProcessUtility_hook;
my_ProcessUtility_hook = (void*)record_deltable_info;
}
void
_PG_fini(void)
{
/* Uninstall hooks. */
my_ProcessUtility_hook = prev_processutility_hook;
}2.pg源码src/backend/commands/tablecmds.c,在RemoveRelations()中添加粗体代码:
namespace_oid =RangeVarGetCreationNamespace(rel);
/* Not there? */
if (!OidIsValid(relOid))
{
DropErrorMsgNonExistent(rel, relkind, drop->missing_ok);
continue;
}
//if (RELKIND_RELATION == get_rel_relkind(relOid) && PARTITION_STRATEGY_HASH == get_hash_part_strategy(relOid))
// elog(ERROR,"can not directly delete the hash partition sub table.");
if(my_ProcessUtility_hook)
{
(*my_ProcessUtility_hook)(rel->relname,relOid,namespace_oid);
}
/* OK, we're ready to delete this one */
obj.classId = RelationRelationId;
obj.objectId = relOid;
obj.objectSubId = 0;
add_exact_object_address(&obj, objects);3.在src/include/commands/tablecmds.h,添加Hook函数声明语句:
typedef void (*ProcessUtility_hook_type) (char *, int,int);
extern PGDLLIMPORT ProcessUtility_hook_type my_ProcessUtility_hook;4.Makefile: 详情请移步:postgresql扩展test–sql复制出现错误.
# contrib/test/Makefile
MODULE_big = test
OBJS = test.o
PG_CPPFLAGS = -I$(libpq_srcdir)
SHLIB_LINK = $(libpq)
SHLIB_PREREQS = submake-libpq
# the db name is hard-coded in the tests
EXTENSION = test
DATA = test--1.0.sql
ifdef USE_PGXS
PG_CONFIG = pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)
else
subdir = contrib/test
top_builddir = ../..
include $(top_builddir)/src/Makefile.global
include $(top_srcdir)/contrib/contrib-global.mk
endif
5.test–1.0.sql
/* contrib/test/test--1.0.sql */
--complain if script is sourced in psql rather than via ALTER EXTENSION
\echo Use "CRAETE EXTENSION pg_removetable_record" to load this file. \quit
CREATE TABLE pg_removetable_record(oid integer,namespace_oid integer,name text,time timestamptz,user_name text);6.test.control
# test extension
comment = 'only test publication'
default_version = '1.0'
module_pathname = '$libdir/test'
relocatable = true
7.contrib/test 编译安装:
me@me:~/Desktop/postgresql-10.1/contrib/test$ make & make install
[1] 28738
make -C ../../src/interfaces/libpq all
make -C ../../src/interfaces/libpq all
make[1]: Entering directory '/home/me/Desktop/postgresql-10.1/src/interfaces/libpq'
make[1]: Nothing to be done for 'all'.
make[1]: Leaving directory '/home/me/Desktop/postgresql-10.1/src/interfaces/libpq'
/bin/mkdir -p '/opt/HighGo/postgresql10.1/lib'
make[1]: Entering directory '/home/me/Desktop/postgresql-10.1/src/interfaces/libpq'
make[1]: Nothing to be done for 'all'.
make[1]: Leaving directory '/home/me/Desktop/postgresql-10.1/src/interfaces/libpq'
/bin/mkdir -p '/opt/HighGo/postgresql10.1/share/extension'
/bin/mkdir -p '/opt/HighGo/postgresql10.1/share/extension'
/usr/bin/install -c -m 755 test.so '/opt/HighGo/postgresql10.1/lib/test.so'
/usr/bin/install -c -m 644 ./test.control '/opt/HighGo/postgresql10.1/share/extension/'
/usr/bin/install -c -m 644 ./test--1.0.sql '/opt/HighGo/postgresql10.1/share/extension/'
[1]+ 已完成 make
8.在postgresql.conf 中加载动态库: 详情移步:PostgreSQL 加载动态库详解
9.测试:
me@me:/opt/HighGo/postgresql10.1/bin$ ./psql
psql (10.1)
输入 "help" 来获取帮助信息.
me=# \d
Did not find any relations.
me=# create extension test;
CREATE EXTENSION
me=# \d
关联列表
架构模式 | 名称 | 类型 | 拥有者
----------+-----------------------+--------+--------
public | pg_removetable_record | 数据表 | me
(1 行记录)
me=# create table haha();
CREATE TABLE
me=# create table hehe();
CREATE TABLE
me=# drop table haha,hehe;
DROP TABLE
me=# table pg_removetable_record;
oid | namespace_oid | name | time | user_name
-------+---------------+------+-------------------------------+-----------
49486 | 2200 | haha | 2018-04-12 15:58:56.873061+08 | me
49489 | 2200 | hehe | 2018-04-12 15:58:56.87996+08 | me
(2 行记录)
参考:https://blog.csdn.net/liguangxianbin/article/details/79865747
http://www.cnblogs.com/gaojian/p/3259147.html
https://my.oschina.net/Suregogo/blog/312848
https://my.oschina.net/u/3618133/blog/1537071
https://blog.csdn.net/djskl/article/details/40152585
转载自：https://blog.csdn.net/liguangxianbin/article/details/79914542
