jeudi 17 décembre 2020

issue regarding postgres function test run in jmeter

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.

now my jmeter config connection config

userthread

sampler

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