Обсуждение: Automatic partition creation?

Поиск
Список
Период
Сортировка

Automatic partition creation?

От
Sbob
Дата:
All;


In the past I have setup inheritance based partition using something 
like the function snippet below, so the creation of new partitions is 
automatic

Is it possible to auto create new partitions as needed with declarative 
partitioning as well?


  BEGIN CODE SNIPPET

     customer_name := NEW.customer_name;
     year := date_part('year', NEW.updated_at);
     month := date_part('month', NEW.updated_at);
     day := date_part('day', NEW.updated_at);
     current__date := date_trunc('day', NEW.updated_at);
     next__date := date_trunc('day', NEW.updated_at) + interval '1 day';

     namespace := 'partitions_cst_ymd';
     suffix := 'c_' || customer_name || '_' || year || '_' || month || 
'_' || day;
     clean_table_name := 'process_states_' || suffix;
     table_name := namespace || '.' || clean_table_name;

     IF NOT EXISTS (SELECT relname FROM pg_class WHERE relname = 
clean_table_name)
     THEN

         index_name_on_pkey := 'idx_' || suffix || '_on_pkey';
         index_name_on_process_native_id := 'idx_' || suffix || 
'_on_process_native_id';

         EXECUTE format('CREATE TABLE IF NOT EXISTS %s (
                 CHECK (
                     customer_name = ''%s'' AND
                     updated_at >= DATE ''%s'' AND
                     updated_at < DATE ''%s'')
             ) INHERITS (public.process_states)', table_name, 
customer_name, current__date, next__date);

         EXECUTE format('CREATE INDEX %s ON %s %s', index_name_on_pkey, 
table_name, '(customer_name, process_native_id)');
         EXECUTE format('CREATE INDEX %s ON %s %s', 
index_name_on_process_native_id, table_name, '(process_native_id)');

     END IF;

     EXECUTE format('INSERT INTO %s SELECT $1.*', table_name) USING NEW;
     set client_min_messages to NOTICE;
     RETURN NULL;

END CODE SNIPPET




Re: Automatic partition creation?

От
Rui DeSousa
Дата:

On Mar 15, 2023, at 1:24 PM, Sbob <sbob@quadratum-braccas.com> wrote:

Is it possible to auto create new partitions as needed with declarative partitioning as well?


I’ve created and used the following function to create yearly partitions.  I would just call the function periodical from cron and provide a lead time; i.e. The partition should be there a month before the new year, etc. 


create or replace function dba.add_yearly_partition(_schema name, _table name, _lead_time interval)
  returns boolean
as $body$
declare
  _create_table_template constant text := '
    create table %1$s.%2$s_%3$s
    partition of %1$s.%2$s
    for values from (''%4$s'') to (''%5$s'')
    ;
  ';
  _date timestamp with time zone := now() + _lead_time;
  _is_valid boolean;
  _sql text;
begin
  for _sql in 
    select replace(
        replace(
            pg_get_expr(c.relpartbound, c.oid)
            , 'FOR VALUES FROM'
            ,'select'
          )
        , 'TO'
        , '<= $1 and $1 <'
      )
      || ';'
    from pg_class p
    join pg_namespace ns on ns.oid = p.relnamespace
      and ns.nspname = _schema
    join pg_inherits i on i.inhparent = p.oid
    join pg_class c on c.oid = i.inhrelid
    where p.relname = _table
    order by c.oid desc
  loop
    execute _sql into _is_valid using _date;
    exit when _is_valid;
  end loop;

  if _is_valid is null then
    raise exception 'Failed to identify partitioned table.';
  elsif not _is_valid then
    execute format(_create_table_template
        , _schema
        , _table
        , extract(year from _date)
        , date_trunc('year', _date)
        , date_trunc('year', _date) + '1 year'::interval
      );
  end if;

  return (not _is_valid);
end;
$body$
  language plpgsql
  set search_path = dba
;

Re: Automatic partition creation?

От
Keith Fiske
Дата:
Auto-creation of new partitions is not built in. However, pg_partman is an extension that allows you do do this using native partitioning 


On Wed, Mar 15, 2023 at 1:24 PM Sbob <sbob@quadratum-braccas.com> wrote:
All;


In the past I have setup inheritance based partition using something
like the function snippet below, so the creation of new partitions is
automatic

Is it possible to auto create new partitions as needed with declarative
partitioning as well?


  BEGIN CODE SNIPPET

     customer_name := NEW.customer_name;
     year := date_part('year', NEW.updated_at);
     month := date_part('month', NEW.updated_at);
     day := date_part('day', NEW.updated_at);
     current__date := date_trunc('day', NEW.updated_at);
     next__date := date_trunc('day', NEW.updated_at) + interval '1 day';

     namespace := 'partitions_cst_ymd';
     suffix := 'c_' || customer_name || '_' || year || '_' || month ||
'_' || day;
     clean_table_name := 'process_states_' || suffix;
     table_name := namespace || '.' || clean_table_name;

     IF NOT EXISTS (SELECT relname FROM pg_class WHERE relname =
clean_table_name)
     THEN

         index_name_on_pkey := 'idx_' || suffix || '_on_pkey';
         index_name_on_process_native_id := 'idx_' || suffix ||
'_on_process_native_id';

         EXECUTE format('CREATE TABLE IF NOT EXISTS %s (
                 CHECK (
                     customer_name = ''%s'' AND
                     updated_at >= DATE ''%s'' AND
                     updated_at < DATE ''%s'')
             ) INHERITS (public.process_states)', table_name,
customer_name, current__date, next__date);

         EXECUTE format('CREATE INDEX %s ON %s %s', index_name_on_pkey,
table_name, '(customer_name, process_native_id)');
         EXECUTE format('CREATE INDEX %s ON %s %s',
index_name_on_process_native_id, table_name, '(process_native_id)');

     END IF;

     EXECUTE format('INSERT INTO %s SELECT $1.*', table_name) USING NEW;
     set client_min_messages to NOTICE;
     RETURN NULL;

END CODE SNIPPET





--
Keith Fiske
Senior Database Engineer
Crunchy Data - http://crunchydata.com