0

I am using Postgres12 and DBeaver. I insert thousands of data using a procedure like this :

do $$
declare
    ctr_max_ID integer;
    ctr_loop Integer := 1;
begin 
    loop
    exit when ctr_loop = 5001; 
    ctr_loop := ctr_loop + 1;  
    select max (cast(id as Integer)) into ctr_max_ID from table1 ;
    insert into table1 (id, data) values (ctr_max_ID + 1, 'DUMMY data');
    raise notice 'ctr_loop: %', ctr_loop;
    end loop;
end; $$;

While that procedure running, I issued another select statement "Select * From table1 Where ID = '1';" This statement is never executed until the procedure finish running. I also run a statement for another table "Select * From table2 Limit 1". This is not executed until stored procedure finish running.

Is this normal ? Or is there some setting somewhere that specifies query has to run one at a time ?

thanks

5
  • The max() + 1 approach is a horrible way to generate a unique id. The main reason being, that this is not safe for concurrent use. A sequence is a much more efficient (and safe) way to do that. Btw: why are you storing numbers in varchar (or text) columns?
    – user1822
    Aug 1, 2022 at 5:20
  • But there is no reason why a select * from table1 should be blocked by this approach. I assume there is more going on, than you are showing us.
    – user1822
    Aug 1, 2022 at 5:23
  • Hi @a_horse_with_no_name, I know its horrible using max()+1 as unique ID. I use it because logical replication does not support sequence. And also, the ID is actually Text, but I "convert" it to integer for the sake of simplicity. But anyway, it is for testing only. I generate the records to monitor the growing of WAL files (1 WAL files every 130++K records). So, what seems to be blocking the second query when the procedure is still running ? Thanks
    – padjee
    Aug 1, 2022 at 6:12
  • 1
    Try with psql. I'm assuming this is a client specific limitation. (e.g. github.com/dbeaver/dbeaver/issues/2764 )
    – Melkij
    Aug 1, 2022 at 8:13
  • yes. thanks for pointing that out @Melkij
    – padjee
    Aug 3, 2022 at 5:34

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.