PostgreSQL Data Rotate Method Introduction: Overwrite History Data by Time

Image for post
Image for post

Background

Example

Method

create table test0(like test including all, constraint ck check(extract(dow from crt_time)=0)) inherits(test);  
create table test1(like test including all, constraint ck check(extract(dow from crt_time)=1)) inherits(test);
create table test2(like test including all, constraint ck check(extract(dow from crt_time)=2)) inherits(test);
create table test3(like test including all, constraint ck check(extract(dow from crt_time)=3)) inherits(test);
create table test4(like test including all, constraint ck check(extract(dow from crt_time)=4)) inherits(test);
create table test5(like test including all, constraint ck check(extract(dow from crt_time)=5)) inherits(test);
create table test6(like test including all, constraint ck check(extract(dow from crt_time)=6)) inherits(test);

create table test_def(like test including all) inherits(test);
postgres=#  explain select * from test where crt_time=now() and extract(dow from crt_time)=0; 
QUERY PLAN
-----------------------------------------------------------------------------------------------------
Append (cost=0.00..65.20 rows=3 width=44)
-> Seq Scan on test (cost=0.00..0.00 rows=1 width=44)
Filter: ((crt_time = now()) AND (date_part('dow'::text, crt_time) = '0'::double precision))
-> Seq Scan on test0 (cost=0.00..32.60 rows=1 width=44)
Filter: ((crt_time = now()) AND (date_part('dow'::text, crt_time) = '0'::double precision))
-> Seq Scan on test_def (cost=0.00..32.60 rows=1 width=44)
Filter: ((crt_time = now()) AND (date_part('dow'::text, crt_time) = '0'::double precision))
(7 rows)
create or replace function ins_tg() returns trigger as $$
declare
begin
case extract(dow from NEW.crt_time)
when 0 then insert into test0 values (NEW.*);
when 1 then insert into test1 values (NEW.*);
when 2 then insert into test2 values (NEW.*);
when 3 then insert into test3 values (NEW.*);
when 4 then insert into test4 values (NEW.*);
when 5 then insert into test5 values (NEW.*);
when 6 then insert into test6 values (NEW.*);
end case;
return null;
end;
$$ language plpgsql strict;
create trigger tg before insert on test for each row execute procedure ins_tg();insert into test values (1,'test',now()+interval '1 day');
insert into test values (1,'test',now()+interval '2 day');
insert into test values (1,'test',now()+interval '3 day');
insert into test values (1,'test',now()+interval '4 day');
insert into test values (1,'test',now()+interval '5 day');
insert into test values (1,'test',now()+interval '6 day');
insert into test values (1,'test',now()+interval '7 day');
postgres=# select tableoid::regclass , * from test;
tableoid | id | info | crt_time
----------+----+------+----------------------------
test0 | 1 | test | 2017-03-26 14:40:48.066905
test1 | 1 | test | 2017-03-27 14:40:50.450942
test2 | 1 | test | 2017-03-28 14:40:52.271922
test4 | 1 | test | 2017-03-23 14:40:22.551928
test5 | 1 | test | 2017-03-24 14:40:24.643933
test6 | 1 | test | 2017-03-25 14:40:28.138913
test3 | 1 | test | 2017-03-22 14:40:20.586945
(7 rows)
1. 开始事务  
begin;

2. 设置锁超时
set lock_timeout = '60s';

3. 查询明天的dow
select extract(dow from current_date+1);
date_part
-----------
3
(1 row)

4. 清除test_def约束
alter table test_def drop constraint IF EXISTS ck;
5. 清除test_def数据
truncate test_def;

6. 重命名test_def
alter table test_def rename to test_def_tmp;

7. 重命名明天的分区表
alter table test3 rename to test_def;

8. test_def_tmp添加约束(明天)
alter table test_def_tmp add constraint ck check(extract(dow from crt_time)=3);

9. test_def_tmp重命名为明天的分区
alter table test_def_tmp rename to test3;

10. 提交或回滚
commit;
如果失败,回滚事务。
Rollback;

Summary

Original Source:

Follow me to keep abreast with the latest technology news, industry insights, and developer trends.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store