Parallel updates on multiple cores
От | Andrei |
---|---|
Тема | Parallel updates on multiple cores |
Дата | |
Msg-id | 717918.13630.qm@web65714.mail.ac4.yahoo.com обсуждение исходный текст |
Ответы |
Re: Parallel updates on multiple cores
Re: Parallel updates on multiple cores |
Список | pgsql-sql |
I have the following case: a simple table<br /><br />drop table test_data;<br />create table test_data (<br />id bigserialnot null primary key,<br />content varchar(50),<br />processed varchar(1)<br />);<br /><br />My function doing theinserts<br /><br />CREATE OR REPLACE FUNCTION populate_test_data(IN nr_records BIGINT, IN proc_nr BIGINT) RETURNS integerAS $$<br />DECLARE<br /> counter BIGINT := 0;<br /> record_val text;<br />BEGIN<br />LOOP <br /> counter:=counter+1;<br/> record_val:=((('v ' || counter) || ' p ') || proc_nr);<br /> insert into test_data(content,processed) values(record_val,'n');<br /> EXIT WHEN counter > nr_records;<br />END LOOP;<br />RETURN0;<br />END;<br />$$ LANGUAGE plpgsql;<br /><br />where nr_records represents the number of inserts, and<br /><br/>CREATE OR REPLACE FUNCTION select_unprocessed(IN start_id BIGINT, IN end_id BIGINT) RETURNS integer AS $$<br />DECLARE<br/> counter BIGINT := 0;<br /> record_val text;<br /> rec record;<br /><br />BEGIN<br />FOR rec IN SELECTid, content, processed FROM test_data WHERE id >= start_id AND id < end_id<br />LOOP <br /> record_val:=rec.content|| '-DONE-';<br /> update test_data set content=record_val, processed='n' where id=rec.id;<br/>END LOOP;<br />RETURN 0;<br /><br />END;<br />$$ LANGUAGE plpgsql;<br /><br />The function above updates therows between the ids start_id and end_id.<br />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 runson one core doing the inserts in parallel, but when i try to run select select_unprocessed(1,5001) and from another connectionselect select_unprocessed(5001, 10001), one of the processes locks the table so the other one has to wait untilthe table is unlocked.<br />Each process updates different parts of the table.<br />Is there a way to do the updatesin parallel on multiple cores?<p>
В списке pgsql-sql по дате отправления: