×
Community Blog PostgreSQL Business Function Code Version Control – Back Up Stored Procedures and Function Codes to Apache Subversion and GitHub Automatically

PostgreSQL Business Function Code Version Control – Back Up Stored Procedures and Function Codes to Apache Subversion and GitHub Automatically

This article explains how to back up stored procedures and function codes to Apache Subversion and GitHub with examples.

By Digoal

Background

There is various code version control software in the market, such as GitLab, GitHub, and Apache Subversion (SVN).

Commercial databases feature strong programming skills, such as PL/SQL in Oracle Database. Many traditional enterprises put business logic with high consistency and reliability into databases, which results in data and part of business codes stored in databases.

PostgreSQL is the most advanced open-source database. It also supports the powerful functional language PL/pgSQL. Through language extension, it also supports programming languages, such as PL/Java, PL/Python, PL/Tcl, and PL/Perl. Moreover, business logic can be addressed through PostgreSQL database functions to ensure data consistency and reliability like commercial databases.

How can you manage the business logic codes stored in databases?

One method is to manage and keep the code manually before and after execution in the database to self-built or public code libraries, such as GitLab, GitHub, and SVN.

Another method is to connect the database directly to the code library, submitting function codes to the code library in real-time.

How does PostgreSQL implement this?

Mechanisms

An automatic mechanism is required if the database submits the modified content or new function codes automatically to the code library in real-time.

PostgreSQL provides two methods for implementation:

  1. One is the event trigger, which is triggered automatically when the data definition language (DDL) is executed. At this time, the DDL content can be extracted. Then, users can do whatever they want. PostgreSQL can operate code libraries, such as GitLab, GitHub, and SVN, through the user-defined function (UDF).
  2. Another method is the hook. Indeed, PostgreSQL provides many hooks, allowing users to do some bypass logic. For example, after applying the EXECUTE statement, the content is intercepted and processed. After the interception, PostgreSQL can operate code libraries, such as GitLab, GitHub, and SVN, through the UDF.

Event Trigger

PostgreSQL's event triggers refer to the event trigger functions that can be triggered and called after certain DDL events occur, in which many items can be processed.

1.  Syntax of Event Trigger

CREATE EVENT TRIGGER name  
    ON event  
    [ WHEN filter_variable IN (filter_value [, ... ]) [ AND ... ] ]  
    EXECUTE PROCEDURE function_name();  

Explanation – The event indicates an event.

ddl_command_start, ddl_command_end, table_rewrite and sql_drop

Definition of Event Trigger

Filter_variable indicates a TAG.

filter_value indicates the command tag corresponding to the event, such as the CREATE FUNCTION statement used in this article. Please see the following figure for details.

2.  Syntax of Event Trigger Functions

The syntax is related to languages. For example, the event trigger functions written in PL/pgSQL are listed below:

create or replace function function_name() returns event_trigger as $$  
declare  
  ...  
begin  
  ...  
end;  
$$ language plpgsql strict;  

3.  System Function Calls Related to Event-Based Triggers

3.1 Pg_event_trigger_ddl_commands() is used to capture the information at the end of the command. This article uses the ObjID field, which is the ObjectID of the function, and then calls pg_get_functiondef (oid) to obtain the definition of the function.

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 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() captures the object to be dropped.

This command is not related to this article and is not listed.

3.3 Pg_ event_trigger_table_rewrite_oid() and pg_event_trigger_table_rewrite_reason() capture the tables and causes involved in table rewrite events.

These two commands are not related to this article and are not listed.

4.  Use Pg_get_functiondef (oid) to obtain the definition of the function.

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  

With these elements, the UDF can record the content of the function in real-time and submit to version control libraries.

Hook

The hooks currently defined by PostgreSQL are listed below within the code, allowing users to use them for bypass.

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  

Example:

These plug-ins use hooks in the database to measure SQL resource overhead, authentication latency, and other aspects.

contrib/pg_stat_statements/pg_stat_statements.c  
  
contrib/auto_explain/auto_explain.c  
  
contrib/auth_delay/auth_delay.c  

For the case of this article, hooks can also be used if users want to write the content of the CREATE FUNCTION statement to SVN automatically. No more examples are provided here.

Local Tables Store Function Codes and Perform Version Control

In addition to storing code into version control software, such as GitHub, GitLab, and SVN, the code can be saved into a table in the database.

Example

1.  Create a Table That Stores Function Codes

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 an Event Trigger Function

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 an Event Trigger

create event trigger et1 on ddl_command_end  when TAG in ('create function') execute procedure push_to_svn_func();  

4.  Test

4.1 Create the Function

create or replace function f123(id int) returns int as $$                                                           
declare  
begin  
return id+1;  
end;  
$$ language plpgsql strict;  
CREATE FUNCTION  

4.2 Create Functions with the Same Name but Different Parameters

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 Create Identical Functions and Write Different Schemas

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 Overwrite the Original Function

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 View Function Content Records

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 Rollback Testing

For example, if users want to roll back the function to a previous version, specify the content of an ID in the svn_func table and run the function.

do language plpgsql $$  
declare  
  sql text;  
begin  
  select content into sql from svn_func where id=2;  
  execute sql;  
end;  
$$;  

Connect the Database Directly to the Code Library (GitHub, GitLab, and SVN)

The previous examples show how to save function versions to the table. It is also very simple to save function contents to code management libraries. The following shows some pseudocode.

Example

1.  Create high-level procedural languages to interact with code management libraries through functions written by these languages.

pljava u, plpython u, .....  

2.  Compile the corresponding PL function with input as content and write it into the code management libraries.

Let's imagine the function name is plpython_svn(content,parameters)

3.  Call the function written in step 2 through an event trigger

4.  Create the event trigger function

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 an event trigger

create event trigger et1 on ddl_command_end  when TAG in ('create function') execute procedure push_to_svn_func();  

Summary

  1. The DDL contents could be written in the table or submitted to code management libraries through event triggers and UDFs.
  2. There are also some other uses of the event trigger. For example, if DDL is not recorded in the redo log when using logical replication, the DDL replication can be done through event triggers.

References

0 0 0
Share on

digoal

210 posts | 13 followers

You may also like

Comments