Amazon web services – Cannot drop a materialized view in postgres

The system views pg_stat_activity can tell you what’s everyone doing, and pg_locks who’s holding what. You can once again re-experience your discomfort by

1. In one session (A), issuing

   create schema my_neighbourhood;
   select set_config('search_path','my_neighbourhood',false);
   create group family_that_owns_neighbourhood superuser inherit;
   alter schema my_neighbourhood owner to family_that_owns_neighbourhood;
   
   create table good_values as select 'school' v1, 'hobbies' v2, 'sports' v3;
   create view daughter as select v1,v2,v3 from good_values;
   

2. Opening another session (B) in parallel and

   create user badboy420 
      in group family_that_owns_neighbourhood inherit superuser;
   start transaction;
   

3. You can probably guess what follows: within that B transaction, reference the view by

   set role badboy420;
   select set_config('search_path','my_neighbourhood',true);
   insert into daughter values ('drugs','alcohol','tiktok');
   

   first one’s enough to get her locked (up).

4. From session A, try to drop view daughter;, pretending this means you’re casually trying to drop daughter off to school – it’ll hang and you’ll have to wait while she’s still hanging out with that punk from session B. Luckily, until the situation’s resolved, it would now also hang if someone tried to alter view daughter rename to their_girlfriend;, so, there’s some silver lining.

The post-mortem you shared confirms that’s pretty much what was going down. If you’re very important, with little regard for whoever stands in your way, you can pg_cancel_backend()

select pg_cancel_backend(pid) from pg_stat_activity 
where pid<>pg_backend_pid() and query ilike 'daughter';

If on top of being that important and ruthless, you’re also in a big rush or Liam Neeson, you can go ahead and pg_terminate_backend() (you brute):

select pg_terminate_backend(pid) from pg_stat_activity 
where pid<>pg_backend_pid() and query ilike 'daughter';

The first option is like finding the guy who’s talking to your daughter and telling them to hang up. The second one is like finding them and ending the conversation by smacking them across the face hard enough that you break both their phone and potentially their jaw.

Note that in both cases above, you’re actually doing that to anyone doing anything about any daughter, not just yours. But one: you get the point, two: that would actually send a good message.

If you want to be surgical about this, you can refer to lock monitoring at PostgreSQL wiki. Also, pg_stat_activity can tell you exactly you who, since when, using what application, from what ip is trying to do what. With all that noted that down you easily track them down, can sue them into oblivion, get a restraining order, get their parole officer involved.

The default behaviour of putting you on hold and telling you to wait is actually a reasonable middle ground between strictness and good manners: it’s waiting for everyone to finish what they had started doing, while already blocking newcomers. A third session trying to access would wait for the first one to complete, then the second one would be allowed to finish the drop, then the third one would be told daughter doesn’t exist. Also, everyone involved can use all that wait time to cool off their temper.