(8.0.22)
https://qiita.com/iwai/items/91410fc07275ec6ff092
イベントスケジューラを使う
select @@event_scheduler;
drop table tab1;
create table tab1
( col1 int not null
, col2 timestamp not null
)
partition by range ( unix_timestamp(col2) )
( partition p20200501_12 values less than (unix_timestamp('2021-05-01 12:00:00') )
, partition p20200501_13 values less than (unix_timestamp('2021-05-01 13:00:00') )
, partition p20200501_14 values less than (unix_timestamp('2021-05-01 14:00:00') )
, partition p20200501_15 values less than (unix_timestamp('2021-05-01 15:00:00') )
);
alter table tab1 add constraint tab1pk primary key(col1,col2);
show create table tab1;
drop procedure proc1;
delimiter //
create procedure proc1(in tabname varchar(100) , in premake int)
begin
set @pname = concat('p', date_format( date_add(now(), interval premake hour), '%Y%m%d_%H'));
select count(*) into @cnt
from information_schema.partitions
where table_schema = 'test'
and table_name = tabname
and partition_name = @pname;
if @cnt = 0 then
set @lt = date_format( date_add(now(), interval 3 hour), '%Y-%m-%d %H:00:00');
set @query = concat('alter table ',tabname,' add partition (partition ', @pname);
set @query = concat(@query, ' values less than (unix_timestamp(''', @lt, ''')))');
prepare stmt1 from @query;
execute stmt1;
deallocate prepare stmt1;
end if;
end //
delimiter ;
drop event event1;
delimiter //
create event event1
on schedule every 5 minute starts now()+ interval 1 minute enable
comment 'event1'
do begin
call proc1('tab1',1);
end //
delimiter ;
show create table tab1;
show events\G
show create event event1\G
show create table tab1;
drop table tab1 purge;
create table tab1
( col1 int not null primary key
, col2 date not null
)
partition by range (col2)
interval(numtoyminterval(1, 'month'))
( partition p1 values less than (to_date('20200101','yyyymmdd'))
, partition p2 values less than (to_date('20200201','yyyymmdd'))
, partition p3 values less than (to_date('20200301','yyyymmdd'))
, partition p4 values less than (to_date('20200401','yyyymmdd'))
);
select table_name, partition_name, high_value
from dba_tab_partitions
where table_owner = 'TEST'
and table_name = 'TAB1'
;
insert into tab1 values(40,to_date('20200401','yyyymmdd') );
commit;
データ追加時にパーティションが追加される。ロールバックしてもパーティションは戻らない
select * from tab1;
select * from tab1 partition(SYS_P2908);
(13)
https://github.com/pgpartman/pg_partman
http://smile-smile-at-smile.blogspot.com/2015/11/gcc-usrlibrpmredhatredhat-hardened-cc1.html
git clone https://github.com/pgpartman/pg_partman.git
cd pg_partman
export PATH=$PATH:/usr/pgsql-13/bin
make install
su - postgres
cd $PGDATA
vim postgresql.conf
shared_preload_libraries = 'pg_partman_bgw'
pg_partman_bgw.interval = 300
pg_partman_bgw.role = 'postgres'
pg_partman_bgw.dbname = 'test'
sudo systemctl restart postgresql-13
psql test
create schema partman;
create extension pg_partman schema partman;
\dx
\dx+ pg_partman
create role partman with login;
grant all on schema partman to partman;
grant all on all tables in schema partman to partman;
grant execute on all functions in schema partman to partman;
grant execute on all procedures in schema partman to partman;
grant all on schema public to partman;
・発生したエラー
gcc: エラー: /usr/lib/rpm/redhat/redhat-hardened-cc1: そのようなファイルやディレクトリはありません
make: *** [<ビルトイン>: src/pg_partman_bgw.o] エラー 1
→ redhat-rpm-configのインストールで解消
-- 動作確認
select parent_table from partman.part_config;
delete from partman.part_config;
drop table tab1 cascade;
create table tab1
(col1 int,
col2 text default 'stuff',
col3 timestamptz not null default now())
partition by range (col3);
create index on tab1 (col3);
\d+ tab1
drop table tab1_template cascade;
create table tab1_template (like tab1);
alter table tab1_template add primary key (col1);
\d tab1_template
select partman.create_parent('public.tab1', 'col3', 'native', 'quarter-hour',
p_premake := 2,
p_automatic_maintenance :='on',
p_template_table := 'public.tab1_template'
);
\d+ tab1
select * from partman.part_config;
update partman.part_config set premake=10
where parent_table='public.tab1'
;
select * from partman.part_config;
SELECT "partman".run_maintenance();
insert into tab1 values(1,'a',now());
select * from tab1;
SELECT "partman".run_maintenance();
★
データが存在しないとパーティションは作成されない。
データが存在するパーティションを基準として新規パーティションが作成される模様。
(2019)
https://www.sqlshack.com/how-to-automate-table-partitioning-in-sql-server/
https://stackoverflow.com/questions/11415993/executing-a-stored-procedure-once-an-hour-automatically
-- 1. パーティションテーブル作成
use test
go
alter database test add filegroup fg1;
alter database test
add file
(
name = fg1,
filename = 'c:\fg\fg1.ndf',
size = 5mb
)
to filegroup fg1;
drop table tab1;
drop partition scheme ps1;
drop partition function pf1;
create partition function pf1(datetime2)
as range right for
values ('2021-05-02 10:00:00', '2021-05-02 11:00:00', '2021-05-02 12:00:00', '2021-05-02 13:00:00')
;
create partition scheme ps1
as partition pf1
all to (fg1)
;
create table tab1
( col1 int not null
, col2 datetime2 not null
) on ps1(col2)
;
alter table tab1 add constraint tab1pk primary key(col1,col2);
select distinct object_name(i.object_id) table_name ,
pf.name partitionfunction,
ps.name partitionscheme,
rv.value range_value
from sys.indexes i
inner join sys.partition_schemes ps on ps.data_space_id = i.data_space_id
inner join sys.partition_functions pf on pf.function_id = ps.function_id
inner join sys.partition_range_values rv on pf.function_id = rv.function_id
;
-- 2. メンテ用プロシージャ作成
create or alter procedure proc1(@tabname varchar(100), @premake int)
as
begin
set nocount on;
declare @cnt integer;
declare @lt varchar(100);
declare @sql_text varchar(1000);
set @lt = cast( format(dateadd(hour, @premake, getdate()),'yyyy-MM-dd HH:00:00') as varchar);
select @cnt = count(*)
from sys.indexes i
inner join sys.partition_schemes ps on ps.data_space_id = i.data_space_id
inner join sys.partition_functions pf on pf.function_id = ps.function_id
inner join sys.partition_range_values rv on pf.function_id = rv.function_id
where object_name(i.object_id) = @tabname
and rv.value = cast(@lt as datetime2)
;
if @cnt = 0
begin
set @sql_text = 'alter partition scheme ps1 next used fg1';
execute (@sql_text)
set @sql_text = 'alter partition function pf1() split range(''' + @lt + ''')'
execute (@sql_text)
end
end
go
-- 3. ジョブ作成
use msdb
go
exec dbo.sp_delete_job @job_name = N'job1' ;
exec dbo.sp_delete_schedule @schedule_name = N'schedule1';
exec dbo.sp_add_job N'job1';
exec dbo.sp_add_jobstep
@job_name = N'job1',
@step_name = N'step1',
@command = N'exec test.dbo.proc1 tab1, 4';
exec dbo.sp_add_schedule
@schedule_name = N'schedule1',
@freq_type = 4,
@freq_interval = 1,
@freq_subday_type = 4,
@freq_subday_interval = 5;
exec dbo.sp_attach_schedule
@job_name = N'job1',
@schedule_name = N'schedule1';
exec dbo.sp_add_jobserver
@job_name = N'job1';
select * from dbo.sysjobs
go
select * from dbo.sysschedules
go
select * from dbo.sysjobschedules
go
SQL Serverエージェントを起動する
