Re: Getting fancy errors when accessing information_schema on 10.5

> Am 30.10.2018 um 08:42 schrieb Laurenz Albe <laurenz.albe@cybertec.at>:
> 
> Axel Rau wrote:
>> here is an example:
>> 
>> SELECT	sequence_name AS relname, sequence_schema AS schemaname
>> 	FROM information_schema.sequences
>> 	WHERE sequence_schema NOT LIKE 'pg\_%' AND sequence_schema != ‚information_schema'': SQLSTATE[42809]: Wrong object type: 7 ERROR: "pg_statistic" is not a sequence
>> 
>> This does not happen as SUPERUSER.
>> Is this a known bug?
> 
> The statement as you wrote it is syntactically incorrect.
> Did some program mutilate your quotes in transfer?
Yes. Sorry for that.
> 
> Anyway, the error message does not make much sense.
> 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';
                                                                                                            QUERY PLAN                                                                                                           
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop Anti Join  (cost=28.78..44.52 rows=1 width=64)
   ->  Nested Loop  (cost=28.78..31.03 rows=1 width=132)
         ->  Hash Join  (cost=28.78..30.18 rows=1 width=72)
               Hash Cond: (s.seqrelid = c.oid)
               ->  Seq Scan on pg_sequence s  (cost=0.00..1.40 rows=40 width=4)
               ->  Hash  (cost=28.56..28.56 rows=22 width=72)
                     ->  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"))
         ->  Index Scan using pg_namespace_oid_index on pg_namespace nc  (cost=0.00..0.57 rows=1 width=68)
               Index Cond: (oid = c.relnamespace)
               Filter: ((NOT pg_is_other_temp_schema(oid)) AND (((nspname)::information_schema.sql_identifier)::text !~~ 'pg\_%'::text) AND (((nspname)::information_schema.sql_identifier)::text <> 'information_schema'::text))
   ->  Index Scan using pg_depend_depender_index on pg_depend  (cost=0.00..6.75 rows=1 width=4)
         Index Cond: ((classid = '1259'::oid) AND (objid = c.oid))
         Filter: (deptype = 'i'::"char")
(14 rows)

> 
> Perhaps this is some fancy kind of catalog corruption…

Maybe, as this does not happen with another instance.

Thanks, Axel
PS: The origin of the query is here:
	https://github.com/nextcloud/3rdparty/blob/8633304ce214a7c0ff8e4fd72052a5ed7a444b6d/doctrine/dbal/lib/Doctrine/DBAL/Platforms/PostgreSqlPlatform.php <https://github.com/nextcloud/3rdparty/blob/8633304ce214a7c0ff8e4fd72052a5ed7a444b6d/doctrine/dbal/lib/Doctrine/DBAL/Platforms/PostgreSqlPlatform.php>
---
PGP-Key:29E99DD6  ☀  computing @ chaos claudius