Re: Automatic partition creation?

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(at)quadratum-braccas(dot)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