Re: [PERFORM] Slow execution of SET ROLE, SET search_path and RESET ROLE

От: Tom Lane
Тема: Re: [PERFORM] Slow execution of SET ROLE, SET search_path and RESET ROLE
Дата: ,
Msg-id: 8329.1512664710@sss.pgh.pa.us
(см: обсуждение, исходный текст)
Ответ на: Re: [PERFORM] Slow execution of SET ROLE, SET search_path and RESET ROLE  (Ulf Lohbrügge)
Список: pgsql-performance

Скрыть дерево обсуждения

[PERFORM] Slow execution of SET ROLE, SET search_path and RESET ROLE  (Ulf Lohbrügge, )
 Re: [PERFORM] Slow execution of SET ROLE, SET search_path and RESET ROLE  (Tom Lane, )
  Re: [PERFORM] Slow execution of SET ROLE, SET search_path and RESET ROLE  (Ulf Lohbrügge, )
 Re: [PERFORM] Slow execution of SET ROLE, SET search_path and RESETROLE  (Andres Freund, )
  Re: [PERFORM] Slow execution of SET ROLE, SET search_path and RESET ROLE  (Ulf Lohbrügge, )
   Re: [PERFORM] Slow execution of SET ROLE, SET search_path and RESET ROLE  (Scott Marlowe, )
    Re: [PERFORM] Slow execution of SET ROLE, SET search_path and RESET ROLE  (Ulf Lohbrügge, )
 Re: [PERFORM] Slow execution of SET ROLE, SET search_path and RESETROLE  (Andres Freund, )
  Re: [PERFORM] Slow execution of SET ROLE, SET search_path and RESET ROLE  (Ulf Lohbrügge, )
 Re: [PERFORM] Slow execution of SET ROLE, SET search_path and RESET ROLE  (Ulf Lohbrügge, )
  Re: [PERFORM] Slow execution of SET ROLE, SET search_path and RESET ROLE  (Tom Lane, )
   Re: [PERFORM] Slow execution of SET ROLE, SET search_path and RESET ROLE  (Ulf Lohbrügge, )
    Re: [PERFORM] Slow execution of SET ROLE, SET search_path and RESET ROLE  (Tom Lane, )

=?UTF-8?Q?Ulf_Lohbr=C3=BCgge?= <> writes:
> 2017-12-07 17:01 GMT+01:00 Tom Lane <>:
>> It looks like the first time such a question is asked within a session,
>> we build and cache a list of all the roles the session user is a member
>> of (directly or indirectly).  That's what's taking the time here ---
>> apparently in your test case, the "admin" role is a member of a whole lot
>> of roles?

> Yes, the user "admin" is member of more than 1k roles.

> So this cache will not invalidate during the lifetime of the session unless
> a new role is added, I guess?

It looks like any update to the role membership catalog (pg_auth_members)
invalidates that cache.  So basically a "GRANT role" or "REVOKE role"
would do it.

> Is there any locking involved when this cache gets invalidated? Could this
> be a source for my earlier observed slow executions?

This particular aspect of things doesn't seem like such a problem to me,
but it's certainly possible that there are other aspects that get
unreasonably slow when there are that many role memberships involved.
Don't see what it'd have to do with SET SEARCH_PATH, though.  Or RESET
ROLE; that doesn't require any permission checks, either.

            regards, tom lane



В списке pgsql-performance по дате сообщения:

От: Claudio Freire
Дата:
Сообщение: Re: pg_dump 3 times as slow after 8.4 -> 9.5 upgrade
От: Flávio Henrique
Дата:
Сообщение: Learning EXPLAIN