Monday, June 9, 2008

[SQL] Parallel updates on multiple cores

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?

No comments: