设计模式但是 Postgres 之监听模式( 一 )

基于监听设计模式，想在数据库中做点实验加深自我理解

监听模式

监听模式是分为观察者和被观察者，被观察者的属性被改变后会有两种方式同步给观察者：
1、直接修改观察者的观察参数（缺乏独立性）
2、发生消息给观察者，在观察者需要的时候可以读取到消息

基于上述理解，构造一个多用户共享表的简单日志模型（不考虑线程安全问题）

实验开始

创建一个实验域，并创建两个额外的用户

  create schema listener;
  
  -- 创建两个用户作为额外的观察者和操作者
  create user listenerA password '123456';
  create user listenerB password '123456';
  
  -- 赋予用户权限, 测试使用，此处给了全部权限
  grant all privileges on schema listener to listenera, listenerb;
  grant all privileges on all tables in schema listener to listenera, listenerb;
  grant all privileges on all sequences in schema listener to listenera, listenerb;

建立一个共享表，可以多用户修改

  drop table listener.tb_public;
  create table listener.tb_public(
      id serial,
      "type" text unique ,
      val int,
      crt timestamp(0),
      udt timestamp(0) default current_timestamp
  );

建立一个公共表更新日志表

  -- 公共表修改日志 记录各用户更改记录
  drop table listener.update_log;
  create table listener.update_log(
      id serial,
      update_type text,
      val_type text,
      old_val int,
      new_val int,
      update_user varchar,
      crt timestamp(0) default current_timestamp
  );

创建触发器

建立触发器后，可实现用户对表中数据的操作，有日志记录

  -- 对每个DML类型 都建立触发器函数
  create function listener.func_insert_or_update_record()
  returns trigger
  language plpgsql
  as
      $$
          begin
              case tg_op
              when 'INSERT' then insert into listener.update_log(update_type, val_type, old_val, new_val, update_user) values ('INSERT', new.type, null, new.val, current_user);
              when 'UPDATE' then insert into listener.update_log(update_type, val_type, old_val, new_val, update_user) values ('UPDATE', new.type, old.val, new.val, current_user);
              end case;
              return new;
          end;
      $$;
  
  create function listener.func_delete_record()
  returns trigger
  language plpgsql
  as
      $$
          begin
              insert into listener.update_log(update_type, val_type, old_val, new_val, update_user) values ('DELETE', null, old.val, null, current_user);
              return new;
          end;
      $$;
  
  create function listener.func_truncate_record()
  returns trigger
  language plpgsql
  as
      $$
          begin
              insert into listener.update_log(update_type, val_type, old_val, new_val, update_user) values ('TRUNCATE', null, null, null, current_user);
              return new;
          end;
      $$;
  
  
  -- 建立触发器
  create trigger trigger_insert_or_update after insert or update of val on listener.tb_public for each row execute function listener.func_insert_or_update_record();
  create trigger trigger_delete after delete on listener.tb_public for each row execute function listener.func_delete_record();
  create trigger trigger_truncate after truncate on listener.tb_public for statement execute function listener.func_truncate_record();

思考

上述模式可以只开放共享表的读写权限，达到admin权限可以查看其他用户的修改记录
那是不是可以做一个简单的交易系统，总日志由中心保管？每个用户只能看到自己的账户日志

尝试

增加一个用户listenerC

创建账户表和用户视图

  -- 全账户表
  create table account.all_accounts(
      id serial,
      acc_owner name,
      acc_balance float8
  );
  
  -- 插入些数据
  insert into account.all_accounts(acc_owner, acc_balance) VALUES ('listenera'::name, 1000);
  insert into account.all_accounts(acc_owner, acc_balance) VALUES ('listenerb'::name, 2000);
  insert into account.all_accounts(acc_owner, acc_balance) VALUES ('listenerc'::name, 3000);
  insert into account.all_accounts(acc_owner, acc_balance) VALUES ('postgres'::name, 99999);
  
  -- 只能看见自己账号余额的视图
  create view account.my_account as
  select acc_owner, acc_balance
  from account.all_accounts
  where acc_owner in
  (select unnest(case when current_user = 'postgres'::name then (select array_agg(a.acc_owner) from account.all_accounts a) else (select array_agg(current_user)) end));
  
  -- 在赋予用户权限后，分别查看对应余额，显示正常
  grant usage on schema account to listenera, listenerb, listenerc;
  grant select on table account.my_account to listenera, listenerb, listenerc;

创建交易日志表和交易函数

  -- 交易申请表 （因为没有发消息机制，不可避免的用户可以直接修改此表）
  create table account.deal_apply(
      id serial8,
      payer name,
      payee name,
      amount float8,
      is_succ boolean,
      failed_reason text,
      crt timestamp
  );
  -- 交易总表 异步处理，由admin根据申请表完成交易日志
  create table account.deal_log(
      id serial8,
      apply_id bigint,
      payer name,
      payee name,
      amount float8,
      crt timestamp
  ) ;
  
  -- 交易函数
  create procedure account.send_money_apply(acc_payee name, acc_amount float8)
  language sql
  as
      $$
          insert into account.deal_apply(payer, payee, amount, crt) values (current_user, acc_payee, acc_amount, current_timestamp);
      $$;
      
  -- admin 处理交易申请
  -- 用户权限补充
  grant all privileges on table account.deal_apply to listenera, listenerb, listenerc;
  grant all privileges on sequence account.deal_apply_id_seq to listenera, listenerb, listenerc;
  

admin异步处理存储过程

  create procedure account.deal_apply_summary()
  language plpgsql
  as
      $$
          declare
              deal_los refcursor;
              log_record account.deal_apply%rowtype;
          begin
              open deal_los for select * from account.deal_apply where is_succ is null order by id;
              loop
                  fetch deal_los into log_record;
                  if log_record is null then exit; end if;
                  if not exists(select 1 from account.all_accounts where acc_owner = log_record.payer)
                  then update account.deal_apply set is_succ = false , failed_reason = '您还未拥有账号' where id = log_record.id;
                  elseif not exists(select 1 from account.all_accounts where acc_owner = log_record.payee)
                      then update account.deal_apply set is_succ = false , failed_reason = '收款方不存在' where id = log_record.id;
                  elseif log_record.amount > (select acc_balance from account.all_accounts where acc_owner = log_record.payer)
                      then update account.deal_apply set is_succ = false , failed_reason = '您的余额不足' where id = log_record.id;
                  else
                      update account.deal_apply set is_succ = true where id = log_record.id;
                      update account.all_accounts set acc_balance = acc_balance - log_record.amount where acc_owner = log_record.payer;
                      update account.all_accounts set acc_balance = acc_balance + log_record.amount where acc_owner = log_record.payee;
                      insert into account.deal_log(apply_id, payer, payee, amount, crt) values (log_record.id, log_record.payer, log_record.payee, log_record.amount, current_timestamp);
                  end if;
              end loop;
              close deal_los;
          end;
      $$;

为方便用户个人查看账号交易日志，增加视图

  create view account.my_deal_log
  as
      with t as (
          select id, payer, payee, amount, is_succ, failed_reason, crt from account.deal_apply where payer = current_user
          union all
          select id, payer, payee, amount, is_succ, failed_reason, crt  from account.deal_apply where payee = current_user and is_succ is true
      )
  select id, payer, payee, amount, is_succ, failed_reason, crt from t order by id asc;

有错误欢迎指出讨论