如何使用動態 SQL 將多個分區添加到 Greenplum 表

我有一個向表添加分區的函式，效果很好。它需要一個模式名稱、一個表名稱和一個日期，並生成並執行一條ALTER TABLE語句來為指定的日期添加一個分區。

我有另一個函式，它根據溢出分區中存在的數據生成日期範圍，並呼叫第一個函式將溢出分區拆分為每個日期的新分區。

但是，我認為不可能這樣做，因為它會因以下錯誤而失敗：

ERROR: relation 6712928 is still open (relcache.c:2417)

我懷疑這是因為它試圖並行執行所有更改語句。誰能想到一種方法來一次執行一個語句？

我嘗試在 FOR 循環中執行查詢並在循環的每次迭代中呼叫一次該函式，但它做同樣的事情。

這是我的兩個功能：

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;

發生這種情況是因為當表上有一個游標打開（活動選擇）時，您試圖更改表以添加分區。所需的鎖衝突。您可以通過首先將分區日期提取到數組中，關閉游標然後從該數組創建分區來解決此問題。程式碼範例如下。

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;

引用自：https://dba.stackexchange.com/questions/126171