Skip site navigation (1) Skip section navigation (2)

Parallel updates on multiple cores

From: Andrei <andrei_view(at)yahoo(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Parallel updates on multiple cores
Date: 2008-06-09 08:29:58
Message-ID: 717918.13630.qm@web65714.mail.ac4.yahoo.com (view raw or flat)
Thread:
Lists: pgsql-sql
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?
       

Responses

pgsql-sql by date

Next:From: Shane AmblerDate: 2008-06-09 10:29:58
Subject: Re: Parallel updates on multiple cores
Previous:From: Niklas JohanssonDate: 2008-06-08 09:59:22
Subject: Re: Need some magic with alternative rows

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group