From: Tom Lane <tgl@sss.pgh.pa.us>
To: Axel Rau <Axel.Rau@Chaos1.DE>
cc: Laurenz Albe <laurenz.albe@cybertec.at>, pgsql-admin@postgresql.org
Subject: Re: Getting fancy errors when accessing information_schema on 10.5
In-reply-to: <44D4C7B1-0DCF-4166-9DB8-B5D75EB2985E@Chaos1.DE>
References: <D8BA4170-2E96-42BE-8CEC-E9FCF75B0DD4@Chaos1.DE>
 <4880e067723cefc1dc2e08a117f96de26ae0f797.camel@cybertec.at>
 <44D4C7B1-0DCF-4166-9DB8-B5D75EB2985E@Chaos1.DE>
Comments: In-reply-to Axel Rau <Axel.Rau@Chaos1.DE>
	message dated "Tue, 30 Oct 2018 12:32:08 +0100"
MIME-Version: 1.0
Content-Type: text/plain; charset="UTF-8"
Content-ID: <20190.1540901834.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Tue, 30 Oct 2018 08:17:14 -0400
Message-ID: <20191.1540901834@sss.pgh.pa.us>
Precedence: bulk

Axel Rau <Axel.Rau@Chaos1.DE> writes:
>> Am 30.10.2018 um 08:42 schrieb Laurenz Albe <laurenz.albe@cybertec.at>:
>> Could you run EXPLAIN on the query and tell us the execution plan?

> EXPLAIN  SELECT sequence_name AS relname, sequence_schema AS schemaname
> FROM information_schema.sequences
> WHERE sequence_schema NOT LIKE 'pg\_%' AND sequence_schema !=3D 'informa=
tion_schema';
> ...
>                      ->  Seq Scan on pg_class c  (cost=3D0.00..28.56 row=
s=3D22 width=3D72)
>                            Filter: ((pg_has_role(relowner, 'USAGE'::text=
) OR has_sequence_privilege(oid, 'SELECT, UPDATE, USAGE'::text)) AND (relk=
ind =3D 'S'::"char"))

Well, there's the problem: for some reason the planner is deciding to
execute the privilege test before the relkind check.

>> Perhaps this is some fancy kind of catalog corruption=E2=80=A6

> Maybe, as this does not happen with another instance.

It doesn't happen for me either.  Looking at the planner code, it seems
like the relkind check should happen first because it'd be cheaper than
the OR condition.  Have you perhaps messed with the cost attributed to
pg_has_role(), has_sequence_privilege(), or chareq()?  You could
investigate with, eg,

select oid::regprocedure, procost from pg_proc
  where proname =3D 'has_sequence_privilege';

			regards, tom lane