PgAUT插件的原理

简介: 在PostgreSQL中实现MySQL的on update current_timestamp功能

在MySQL中,如果一个列的类型是TIMESTAMPDATETIME,并且设置了on update current_timestamp,则该字段的值会随着update命令更新行记录时自动更新成当前时间。这个特性在应用开发中非常有用,方便记录每一行最后的更新时间。

但这个特性不是标准SQL语法,数据库迁移到PostgreSQL后不得不在每条update语句里添加set updated_at = current_timestamp,难免会有遗漏。因此,我开发了PgAUT插件,提供类似MySQL的功能。

一、标记自动更新字段

插件的原理是在创建表的时候,自动创建一个与之相应的trigger,在更新语句执行之前把需要自动更新的字段的值统一赋值为clock_timestamp()

为了能标记出哪些字段是需要自动更新,使用“域”为timestamp类型创建了一个特殊的别名,后续用这个别名作为需要自动更新字段的类型:

create domain auto_update_timestamp as timestamp;

-- 创建表
create table foo (
  id bigint primary key,
  c1 auto_update_timestamp default current_timestamp
);
-- 修改表
alter table foo add column c2 auto_update_timestamp;

二、创建触发器(Trigger)

知道了哪些列需要自动更新后,可以给表绑定一个触发器,在更新的时候自动更新值:

create or replace foo_on_update_handler() returns trigger as 
$$

begin
  new.c1 = clock_timestamp();
  new.c2 = clock_timestamp();
  return new;
end;

$$
 language plpgsql;

create trigger foo_on_update_trigger
 before update on foo
 for each row execute
 procedure foo_on_update_handler();

三、事件触发器(Event Trigger)

有别与普通的触发器,事件触发器能捕捉所有DDL的变动,例如CREATE TABLEALTER TABLEDROP TABLE等。利用这个特性,就能实现在创建表的时候,自动创建与之相应的触发器:

create or replace function table_event_trigger_handler()
 returns event_trigger as 
$$

declare
  _e record;
  _sql text;
  _schema_name text;
  _table_name text;
begin
  -- 通过 pg_event_trigger_ddl_commands 获取当前变更的元素信息
  for _e in select * from pg_event_trigger_ddl_commands() loop
    if _e.object_type = 'table' and _e.command_tag = 'CREATE TABLE' then
      -- 从 pg_event_trigger_ddl_commands 无法拿到表的名称
      -- 只能手工从 pg_class 中获取 schema 和 table 的名称
      select
        pg_namespace.nspname,
        pg_class.relname
      into
        _schema_name,
        _table_name
      from
        pg_class
      inner join
        pg_namespace
      on
        pg_class.relnamespace = pg_namespace.oid
      where
        pg_class.oid = _e.objid;

      -- 从列元信息表中获得变更表的所有类型为 auto_update_timestamp 的列名
      -- 拼装成一组 new.<column-name> := clock_timestamp();
      select
        string_agg('  new.' || column_name || ' := clock_timestamp();', E'\n')
      into
        _sql
      from
        information_schema.columns
      where
        table_schema = _schema_name
        and table_name = _table_name
        and domain_name = 'auto_update_timestamp';

      -- 执行动态语句,创建触发器函数
      execute format($SQL$
create or replace function %s.%s_on_update_handler() returns trigger as $HANDLER$
begin
  %s
  return new;
end;
$HANDLER$ language plpgsql;
$SQL$, _schema_name, _table_name, _sql);

      -- 执行动态语句,创建触发器
      execute format($SQL$
create trigger %s_%s_on_update_trigger
 before update on %s.%s
 for each row execute
 procedure %s.%s_on_update_handler()
$SQL$, _schema_name, _table_name
     , _schema_name, _table_name
     , _schema_name, _table_name);

    end if;
  end loop;
end;

$$
 language plpgsql;

-- 创建事件触发器,处理CREATE TABLE事件
create event trigger table_event_trigger
  on ddl_command_end
  when tag in ('CREATE TABLE')
  execute procedure table_event_trigger_handler();

四、补齐其他事件触发器

上述代码实现了CREATE TABLE时自动创建触发器函数与触发器,并绑定到新建的表上,还需要创建ALTER TABLE处理添加或删除表字段时更新触发器函数,以及DROP TABLE处理时删除触发器函数。

细节可以参考插件的源码:https://github.com/redraiment/pgaut/blob/master/pgaut--1.0.0.sql

目录
相关文章
|
监控 应用服务中间件 Shell
pinpoint插件开发之一:牛刀小试,调整gson插件
pinpoint自定义插件开发起步,先拿已有插件做微调,把基本套路摸清楚
470 0
pinpoint插件开发之一:牛刀小试,调整gson插件
|
13天前
|
存储 数据库 数据安全/隐私保护
Duplicator插件的主要功能是什么?
【6月更文挑战第4天】Duplicator插件的主要功能是什么?
8 1
|
7月前
|
前端开发 API 开发者
热更新原理
热更新原理
111 0
|
8月前
|
API 开发者
🚀两个简单的自定义插件,探究Vite的插件机制
🚀两个简单的自定义插件,探究Vite的插件机制
|
12月前
|
存储 IDE Java
c++插件化 NDD源码的插件机制实现解析
c++插件化 NDD源码的插件机制实现解析
|
12月前
|
设计模式 存储 开发框架
C++ 插件机制的实现原理、过程、及使用
C++ 插件机制的实现原理、过程、及使用
|
前端开发 PHP
Yii2如何开发插件?底层原理是什么?
Yii2如何开发插件?底层原理是什么?
190 0
|
开发者
Google浏览器中扩展插件方法
Google浏览器中扩展插件方法
304 0
Google浏览器中扩展插件方法
|
数据库
插件配置设计
插件配置设计
83 0
|
Web App开发 前端开发 JavaScript
从面试题到插件机制的小思考
从面试题到插件机制的小思考
74 0
从面试题到插件机制的小思考