Postgres create policy concurrently

Question

I have a postgres table already has row level security enabled, with policy for role1. I want to create another policy for role2. However, when I run

CREATE POLICY policy_name ON {table} AS PERMISSIVE FOR SELECT TO role2 USING (TRUE);

The CREATE POLICY command wants to add a access exclusive lock to the table. However, role1 has active select statement on table, so the CREATE POLICY have to wait for all select statement to finish.

Why the create policy needs access exclusive block? How to get around the lock to run concurrently?

Add to question text the policy for role1.

Adrian Klaver
– Adrian Klaver

2024-11-14 17:21:57 +00:00
Commented Nov 14, 2024 at 17:21 — Adrian Klaver
– Adrian Klaver, Commented Nov 14, 2024 at 17:21

Laurenz Albe · Accepted Answer · 2024-11-15 09:14:24Z

0

The ACCESS EXCLUSIVE lock has been there from the original commit on, and there is no trace about the lock level in the mailing list discussion. So we cannot answer why it was written like that.

However, it makes a lot of sense to me. Imagine a bulk update, and the rules for which rows can be updated change in mid-operation...

Really, the problem is in your application. If there is no time when there is not a long-running transaction, that database will give you trouble in many other ways, and you need to take down time for any sort of schema modification.

answered Nov 15, 2024 at 9:14

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres create policy concurrently

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related