By Digoal
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?
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:
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.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.
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_dropFilter_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.
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.
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.
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;
$$; 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.
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(); Insert and Update Time of PostgreSQL Trace Record (Row and Tuple) – SPI Moddatetime Trigger
digoal - June 28, 2021
Alibaba Clouder - August 16, 2019
ApsaraDB - September 19, 2022
Michael Peng - September 24, 2019
ApsaraDB - June 15, 2023
Alibaba Cloud Native Community - December 1, 2021
PolarDB for PostgreSQL
Alibaba Cloud PolarDB for PostgreSQL is an in-house relational database service 100% compatible with PostgreSQL and highly compatible with the Oracle syntax.
Learn More
AnalyticDB for PostgreSQL
An online MPP warehousing service based on the Greenplum Database open source program
Learn More
Fraud Detection
A risk management solution that features real-time analysis and accurate identification and is suitable for all business scenarios.
Learn More
ApsaraDB RDS for PostgreSQL
An on-demand database hosting service for PostgreSQL with automated monitoring, backup and disaster recovery capabilities
Learn MoreMore Posts by digoal
