Re: Getting fancy errors when accessing information_schema on 10.5

> Am 30.10.2018 um 13:17 schrieb Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
>
> Axel Rau <Axel(dot)Rau(at)Chaos1(dot)DE <mailto:Axel(dot)Rau(at)Chaos1(dot)DE>> writes:
>>> Am 30.10.2018 um 08:42 schrieb Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at <mailto:laurenz(dot)albe(at)cybertec(dot)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 != 'information_schema';
>> ...
>> -> Seq Scan on pg_class c (cost=0.00..28.56 rows=22 width=72)
>> Filter: ((pg_has_role(relowner, 'USAGE'::text) OR has_sequence_privilege(oid, 'SELECT, UPDATE, USAGE'::text)) AND (relkind = '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…
>
>> 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()?

Not by intention. The instance has some history, it go back to 8.x I think.
> You could
> investigate with, eg,
>
> select oid::regprocedure, procost from pg_proc
> where proname = 'has_sequence_privilege';

nextcloud=> select oid::regprocedure, procost from pg_proc
nextcloud-> where proname = 'has_sequence_privilege';
oid | procost
----------------------------------------+---------
has_sequence_privilege(name,text,text) | 1
has_sequence_privilege(name,oid,text) | 1
has_sequence_privilege(oid,text,text) | 1
has_sequence_privilege(oid,oid,text) | 1
has_sequence_privilege(text,text) | 1
has_sequence_privilege(oid,text) | 1
(6 rows)

Axel
---
PGP-Key:29E99DD6 ☀ computing @ chaos claudius