range partitions interval, locks #52
Description
Last version of postgrespro, pg_pathman from it's contrib package.
Problem 1: unable to create several partitions within single transaction.
Problem 2: pg_pathman creates next partition with incorrect end_value despite interval parameter
(look at the result partitions of last query).
Init query:
CREATE SCHEMA main;
CREATE SCHEMA pathman;
CREATE EXTENSION pg_pathman SCHEMA pathman;
SET search_path = main, pg_catalog;
CREATE FUNCTION tr_registry_after_all_stmt() RETURNS trigger
LANGUAGE plpgsql
AS $$
begin
raise notice 'tr_registry_after_all_stmt';
return null;
end
$$;
CREATE FUNCTION tr_registry_before_all_row() RETURNS trigger
LANGUAGE plpgsql
AS $$
begin
raise notice 'tr_registry_before_all_row';
return case when TG_OP = 'DELETE' then old else new end;
end
$$;
CREATE TABLE main.transaction
(
id bigserial primary key,
pdate date NOT NULL DEFAULT (now())::date
);
CREATE TABLE main.participant
(
tid bigint NOT NULL,
cid integer NOT NULL,
pdate date NOT NULL DEFAULT (now())::date,
PRIMARY KEY (tid, cid)
);
CREATE TABLE main.registry
(
pdate date NOT NULL,
tid bigint NOT NULL,
post_id integer NOT NULL,
PRIMARY KEY (tid, post_id)
);
CREATE TRIGGER tr_registry_after_all_stmt
AFTER INSERT OR UPDATE OR DELETE
ON main.registry
FOR EACH STATEMENT
EXECUTE PROCEDURE main.tr_registry_after_all_stmt();
CREATE TRIGGER tr_registry_before_all_row
BEFORE INSERT OR UPDATE OR DELETE
ON main.registry
FOR EACH ROW
EXECUTE PROCEDURE main.tr_registry_before_all_row();
CREATE FUNCTION after_split_participant(params jsonb) RETURNS void
LANGUAGE plpgsql
AS $_$
declare
_c text := params->>'partition';
begin
execute 'alter table main.' || _c || '
add foreign key(tid) references main.transaction_' || substring(_c from '_(\d+)$') || '(id)
on delete cascade';
end
$_$;
select pathman.set_init_callback('main.participant', 'main.after_split_participant');
CREATE FUNCTION after_split_registry(params jsonb) RETURNS void
LANGUAGE plpgsql
AS $_$
declare
_c text := params->>'partition';
begin
execute 'alter table main.' || _c || '
add foreign key(tid) references main.transaction_' || substring(_c from '_(\d+)$') || '(id)
on delete cascade deferrable initially immediate';
execute 'create trigger tr_' || _c || '_before_all_row
before insert or update or delete
on main.' || _c || '
for each row
execute procedure main.tr_registry_before_all_row()';
execute 'create trigger tr_' || _c || '_after_all_stmt
after insert or update or delete
on main.' || _c || '
for each statement
execute procedure main.tr_registry_after_all_stmt()';
end
$_$;
select pathman.set_init_callback('main.registry', 'main.after_split_registry');
select pathman.create_range_partitions('main.transaction', 'pdate', '2016-01-01'::date, '1 year'::interval, 1);
select pathman.create_range_partitions('main.registry', 'pdate', '2016-01-01'::date, '1 year'::interval, 1);
select pathman.create_range_partitions('main.participant', 'pdate', '2016-01-01'::date, '1 year'::interval, 1);
test query:
do
$$
declare
_tid bigint;
_pdate date := now() + '1year'::interval;
begin
insert into main.transaction(pdate) values (_pdate)
returning id into _tid;
insert into main.participant (tid, cid, pdate)
values (_tid, 1, _pdate);
insert into main.registry (pdate, tid, post_id)
values (_pdate, _tid, 1);
end
$$;