From: Tom Lane <tgl@sss.pgh.pa.us>
To: =?UTF-8?Q?Ulf_Lohbr=C3=BCgge?= <ulf.lohbruegge@gmail.com>
cc: Scott Marlowe <scott.marlowe@gmail.com>,
        Andres Freund <andres@anarazel.de>,
        "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: [PERFORM] Slow execution of SET ROLE,
 SET search_path and RESET ROLE
In-reply-to: 
 <CABZYQRJtwNx4sNS7xwCrLDoVxSCmS5CyEOgkf62KKMmiZw4_LQ@mail.gmail.com>
References: 
 <CABZYQRJZp78vAJ2byse8AO75v-CWuj6qNdDmUtfMzRb+o7HX=g@mail.gmail.com>
 <6684.1512662473@sss.pgh.pa.us>
 <CABZYQRJtwNx4sNS7xwCrLDoVxSCmS5CyEOgkf62KKMmiZw4_LQ@mail.gmail.com>
Comments: In-reply-to =?UTF-8?Q?Ulf_Lohbr=C3=BCgge?=
 <ulf.lohbruegge@gmail.com>
	message dated "Thu, 07 Dec 2017 17:15:34 +0100"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <8328.1512664710.1@sss.pgh.pa.us>
Date: Thu, 07 Dec 2017 11:38:30 -0500
Message-ID: <8329.1512664710@sss.pgh.pa.us>
Precedence: bulk

=?UTF-8?Q?Ulf_Lohbr=C3=BCgge?= <ulf.lohbruegge@gmail.com> writes:
> 2017-12-07 17:01 GMT+01:00 Tom Lane <tgl@sss.pgh.pa.us>:
>> 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