代码版本: postgresql-10.1
一、直接写入内核代码
1.创建系统表
修改postgres.bki文件，添加创建系统表语句
1create pg_subscription_rel 6102 without_oids 2 ( 3 srsubid = oid , 4 srrelid = oid , 5 srsubstate = char , 6 srsublsn = pg_lsn 7 ) 8open pg_subscription_rel 9close pg_subscription_rel 10# by sunbeau 11create sunbeau_droprecord 88889 12 ( 13 tableiod = oid , 14 tablename = name , 15 namespaceid = oid , 16 ownerid = oid 17 ) 18open sunbeau_droprecord 19close sunbeau_droprecord 20declare toast 2830 2831 on pg_attrdef 21declare toast 2832 2833 on pg_constraint 22declare toast 2834 2835 on pg_description
2. 修改 tablecmd.c 文件
1）添加头文件
1#include "libpq-fe.h" //直接添加头文件编译会找不到，可以使用 /xxx/src/interfaces/libpq/libpq-fe.h 绝对路径 2
或者修改 src/backend/commands/Makefile
添加
1override CPPFLAGS := -I. -I$(libpq_srcdir) $(CPPFLAGS) 2
2）添加两个函数
1Oid 2MyGetOwnerId(Oid class_oid) 3{ 4 HeapTuple tuple; 5 Oid ownerId; 6 7 tuple = SearchSysCache1(RELOID, ObjectIdGetDatum(class_oid)); 8 if (!HeapTupleIsValid(tuple)) 9 ereport(ERROR, 10 (errcode(ERRCODE_UNDEFINED_TABLE), 11 errmsg("relation with OID %u does not exist", class_oid))); 12 13 ownerId = ((Form_pg_class) GETSTRUCT(tuple))->relowner; 14 15 ReleaseSysCache(tuple); 16 return ownerId; 17} 18 19static 20void Write_info(Oid tableOid,char * tableName,Oid NameSpaceId,Oid Ownid) 21{ 22 char conninfo[256]; 23 char SQL[256]; 24 25 PGconn *conn = NULL; 26 PGresult *res; 27 char * DBname; 28 char * Username; 29 30 DBname = get_database_name(MyDatabaseId); 31 // Username = GetUserNameFromId(GetUserId(), true); 32 Username = GetUserNameFromId(10, true); // 10 speruser 33 sprintf(conninfo, "dbname = %s user = %s", DBname, Username); 34 sprintf(SQL, "insert into sunbeau_droprecord values(%d,\'%s\',%d,%d);", tableOid, tableName, NameSpaceId, Ownid); 35 conn = PQconnectdb(conninfo); 36 if (PQstatus(conn) != CONNECTION_OK) 37 { 38 printf("Connection to database failed"); 39 } 40 res = PQexec(conn,SQL); 41 PQclear(res); 42 PQfinish(conn); 43} 44
3）修改RemoveRelations函数
1/* 2 * RemoveRelations 3 * Implements DROP TABLE, DROP INDEX, DROP SEQUENCE, DROP VIEW, 4 * DROP MATERIALIZED VIEW, DROP FOREIGN TABLE 5 */ 6void 7RemoveRelations(DropStmt *drop) 8{ 9 ObjectAddresses *objects; 10 char relkind; 11 ListCell *cell; 12 int flags = 0; 13 LOCKMODE lockmode = AccessExclusiveLock; 14 15 /* DROP CONCURRENTLY uses a weaker lock, and has some restrictions */ 16 if (drop->concurrent) 17 { 18 flags |= PERFORM_DELETION_CONCURRENTLY; 19 lockmode = ShareUpdateExclusiveLock; 20 Assert(drop->removeType == OBJECT_INDEX); 21 if (list_length(drop->objects) != 1) 22 ereport(ERROR, 23 (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), 24 errmsg("DROP INDEX CONCURRENTLY does not support dropping multiple objects"))); 25 if (drop->behavior == DROP_CASCADE) 26 ereport(ERROR, 27 (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), 28 errmsg("DROP INDEX CONCURRENTLY does not support CASCADE"))); 29 } 30 31 /* 32 * First we identify all the relations, then we delete them in a single 33 * performMultipleDeletions() call. This is to avoid unwanted DROP 34 * RESTRICT errors if one of the relations depends on another. 35 */ 36 37 /* Determine required relkind */ 38 switch (drop->removeType) 39 { 40 case OBJECT_TABLE: 41 relkind = RELKIND_RELATION; 42 break; 43 44 case OBJECT_INDEX: 45 relkind = RELKIND_INDEX; 46 break; 47 48 case OBJECT_SEQUENCE: 49 relkind = RELKIND_SEQUENCE; 50 break; 51 52 case OBJECT_VIEW: 53 relkind = RELKIND_VIEW; 54 break; 55 56 case OBJECT_MATVIEW: 57 relkind = RELKIND_MATVIEW; 58 break; 59 60 case OBJECT_FOREIGN_TABLE: 61 relkind = RELKIND_FOREIGN_TABLE; 62 break; 63 64 default: 65 elog(ERROR, "unrecognized drop object type: %d", 66 (int) drop->removeType); 67 relkind = 0; /* keep compiler quiet */ 68 break; 69 } 70 71 /* Lock and validate each relation; build a list of object addresses */ 72 objects = new_object_addresses(); 73 74 foreach(cell, drop->objects) 75 { 76 RangeVar *rel = makeRangeVarFromNameList((List *) lfirst(cell)); 77 Oid relOid; 78 ObjectAddress obj; 79 struct DropRelationCallbackState state; 80 81 /* 82 * These next few steps are a great deal like relation_openrv, but we 83 * don't bother building a relcache entry since we don't need it. 84 * 85 * Check for shared-cache-inval messages before trying to access the 86 * relation. This is needed to cover the case where the name 87 * identifies a rel that has been dropped and recreated since the 88 * start of our transaction: if we don't flush the old syscache entry, 89 * then we'll latch onto that entry and suffer an error later. 90 */ 91 AcceptInvalidationMessages(); 92 93 /* Look up the appropriate relation using namespace search. */ 94 state.relkind = relkind; 95 state.heapOid = InvalidOid; 96 state.partParentOid = InvalidOid; 97 state.concurrent = drop->concurrent; 98 relOid = RangeVarGetRelidExtended(rel, lockmode, true, 99 false, 100 RangeVarCallbackForDropRelation, 101 (void *) &state); 102 103 104 /* Not there? */ 105 if (!OidIsValid(relOid)) 106 { 107 DropErrorMsgNonExistent(rel, relkind, drop->missing_ok); 108 continue; 109 } 110 111 /* OK, we're ready to delete this one */ 112 obj.classId = RelationRelationId; 113 obj.objectId = relOid; 114 obj.objectSubId = 0; 115 116 add_exact_object_address(&obj, objects); 117 Write_info(relOid,rel->relname,RangeVarGetCreationNamespace(rel),MyGetOwnerId(relOid)); // add by sunbeau 118 119 } 120 121 122 performMultipleDeletions(objects, drop->behavior, flags); 123 124 free_object_addresses(objects); 125} 126
3. 修改 src/backend/Makefile 文件
LIBS += -L$(top_builddir)/src/interfaces/libpq -lpq
4.进入src/interfaces/libpq 编译文件
在编译链接src/backend/下的postgres程序时，libpq库文件还没有编译会报错，所以需要先进行本步骤先编译
1cd src/interfaces/libpq 2make
5.编译安装数据库
返回源代码根目录编译安装
make ; make install
6.测试
二、使用插件
1.创建系统表
同一 略
2.添加自定义hook
1）修改 tablecmd.h 文件
1typedef void (*TableDropRecord_hook_type) (Oid tableOid,char * tableName,Oid NameSpaceId,Oid Ownid); 2 3extern PGDLLIMPORT TableDropRecord_hook_type TableDropRecord_hook;
2）修改 tablecmd.c 文件
1#include "utils/snapmgr.h" 2#include "utils/syscache.h" 3#include "utils/tqual.h" 4#include "utils/typcache.h" 5 6 7TableDropRecord_hook_type TableDropRecord_hook = NULL; // add by sunbeau 8 9/* 10 * ON COMMIT action list 11 */ 12typedef struct OnCommitItem 13{ 14 Oid relid; /* relid of relation */ 15 OnCommitAction oncommit; /* what to do at end of xact */ 16
【1】添加MyGetOwnerId函数，同一 。
【2】修改RemoveRelations函数，将一中的Write_info调用函数去掉
1// Write_info(relOid,rel->relname,RangeVarGetCreationNamespace(rel),MyGetOwnerId(relOid)); // add by sunbeau 2if (TableDropRecord_hook) // add by sunbeau 3{ 4 (*TableDropRecord_hook)(relOid,rel->relname,RangeVarGetCreationNamespace(rel),MyGetOwnerId(relOid)); 5}
3.编写插件
1）postgresql-10.1/contrib 文件目录下创建 droprecord 目录
2）创建Makefile
1# contrib/droprecord/Makefile 2 3#MODULES = droprecord 4MODULE_big = droprecord 5OBJS = droprecord.o $(WIN32RES) 6PGFILEDESC = "droprecord" 7PG_CPPFLAGS = -I$(libpq_srcdir) 8SHLIB_LINK = $(libpq) 9 10ifdef USE_PGXS 11PG_CONFIG = pg_config 12PGXS := $(shell $(PG_CONFIG) --pgxs) 13include $(PGXS) 14else 15subdir = contrib/droprecord 16top_builddir = ../.. 17include $(top_builddir)/src/Makefile.global 18include $(top_srcdir)/contrib/contrib-global.mk 19endif
3）创建droprecord.c
1#include <stdio.h> 2#include <stdlib.h> 3#include <string.h> 4 5#include "postgres.h" 6#include "miscadmin.h" 7#include "nodes/parsenodes.h" 8#include "nodes/pg_list.h" 9#include "catalog/pg_class.h" 10#include "executor/executor.h" 11#include "tcop/utility.h" 12#include "postgres.h" 13 14#include "access/htup_details.h" 15#include "access/reloptions.h" 16#include "access/twophase.h" 17#include "access/xact.h" 18#include "access/xlog.h" 19#include "catalog/catalog.h" 20#include "catalog/namespace.h" 21#include "catalog/toasting.h" 22#include "commands/alter.h" 23#include "commands/async.h" 24#include "commands/cluster.h" 25#include "commands/comment.h" 26#include "commands/collationcmds.h" 27#include "commands/conversioncmds.h" 28#include "commands/copy.h" 29#include "commands/createas.h" 30#include "commands/dbcommands.h" 31#include "commands/defrem.h" 32#include "commands/discard.h" 33#include "commands/event_trigger.h" 34#include "commands/explain.h" 35#include "commands/extension.h" 36#include "commands/matview.h" 37#include "commands/lockcmds.h" 38#include "commands/policy.h" 39#include "commands/portalcmds.h" 40#include "commands/prepare.h" 41#include "commands/proclang.h" 42#include "commands/schemacmds.h" 43#include "commands/seclabel.h" 44#include "commands/sequence.h" 45#include "commands/tablecmds.h" 46#include "commands/tablespace.h" 47#include "commands/trigger.h" 48#include "commands/typecmds.h" 49#include "commands/user.h" 50#include "commands/vacuum.h" 51#include "commands/view.h" 52#include "miscadmin.h" 53#include "parser/parse_utilcmd.h" 54#include "postmaster/bgwriter.h" 55#include "rewrite/rewriteDefine.h" 56#include "rewrite/rewriteRemove.h" 57#include "storage/fd.h" 58#include "tcop/pquery.h" 59#include "tcop/utility.h" 60#include "utils/acl.h" 61#include "utils/guc.h" 62#include "utils/syscache.h" 63#include "libpq-fe.h" 64 65PG_MODULE_MAGIC; 66 67void _PG_init(void); 68void _PG_fini(void); 69 70static TableDropRecord_hook_type prev_TableDropRecord_hook = NULL; 71 72static 73void 74TableDropRecord(Oid tableOid,char * tableName,Oid NameSpaceId,Oid Ownid) 75{ 76 char conninfo[256]; 77 char SQL[256]; 78 79 PGconn *conn = NULL; 80 PGresult *res; 81 char * DBname; 82 char * Username; 83 84 DBname = get_database_name(MyDatabaseId); 85 // Username = GetUserNameFromId(GetUserId(), true); 86 Username = GetUserNameFromId(10, true); // 10 speruser 87 sprintf(conninfo, "dbname = %s user = %s", DBname, Username); 88 sprintf(SQL, "insert into sunbeau_droprecord values(%d,\'%s\',%d,%d);", tableOid, tableName, NameSpaceId, Ownid); 89 conn = PQconnectdb(conninfo); 90 if (PQstatus(conn) != CONNECTION_OK) 91 { 92 printf("Connection to database failed"); 93 } 94 res = PQexec(conn,SQL); 95 PQclear(res); 96 PQfinish(conn); 97} 98 99// Install Hook 100void 101_PG_init(void) 102{ 103 prev_TableDropRecord_hook = TableDropRecord_hook; 104 TableDropRecord_hook = TableDropRecord; 105} 106 107 108// Uninstall Hook 109void 110_PG_fini(void) 111{ 112 TableDropRecord_hook = prev_TableDropRecord_hook; 113} 114
4）编译安装插件
1[sunbeau@centos7 ~/postgresql-10.1/contrib/droprecord]$ ll 2total 8 3-rw-rw-r--. 1 sunbiao sunbiao 2887 May 17 10:13 droprecord.c 4-rw-rw-r--. 1 sunbiao sunbiao 433 May 17 15:03 Makefile 5[sunbeau@centos7 ~/postgresql-10.1/contrib/droprecord]$ make ; make install
初始化数据库修改 postgresql.conf 加载插件
shared_preload_libraries = 'droprecord' # (change requires restart)
5）启动数据库测试
略