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@xxxxxxxxxxxxxxxxxxxxx> 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
