Automatic Cleaning and Scheduling of PostgreSQL Rotate Tables — Constraints and Triggers

Image for post
Image for post

Background

Method

Example

create table t1_0 (id int, info text, crt_time timestamp, check (substring(to_char(crt_time,'yyyymmddhh24miss'), 11, 1)='0'));    
create table t1_1 (id int, info text, crt_time timestamp, check (substring(to_char(crt_time,'yyyymmddhh24miss'), 11, 1)='1'));
create table t1_2 (id int, info text, crt_time timestamp, check (substring(to_char(crt_time,'yyyymmddhh24miss'), 11, 1)='2'));
create table t1_3 (id int, info text, crt_time timestamp, check (substring(to_char(crt_time,'yyyymmddhh24miss'), 11, 1)='3'));
create table t1_4 (id int, info text, crt_time timestamp, check (substring(to_char(crt_time,'yyyymmddhh24miss'), 11, 1)='4'));
create table t1_5 (id int, info text, crt_time timestamp, check (substring(to_char(crt_time,'yyyymmddhh24miss'), 11, 1)='5'));
create or replace function tg() returns trigger as $$  
declare
begin
insert into t_const values (to_timestamp(substring(to_char(NEW.crt_time,'yyyymmddhh24miss'), 1, 11)||'000','yyyymmddhh24miss')) on conflict (crt_time) do nothing;
return null;
end;
$$ language plpgsql strict;

create trigger tg1 after insert on t1_0 for each row execute procedure tg();
create trigger tg1 after insert on t1_1 for each row execute procedure tg();
create trigger tg1 after insert on t1_2 for each row execute procedure tg();
create trigger tg1 after insert on t1_3 for each row execute procedure tg();
create trigger tg1 after insert on t1_4 for each row execute procedure tg();
create trigger tg1 after insert on t1_5 for each row execute procedure tg();
create or replace function tg_truncate() returns trigger as $$  
declare
suffix int := substring(to_char(NEW.crt_time,'yyyymmddhh24miss'), 11, 1)::int;
begin
set lock_timeout = '1s';

for i in 0..5 loop
if i=suffix then
continue;
end if;

if suffix=0 and i=5 then
continue;
end if;

if i=suffix-1 then
continue;
end if;

execute 'truncate t1_'||i;

raise notice 'truncated %', 't1_'||i;
end loop;


return null;
end;
$$ language plpgsql strict;
create trigger tg1 after insert on t_const for each row execute procedure tg_truncate();
postgres=# insert into t1_0 values (1,now(),'2018-01-02 10:00:40');  
NOTICE: truncated t1_1
NOTICE: truncated t1_2
NOTICE: truncated t1_3
NOTICE: truncated t1_4
INSERT 0 1
postgres=# insert into t1_0 values (1,now(),'2018-01-02 10:00:40');  
INSERT 0 1
postgres=# insert into t1_0 values (1,now(),'2018-01-02 10:01:50');
INSERT 0 1

Summary

References

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