java - How to rectify the conflicts which occur at concurrent transactions in PostgreSQL -

i have function checks maximum value of pid , inserts database.

but raises conflict while have concurrent transactions!

so how can avoid conflict!

create table tablea(   sno serial,   pid integer,   ppid integer,   pname text   ); 

--this function checks maximum value of pid , inserts database.

create or replace function insert_details(_pname text) returns void $body$ declare     _pid int; begin    _pid := (select max(pid) tablea);    _pid := coalesce(_pid, 0) + 1;     insert tablea(pid,pname)    values(_pid,_pname); end; $body$ language plpgsql volatile cost 100;  --(sub party)this function checks maximum value of ppid , inserts database.(sub-party)  create or replace function insert_details(_pid int,_pname text) returns void $body$ declare         _ppid int; begin    _ppid := (select max(ppid) tablea pid=_pid);    _ppid := coalesce(_ppid, 0) + 1;     insert tablea(pid,ppid,pname)    values (_pid,_ppid,_pname); end; $body$ language plpgsql volatile cost 100; 

my requirement : when hit submit button front end field(pname) should inserted tablea,by incrementing pid +1, cant remove pid tablea,and pid kept integer can insert record same pid & flow same ppid too..

so should change table structure or else other way rectify conflicts in concurrent transactions.

this how whole concept sql fiddle demo

on way avoid conflicts permanently , drop redundant pid table. use sno instead purposes. , disallow direct input column, default attached sequence cannot circumvented. sno can have gaps, that's design. works flawlessly unique column under concurrent load. , later requests (starting later) higher serial number. that's serial column for.

if need gapless "pid", can emulate 1 row_number():

select *, row_number() on (order sno) pid tablea 

now, synthesized pid not stable if rows can deleted. what's point of exercise if there can gaps in numbering after all?