PostgreSQL, partitioned index, concurrently, procedure, stored procedure, transaction

set lock_timeout=xx; - Avoid waiting for a long time for the lock to cause an avalanche.
loop xx..xxxx
create index [if not exists] [concurrently] ?; - just block dml in the process
commit or rollback; - Each time an index is created, the transaction ends and the lock related to this index is released.
end loop;
reset lock_timeout;
Example
Create several indexes on one table, and create indexes on several tables, the effect is the same, just change the content of the stored procedure.
create table tab2(uid int, info text);
insert into tab2 select generate_series(1,100), random()::text;
Create a function to execute SQL and support setting lock timeout:
create or replace function exec_sql(text,text) returns boolean as $$
declare
begin
execute format('set lock_timeout=%L', $1);
execute $2;
reset lock_timeout;
return true;
exception when others then
reset lock_timeout;
return false;
end;
$$ language plpgsql strict;
Create a stored procedure, create a bunch of indexes on tab2
create or replace procedure do_sqls() as $$
declare
begin
for i in 1..65 loop
if exec_sql('1s', format('create index IF NOT EXISTS idx_%s on tab2 (uid)', i)) then
raise notice'success index: %','idx_'||i;
commit; - After each partition index is created, the transaction ends and the lock is automatically released
else
rollback; - After each partition index is created, the transaction ends and the lock is automatically released
raise notice'not success, lock_timeout index: %','idx_'||i;
end if;
- perform pg_sleep(5); - Adding a sleep can lengthen the entire time, so that there is a time window to simulate the dml non-blocking situation.
end loop;
- reset lock_timeout;
end;
$$ language plpgsql;

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Pankaj kushwaha

Pankaj kushwaha

Database/System Administrator | DevOPS | Cloud Specialist | DevOPS