I have the following case: a simple table
drop table test_data;
create table test_data (
id bigserial not null primary key,
content varchar(50),
processed varchar(1)
);
My function doing the inserts
CREATE OR REPLACE FUNCTION populate_test_data(IN nr_records BIGINT, IN proc_nr
BIGINT) RETURNS integer AS $$
DECLARE
counter BIGINT := 0;
record_val text;
BEGIN
LOOP
counter:=counter+1;
record_val:=((('v ' || counter) || ' p ') || proc_nr);
insert into test_data(content, processed) values(record_val,'n');
EXIT WHEN counter > nr_records;
END LOOP;
RETURN 0;
END;
$$ LANGUAGE plpgsql;
where nr_records represents the number of inserts, and
CREATE OR REPLACE FUNCTION select_unprocessed(IN start_id BIGINT, IN end_id
BIGINT) RETURNS integer AS $$
DECLARE
counter BIGINT := 0;
record_val text;
rec record;
BEGIN
FOR rec IN SELECT id, content, processed FROM test_data WHERE id >= start_id
AND id < end_id
LOOP
record_val:=rec.content || '-DONE-';
update test_data set content=record_val, processed='n' where id=rec.id;
END LOOP;
RETURN 0;
END;
$$ LANGUAGE plpgsql;
The function above updates the rows between the ids start_id and end_id.
I have a quad core procesor so i run two separate connections to the database:
select populate_test_data(5000,1) and another select
populate_test_data(5000,2). In this case each function runs on one core doing
the inserts in parallel, but when i try to run select
select_unprocessed(1,5001) and from another connection select
select_unprocessed(5001, 10001), one of the processes locks the table so the
other one has to wait until the table is unlocked.
Each process updates different parts of the table.
Is there a way to do the updates in parallel on multiple cores?
