拍拍网站源码,手机网站app,修改wordpress文件夹后,成都网站推广经理前言
最近有客户反馈兼容的dba_triggers视图中#xff0c;同一个触发器的trigger_event被拆成了多行#xff0c;和ORACLE中表现不一致#xff0c;于是我进行了一些分析#xff0c;发现是在其引用的information_schema.triggers视图中就已经拆开成了INSERT/DELETE/UPDATE同一个触发器的trigger_event被拆成了多行和ORACLE中表现不一致于是我进行了一些分析发现是在其引用的information_schema.triggers视图中就已经拆开成了INSERT/DELETE/UPDATE但是这些属性都是通过tgtype这一个int2整型的字段获取的甚至连before/after/instead of/row/statement 等都是通过这一个字段。一个值存多种信息这在ORACLE的数据字典视图里很常见无非就是按二进制位来判断于是我尝试自己猜一猜看能不能从这个数字中识别出规律。
测试和分析
先建一堆测试触发器
create schema test1;
create schema test2;CREATE TABLE test1.test_trigger_src_tbl(id1 INT, id2 INT, id3 INT);CREATE OR REPLACE FUNCTION test1.tri_test_func() RETURNS TRIGGER AS$$DECLAREBEGININSERT INTO test_trigger_des_tbl VALUES(NEW.id1, NEW.id2, NEW.id3);RETURN NEW;END$$ LANGUAGE PLPGSQL;--before insert/update row
CREATE TRIGGER test_triggerBEFORE insert or update ON test1.test_trigger_src_tblFOR EACH ROWEXECUTE PROCEDURE test1.tri_test_func();CREATE TABLE test2.test_trigger_src_tbl(id1 INT, id2 INT, id3 INT);CREATE OR REPLACE FUNCTION test2.tri_test_func() RETURNS TRIGGER AS$$DECLAREBEGININSERT INTO test_trigger_des_tbl VALUES(NEW.id1, NEW.id2, NEW.id3);RETURN NEW;END$$ LANGUAGE PLPGSQL;--不同schema下的同名触发器
CREATE TRIGGER test_triggerBEFORE insert or update ON test2.test_trigger_src_tblFOR EACH ROWEXECUTE PROCEDURE test2.tri_test_func();CREATE OR REPLACE FUNCTION test1.tri_test_func1() RETURNS TRIGGER AS$$DECLAREBEGININSERT INTO test_trigger_des_tbl VALUES(NEW.id1, NEW.id2, NEW.id3);RETURN NEW;END$$ LANGUAGE PLPGSQL;-- before insert row
CREATE TRIGGER test_trigger1BEFORE insert ON test1.test_trigger_src_tblFOR EACH ROWEXECUTE PROCEDURE test1.tri_test_func1();CREATE OR REPLACE FUNCTION test1.tri_test_func2() RETURNS TRIGGER AS$$DECLAREBEGININSERT INTO test_trigger_des_tbl VALUES(NEW.id1, NEW.id2, NEW.id3);RETURN NEW;END$$ LANGUAGE PLPGSQL;--before insert/delete row
CREATE TRIGGER test_trigger2BEFORE insert or DELETE ON test1.test_trigger_src_tblFOR EACH ROWEXECUTE PROCEDURE test1.tri_test_func2();CREATE OR REPLACE FUNCTION test1.tri_test_func3() RETURNS TRIGGER AS$$DECLAREBEGININSERT INTO test_trigger_des_tbl VALUES(NEW.id1, NEW.id2, NEW.id3);RETURN NEW;END$$ LANGUAGE PLPGSQL;--after insert/delete/update row
CREATE TRIGGER test_trigger3AFTER insert or delete or UPDATE ON test1.test_trigger_src_tblFOR EACH ROWEXECUTE PROCEDURE test1.tri_test_func3();CREATE OR REPLACE FUNCTION test1.tri_test_func4() RETURNS TRIGGER AS$$DECLAREBEGININSERT INTO test_trigger_des_tbl VALUES(NEW.id1, NEW.id2, NEW.id3);RETURN NEW;END$$ LANGUAGE PLPGSQL;create view test1.test_trigger_src_tbl_V as select * from test1.test_trigger_src_tbl;--instead delete row
CREATE TRIGGER test_trigger4instead OF DELETE ON test1.test_trigger_src_tbl_VFOR EACH ROWEXECUTE PROCEDURE test1.tri_test_func4();CREATE OR REPLACE FUNCTION test1.tri_test_func5() RETURNS TRIGGER AS$$DECLAREBEGININSERT INTO test_trigger_des_tbl VALUES(NEW.id1, NEW.id2, NEW.id3);RETURN NEW;END$$ LANGUAGE PLPGSQL;--before truncate statement
CREATE TRIGGER test_trigger5before truncate ON test1.test_trigger_src_tblFOR EACH STATEMENT EXECUTE PROCEDURE test1.tri_test_func5();CREATE OR REPLACE FUNCTION test1.tri_test_func6() RETURNS TRIGGER AS$$DECLAREBEGININSERT INTO test_trigger_des_tbl VALUES(NEW.id1, NEW.id2, NEW.id3);RETURN NEW;END$$ LANGUAGE PLPGSQL;--after delete row
CREATE TRIGGER test_trigger6AFTER delete ON test1.test_trigger_src_tblFOR EACH ROWEXECUTE PROCEDURE test1.tri_test_func6();CREATE OR REPLACE FUNCTION test1.tri_test_func7() RETURNS TRIGGER AS$$DECLAREBEGININSERT INTO test_trigger_des_tbl VALUES(NEW.id1, NEW.id2, NEW.id3);RETURN NEW;END$$ LANGUAGE PLPGSQL;--after truncate statement
CREATE TRIGGER test_trigger7AFTER truncate ON test1.test_trigger_src_tblFOR EACH STATEMENT EXECUTE PROCEDURE test1.tri_test_func7();CREATE OR REPLACE FUNCTION test1.tri_test_func8() RETURNS TRIGGER AS$$DECLAREBEGININSERT INTO test_trigger_des_tbl VALUES(NEW.id1, NEW.id2, NEW.id3);RETURN NEW;END$$ LANGUAGE PLPGSQL;--after update/delete row
CREATE TRIGGER test_trigger8AFTER update or delete ON test1.test_trigger_src_tblFOR EACH ROWEXECUTE PROCEDURE test1.tri_test_func8();然后查询pg_trigger表并将tgtype转换成二进制数值显示注意这里int2无法直接转换成bit类型)
select tgname,tgtype,tgtype::int4::bit(8) from pg_trigger;tgnametgtypetgtype实际类型test_trigger2300010111before insert update rowtest_trigger2300010111before insert updatetest_trigger1700000111before insert rowtest_trigger21500001111before insert/delete rowtest_trigger32900011101after insert/delete/update rowtest_trigger47301001001instead delete rowtest_trigger53400100010before truncate statementtest_trigger6900001001after delete rowtest_trigger73200100000after truncate statementtest_trigger82500011001after update/delete row
得到这个信息小学二年级都能看出规律。我们在表格中转置一下看看
tgnametest_triggertest_triggertest_trigger1test_trigger2test_trigger3test_trigger4test_trigger5test_trigger6test_trigger7test_trigger8tgtype2323715297334932257000000000060000010000500000010104110010000130001110101211111000001111100100001111110101实际类型before insert update rowbefore insert updatebefore insert rowbefore insert/delete rowafter insert/delete/update rowinstead delete rowbefore truncate statementafter delete rowafter truncate statementafter update/delete row
然后很容易就能对比得到每个二进制位所表示的含义
第几位含义7无用位6是否insead of5是否truncate4是否update3是否delete2是否insert1是否before不是before就是after)0是否row不是row就是statement
以上都是纯用SQL查询比较猜出来的虽然过程也比较简单但还是有点费时间。但实际上如果能看懂C语言源码一眼就能知道应该怎么去解析tgtype openGauss-server\src\include\catalog\pg_trigger.h
/* Bits within tgtype */
#define TRIGGER_TYPE_ROW (1 0)
#define TRIGGER_TYPE_BEFORE (1 1)
#define TRIGGER_TYPE_INSERT (1 2)
#define TRIGGER_TYPE_DELETE (1 3)
#define TRIGGER_TYPE_UPDATE (1 4)
#define TRIGGER_TYPE_TRUNCATE (1 5)
#define TRIGGER_TYPE_INSTEAD (1 6)另外为什么明明有INSERT/DELETE/UPDATE/TRUNCATE四种但information_schema.triggers里为什么没有truncate的呢其实在视图里明确有写 -- hard-wired refs to TRIGGER_TYPE_INSERT, TRIGGER_TYPE_DELETE,-- TRIGGER_TYPE_UPDATE; we intentionally omit TRIGGER_TYPE_TRUNCATE(VALUES (4, INSERT),(8, DELETE),(16, UPDATE)) AS em (num, text)我们故意省略TRIGGER_TYPE_TRUNCATE 至于原作者为什么故意省略这个就猜不到了。 知道以上规则后我们可以尝试自己写一个dba_trigger视图这里对event提供两种写法
case when tgtype320 then TRUNCATEwhen tgtype60 and tgtype160 and tgtype80 then INSERT OR UPDATE OR DELETEwhen tgtype60 and tgtype160 and tgtype80 then INSERT OR UPDATEwhen tgtype60 and tgtype160 and tgtype80 then INSERTwhen tgtype60 and tgtype160 and tgtype80 then UPDATE OR DELETEwhen tgtype60 and tgtype160 and tgtype80 then UPDATEwhen tgtype60 and tgtype160 and tgtype80 then DELETEwhen tgtype60 and tgtype160 and tgtype80 then INSERT OR DELETE
endcase substring(tgtype::int4::bit(8) from 3 for 5)when B01110 then INSERT OR UPDATE OR DELETEwhen B01011 then INSERT OR UPDATEwhen B00011 then INSERTwhen B01100 then UPDATE OR DELETEwhen B00100 then DELETEwhen B00111 then INSERT OR DELETEwhen B10001 then TRUNCATE
end视图代码地址
https://gitee.com/enmotech/cmpat-tools/blob/master/Oracle_Views.sql
结尾
开头有提到在ORACLE中也经常这样处理属性值比如以下就是一个oracle的user_triggers视图的一段节选用这一个property字段表示了很多种属性
decode(bitand(t.property, 8192),8192, decode(bitand(t.property, 131072),131072, REVERSE, FORWARD), NO),
decode(bitand(t.property, 16384),16384, YES, NO),
decode(bitand(t.property, 32768),32768, YES, NO),好的设计都是相通的虽然这种方式不利于直接用SQL从数据字典基表中查询明确的属性值但是能节省很多存储空间并且在内存中直接判断二进制会更快比字符串更快。 本文作者 DarkAthena本文链接 https://www.darkathena.top/archives/opengauss-pgtrigger-tgtype-decode版权声明 本博客所有文章除特别声明外均采用CC BY-NC-SA 3.0 许可协议。转载请注明出处
