'postgresql function to process partitions separately
I have multiple loosely connected tables, all partitioned the same way by the same key:
create table owner( user_id uuid primary key, owned_thing int ) partition by hash(user_id);
create table owner_part1 partition of owner for values with (modulus 2, remainder 0);
create table owner_part2 partition of owner for values with (modulus 2, remainder 1);
create table reader( user_id uuid primary key, book text ) partition by hash(user_id);
create table reader_part1 partition of reader for values with (modulus 2, remainder 0);
create table reader_part2 partition of reader for values with (modulus 2, remainder 1);
create table responsible(user_id uuid primary key, some_data bigint ) partition by hash(user_id);
create table responsible_part1 partition of responsible for values with (modulus 2, remainder 0);
create table responsible_part2 partition of responsible for values with (modulus 2, remainder 1);
I want to write a function that works on all those tables, but only one partition at a time. The reason is that I want to call this function concurrently for each partition, to process them in parallel. Something like:
CREATE OR REPLACE FUNCTION process_partition(
partition integer
) RETURNS VOID
LANGUAGE plpgsql AS
$BODY$
BEGIN
insert into user_part<n>;
delete from owner_part<N>;
....
END
$BODY$
How can I achieve this elegantly? I can access the partition tables individually, eg select * from owners_part2
. However, my problem is, that I have to hardcode the table name in the function body (the <N>
in my example above). I wanted to avoid having all my complex code written inside string literals and then inserting the table names, because ppl say that this query-as-string approach is less efficient. But it also "feels wrong" to pack all my logic inside strings. I also want to avoid duplicating the body for every partition, obviously.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
Solution | Source |
---|