postgresql 使用整理：分表与触发器

测试海量数据库，正好使用存储过程测试，顺便整理一下。

分表

分表是postgresql储存海量数据的基础,分表比较合适存储记录型的数据，一般按时间进行分表。

先创建主表，使用字段id为主键, ptime创建索引。

CREATETABLE test

(

idbigint NOT NULL,

codecharacter varying(64) NOT NULL,

ptimetimestamp without time zone,

CONSTRAINTtest_pkey PRIMARY KEY (id )

);

CREATE INDEX IX_test_ptime

ONtest(ptime) ;

创建子表

createtable test_2012_02_01(

check(ptime>=date '2012-01-01' and ptime<date '2012-00-02'))

inherits(test);

ALTERTABLE test_2012_02_01 ADD

CONSTRAINTtest_2012_02_01_pkey PRIMARY KEY (id )

CREATE INDEX IX_test_2012_02_01 _ptime

ONtest(ptime) ;

分表其实是新数据库表，字段声明从主表继承。使用主表查询postgresql会关联查询分表，删除分表的记录也可以通过主表操作，但是插入数据必须指明使用那张表。另需要注意分表的主键与索引不会从主表继承,需要在分表上单独创建。

主表在实际使用中一般不用存储数据，数据存储在分表，如果是按时间的分别，数据按照时间老化，这时可以使用删除（drop）老分表来删除数据

触发器

上述分表策略有两个问题，插入数据需要需要指定表，需要提前创建分表。如按时间分表的情况下，一般要求做到统一向主表插入，按照时间计算出插入的分表，如分表不存在这自动创建分表。这样分表就对于业务进行屏蔽。

给test设定添加触发器

CREATETRIGGER tri_test_insert_id

BEFOREINSERT

ONtest

FOREACH ROW

EXECUTEPROCEDURE tri_test_insert();

创建触发函数实现，根据时间选择表插入数据，如果表不未定义异常未创建则创建表。

ps：一般使用pgsql存储过程使用RAISENOTICE 做过程打印跟踪代码走向。

CREATEOR REPLACE FUNCTION tri_test_insert()

RETURNSTRIGGER AS

$BODY$

declare

sqlvarchar;

tmp_datedate;

tablename varchar;

begin

tmp_date= DATE(NEW.Ptime);

tablename= 'test_' || to_char(tmp_date, 'YYYY_MM_DD');

sql= 'INSERT INTO '||tablename||'(id,code,ptime) VALUES('''||NEW.id||''','''|| NEW.code||''','''|| NEW.ptime || ''' )';

RAISENOTICE 'sql: %', sql;

EXECUTEsql;

returnnull;

exception

whenundefined_table then

sql= 'create table '||tablename||' (check (ptime>= ''' || tmp_date || ''' and ptime< ''' || tmp_date+1 || ''')) inherits(test)';

RAISENOTICE 'sql: %', sql;

EXECUTEsql;

sql='ALTERTABLE ' || tablename || ' ADD CONSTRAINT ' || tablename || '_pkey PRIMARY KEY (id)';

RAISENOTICE 'sql: %', sql;

EXECUTEsql;

sql='CREATE INDEX IX_'||tablename|| '_PTIME ON '||tablename|| '(ptime)';

RAISENOTICE 'sql: %', sql;

EXECUTEsql;

sql= 'INSERT INTO '||tablename||' VALUES('''||NEW.id||''','''||NEW.code||''','''|| NEW.ptime || ''' )';

RAISENOTICE 'sql: %', sql;

EXECUTEsql;

returnnull;

end;

$BODY$

LANGUAGEplpgsql VOLATILE;