greenplum에서 appendonly = "YES" 데이터삭제
appendonly가 속도는 빠르지만 삭제가 안되서 하는수 없이 만듬.
허접한 쿼리지만 그린플럼 성능이 있어서 느리지는 않네요.
CREATE OR REPLACE FUNCTION public.partition_delete_check(range_start_input text, range_end_input text, comp_input text, input_type character varying)
RETURNS integer AS
$BODY$
DECLARE
sql text;
start_val integer;
end_val integer;
BEGIN-- prepared statement
sql := 'select coalesce(max(1),0) where '||range_start_input||' <= '''||comp_input||'''::'||input_type;
execute sql into start_val;
sql := 'select coalesce(max(1),0) where '||range_end_input||' > '''||comp_input||'''::'||input_type;
execute sql into end_val;
return start_val*end_val;
END;
$BODY$
LANGUAGE plpgsql IMMUTABLE;
--------------------------------------------------------------------------------------------------------
-- Function: public.partition_delete_text(character varying, character varying, character varying, character varying)
-- DROP FUNCTION public.partition_delete_text(character varying, character varying, character varying, character varying);
CREATE OR REPLACE FUNCTION public.partition_delete_text(in_schemaname character varying, in_tablename character varying, in_delete_value character varying, in_interval character varying)
RETURNS character varying AS
$BODY$
DECLARE
sql text;
p_col_name varchar(50);
p_col_type varchar(20);
p_ptable_name varchar(100);
p_storage_type varchar(2);
o_sql varchar(2000);
BEGIN-- prepared statement
select --b.nspname, a.relname,
c.columnname, e.typname
from pg_class a
join pg_namespace b
on a.relnamespace = b.oid
join pg_partition_columns c
on c.schemaname = b.nspname
and c.tablename = a.relname
join pg_attribute d
on a.oid = d.attrelid
and c.columnname = d.attname
join pg_type e
on e.oid = d.atttypid
where b.nspname = in_schemaname
and a.relname = in_tablename
into p_col_name, p_col_type;
select case when max(c.reloptions[1]) = 'appendonly=true' then 'a' else 'h' end storage_type
, max(a.partitiontablename) partitiontablename
from pg_partitions a
join pg_namespace b
on a.partitionschemaname = b.nspname
join pg_class c
on b.oid = c.relnamespace
and a.partitiontablename = c.relname
where a.partitionrank > 0
and a.schemaname = in_schemaname
and a.tablename = in_tablename
and public.partition_delete_check(a.partitionrangestart,a.partitionrangeend,in_delete_value,p_col_type) = 1
into p_storage_type, p_ptable_name;
o_sql := '';
if p_storage_type = 'a' and p_col_type not like '%char%' then
sql := 'create temp table '||p_ptable_name||'_delete
as
select * from '||in_schemaname||'.'||p_ptable_name||'
where not ('||p_col_name||' >= '''||in_delete_value||'''::'||p_col_type||'
and '||p_col_name||' < '''||in_delete_value||'''::'||p_col_type||' + interval ''1 '||in_interval||''' );';
o_sql := o_sql || ' ' ||sql;
sql := 'truncate table '||in_schemaname||'.'||p_ptable_name||'; ';
o_sql := o_sql || ' ' ||sql;
sql := 'insert into '||in_schemaname||'.'||in_tablename||' select * from '||p_ptable_name||'_delete; ';
o_sql := o_sql || ' ' ||sql;
elsif p_storage_type = 'a' and p_col_type like '%char%' then
sql := 'create temp table '||p_ptable_name||'_delete
as
select * from '||in_schemaname||'.'||p_ptable_name||'
where not ('||p_col_name||' = '''||in_delete_value||'''::'||p_col_type||' );';
o_sql := o_sql || ' ' ||sql;
sql := 'truncate table '||in_schemaname||'.'||p_ptable_name||'; ';
o_sql := o_sql || ' ' ||sql;
sql := 'insert into '||in_schemaname||'.'||in_tablename||' select * from '||p_ptable_name||'_delete; ';
o_sql := o_sql || ' ' ||sql;
elsif p_storage_type = 'h' and p_col_type not like '%char%' then
sql := 'delete from '||in_schemaname||'.'||in_tablename||'
where ('||p_col_name||' >= '''||in_delete_value||'''::'||p_col_type||'
and '||p_col_name||' < '''||in_delete_value||'''::'||p_col_type||' + interval ''1 '||in_interval||''' ); ';
o_sql := o_sql || ' ' ||sql;
elsif p_storage_type = 'h' and p_col_type like '%char%' then
sql := 'delete from '||in_schemaname||'.'||in_tablename||'
where ('||p_col_name||' = '''||in_delete_value||'''::'||p_col_type||' ); ';
o_sql := o_sql || ' ' ||sql;
end if;
return o_sql;
END;
$BODY$
LANGUAGE plpgsql IMMUTABLE;
--------------------------------------------------------------------------------------------------------
-- Function: public.partition_delete_data(character varying, character varying, character varying, character varying)
-- DROP FUNCTION public.partition_delete_data(character varying, character varying, character varying, character varying);
CREATE OR REPLACE FUNCTION public.partition_delete_data(in_schemaname character varying, in_tablename character varying, in_delete_value character varying, in_interval character varying)
RETURNS void AS
$BODY$
DECLARE
sql text;
p_col_name varchar(50);
p_col_type varchar(20);
p_ptable_name varchar(100);
p_storage_type varchar(2);
BEGIN-- prepared statement
select --b.nspname, a.relname,
c.columnname, e.typname
from pg_class a
join pg_namespace b
on a.relnamespace = b.oid
join pg_partition_columns c
on c.schemaname = b.nspname
and c.tablename = a.relname
join pg_attribute d
on a.oid = d.attrelid
and c.columnname = d.attname
join pg_type e
on e.oid = d.atttypid
where b.nspname = in_schemaname
and a.relname = in_tablename
into p_col_name, p_col_type;
select case when c.reloptions[1] = 'appendonly=true' then 'a' else 'h' end storage_type
, a.partitiontablename
from pg_partitions a
join pg_namespace b
on a.partitionschemaname = b.nspname
join pg_class c
on b.oid = c.relnamespace
and a.partitiontablename = c.relname
where a.partitionrank > 0
and a.schemaname = in_schemaname
and a.tablename = in_tablename
and public.partition_delete_check(a.partitionrangestart,a.partitionrangeend,in_delete_value,p_col_type) = 1
into p_storage_type, p_ptable_name;
if p_storage_type = 'a' then
sql := 'create temp table '||p_ptable_name||'_delete
as
select * from '||in_schemaname||'.'||p_ptable_name||'
where not ('||p_col_name||' >= '''||in_delete_value||'''::'||p_col_type||'
and '||p_col_name||' < '''||in_delete_value||'''::'||p_col_type||' + interval ''1 '||in_interval||''' )';
--return next sql;
execute sql;
sql := 'truncate table '||in_schemaname||'.'||p_ptable_name;
--return next sql;
execute sql;
sql := 'insert into '||in_schemaname||'.'||in_tablename||' select * from '||p_ptable_name||'_delete';
--return next sql;
execute sql;
elsif p_storage_type = 'h' and p_col_type not like '%char%' then
sql := 'delete from '||in_schemaname||'.'||in_tablename||'
where ('||p_col_name||' >= '''||in_delete_value||'''::'||p_col_type||'
and '||p_col_name||' < '''||in_delete_value||'''::'||p_col_type||' + interval ''1 '||in_interval||''' )';
execute sql;
elsif p_storage_type = 'h' and p_col_type like '%char%' then
sql := 'delete from '||in_schemaname||'.'||in_tablename||'
where ('||p_col_name||' = '''||in_delete_value||'''::'||p_col_type||' )';
execute sql;
end if;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;