agora inbox for pgsql-performance@postgresql.org
help / color / mirror / Atom feedFrom: Tom Lane <tgl@sss.pgh.pa.us>
To: Ulf Lohbrügge <ulf.lohbruegge@gmail.com>
Cc: Scott Marlowe <scott.marlowe@gmail.com>
Cc: Andres Freund <andres@anarazel.de>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: [PERFORM] Slow execution of SET ROLE, SET search_path and RESET ROLE
Date: Thu, 07 Dec 2017 11:38:30 -0500
Message-ID: <8329.1512664710@sss.pgh.pa.us> (raw)
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>
=?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
view thread (13+ messages)
Message-ID: <8329.1512664710@sss.pgh.pa.us>
Permalink: ../8329.1512664710@sss.pgh.pa.us/
Also on: postgresql.org/message-id/8329.1512664710@sss.pgh.pa.us
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, ulf.lohbruegge@gmail.com, scott.marlowe@gmail.com, andres@anarazel.de
Subject: Re: [PERFORM] Slow execution of SET ROLE, SET search_path and RESET ROLE
In-Reply-To: <8329.1512664710@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox