标签
PostgreSQL , 存储过程 , plpgsql , plpython , pljava , svn , github , 版本管理
背景
市面上有很多代码的版本管理软件，比如gitlab, github, svn等等。
商业数据库的编程能力较强，比如Oracle的PL/SQL，很多传统企业会将对一致性、可靠性要求非常高的业务逻辑放到数据库中，这就造成了数据库内不仅仅存储数据，也存储了部分业务代码。
PostgreSQL 作为开源界最高级的开源数据库，同样支持强大的函数语言plpgsql，同时通过语言扩展，支持pljava, plpython, pltcl, plperl等等，同样，使用PostgreSQL数据库函数处理业务逻辑，也可以像商业数据库一样保证数据一致性、可靠性。
但是问题来了，数据库中存储的业务逻辑代码，如何管理呢？
一种方法是在人工管理，在数据库中执行前、后保留代码到gitlab, github, svn等自建或公共的代码库中。
另一种方法是让数据库直接对接代码库，实时将函数代码提交到代码库。
我们看看PostgreSQL如何实现？
机制
如果要让数据库自动、实时的将修改或新建的函数代码内容提交到代码库，首先要有一个自动机制。
PostgreSQL 有两种机制可以实现：
1. 一种是事件触发器，在执行DDL时，自动触发，此时可以提取DDL内容，然后你想干什么就随你了，PostgreSQL可通过自定义函数操作GITHUB,GITLAB,SVN等。
2. 另一种方法是HOOK，是的，PostgreSQL提供了很多HOOK，允许用户通过钩子做一些旁路逻辑，比如我们在EXECUTE后，截获execute的内容并处理它。截获后PostgreSQL可通过自定义函数操作GITHUB,GITLAB,SVN等。
事件触发器
PostgreSQL的事件触发器指在发生某些DDL事件后，可以触发调用事件触发器函数，函数中我们可以处理很多东西。
1. 事件触发器语法
https://www.postgresql.org/docs/9.6/static/sql-createeventtrigger.html
CREATE EVENT TRIGGER name
ON event
[ WHEN filter_variable IN (filter_value [, ... ]) [ AND ... ] ]
EXECUTE PROCEDURE function_name();
解说
event：指事件，
ddl_command_start, ddl_command_end, table_rewrite and sql_drop。
https://www.postgresql.org/docs/9.6/static/event-trigger-definition.html
filter_variable：TAG
filter_value：指event对应的command tag，比如本文要用到的CREATE FUNCTION，详见如下
https://www.postgresql.org/docs/9.6/static/event-trigger-matrix.html
2. 事件触发器函数的语法
与语言有关，比如plpgsql语言写的事件触发器函数为
create or replace function function_name() returns event_trigger as $$
declare
...
begin
...
end;
$$ language plpgsql strict;
3. 事件触发器相关的系统函数调用
https://www.postgresql.org/docs/9.6/static/functions-event-triggers.html
3.1 pg_event_trigger_ddl_commands() 捕获命令结束时的信息，本文要用到objid字段，即函数的objectid，然后调用pg_get_functiondef(oid)得到函数的定义。
| Name | Type | Description |
|---|---|---|
| classid | Oid | OID of catalog the object belongs in |
| objid | Oid | OID of the object in the catalog |
| objsubid | integer | Object sub-id (e.g. attribute number for columns) |
| command_tag | text | command tag |
| object_type | text | Type of the object |
| schema_name | text | Name of the schema the object belongs in, if any; otherwise NULL. No quoting is applied. |
| object_identity | text | Text rendering of the object identity, schema-qualified. Each and every identifier present in the identity is quoted if necessary. |
| in_extension | bool | whether the command is part of an extension script |
| command | pg_ddl_command | A complete representation of the command, in internal format. This cannot be output directly, but it can be passed to other functions to obtain different pieces of information about the command. |
3.2 pg_event_trigger_dropped_objects() 捕获被DROP的对象
与本文无关，不列出
3.3 pg_event_trigger_table_rewrite_oid()和pg_event_trigger_table_rewrite_reason() 捕获table rewrite事件涉及的表和原因
与本文无关，不列出
4. 获取函数定义，pg_get_functiondef(oid)
postgres=# \df *.*def*
List of functions
Schema | Name | Result data type | Argument data types | Type
------------+-----------------------------+------------------+-----------------------+---------
pg_catalog | pg_get_function_arg_default | text | oid, integer | normal
pg_catalog | pg_get_functiondef | text | oid | normal
pg_catalog | pg_get_indexdef | text | oid | normal
pg_catalog | pg_get_indexdef | text | oid, integer, boolean | normal
pg_catalog | pg_get_ruledef | text | oid | normal
pg_catalog | pg_get_ruledef | text | oid, boolean | normal
pg_catalog | pg_get_triggerdef | text | oid | normal
pg_catalog | pg_get_triggerdef | text | oid, boolean | normal
pg_catalog | pg_get_viewdef | text | oid | normal
pg_catalog | pg_get_viewdef | text | oid, boolean | normal
pg_catalog | pg_get_viewdef | text | oid, integer | normal
pg_catalog | pg_get_viewdef | text | text | normal
pg_catalog | pg_get_viewdef | text | text, boolean | normal
有了这些要素，我们就可以利用UDF，实时的记录函数的内容，并提到版本管理库了。
HOOK（钩子）
在代码中，我们可以得到目前PG已经定义了哪些HOOK，允许你使用其进行旁路。
grep -i hook src/tools/pgindent/typedefs.list
ClientAuthentication_hook_type
CoerceParamHook
ExecutorCheckPerms_hook_type
ExecutorEnd_hook_type
ExecutorFinish_hook_type
ExecutorRun_hook_type
ExecutorStart_hook_type
ExplainOneQuery_hook_type
FmgrHookEventType
GucBoolAssignHook
GucBoolCheckHook
GucEnumAssignHook
GucEnumCheckHook
GucIntAssignHook
GucIntCheckHook
GucRealAssignHook
GucRealCheckHook
GucShowHook
GucStringAssignHook
GucStringCheckHook
PGNoticeHooks
ParamFetchHook
ParseParamRefHook
ParserSetupHook
PostParseColumnRefHook
PreParseColumnRefHook
ProcessUtility_hook_type
VariableAssignHook
check_password_hook_type
create_upper_paths_hook_type
emit_log_hook_type
explain_get_index_name_hook_type
fmgr_hook_type
get_attavgwidth_hook_type
get_index_stats_hook_type
get_relation_info_hook_type
get_relation_stats_hook_type
join_search_hook_type
needs_fmgr_hook_type
object_access_hook_type
planner_hook_type
post_parse_analyze_hook_type
row_security_policy_hook_type
set_join_pathlist_hook_type
set_rel_pathlist_hook_type
shmem_startup_hook_type
例子
这些插件使用到数据库的HOOK，比如用来统计SQL的资源开销，认证延迟等。
contrib/pg_stat_statements/pg_stat_statements.c
contrib/auto_explain/auto_explain.c
contrib/auth_delay/auth_delay.c
本文的CASE，你如果要将CREATE FUNCTION的内容，自动写入SVN，也能使用钩子完成，不再举例。
本地表存储函数代码，版本管理
我们除了可以将代码存入版本管理软件GITHUB、GITLAB、SVN等，还有一种简便的方法，比如存入数据库的表里面。
例子
1. 创建存储函数代码的表
create table svn_func(
id serial8 primary key, -- 序列
tx int8, -- 事务号
objid oid, -- 函数唯一标示 pg_proc.oid
object_type text, -- 类型
schema_name text, -- schema name
object_identity text, -- 全长对象名: schema_name.object_name
in_extension bool, -- 对象是否属于extension
crt_time timestamp, -- DDL时间
content text -- DDL翻译成文本
);
2. 创建事件触发器函数
create or replace function push_to_svn_func() returns event_trigger as $$
declare
r record;
begin
for r in SELECT * FROM pg_event_trigger_ddl_commands() LOOP
insert into svn_func(tx, objid, object_type, schema_name, object_identity, in_extension, crt_time, content)
values
(
txid_current(),
r.objid,
r.object_type,
r.schema_name,
r.object_identity,
r.in_extension,
now(),
pg_get_functiondef(r.objid)
);
end LOOP;
end;
$$ language plpgsql strict;
3. 创建事件触发器
create event trigger et1 on ddl_command_end when TAG in ('create function') execute procedure push_to_svn_func();
4. 测试
4.1 创建函数
create or replace function f123(id int) returns int as $$
declare
begin
return id+1;
end;
$$ language plpgsql strict;
CREATE FUNCTION
4.2 创建同名，但是参数不同的函数
create or replace function f123(id int, diff int) returns int as $$
declare
begin
return id+diff;
end;
$$ language plpgsql strict;
CREATE FUNCTION
4.3 创建完全相同的函数，写入不同的SCHEMA
postgres=# create schema test;
CREATE SCHEMA
postgres=# create or replace function test.f123(id int, diff int) returns int as $$
declare
begin
return id+diff;
end;
$$ language plpgsql strict;
CREATE FUNCTION
4.4 覆盖创建原有函数
postgres=# create or replace function test.f123(id int, diff int) returns int as $$
declare
begin
return id+diff;
end;
$$ language plpgsql strict;
CREATE FUNCTION
4.5 查看函数内容记录
postgres=# select * from svn_func;
-[ RECORD 1 ]---+----------------------------------------------------------------------------------------------------------------
id | 1
tx | 46056990
objid | 172533
object_type | function
schema_name | public
object_identity | public.push_to_svn_func()
in_extension | f
crt_time | 2017-03-05 13:37:25.518273
content | CREATE OR REPLACE FUNCTION public.push_to_svn_func() +
| RETURNS event_trigger +
| LANGUAGE plpgsql +
| STRICT +
| AS $function$ +
| declare +
| r record; +
| begin +
| for r in SELECT * FROM pg_event_trigger_ddl_commands() LOOP +
| insert into svn_func(tx, objid, object_type, schema_name, object_identity, in_extension, crt_time, content)+
| values +
| ( +
| txid_current(), +
| r.objid, +
| r.object_type, +
| r.schema_name, +
| r.object_identity, +
| r.in_extension, +
| now(), +
| pg_get_functiondef(r.objid) +
| ); +
| end LOOP; +
| end; +
| $function$ +
|
-[ RECORD 2 ]---+----------------------------------------------------------------------------------------------------------------
id | 2
tx | 46056991
objid | 172508
object_type | function
schema_name | public
object_identity | public.f123(integer)
in_extension | f
crt_time | 2017-03-05 13:37:50.630288
content | CREATE OR REPLACE FUNCTION public.f123(id integer) +
| RETURNS integer +
| LANGUAGE plpgsql +
| STRICT +
| AS $function$ +
| declare +
| begin +
| return id+1; +
| end; +
| $function$ +
|
-[ RECORD 3 ]---+----------------------------------------------------------------------------------------------------------------
id | 3
tx | 46056992
objid | 172573
object_type | function
schema_name | public
object_identity | public.f123(integer,integer)
in_extension | f
crt_time | 2017-03-05 13:38:38.068266
content | CREATE OR REPLACE FUNCTION public.f123(id integer, diff integer) +
| RETURNS integer +
| LANGUAGE plpgsql +
| STRICT +
| AS $function$ +
| declare +
| begin +
| return id+diff; +
| end; +
| $function$ +
|
-[ RECORD 4 ]---+----------------------------------------------------------------------------------------------------------------
id | 4
tx | 46056994
objid | 172575
object_type | function
schema_name | test
object_identity | test.f123(integer,integer)
in_extension | f
crt_time | 2017-03-05 13:39:06.332268
content | CREATE OR REPLACE FUNCTION test.f123(id integer, diff integer) +
| RETURNS integer +
| LANGUAGE plpgsql +
| STRICT +
| AS $function$ +
| declare +
| begin +
| return id+diff; +
| end; +
| $function$ +
|
-[ RECORD 5 ]---+----------------------------------------------------------------------------------------------------------------
id | 5
tx | 46056995
objid | 172575
object_type | function
schema_name | test
object_identity | test.f123(integer,integer)
in_extension | f
crt_time | 2017-03-05 13:39:14.66429
content | CREATE OR REPLACE FUNCTION test.f123(id integer, diff integer) +
| RETURNS integer +
| LANGUAGE plpgsql +
| STRICT +
| AS $function$ +
| declare +
| begin +
| return id+diff; +
| end; +
| $function$ +
|
4.6 回退测试
比如你想将某个函数，回退到以前的版本，在svn_func表中选定一条ID的content, 执行即可。
do language plpgsql $$
declare
sql text;
begin
select content into sql from svn_func where id=2;
execute sql;
end;
$$;
数据库直连代码库(github,gitlab,svn...)
前面的例子介绍了如何将函数版本存入表中，如果你想将函数内容存入代码管理库，也很简单，下面提供一些伪代码。
例子
1. 创建高级过程语言，通过他们编写的函数与代码管理库交互。
pljava u, plpython u, .....
2. 编写对应的pl函数，输入为content等, 写入代码管理库。
假设函数名为plpython_svn(content,其他参数);
3. 将第二步编写的函数，通过事件触发器调用。
4. 创建事件触发器函数
create or replace function push_to_svn_func() returns event_trigger as $$
declare
r record;
begin
for r in SELECT * FROM pg_event_trigger_ddl_commands() LOOP
insert into svn_func(tx, objid, object_type, schema_name, object_identity, in_extension, crt_time, content)
values
(
txid_current(),
r.objid,
r.object_type,
r.schema_name,
r.object_identity,
r.in_extension,
now(),
pg_get_functiondef(r.objid)
);
-- 调用plpython_svnc(....), 将内容提交到SVN
end LOOP;
end;
$$ language plpgsql strict;
5. 创建事件触发器
create event trigger et1 on ddl_command_end when TAG in ('create function') execute procedure push_to_svn_func();
小结
1. 通过事件触发器、UDF，我们可以将DDL的内容写入表中，也可以提交到代码管理库中。
2. 事件触发器其他用途，譬如我们使用逻辑复制，DDL不记录在REDO中，幸好可以通过事件触发器完成DDL复制。
参考
https://www.postgresql.org/docs/9.6/static/functions-event-triggers.html
https://www.postgresql.org/docs/9.6/static/event-trigger-definition.html
《PostgreSQL Oracle 兼容性之 - 事件触发器实现类似Oracle的回收站功能》
《PostgreSQL 事件触发器 - DDL审计 , DDL逻辑复制 , 打造DDL统一管理入》
《PostgreSQL 事件触发器 - PostgreSQL 9.3 Event Trigger》
《PostgreSQL 事件触发器 - DDL审计 , DDL逻辑复制 , 打造DDL统一管理入》
pgsql-http插件
https://github.com/pramsey/pgsql-http