Thread: Automatic partition creation?

Automatic partition creation?

From

Sbob

Date:

15 March 2023, 20:24:19

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?

From

Rui DeSousa

Date:

15 March 2023, 21:20:00

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?

From

Keith Fiske

Date:

15 March 2023, 21:53:29

Auto-creation of new partitions is not built in. However, pg_partman is an extension that allows you do do this using native partitioning

https://github.com/pgpartman/pg_partman

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