开发者社区> 德哥> 正文

PostgreSQL Oracle 兼容性之 - ALTER TRIGGER ENABLE|DISABLE

简介:
+关注继续查看

标签

PostgreSQL , 触发器 , enable , disable


背景

Oracle 临时关闭或启用触发器的语法如下

https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_4001.htm

ALTER TRIGGER [ schema. ]trigger    
  { ENABLE    
  | DISABLE    
  | RENAME TO new_name    
  | COMPILE [ DEBUG ]    
      [ compiler_parameters_clause    
        [ compiler_parameters_clause ] ... ]    
      [ REUSE SETTINGS ]    
  } ;    

PostgreSQL 触发器开关语法更丰富一些

PostgreSQL中对应的语法在alter table中,因为触发器与表相关,这样设计语法也符合逻辑的。

https://www.postgresql.org/docs/devel/static/sql-altertable.html

ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ]    
    action [, ... ]    
    
    DISABLE TRIGGER [ trigger_name | ALL | USER ]    
    ENABLE TRIGGER [ trigger_name | ALL | USER ]    
    
    ENABLE REPLICA TRIGGER trigger_name    
    ENABLE ALWAYS TRIGGER trigger_name    

细节(注意触发器被触发还受制于session_replication_role参数与replica|always的配置。)

These forms configure the firing of trigger(s) belonging to the table. A disabled trigger is still known to the system, but is not executed when its triggering event occurs. For a deferred trigger, the enable status is checked when the event occurs, not when the trigger function is actually executed. One can disable or enable a single trigger specified by name, or all triggers on the table, or only user triggers (this option excludes internally generated constraint triggers such as those that are used to implement foreign key constraints or deferrable uniqueness and exclusion constraints). Disabling or enabling internally generated constraint triggers requires superuser privileges; it should be done with caution since of course the integrity of the constraint cannot be guaranteed if the triggers are not executed.

The trigger firing mechanism is also affected by the configuration variable session_replication_role. Simply enabled triggers (the default) will fire when the replication role is “origin” (the default) or “local”. Triggers configured as ENABLE REPLICA will only fire if the session is in “replica” mode, and triggers configured as ENABLE ALWAYS will fire regardless of the current replication role.

The effect of this mechanism is that in the default configuration, triggers do not fire on replicas. This is useful because if a trigger is used on the origin to propagate data between tables, then the replication system will also replicate the propagated data, and the trigger should not fire a second time on the replica, because that would lead to duplication. However, if a trigger is used for another purpose such as creating external alerts, then it might be appropriate to set it to ENABLE ALWAYS so that it is also fired on replicas.

This command acquires a SHARE ROW EXCLUSIVE lock.

trigger_name

Name of a single trigger to disable or enable.

ALL

Disable or enable all triggers belonging to the table. (This requires superuser privilege if any of the triggers are internally generated constraint triggers such as those that are used to implement foreign key constraints or deferrable uniqueness and exclusion constraints.)

USER

Disable or enable all triggers belonging to the table except for internally generated constraint triggers such as those that are used to implement foreign key constraints or deferrable uniqueness and exclusion constraints.

(内部用于foreign key, unique, 排他 等约束的触发器除外)

关闭或开启指定或所有表,或者某个用户,某个schema下的所有表的触发器。

使用函数,参考:

https://stackoverflow.com/questions/3942258/how-do-i-temporarily-disable-triggers-in-postgresql

For example if you have tables in a particular namespace it could be:

create or replace function disable_triggers(a boolean, nsp character varying) returns void as
$$
declare 
act character varying;
r record;
begin
    if(a is true) then
        act = 'disable';
    else
        act = 'enable';
    end if;

    for r in select c.relname from pg_namespace n
        join pg_class c on c.relnamespace = n.oid and c.relhastriggers = true
        where n.nspname = nsp
    loop
        execute format('alter table %I %s trigger all', r.relname, act); 
    end loop;
end;
$$
language plpgsql;
If you want to disable all triggers with certain trigger function it could be:

create or replace function disable_trigger_func(a boolean, f character varying) returns void as
$$
declare 
act character varying;
r record;
begin
    if(a is true) then
        act = 'disable';
    else
        act = 'enable';
    end if;

    for r in select c.relname from pg_proc p 
        join pg_trigger t on t.tgfoid = p.oid
        join pg_class c on c.oid = t.tgrelid
        where p.proname = f
    loop
        execute format('alter table %I %s trigger all', r.relname, act); 
    end loop;
end;
$$
language plpgsql;

also dosent work for me in Postgres 9.1. i use the two function described by bartolo-otrit with some modification. I modified the first function to make it work for me because the namespace or the schema must be present to identify the table correctly. The new code is :

CREATE OR REPLACE FUNCTION disable_triggers(a boolean, nsp character varying)
  RETURNS void AS
$BODY$
declare 
act character varying;
r record;
begin
    if(a is true) then
        act = 'disable';
    else
        act = 'enable';
    end if;

    for r in select c.relname from pg_namespace n
        join pg_class c on c.relnamespace = n.oid and c.relhastriggers = true
        where n.nspname = nsp
    loop
        execute format('alter table %I.%I %s trigger all', nsp,r.relname, act); 
    end loop;
end;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION disable_triggers(boolean, character varying)
  OWNER TO postgres;
then i simply do a select query for every schema :

SELECT disable_triggers(true,'public');
SELECT disable_triggers(true,'Adempiere');

参考

https://www.postgresql.org/docs/devel/static/sql-altertable.html

https://www.postgresql.org/docs/devel/static/runtime-config-client.html#GUC-SESSION-REPLICATION-ROLE

https://stackoverflow.com/questions/3942258/how-do-i-temporarily-disable-triggers-in-postgresql

《PostgreSQL rotate table 自动清理调度 - 约束,触发器》

《PostgreSQL 事件触发器应用 - DDL审计记录 + 异步通知(notify)》

《数据入库实时转换 - trigger , rule》

《(流式、lambda、触发器)实时处理大比拼 - 物联网(IoT)\金融,时序处理最佳实践》

《快速入门PostgreSQL应用开发与管理 - 7 函数、存储过程和触发器》

《PostgreSQL 10.0 preview 功能增强 - 触发器函数内置中间表》

《PostgreSQL 安全陷阱 - 利用触发器或规则,结合security invoker函数制造反噬陷阱》

《use PostgreSQL trigger manage stock & offer infomation》

《PostgreSQL trigger/rule based replication configure, DISABLE/ENABLE [ REPLICA | ALWAYS ] TRIGGER | RULE》

《PostgreSQL Oracle 兼容性之 - 事件触发器实现类似Oracle的回收站功能》

《PostgreSQL 触发器应用 - use trigger audit record which column modified, insert, delete.》

《use event trigger function record user who alter table's SQL》

《PostgreSQL 事件触发器 - DDL审计 , DDL逻辑复制 , 打造DDL统一管理入》

《PostgreSQL 触发器应用 - (触发器WHEN)前置条件过滤跟踪目标记录》

《PostgreSQL 闪回 - flash back query emulate by trigger》

《PostgreSQL 事件触发器 - PostgreSQL 9.3 Event Trigger》

《表级复制(base on trigger) -- PostgreSQL general sync and async multi-master replication trigger function》

《PostgreSQL 触发器 用法详解 2》

《PostgreSQL 触发器 用法详解 1》

《递归优化CASE - performance tuning case :use cursor\trigger\recursive replace (group by and order by) REDUCE needed blockes scan》

《PostgreSQL general public partition table trigger》

《表级复制(base on trigger) -- multi master replication & performance tuning》

《表级复制(base on trigger) -- one(rw) to many(ro|rw)》

《PostgreSQL 跟踪DDL时间 - cann't use pg_class's trigger trace user_table's create,modify,delete Time》

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
阿里云服务器怎么设置密码?怎么停机?怎么重启服务器?
如果在创建实例时没有设置密码,或者密码丢失,您可以在控制台上重新设置实例的登录密码。本文仅描述如何在 ECS 管理控制台上修改实例登录密码。
19672 0
阿里云服务器如何登录?阿里云服务器的三种登录方法
购买阿里云ECS云服务器后如何登录?场景不同,阿里云优惠总结大概有三种登录方式: 登录到ECS云服务器控制台 在ECS云服务器控制台用户可以更改密码、更换系.
24771 0
解读PostgreSQL Oracle 兼容性之 - performance insight(性能洞察)
标签 PostgreSQL , perf insight , 等待事件 , 采样 , 发现问题 , Oracle 兼容性 背景 通常普通的监控会包括系统资源的监控: cpu io 内存 网络 等,但是仅凭资源的监控,当问题发生时,如何快速的定位到问题在哪里?需要更高级的监控: 更高级的监控方法通常是从数据库本身的
503 0
ArcGIS 客户端跨版本连接Oracle 地理数据库时的兼容性说明
很多用户都会有这样的疑问: 1:我是否可以使用ArcGIS Desktop9.3连接ArcSDE10 2:我是否可以使用ArcGIS 10.
988 0
+关注
德哥
公益是一辈子的事, I'm digoal, just do it.
2153
文章
245
问答
来源圈子
更多
阿里云数据库:帮用户承担一切数据库风险,给您何止是安心!支持关系型数据库:MySQL、SQL Server、PostgreSQL、PPAS(完美兼容Oracle)、自研PB级数据存储的分布式数据库Petadata、自研金融级云数据库OceanBase支持NoSQL数据库:MongoDB、Redis、Memcache更有褚霸、丁奇、德哥、彭立勋、玄惭、叶翔等顶尖数据库专家服务。
+ 订阅
相关文档: 云数据库 OceanBase 版 可信账本数据库 云原生关系型数据库 PolarDB PostgreSQL引擎
文章排行榜
最热
最新
相关电子书
更多
OceanBase 入门到实战教程
立即下载
阿里云图数据库GDB,加速开启“图智”未来.ppt
立即下载
实时数仓Hologres技术实战一本通2.0版(下)
立即下载