bug18130.sql

text/plain

Download

-- run this, then \copy test from 'bug18130.csv' csv

create table if not exists test (
    a smallint,
    b bigint,
    c bigint,
    d bigint,
    e smallint,
    plan_date date,
    g numeric(18,7),
    h bigint,
    i timestamptz,
    j bigint,
    k integer,
    l smallint,
    primary key (a, b, c, d, e, plan_date)
) partition by list(a);

/* this is just to generate partition structure automatically */
create or replace function test_add_partitions(a integer, year integer)
    returns void
    volatile strict
as
$$
declare
    parent text;
    root text;
begin
    root := 'test_' || a::text;
    execute 'create table if not exists ' || root || ' partition of test for
values in (' || a::text || ') partition by hash (b);';
    for b in 0..7 loop
            parent := root || '_' || b::text;

            execute 'create table if not exists ' || parent ||
                    ' partition of ' || root || ' for values with (modulus
8, remainder ' || b::text || ')' ||
                    ' partition by range (plan_date);';

            execute 'create table if not exists ' ||
                    parent || '_y' || year::text || 'm1 partition of ' ||
parent ||
                    ' for values from (''' || year::text || '-01-01'') to
(''' || year::text ||
                    '-02-01'');';
            execute 'create table if not exists ' ||
                    parent || '_y' || year::text || 'm2 partition of ' ||
parent ||
                    ' for values from (''' || year::text || '-02-01'') to
(''' || year::text ||
                    '-03-01'');';
            execute 'create table if not exists ' ||
                    parent || '_y' || year::text || 'm3 partition of ' ||
parent ||
                    ' for values from (''' || year::text || '-03-01'') to
(''' || year::text ||
                    '-04-01'');';
            execute 'create table if not exists ' ||
                    parent || '_y' || year::text || 'm4 partition of ' ||
parent ||
                    ' for values from (''' || year::text || '-04-01'') to
(''' || year::text ||
                    '-05-01'');';
            execute 'create table if not exists ' ||
                    parent || '_y' || year::text || 'm5 partition of ' ||
parent ||
                    ' for values from (''' || year::text || '-05-01'') to
(''' || year::text ||
                    '-06-01'');';
            execute 'create table if not exists ' ||
                    parent || '_y' || year::text || 'm6 partition of ' ||
parent ||
                    ' for values from (''' || year::text || '-06-01'') to
(''' || year::text ||
                    '-07-01'');';
            execute 'create table if not exists ' ||
                    parent || '_y' || year::text || 'm7 partition of ' ||
parent ||
                    ' for values from (''' || year::text || '-07-01'') to
(''' || year::text ||
                    '-08-01'');';
            execute 'create table if not exists ' ||
                    parent || '_y' || year::text || 'm8 partition of ' ||
parent ||
                    ' for values from (''' || year::text || '-08-01'') to
(''' || year::text ||
                    '-09-01'');';
            execute 'create table if not exists ' ||
                    parent || '_y' || year::text || 'm9 partition of ' ||
parent ||
                    ' for values from (''' || year::text || '-09-01'') to
(''' || year::text ||
                    '-10-01'');';
            execute 'create table if not exists ' ||
                    parent || '_y' || year::text || 'm10 partition of ' ||
parent ||
                    ' for values from (''' || year::text || '-10-01'') to
(''' || year::text ||
                    '-11-01'');';
            execute 'create table if not exists ' ||
                    parent || '_y' || year::text || 'm11 partition of ' ||
parent ||
                    ' for values from (''' || year::text || '-11-01'') to
(''' || year::text ||
                    '-12-01'');';
            execute 'create table if not exists ' ||
                    parent || '_y' || year::text || 'm12 partition of ' ||
parent ||
                    ' for values from (''' || year::text || '-12-01'') to
(''' || (year + 1)::text ||
                    '-01-01'');';
        end loop;
end;
$$ language plpgsql;

select test_add_partitions(0, 2022);
select test_add_partitions(0, 2023);
select test_add_partitions(0, 2024);

create or replace function t_test_trigger_func()
    returns trigger as
$$
BEGIN
--    NEW.h := nextval(TG_ARGV[0]::regclass);
--    NEW.i := NOW();
    RETURN NEW;
END;
$$ language plpgsql volatile;

-- create sequence if not exists test_seq;

create or replace trigger t_test_trigger before insert or update on test for each row execute procedure t_test_trigger_func(); -- ('test_seq');