holly's wiki

constraint_exclusionを有効にする。on or partitionで選択できるようだが、とりあえずon

constraint_exclusion = on; 

ログを保存するなどの想定で。

create table logs (
	id serial,
	content varchar(255),
	created timestamp default current_timestamp,
	updated timestamp default current_timestamp,
	primary key(id)
);

CREATE OR REPLACE FUNCTION logs_insert() RETURNS TRIGGER AS
  $$
     DECLARE
       part text; -- パーティション・テーブルの名前
     BEGIN
       -- キー値から計算 : logs_[年][月]
       part := 'logs_' || to_char(new.created, 'YYYYMM');
       -- new を渡す
       EXECUTE 'INSERT INTO ' || part || ' VALUES(($1).*)' USING new;
       RETURN NULL;
     END;
 $$
 LANGUAGE plpgsql;

CREATE TRIGGER logs_insert_trigger 
   BEFORE INSERT ON logs 
    FOR EACH ROW EXECUTE PROCEDURE logs_insert(); 

  CREATE TABLE logs_201106() INHERITS(logs);
  ALTER TABLE  logs_201106 ADD CONSTRAINT created_check CHECK(created >= '2011-06-01' AND created < '2011-07-01');
  CREATE INDEX logs_201106_created_idx ON logs_201106(created);

※8.3以降はCREATE TABLE LIKEが使用できるので、indexは親テーブルにあらかじめ登録しておくことが可能
  CREATE INDEX logs_created_idx ON logs(created);
  CREATE TABLE logs_201206 (
      LIKE
      logs INCLUDING INDEXES [INCLUDING DEFAULTS INCLUDING CONSTRAINTS],
      CHECK(created >= '2012-06-01' AND created < '2012-07-01')
  ) INHERITS(logs);


  CREATE TABLE logs_201207 (
      LIKE logs INCLUDING INDEXES,
      CHECK(created >= '2012-07-01' AND created < '2012-08-01')
  ) INHERITS(logs);

  CREATE TABLE logs_201208 (
      LIKE logs INCLUDING INDEXES,
      CHECK(created >= '2012-08-01' AND created < '2012-09-01')
  ) INHERITS(logs);

  CREATE TABLE logs_201109 (
      LIKE logs INCLUDING INDEXES,
      CHECK(created >= '2012-09-01' AND created < '2012-10-01')
  ) INHERITS(logs);

以下の警告がいろいろ出るが気にしなくてよい

NOTICE:  merging column "id" with inherited definition
NOTICE:  merging column "content" with inherited definition
NOTICE:  merging column "created" with inherited definition
NOTICE:  merging column "updated" with inherited definition

以下の場合だと実際はlogs_201206にinsertされる。アプリケーションからは月ごとにテーブルが分かれていることを意識する必要はない

insert into logs(content) values('hello');
 select * from logs where updated between '2012-06-01' and '2012-06-30';
 id | content |          created           |          updated
----+---------+----------------------------+----------------------------
  1 | aiueo   | 2012-06-17 15:51:03.120014 | 2012-06-17 15:51:03.120014
(1 row)

今回の例では月次で分けているので、過去の古いテーブルを削除などしたい場合は以下を実行する

drop table logs_201206;

元のテーブルごと消す場合は以下

drop table logs cascade;

上記手順でも十分可能だが、継承テーブルをあらかじめ作成などしているので、どこかのタイミングで継承テーブルを作成する必要がでてくる。以下はそれらの改良を加えたパターン

-- logsテーブル作成、createdにindexをはるところまでは同じ
-- CREATE TABLE logs....
-- CREATE INDEX logs_created_idx ON logs(created);

-- stored procedureに存在しない場合はtableを作成する処理を仕込んでおく
-- stored procedureに存在しない場合はtableを作成する処理を仕込んでおく
CREATE OR REPLACE FUNCTION logs_insert() RETURNS TRIGGER AS
  $$
     DECLARE
       logs text:= 'logs';
       part text; -- パーティション・テーブルの名前変数
       t_now timestamp;
       t_begin timestamp;
       t_end timestamp;
     BEGIN
       -- キー値から計算 : logs_[年][月]
       part := logs || '_' || to_char(new.created, 'YYYYMM');
       -- 存在しない場合はパーティショニングテーブル作成
       IF NOT EXISTS(select tablename from pg_catalog.pg_tables where schemaname = current_schema and tablename = part) THEN
           t_now   := NEW.created;
           t_begin := date_trunc('month', t_now);
           t_end   := t_begin + '1 month'::interval;
           EXECUTE 'create table ' || quote_ident(part) || 
                   '(like ' || logs || ' including indexes including defaults including constraints, ' ||
                   'check(created >= ' || quote_literal(t_begin) || ' and created < ' || quote_literal(t_end) || ')' || ') inherits(' || logs || ')';
       END IF;
       -- 登録する new を渡す
       EXECUTE 'insert into ' || part || ' values(($1).*)' USING NEW;
       RETURN NULL;
     END;
 $$
 LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS logs_insert_trigger_instance ON logs;
CREATE TRIGGER logs_insert_trigger_instance
  BEFORE INSERT ON logs
  FOR EACH ROW EXECUTE PROCEDURE logs_insert();