Обсуждение: Automatic partition creation?
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
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?
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
;
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