public inbox for [email protected]
help / color / mirror / Atom feedLocks under the hood on re-mat and dropping triggers
4+ messages / 3 participants
[nested] [flat]
* Locks under the hood on re-mat and dropping triggers
@ 2022-05-19 21:37 Wells Oliver <[email protected]>
2022-05-20 07:12 ` Re: Locks under the hood on re-mat and dropping triggers Laurenz Albe <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: Wells Oliver @ 2022-05-19 21:37 UTC (permalink / raw)
To: pgsql-admin
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 protected] <[email protected]>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Locks under the hood on re-mat and dropping triggers
2022-05-19 21:37 Locks under the hood on re-mat and dropping triggers Wells Oliver <[email protected]>
@ 2022-05-20 07:12 ` Laurenz Albe <[email protected]>
2022-05-20 19:35 ` Re: Locks under the hood on re-mat and dropping triggers Wells Oliver <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: Laurenz Albe @ 2022-05-20 07:12 UTC (permalink / raw)
To: Wells Oliver <[email protected]>; pgsql-admin
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Locks under the hood on re-mat and dropping triggers
2022-05-19 21:37 Locks under the hood on re-mat and dropping triggers Wells Oliver <[email protected]>
2022-05-20 07:12 ` Re: Locks under the hood on re-mat and dropping triggers Laurenz Albe <[email protected]>
@ 2022-05-20 19:35 ` Wells Oliver <[email protected]>
2022-05-20 20:41 ` Re: Locks under the hood on re-mat and dropping triggers Tom Lane <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: Wells Oliver @ 2022-05-20 19:35 UTC (permalink / raw)
To: Laurenz Albe <[email protected]>; +Cc: pgsql-admin
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.
On Fri, May 20, 2022 at 12:12 AM Laurenz Albe <[email protected]>
wrote:
> 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
>
--
Wells Oliver
[email protected] <[email protected]>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Locks under the hood on re-mat and dropping triggers
2022-05-19 21:37 Locks under the hood on re-mat and dropping triggers Wells Oliver <[email protected]>
2022-05-20 07:12 ` Re: Locks under the hood on re-mat and dropping triggers Laurenz Albe <[email protected]>
2022-05-20 19:35 ` Re: Locks under the hood on re-mat and dropping triggers Wells Oliver <[email protected]>
@ 2022-05-20 20:41 ` Tom Lane <[email protected]>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2022-05-20 20:41 UTC (permalink / raw)
To: Wells Oliver <[email protected]>; +Cc: Laurenz Albe <[email protected]>; pgsql-admin
Wells Oliver <[email protected]> writes:
> 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.
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2022-05-20 20:41 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-05-19 21:37 Locks under the hood on re-mat and dropping triggers Wells Oliver <[email protected]>
2022-05-20 07:12 ` Laurenz Albe <[email protected]>
2022-05-20 19:35 ` Wells Oliver <[email protected]>
2022-05-20 20:41 ` Tom Lane <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox