[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?
Re: [SQL] Parallel updates on multiple cores
Andrei wrote: 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? Wait until the other is done or wait until it has done what it needs to? If it appears to not update the records I would look at the id ranges you are passing. You insert 5000 rows with the first function then you tell the update function to update row id's 1 to 50001 - have you reset the sequence for the id column? or do you drop and create the table before each test? My guess is no updates appear to happen as the id's entered by the serial type are larger than 1. Also you update with processed='n' - is that what you want? Is that the only column you look at to see that it is done? Transactions would be the only cause of the problem you describe. I am guessing that you use bigger numbers than 5000 in your tests and the examples above use overlapping id's. If the first updates row 5001 early then the second may need to wait until it commits to update it again. This can work the other way 5001 is updated by the second locking it until it finishes and the first waits until the second commits to update it again. With 5000 rows I wouldn't expect to see a time difference. Without an order by in the select the rows can be returned and updated in any order. -- Shane Ambler pgSQL (at) Sheeky (dot) Biz Get Sheeky @ http://Sheeky.Biz -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Parallel updates on multiple cores
On Mon, 2008-06-09 at 01:29 -0700, Andrei wrote: > 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. Your ranges overlap. So one waits for the other on tuple=5001. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Unable to create function which takes no arguments
I am a project manager for a popular forum system. We are adding support for PostgreSQL in the next version (which is currently in beta), and have added several PostgreSQL functions to emulate MySQL functions of the same name. I am trying to create a "UNIX_TIMESTAMP()" function in PostgreSQL, which will return the current timestamp. However, whenever I try to add this function in phpPgAdmin, it says 'Syntax error at or near ")" at character 28'. This is the SQL I'm using: CREATE FUNCTION unix_timestamp() RETURNS integer AS ' SELECT current_timestamp()::int4 AS result; ' LANGUAGE SQL; The documentation indicates that the arguments are optional, and even shows an example of a function with no arguments. How can I create this function? -- Michael Eshom Christian Oldies Fan Cincinnati, Ohio
Re: [SQL] Unable to create function which takes no arguments
Hello current_timestamp is some more than less constant :) try, please postgres=# select extract(epoch from current_timestamp); date_part -- 1213030028.17068 (1 row) or postgres=# select extract(epoch from current_timestamp)::int; date_part 1213030113 (1 row) Pavel 2008/6/9 Michael Eshom <[EMAIL PROTECTED]>: > I am a project manager for a popular forum system. We are adding support for > PostgreSQL in the next version (which is currently in beta), and have added > several PostgreSQL functions to emulate MySQL functions of the same name. > > I am trying to create a "UNIX_TIMESTAMP()" function in PostgreSQL, which > will return the current timestamp. However, whenever I try to add this > function in phpPgAdmin, it says 'Syntax error at or near ")" at character > 28'. > > This is the SQL I'm using: > > CREATE FUNCTION unix_timestamp() RETURNS integer AS ' > SELECT current_timestamp()::int4 AS result; > ' LANGUAGE SQL; > > The documentation indicates that the arguments are optional, and even shows > an example of a function with no arguments. How can I create this function? > -- > > Michael Eshom > Christian Oldies Fan > Cincinnati, Ohio -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Unable to create function which takes no arguments
On Mon, Jun 09, 2008 at 12:05:52PM -0400, Michael Eshom wrote: > I am trying to create a "UNIX_TIMESTAMP()" function in PostgreSQL, which > will return the current timestamp. However, whenever I try to add this > function in phpPgAdmin, it says 'Syntax error at or near ")" at > character 28'. yes, but the problem is not in this line: > CREATE FUNCTION unix_timestamp() RETURNS integer AS ' it is in this: > SELECT current_timestamp()::int4 AS result; # CREATE FUNCTION unix_timestamp() RETURNS integer AS ' SELECT current_timestamp()::int4 AS result; ' LANGUAGE SQL; ERROR: syntax error at or near ")" LINE 2: SELECT current_timestamp()::int4 AS result; ^ what's more, when you fix () issue inside of function it will still be broken: # CREATE FUNCTION unix_timestamp() RETURNS integer AS 'SELECT current_timestamp::int4 AS result;' LANGUAGE SQL; ERROR: cannot cast type timestamp with time zone to integer LINE 1: ...p() RETURNS integer AS 'SELECT current_timestamp::int4 AS re... ^ (it might work in older postgresql versions, i'm not sure). to make it sane write it that way: CREATE FUNCTION unix_timestamp() RETURNS integer AS ' SELECT extract(epoch from current_timestamp)::int4; ' LANGUAGE SQL; depesz -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Unable to create function which takes no arguments
IIRC, current_timestamp doesn't require parens. You could try something like this: select extract(epoch from current_timestamp)::int4 as result; -Mark On Mon, 2008-06-09 at 12:05 -0400, Michael Eshom wrote: > I am a project manager for a popular forum system. We are adding > support for PostgreSQL in the next version (which is currently in > beta), and have added several PostgreSQL functions to emulate MySQL > functions of the same name. > > I am trying to create a "UNIX_TIMESTAMP()" function in PostgreSQL, > which will return the current timestamp. However, whenever I try to > add this function in phpPgAdmin, it says 'Syntax error at or near ")" > at character 28'. > > This is the SQL I'm using: > > CREATE FUNCTION unix_timestamp() RETURNS integer AS ' > SELECT current_timestamp()::int4 AS result; > ' LANGUAGE SQL; > > The documentation indicates that the arguments are optional, and even > shows an example of a function with no arguments. How can I create > this function? > -- > > > Michael Eshom > Christian Oldies Fan > Cincinnati, Ohio > > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
