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;
Best Answer
This is happening because you are trying to alter the table to add partitions when there is a cursor open (active select) on the table. The required locks conflict. You can fix that by first fetching the partition dates into an array, close the cursor and then create partitions from that array. Code sample is below.