From: Rui DeSousa <rui@crazybean.net>
To: Sbob <sbob@quadratum-braccas.com>
Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Subject: Re: Automatic partition creation?
Date: Wed, 15 Mar 2023 14:20:00 -0400
Message-ID: <F2EE8A8E-FC1F-465F-981F-D7D28A56FE70@crazybean.net> (raw)
In-Reply-To: <001805b4-c039-49b1-344b-9c52016c6f3b@quadratum-braccas.com>
References: <001805b4-c039-49b1-344b-9c52016c6f3b@quadratum-braccas.com>
> 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
;
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: rui@crazybean.net, sbob@quadratum-braccas.com, pgsql-admin@lists.postgresql.org
Subject: Re: Automatic partition creation?
In-Reply-To: <F2EE8A8E-FC1F-465F-981F-D7D28A56FE70@crazybean.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox