pgsql 分区试验及创建规则失误导致创建触发器错误

鉴于mySQL前途不明决定转向PostgreSQL。初步了解pgsql后发现其相当的强大，于是考虑把一个小项目迁移到pgsql上。对其中日志表想玩玩pgsql的分区，最开始是参考pgsql手册以规则（RULE）的方式来实现每一年一个日志表

?

-- 禁止更新主表CREATE RULE no_insert_on_tb_log AS ON INSERT TO tb_log    DO INSTEAD NOTHING;DROP TABLE IF EXISTS tb_log_y2010 CASCADE;CREATE TABLE tb_log_y2010 (	FOREIGN KEY (cid) REFERENCES tb_user ON DELETE CASCADE ON UPDATE CASCADE,	CHECK ( log_time >= '2010-01-01' AND log_time <  '2010-12-31 24:0:0' )) INHERITS (tb_log)WITH (OIDS=FALSE);CREATE OR REPLACE RULE insert_log_y2010 AS ON INSERT TO tb_log	WHERE ( ctime >= '2010-01-01' AND ctime <  '2010-12-31 24:0:0' ) 	DO INSTEAD	INSERT INTO tb_log_y2010 (log_code, log_stat, uid, log_ip, log_msg, log_time?) VALUES 	( NEW.log_code, NEW.log_stat, NEW.uid, NEW.log_ip, NEW.log_msg, NEW.log_time ) ;

后来觉得每年需要生成新的日志表以及相应的规则，用shell脚本来做不熟悉。于是决定换成触发器的方式：

?

DROP TABLE IF EXISTS tb_log_y2010;CREATE TABLE tb_log_y2010 (	FOREIGN KEY (uid) REFERENCES tb_users ON DELETE CASCADE ON UPDATE CASCADE,	CHECK ( log_time >=  '2010-01-01' AND log_time < '2011-01-01' )) INHERITS (tb_log)WITH (OIDS=FALSE);INSERT INTO row_counts VALUES ('tb_log_y2010', 0);	CREATE TRIGGER tb_log_y2010_count AFTER INSERT OR DELETE ON tb_log_y2010     FOR EACH ROW EXECUTE PROCEDURE count_trig();CREATE OR REPLACE FUNCTION insert_log() RETURNS TRIGGER AS $$	-- 重定向对tb_log表写入到相应年份分区表，如果目标表不存在则由common.php中 write_log() 自动创建	DECLARE		tbname		TEXT :=  'tb_log_y' || to_char(current_timestamp, 'YYYY');	BEGIN		EXECUTE 'INSERT INTO ' || tbname  ||                        ' (log_code, log_stat, uid, log_ip, log_msg, log_time) ' ||			' VALUES ( ''' || NEW.log_code ||''','''|| NEW.log_stat ||''', '''|| NEW.uid ||                        ''','''|| NEW.log_ip ||''','''||			NEW.log_msg ||''','''|| NEW.log_time || ''' ) ';		RETURN NULL;	END;$$ LANGUAGE plpgsql;DROP TRIGGER IF EXISTS insert_log_trig ON tb_log CASCADE;CREATE TRIGGER insert_log_trig BEFORE INSERT ON tb_log 	    FOR EACH ROW EXECUTE PROCEDURE public.insert_log() ;

?

然后在PHP写日志的函数里面判断错误代码如果为 ‘42P01’ 则表示（该年）日志表不存在，于是就新建当年日志表并且重新写入出错时的SQL语句。

?

接下来就写PHP函数来迁移数据，但是遇上错误

?

仔细回忆原来log_user字段已经被uid字段替代，不过写的触发器、触发器函数以及日志表都已经更改了的啊。用Navicat Lite 查看触发器函数也没问题。后来用EMS查看就发现了问题，怎么函数定义完全不相同呢？

?

?

?

一点击Function name下拉列表发现具有有两个地方存在同样的名称：

?

?

?

再回忆，想起最开始用规则方式时曾经试图在日志的重写规则中判断table是否存在并新建：

?

CREATE OR REPLACE FUNCTION insert_log () RETURNS trigger AS $BODY$	DECLARE		curryear	TEXT := to_char(current_timestamp, 'YYYY');		yearbegin	TEXT;		yearend		TEXT;		tblname		TEXT ;		q			TEXT;	BEGIN		tblname := 'tb_log_y' || curryear ;		IF NOT EXISTS (SELECT relname FROM pg_class WHERE relname = tblname) THEN			yearbegin	:= curryear || '-01-01';			yearend		:= curryear || '-01-01 24:0:0';			q := $q$CREATE TABLE tblname (					FOREIGN KEY (log_user) REFERENCES tb_users ON DELETE CASCADE ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?      ON UPDATE CASCADE,					CHECK ( log_time >= '$q$ || yearbegin || $q$' AND log_time < '$q$ ||                                             ?yearend || $q$' )				) INHERITS (tb_log)				WITH (OIDS=FALSE);			EXECUTE q;		END IF;		EXECUTE 'INSERT INTO tb_log_y' ||  || ' (log_code, log_stat, log_user,log_ip, log_msg,? ? ? ? ? ? ? ? ? ? ? ? log_time) ' || ' VALUES ( ''' || NEW.log_code ||''','''|| NEW.log_stat ||''','''|| ? ? ? ? ? ? ? ? ? ? ? ? NEW.log_user || ''','''|| NEW.log_ip ||''','''|| NEW.log_msg ||''','''||? ? ? ? ? ? ? ? ? ? ? ? NEW.log_time ||''' ) ';		RETURN NULL;	END;$BODY$  LANGUAGE 'plpgsql' ;

不知怎么地就跑到pg_catalog里面去了，导致pg_catalog和pulic存在同名函数。前者是最开始的规则，后者是触发器函数，结果在创建insert_log_trig()触发器时就出了问题使用了pg_catalog中的过程函数。指定过程函数shema解决问题：

?

再看看pg_catalog里面那个的DDL，没明白是怎么创建到pg_catalog上面去的，因为创建所有对象时都没使用shema前缀

?

CREATE OR REPLACE FUNCTION "pg_catalog"."NewProc"()  RETURNS "pg_catalog"."trigger" AS $BODY$	DECLARE		curryear	TEXT := to_char(current_timestamp, 'YYYY');		yearbegin	TEXT;		yearend		TEXT;		tblname		TEXT ;		q			TEXT;	BEGIN		tblname := 'tb_log_y' || curryear ;		IF NOT EXISTS (SELECT relname FROM pg_class WHERE relname = tblname) THEN			yearbegin	:= curryear || '-01-01';			yearend		:= curryear || '-01-01 24:0:0';			q := $q$CREATE TABLE tblname (					FOREIGN KEY (log_user) REFERENCES tb_user ON DELETE CASCADE ON UPDATE CASCADE,					CHECK ( log_time >= '$q$ || yearbegin || $q$' AND log_time < '$q$ || yearend || $q$' )				) INHERITS (tb_log)				WITH (OIDS=FALSE);			EXECUTE q;		END IF;		EXECUTE 'INSERT INTO tb_log_y' ||  || ' (log_code, log_stat, log_user,log_ip, log_msg, log_time) ' ||			' VALUES ( ''' || NEW.log_code ||''','''|| NEW.log_stat ||''','''|| NEW.log_user ||			''','''|| NEW.log_ip ||''','''|| NEW.log_msg ||''','''|| NEW.log_time ||''' ) ';		RETURN NULL;	END;$BODY$  LANGUAGE 'plpgsql' ;

生命在于折腾。pgsql也挺好玩的

?

?

?

?

.