CREATE TABLE t1 ( id serial int, col text ); insert into t1(col) values('old_value');
now, I need lock this table from data modifying, while below plsql block is running
DO $$ declare res1 TEXT; res2 TEXT; BEGIN --PERFORM pg_advisory_lock( ) select col from t1 where id = 1 into res1; FOR i in 1..2000000000 LOOP -- this is just for waiting several second END LOOP; select col from t1 where id = 1 into res2; RAISE NOTICE '% - %', res1, res2; --PERFORM pg_advisory_unlock( ) END; $$ LANGUAGE PLPGSQL
So while this block is running, I run other query:
update t1 SET col = 'new_value' where id = 1;
This query works immediately and updates row when running plsql block is not completed.
I need contrary, I need that update not worked and waited, while plsql block running.
I suppose that
pg_advisory_unlock() would help, but how to use it, I don't understood, what is key parameter for these functions, I don't get it.
And also not sure, if these functions will work at all.
any help would be appreciated.