Re: Getting fancy errors when accessing information_schema on 10.5

> Am 30.10.2018 um 13:17 schrieb Tom Lane <tgl@sss.pgh.pa.us>:
> 
> Axel Rau <Axel.Rau@Chaos1.DE <mailto:Axel.Rau@Chaos1.DE>> writes:
>>> Am 30.10.2018 um 08:42 schrieb Laurenz Albe <laurenz.albe@cybertec.at <mailto: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 != '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