How can I add multiple partitions to a Greenplum table using dynamic SQL

Question

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;

• Solomon Rutzky: Would it be appropriate to tag this with "PostgreSQL"? and if so, maybe a version # if applicable.
• PhilHibbs: I think the SPLIT PARTITION clause is a Greenplum extension, and also the parallel execution problem may be Greenplum-specific.
• Solomon Rutzky: I understand that, but isn't Greenplum an implementation of PostgreSQL? I was not suggesting to remove the Greenplum tag, as that does qualify this as being a Greenplum-specific question. But you might get more help from people that know PostgreSQL and are filtering on that to tag it as such.

Answer 1

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.

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;
 p_part_dates date[];
 part_cntr int;
 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
 part_cntr := 0;
 FOR q_add IN EXECUTE adder LOOP
 part_cntr := part_cntr + 1;
 p_part_dates[part_cntr] = q_add.dd;
 -- SELECT dv_util.create_partition(p_schema,p_table,q_add.dd) INTO result;
 END LOOP;
 for i in 1..part_cntr loop
 select dv_util.create_partition(p_schema,p_table,p_part_dates[i]) INTO result;
 end loop;
 RETURN true;
 ELSE
 RETURN false;
 END IF;
 END;
 $BODY$
 LANGUAGE plpgsql VOLATILE;