Re: Getting fancy errors when accessing information_schema on 10.5

> Am 30.10.2018 um 08:42 schrieb Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)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