`
waiting
  • 浏览: 234622 次
  • 性别: Icon_minigender_1
  • 来自: cq
社区版块
存档分类
最新评论

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函数来迁移数据,但是遇上错误

42703:错误: 记录"new"没有字段"log_user" CONTEXT: 在EXECUTE 语句的第27行的PL/pgSQL函数"insert_log":

 

仔细回忆原来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解决问题:

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() ;

 

再看看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也挺好玩的

 

 

 

 

.

分享到:
评论

相关推荐

    pgsql创建自增ID,建表,创建索引,创建分区表.txt

    pgsql创建自增ID,建表,创建索引,创建分区表

    Python-实现轻松创建PostgreSQL的分区表DDL

    分区表通过将数据根据预定义的规则分布到不同的子表中,提高了查询效率,尤其对于大数据量的表来说效果显著。例如,如果一个订单表按照日期进行分区,那么查询某一年的数据时,只需要扫描对应年份的分区,而无需遍历...

    PostgreSQL简单函数创建

    fexceptions -fstack-protector --param=ssp-buffer-size=4 -m64 -mtune=generic -I/usr/include/pgsql/server -D_GNU_SOURCE -fPIC -DPIC -shared -o func1.so func1.c ``` 4. **安装函数**:使用`pg_ctl`或者在...

    PgSQl临时表创建及应用实例解析

    创建前可先删除 drop table tmp0 创建临时表 select * into temp table tmp0 from xxx create index idx_tmp0_inner_cd on tmp0(inner_cd); 查看临时表是否创建,返回0表示未创建,1为创建 select count(*) from ...

    PostgreSQL 创建表分区

    在这个例子中,我们创建了两个分区`users_0`和`users_1`,分别用于存储`uid`在0到99和100及以上的数据。 ```sql CREATE TABLE users_0 (check (uid &gt;= 0 AND uid )) INHERITS (users); CREATE TABLE users_1 ...

    PostgreSQL表分区和子表及删除所有的数据库表.zip

    PostgreSQL表分区和子表及删除所有的数据库表 最近需求要求统计DNS近7天每天的解析情况。数据量相对大,所以我这边对表进行分区。 对每天的数据进行分区存储。主表只存储近7天的数据,7天之前的数据删掉。所以我...

    SQL查询出表、存储过程、触发器的创建时间和最后修改时间示例

    在SQL数据库管理中,了解表、存储过程和触发器的创建时间和最后修改时间对于系统维护、版本追踪以及问题排查至关重要。下面将详细讲解如何通过SQL查询获取这些信息,并提供具体的代码示例。 首先,让我们来看如何...

    pgsql安装,绿色

    pgsql,全称PostgreSQL,是一种开源的对象关系型数据库管理系统(ORDBMS),以其高度的稳定性和强大的功能在IT行业中广受赞誉。对于“pgsql绿色版本安装”这个问题,我们可以理解为用户正在尝试安装一个免安装、便携...

    mybatis动态创建数据库表

    在“mybatis动态创建数据库表”这个主题中,我们将深入探讨如何利用MyBatis实现数据库表的动态创建、数据插入以及数据修改。 一、动态创建数据库表 MyBatis本身并不直接支持动态创建数据库表,但可以通过结合Spring...

    解密PGSQL数据库引擎

    4、PGSQL 数据库引擎的优化与维护4.1 索引优化:合理创建和使用索引可以显著提升查询速度,但过多的索引可能会降低插入和更新的速度。因此,索引设计应兼顾查询性能和写入性能。 4.2 参数调优:通过对PGSQL配置参数...

    PostgreSQL sql语句:创建用户,创建数据库,用户授权

    PostgreSQL sql语句:创建用户,创建数据库,用户授权

    pgsql连接工具,版本6.8

    【pgsql连接工具,版本6.8】是一款专用于管理和操作PostgreSQL数据库的客户端软件,它在数据库管理员和开发人员的工作中扮演着至关重要的角色。PostgreSQL,简称pgsql,是一种功能强大的开源关系型数据库管理系统,...

    PostgreSQL分区表(partitioning)应用实例详解

    官方给出的指导意见是:当表的大小超过了数据库服务器的物理内存大小则应当使用分区表,接下来结合一个例子具体记录一下创建分区表的详细过程。 创建分区表 首先看一下需求,现在有一张日志表,现在需要按表中的操作...

    pgsql中使用mybatis生成实体类

    在使用PostgreSQL(pgsql)数据库时,MyBatis是一个非常流行的持久层框架,它提供了将SQL查询映射到Java对象的灵活方式。本篇文章将详细探讨如何在pgsql环境中利用MyBatis来生成实体类,从而简化开发过程。 首先,...

    osm2pgsql(OSM数据导入到Postgres中工具)

    **osm2pgsql详解** osm2pgsql是一个用于将OpenStreetMap(OSM)数据导入到PostgreSQL数据库的工具,它是OSM数据处理生态中的重要组成部分。OSM是一种开源的地理信息系统,允许用户自由地创建、编辑和分享地理数据。...

    winform导出PGSQL表结构

    在.NET Framework 4.5.1环境下,使用C#开发WinForm应用程序来导出PostgreSQL(简称PGSQL)数据库的表结构信息至Excel文件是一项常见的数据处理任务。这个任务涉及了几个关键的技术点,包括数据库连接、数据查询、...

    postgis-shp2pgsql

    3. **创建空间表**:Shp2pgsql会自动检测Shapefile的字段类型,并在数据库中创建相应的表结构。如果需要,可以自定义表的字段名和数据类型。 4. **数据导入**:工具会将Shapefile的数据转化为SQL INSERT语句,通过...

    pgsql表修改

    PgSQL表修改详解 PostgreSQL中的ALTER TABLE语句是用来修改表结构的,包括添加、删除、修改列属性、修改表名、修改schema、添加、删除约束、触发器、规则等。 修改表名 ALTER TABLE name RENAME TO new_name; ...

Global site tag (gtag.js) - Google Analytics