postgresql - Does Setting search_path Block In Postgres?

Question

I'm trying to debug high contention of LWLock:lock_manager in Amazon Aurora Postgres 9.6. It looks like this:

This happens during when I try to setup a new schema for a new user. I lock a central table (SELECT 1 FROM main_schema.schemas WHERE where Id = userId FOR UPDATE). This should lock only that row. I do this new setup which can take 20-40 seconds (mostly in a big transaction, adds 3 dozen tables and dozens of indexes/fks). However, for that 20-40 seconds most traffic to Aurora starts hanging on lock_manager when other queries try to call SET LOCAL search_path in their own transactions to their own schemas, shown below:

This is the search_path I set on other users during other traffic:

SET LOCAL search_path TO sub_schema_{x}, extensions;

As a result, there are a few thousands roles.

Note: Amazon/Postgres explains lock_manager as code that just observes or waits to examine backend for locks. It is not a specific read or write lock it seems.

Dozens of these queries seem to hang. My questions:

1. Does setting LOCAL search_path somehow acquires a lock on something?
2. What other locks can cause this sort of contention?

question from:https://stackoverflow.com/questions/66051639/does-setting-search-path-block-in-postgres

Answer 1

Waitting for answers