Subject: Locks under the hood on re-mat and dropping triggers

From: Wells Oliver <[email-masked]>
Date: Thu, 19 May 2022 14:37:47 -0700

Dropping triggers from some table yields a lock while a concurrent refresh
of a materialized view in another schema entirely is running-- why is this?

-- 
Wells Oliver
[email-masked] <[email-masked]>

---

From: Laurenz Albe <[email-masked]>
Date: Fri, 20 May 2022 09:12:12 +0200

On Thu, 2022-05-19 at 14:37 -0700, Wells Oliver wrote:
> Dropping triggers from some table yields a lock while a concurrent refresh of a materialized
> view in another schema entirely is running-- why is this?

That is because dropping a trigger requires a (brief) ACCESS EXCLUSIVE lock on the table,
which conflicts with all concurrent access to the table.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

---

From: Wells Oliver <[email-masked]>
Date: Fri, 20 May 2022 12:35:47 -0700

but what I am seeing is this lock even when the trigger and table are not
at all used by an entirely separate mat-view being re-materialized.

---

From: Tom Lane <[email-masked]>
Date: Fri, 20 May 2022 16:41:14 -0400

> at all used by an entirely separate mat-view being re-materialized.

You'd need to show a test case.  Personally I'm wondering if there's a
foreign key constraint connecting the troublesome table to some table
used by the matview.

			regards, tom lane