i am testing a plpgsql function in jmeter. The following sample is to replicate the issue. i have a table named sing with definition as follows
db=# \d sing
Table "schema1.sing"
Column | Type |
---|---|
id | bigint |
valr | numeric |
and my plpgsql function is as follows
create or replace function schema1.insissue(val text) returns text as $$
declare
_p text;_h text;
ids text[];
valid numeric := functiontochangetoid(val); // a sample function to change value into id.
slid bigint:= nextval('rep_s'); // sequence value
dup text := null;
begin
select array_agg(id) from sing where valr = valid into ids;
raise notice 'ids %',ids;
if coalesce(array_upper(ids,1),0) > 0 then
dup = 'FAIL';
end if;
raise notice 'dup %',dup;
if dup is null then
insert into sing values (slid,valid);
return 'SUCCESS'|| slid;
end if;
return 'FAIL';
exception
when others then
get stacked diagnostics
_p := pg_exception_context,_h := pg_exception_hint;
raise notice 'sqlerrm >> :%',sqlerrm;
raise notice 'position >> :%',_p;
raise notice 'hint >> :%',_h;
return 'FAIL';
end;
$$ language plpgsql;
simply in my function it checks if the value exist in valr column of sing table and if not exist inserts the value to the table.
to connect i use postgresql-42.2.14.jar. when the ramp up period is 1 sec IE 200 request in one second the function creates duplicate values like this, when ramp up period is 100 sec no issue.
db=# select * from sing;
id | valr |
---|---|
897 | 1095 |
898 | 1095 |
900 | 1095 |
899 | 1095 |
901 | 1095 |
902 | 1095 |
903 | 1095 |
but it shoul be actually like this
db=# select * from sing;
id | valr |
---|---|
897 | 1095 |
how can i avoid these type of duplicate values ? because my app will have high traffic may be 100 calls in second also i can't make "valr" column a primary key. because it contains other type of values.
my postgres version
db=# select version();
version
------------------------------------------------------------------------------------------------------------------
PostgreSQL 12.3 (Debian 12.3-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
Aucun commentaire:
Enregistrer un commentaire