Axel Rau <Axel.Rau@xxxxxxxxx> writes:Am 30.10.2018 um 08:42 schrieb Laurenz Albe <laurenz.albe@xxxxxxxxxxx>:
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 toexecute 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 seemslike the relkind check should happen first because it'd be cheaper thanthe OR condition. Have you perhaps messed with the cost attributed topg_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
|
