CODEWITHSUNDEEP
postgresqlpostgresql-12
Mano
Mano

Reputation: 711

Postgres Tuple concurrently updated during dynamic Revoke

I am trying to revoke all the dependencies(several hundreds) of a set of Postgres roles before dropping the role.

I was executing the below script.

do
$$
declare
rec record;
begin
for rec in 
select distinct u.table_schema, u.table_name, r.rolname
from pg_roles r join information_schema.role_table_grants u
on r.rolname = u.grantee
where (r.rolname ilike '%mentor%')
LOOP
execute concat('REVOKE ALL ON ', rec.table_schema, '.', rec.table_name, ' FROM ', rec.rolname);
END LOOP;
end;
$$;

It worked just fine for one set of roles, but when I tried for another set of roles(that has 'mentor' in its name) it was throwing the below error.

ERROR: tuple concurrently updated
CONTEXT: SQL statement "REVOKE ALL ON schema_main.tab_gtl_ltrs from mentor_1"
PL/plSQL function inline_code_block line 106 at EXECUTE

Can someone help on how to resolve this? I'm aware that this type of error for DML statements can be resolved by locking the rows by using 'FOR' statement. But I am not able to find the solution for this case.

Upvotes: 0

Views: 827

Answers (1)

Mano
Mano

Reputation: 711

The best possible workaround was to use REASSIGN OWNED BY and DROP OWNED by to resolve this issue.

REASSIGN OWNED BY mentor_1 TO postgres;
DROP OWNED BY mentor_1;
DROP ROLE mentor_1;

Upvotes: 0

Related Questions