I have a function that adds a partition to a table, that works just fine. It takes a schema name, a table name, and a date, and generates and executes an ALTER TABLE
statement to add a partition for the specified date.
I have another function that generates a date range based on the data present in the overflow partition, and calls the first function to split the overflow partition into a new partition for each date.
However, I don't think that it is possible to do it this way, as it fails with this error:
ERROR: relation 6712928 is still open (relcache.c:2417)
I suspect that this is because it's trying to do all the alter statements in parallel. Can anyone think of a way to execute the statements one at a time?
I tried executing the query in a FOR loop and calling the function once per iteration of the loop, but it does the same thing.
Here are my two functions:
CREATE OR REPLACE FUNCTION dv_util.create_partition(p_schema character varying, p_table character varying, p_date date) RETURNS boolean AS
$BODY$
DECLARE
splitter varchar(10000);
counter integer;
BEGIN
splitter := 'ALTER TABLE '||p_schema||'.'||p_table||' SPLIT DEFAULT PARTITION START ('''||p_date::text||'''::date) INCLUSIVE END ('''||(p_date + interval '1 days')::date::text||'''::date) EXCLUSIVE INTO (PARTITION "'||p_date::text||'", default partition)';
SELECT count(*) INTO counter FROM information_schema.tables WHERE table_schema = p_schema AND table_name = p_table;
IF counter > 0 THEN -- table exists
SELECT count(*) INTO counter FROM information_schema.tables WHERE table_schema = p_schema AND table_name = p_table||'_1_prt_'||p_date::text;
IF counter = 0 THEN -- partition does not exist
EXECUTE splitter;
RETURN true;
ELSE
RETURN false;
END IF;
ELSE
RETURN false;
END IF;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;
CREATE OR REPLACE FUNCTION dv_util.add_partitions(p_schema character varying, p_table character varying) RETURNS boolean AS
$BODY$
DECLARE
adder varchar(10000); -- Query to generate all the partition parameters
c_adding refcursor; -- Cursor to iterate over the partiton parameters
q_add RECORD;
result boolean;
counter integer;
BEGIN
adder := 'with source as (SELECT * FROM '||p_schema||'.'||p_table||')';
adder := adder || ', over as (SELECT * FROM '||p_schema||'.'||p_table||'_1_prt_overflow)';
adder := adder || ', series as ';
adder := adder || '(SELECT generate_series( greatest(';
adder := adder || ' ( least( coalesce((select min(std_date_utc) from over),current_date)';
adder := adder || ' , coalesce((select (max(std_date_utc) + interval ''1 day'')::date from source)),current_date) - ''2001-01-01'')::integer';
adder := adder || ' , 0 )::integer';
adder := adder || ' , (greatest( (select max(std_date_utc) from over)';
adder := adder || ' , current_date + interval ''30 day'')::date - ''2001-01-01'')::integer ))';
adder := adder || 'select ((''2001-01-01''::date) + (generate_series * (interval ''1 day'')))::date dd from series';
SELECT count(*) INTO counter FROM information_schema.tables WHERE table_schema = p_schema AND table_name = p_table||'_1_prt_overflow';
IF counter > 0 THEN
FOR q_add IN EXECUTE adder LOOP
SELECT dv_util.create_partition(p_schema,p_table,q_add.dd) INTO result;
END LOOP;
RETURN true;
ELSE
RETURN false;
END IF;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;
SPLIT PARTITION
clause is a Greenplum extension, and also the parallel execution problem may be Greenplum-specific.