Automatic partition creation?

From: Sbob <sbob(at)quadratum-braccas(dot)com>
To: 'Pgsql-admin' <pgsql-admin(at)lists(dot)postgresql(dot)org>
Subject: Automatic partition creation?
Date: 2023-03-15 17:24:19
Message-ID: 001805b4-c039-49b1-344b-9c52016c6f3b@quadratum-braccas.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Rui DeSousa 2023-03-15 18:20:00 Re: Automatic partition creation?
Previous Message Laurenz Albe 2023-03-14 21:31:39 Re: Oracle characterset and collation to Postgresql